Rollback transactions with LOCK TABLES

Question

I have a PHP/5.2 driven application that uses transactions under MySQL/5.1 so it can rollback multiple inserts if an error condition is met. I have different reusable functions to insert different type of items. So far so good.

Now I need to use table locking for some of the inserts. As the official manual suggests, I'm using SET autocommit=0 instead of START TRANSACTION so LOCK TABLES does not issue an implicit commit. And, as documented, unlocking tables implicitly commits any active transaction:

• http://dev.mysql.com/doc/refman/5.1/en/lock-tables-and-transactions.html

And here lies the problem: if I simply avoid UNLOCK TABLES, it happens that the second call to LOCK TABLES commits pending changes!

It appears that the only way is to perform all necessary LOCK TABLES in a single statement. That's a mainteinance nightmare.

Does this issue have a sensible workaround?

Here's a little test script:

DROP TABLE IF EXISTS test;

CREATE TABLE test (
    test_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    random_number INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (test_id)
)
COLLATE='utf8_spanish_ci'
ENGINE=InnoDB;


-- No table locking: everything's fine
START TRANSACTION;
INSERT INTO test (random_number) VALUES (ROUND(10000* RAND()));
SELECT * FROM TEST ORDER BY test_id;
ROLLBACK;
SELECT * FROM TEST ORDER BY test_id;



-- Table locking: everything's fine if I avoid START TRANSACTION
SET autocommit=0;
INSERT INTO test (random_number) VALUES (ROUND(10000* RAND()));
SELECT * FROM TEST ORDER BY test_id;
ROLLBACK;
SELECT * FROM TEST ORDER BY test_id;
SET autocommit=1;



-- Table locking: I cannot nest LOCK/UNLOCK blocks
SET autocommit=0;
LOCK TABLES test WRITE;
INSERT INTO test (random_number) VALUES (ROUND(10000* RAND()));
SELECT * FROM TEST ORDER BY test_id;
ROLLBACK;
UNLOCK TABLES; -- Implicit commit
SELECT * FROM TEST ORDER BY test_id;
SET autocommit=1;


-- Table locking: I cannot chain LOCK calls ether
SET autocommit=0;
LOCK TABLES test WRITE;
INSERT INTO test (random_number) VALUES (ROUND(10000* RAND()));
SELECT * FROM TEST ORDER BY test_id;
-- UNLOCK TABLES;
LOCK TABLES test WRITE; -- Implicit commit
INSERT INTO test (random_number) VALUES (ROUND(10000* RAND()));
SELECT * FROM TEST ORDER BY test_id;
-- UNLOCK TABLES;
ROLLBACK;
SELECT * FROM TEST ORDER BY test_id;
SET autocommit=1;

Answer 1

Apparently, LOCK TABLES cannot be fixed to play well with transactions. A workaround is to replace it with SELECT .... FOR UPDATE. You don't need any special syntax (you can use regular START TRANSACTION) and it works as expected:

START TRANSACTION;
SELECT COUNT(*) FROM foo FOR UPDATE; -- Lock issued
INSERT INTO foo (foo_name) VALUES ('John');
SELECT COUNT(*) FROM bar FOR UPDATE; -- Lock issued, no side effects
ROLLBACK; -- Rollback works as expected

Please note that COUNT(*) is just an example, you can normally use the SELECT statement to fetch data you actually need ;-)

(This information was provided by Frank Heikens.)