Geek Answers Handbook

Rollback transactions with LOCK TABLES

I have a PHP / 5.2-driven application that uses transactions in MySQL / 5.1 so that it can roll back multiple inserts if the error condition is met. I have different reuse functions to insert various types of elements. So far so good.

Now I need to use table locking for some inserts. As the official guide suggests, I use SET autocommit=0instead START TRANSACTION, so it LOCK TABLESdoesn’t produce implicit commits. And, as documented, unlocking tables implicitly commits any active transaction:

And here's the problem: if I just avoid it UNLOCK TABLES, it happens that the second call LOCK TABLESmakes pending changes!

It seems that the only way is to fulfill all the necessary LOCK TABLESin one expression. This is a major nightmare.

Does this problem have a reasonable solution?

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 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 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;
+5
source share
1 answer

Apparently, LOCK TABLESit cannot be fixed to play well with transactions. The workaround is to replace it with SELECT .... FOR UPDATE . You do not need special syntax (you can use the usual one 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

Note that COUNT(*)this is just an example, usually you can use the SELECT statement to get the data that you really need; -)

( ​​ .)

+4

More articles:


All Articles