使用 LOCK TABLES 回滚事务

Question

我有一个 PHP/5.2 驱动的应用程序，它使用 MySQL/5.1 下的事务，因此如果满足错误条件，它可以回滚多个插入。我有不同的可重用函数来插入不同类型的项目。到目前为止，一切都很好。

现在我需要对某些插入使用表锁定。正如官方手册所建议的，我使用 SET autocommit=0 而不是 START TRANSACTION，因此 LOCK TABLES 不会发出隐式提交。并且，如文档所述，解锁表会隐式提交任何活动事务：

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

这就是问题所在：如果我只是避免解锁表，碰巧第二次调用 LOCK TABLES 提交了挂起的更改！

看来唯一的方法是在单个语句中执行所有必要的LOCK TABLES。这真是一场维护噩梦。

这个问题有明智的解决方法吗？

这是一个小测试脚本：

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

显然，LOCK TABLES 无法修复以很好地处理事务。解决方法是将其替换为 SELECT .... FOR更新。您不需要任何特殊语法（您可以使用常规 START TRANSACTION），它会按预期工作：

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

请注意，COUNT(*) 只是一个示例，您可以通常使用 SELECT 语句来获取您实际需要的数据;-)

（此信息由 Frank Heikens 提供。）