From: Michael Dykman
Date: November 5 2009 5:35am
Subject: Re: Help with InnoDB and locked transactions
List-Archive: http://lists.mysql.com/mysql/219255
Message-Id: <814b9a820911042135x6384897fh8e732c22f7698227@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 <daevid@stripped> 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 =3D=3D '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-transactions.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@stripped

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