Help with InnoDB and locked transactions

I am new to transactions, but they're causing me a lot of grief right now...

Basically I do this:

sql_query('agis_core_master', 'SET autocommit=0');
sql_query('agis_core_master', 'START TRANSACTION');

$query_result .= sql_query('agis_core_master', "UPDATE registration
SET registration_name = ? WHERE id_aircraft = ? LIMIT 1",
array($data['registration_name'], $data['id']));

$query_result .= sql_query('agis_core_master', "UPDATE sdu_tray SET
sdu_tray_id = ? WHERE id_aircraft = ? LIMIT 1", array($data['sdu_tray_id'],
$data['id']));

if ($query_result == '11')
sql_query('agis_core_master', 'COMMIT');
else
sql_query('agis_core_master', 'ROLLBACK');

//weird that I don't need to do this, since I had to set it manually
up there!?
//sql_query('agis_core_master', 'SET autocommit=1');

But it seems that sometimes these UPDATEs take a long time (longer than they
should) and so they just accumulate locks in mysql and I can't figure out
how to clear them all. If I refresh my page, it exacerbates the situation
with now even more locks.

I'm confused especially because the documentation is not only sparce in
examples, but also seems to contradict itself.

http://dev.mysql.com/doc/refman/5.0/en/commit.html

SET autocommit=0;
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

"Beginning a transaction causes any pending transaction to be committed. ...
Beginning a transaction also causes table locks acquired with LOCK TABLES to
be released, as though you had executed UNLOCK TABLES. "

I don't understand why I have to manually 'SET autocommit=0' and then it's
even more confusing why I don't have to 'SET autocommit=1' (or do I? -- I've
tried it both ways and it doesn't seem to help)

Furthermore this page gives a completely different example/usage:
http://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-trans actions.html

"The correct way to use LOCK TABLES and UNLOCK TABLES with transactional
tables, such as InnoDB tables, is to begin a transaction with SET autocommit
= 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK
TABLES until you commit the transaction explicitly. For example, if you need
to write to table t1 and read from table t2, you can do this:"

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

So WTF? How am I supposed to really do this transaction stuff, and HOW do I
force the clearing of locks or stuck queries or whatever should something
S.T.B.?

I've logged in as root and typed all sorts of commands to no avail:

SET autocommit=1;
START TRANSACTION;
UNLOCK TABLES;
COMMIT;
ROLLBACK;
FLUSH TABLES WITH READ LOCK;
FLUSH TABLES WITH WRITE LOCK;

But even if there is some magic incantation that root can use to "fix" this,
it seems pretty fragile that ANY QUERY I can make should cause my RDBMS to
be hosed like this. Why isn't InnoDB/MySQL more resilient and bounce back,
clearing locks, etc.

"/etc/init.d/mysql restart" will "fix" the problem, but we have nearly a
billion rows, so bringing the database down and up is painfully slow as it
does it's auto-checking and "fixing" of rows and whatever other magic it
does behind the scenes. Not an option.

Nor do we want/need to be sitting there restarting servers or issuing CLI
commands to fix the database should it be hosed by some failed transaction.
Isn't that the point of transactions?

We're running Ubuntu LTS 8.04 and mysql 5.0.51a (with all patches, etc.)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Help with InnoDB and locked transactions

Ok, take a deep breath.

Your basic strategy looks sound.. you generally don't want to lock
tables unless you are doing something specific that demands it.

start, modify, commit should cover all your normal success cases just fine.

A few boiler-plate questions
your id_aircraft, it is a unique index? perhaps a primary key?
how many rows do you have in that table?
what is it's table type
are there any other processes which might also be accessing that data?
how is the data directory mounted on your system (what os btw?)
you are using PHP through a wrapper library of some kind? as you
don't have access to the raw connection resource, I can't help but
wonder if your client interface isn't playing with your hand

the reason you put 'auto-commit=3D0;' is because many client apis
explicily create connections as auto-commit=3D1, which makes
transactions impossible. you only need to do it once at the beginning
of the connection.

to get a view of what is going on while your lock is pending, try
logging into the console as root and type

show full processlist;

this shold show you your locked process as well as any other processes
it might be conflicting with.

- michael dykman
On Wed, Nov 4, 2009 at 10:35 PM, Daevid Vincent wrote:
> I am new to transactions, but they're causing me a lot of grief right now=
....
>
> Basically I do this:
>
> =A0 =A0 =A0 =A0sql_query('agis_core_master', 'SET autocommit=3D0');
> =A0 =A0 =A0 =A0sql_query('agis_core_master', 'START TRANSACTION');
>
> =A0 =A0 =A0 =A0$query_result .=3D sql_query('agis_core_master', "UPDATE r=
egistration
> SET registration_name =3D ? WHERE id_aircraft =3D ? LIMIT 1",
> array($data['registration_name'], $data['id']));
>
> =A0 =A0 =A0 =A0$query_result .=3D sql_query('agis_core_master', "UPDATE s=
du_tray SET
> sdu_tray_id =3D ? WHERE id_aircraft =3D ? LIMIT 1", array($data['sdu_tray=
_id'],
> $data['id']));
>
> =A0 =A0 =A0 =A0if ($query_result == '11')
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0sql_query('agis_core_master', 'COMMIT');
> =A0 =A0 =A0 =A0else
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0sql_query('agis_core_master', 'ROLLBACK');
>
> =A0 =A0 =A0 =A0//weird that I don't need to do this, since I had to set i=
t manually
> up there!?
> =A0 =A0 =A0 =A0//sql_query('agis_core_master', 'SET autocommit=3D1');
>
> But it seems that sometimes these UPDATEs take a long time (longer than t=
hey
> should) and so they just accumulate locks in mysql and I can't figure out
> how to clear them all. If I refresh my page, it exacerbates the situation
> with now even more locks.
>
> I'm confused especially because the documentation is not only sparce in
> examples, but also seems to contradict itself.
>
> http://dev.mysql.com/doc/refman/5.0/en/commit.html
>
> =A0 =A0 =A0 =A0SET autocommit=3D0;
> =A0 =A0 =A0 =A0START TRANSACTION;
> =A0 =A0 =A0 =A0SELECT @A:=3DSUM(salary) FROM table1 WHERE type=3D1;
> =A0 =A0 =A0 =A0UPDATE table2 SET summary=3D@A WHERE type=3D1;
> =A0 =A0 =A0 =A0COMMIT;
>
> "Beginning a transaction causes any pending transaction to be committed. =
....
> Beginning a transaction also causes table locks acquired with LOCK TABLES=
to
> be released, as though you had executed UNLOCK TABLES. "
>
> I don't understand why I have to manually 'SET autocommit=3D0' and then i=
t's
> even more confusing why I don't have to 'SET autocommit=3D1' (or do I? --=
I've
> tried it both ways and it doesn't seem to help)
>
> Furthermore this page gives a completely different example/usage:
> http://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-trans actions.html
>
> "The correct way to use LOCK TABLES and UNLOCK TABLES with transactional
> tables, such as InnoDB tables, is to begin a transaction with SET autocom=
mit
> =3D 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UN=
LOCK
> TABLES until you commit the transaction explicitly. For example, if you n=
eed
> to write to table t1 and read from table t2, you can do this:"
>
> =A0 =A0 =A0 =A0SET autocommit=3D0;
> =A0 =A0 =A0 =A0LOCK TABLES t1 WRITE, t2 READ, ...;
> =A0 =A0 =A0 =A0... do something with tables t1 and t2 here ...
> =A0 =A0 =A0 =A0COMMIT;
> =A0 =A0 =A0 =A0UNLOCK TABLES;
>
> So WTF? How am I supposed to really do this transaction stuff, and HOW do=
I
> force the clearing of locks or stuck queries or whatever should something
> S.T.B.?
>
> I've logged in as root and typed all sorts of commands to no avail:
>
> SET autocommit=3D1;
> START TRANSACTION;
> UNLOCK TABLES;
> COMMIT;
> ROLLBACK;
> FLUSH TABLES WITH READ LOCK;
> FLUSH TABLES WITH WRITE LOCK;
>
> But even if there is some magic incantation that root can use to "fix" th=
is,
> it seems pretty fragile that ANY QUERY I can make should cause my RDBMS t=
o
> be hosed like this. Why isn't InnoDB/MySQL more resilient and bounce back=
,
> clearing locks, etc.
>
> "/etc/init.d/mysql restart" will "fix" the problem, but we have nearly a
> billion rows, so bringing the database down and up is painfully slow as i=
t
> does it's auto-checking and "fixing" of rows and whatever other magic it
> does behind the scenes. Not an option.
>
> Nor do we want/need to be sitting there restarting servers or issuing CLI
> commands to fix the database should it be hosed by some failed transactio=
n.
> Isn't that the point of transactions?
>
> We're running Ubuntu LTS 8.04 and mysql 5.0.51a (with all patches, etc.)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Help with InnoDB and locked transactions

Daevid Vincent daevid.com> writes:


> But it seems that sometimes these UPDATEs take a long time (longer
> than they should) and so they just accumulate locks in mysql and I
> can't figure out how to clear them all. If I refresh my page, it
> exacerbates the situation with now even more locks.

Next time when you see this issue, login to your mysql server and from
the mysql prompt, do "show innodb status\G". It will show the details
of statements which are locked or are in deadlock. If you want faster
timeouts for your queries, you can set the innodb_lock_wait_timeout to
a lower value than the default of 50 secs




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org