transaction deadlock when using "autocommit"

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
theclay7
Forum Commoner
Posts: 50
Joined: Wed Feb 19, 2003 3:17 am

transaction deadlock when using "autocommit"

Post by theclay7 »

In PHP:

Transaction is a big problem in MySQL --> here is what I have done
Since the DB is InnoDB, I tried to "set autocommit=0" so that it can be rollback if it wants, and data will not be actually updated into the file disk unless a "commit" is performed.

I typed:

$autocommit = "SET autocommit=0";
$setcommit = mysql_query($autocommit);
echo "<font color = yellow> Setcommit = $setcommit </font><BR>";

and then it worked once, but then for the second time, when I go to mySQL and try to do something, it came an error of :

ERROR 1205: Lock wait timeout exceeded; Try restarting transaction

which is probably the deadlock problem...I have to release the lock by
i) show processlist; (and see which thread is sleeping)
ii) kill thread_id;

Does anyone know how to perform transaction with php using InnoDB?
Post Reply