Re: lock and transaction

[Jay Pipes]

felix wrote:
>
> Don't use (UN)LOCK TABLES with transactional
> tables.
> See
> http://dev.mysql.com/doc/mysql/en/lock-tables.html
>
>

This is incorrect. Gianpaolo, the reason you are seeing the behaviour you are seeing is because the LOCK TABLES and UNLOCK TABLES commands issue an implicit COMMIT under AUTOCOMMIT mode. To remedy the situation, you should use SET @@AUTOCOMMIT=0; before starting your transaction:

SET @@AUTOCOMMIT=0;
START TRANSACTION;
lock table customers WRITE;
UPDATE `customers` SET `Customer Name`='MODIFYcustomer' WHERE `idcustomer`=1;
unlock table;
ROLLBACK;

You can read more about autocommit mode here:

http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html

and the implicit COMMIT statements here:

http://dev.mysql.com/doc/mysql/en/innodb-implicit-command-or-rollback.html

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com