From: Daevid Vincent
Date: November 5 2009 3:35am
Subject: Help with InnoDB and locked transactions
List-Archive: http://lists.mysql.com/mysql/219253
Message-Id: <63D2482C81994ABEB3F17CB6E0A8F958@mascorp.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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-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 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.)