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;

I need locking to ensure that only one process is able to use a sequence number for current year and no gaps are left in the sequence. The real problem is that MySQL commits non-validated data sets automatically when you try to use a feature that's not transaction aware such as table locking thus beating the whole point of using transactions. — Álvaro González
Can't you use SELECT .... FOR UPDATE; ? Works fine within transactions, no problem at all. Use a single record for the sequence and update this record each time. — Frank Heikens
@Frank Heikens: I've done some quick testing and SELECT ... FOR UPDATE is transaction safe. I'll analyse whether I can replace my logic with it (MySQL documentation about it could hardly be more confusing). You should expand your comment into a proper answer. Stating that LOCK TABLES cannot be fixed to play well with transactions and providing a workaround is very useful info. — Álvaro González
@Frank Heikens - I've created an actual answer so the question can be tagged as answered. — Álvaro González

Álvaro González Álvaro González · Accepted Answer · 2011-02-09T10:23:35

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

Rollback transactions with LOCK TABLES

1 Answers