使用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

问题在于：如果我简单地避免使用UNLOCK TABLES，那么第二次调用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;

- Álvaro González

---

为什么需要锁定？真正的问题是什么？ - Frank Heikens

我需要锁定以确保只有一个进程能够使用当前年份的序列号，并且序列中不留下任何间隙。真正的问题在于，当您尝试使用不支持事务的功能（如表锁定）时，MySQL会自动提交未经验证的数据集，从而打破了使用事务的整个目的。 - Álvaro González

你不能使用SELECT .... FOR UPDATE;吗？在事务中可以很好地工作，没有任何问题。为序列使用单个记录，并每次更新此记录即可。 - Frank Heikens

@Frank Heikens：我进行了一些快速测试，SELECT ... FOR UPDATE是事务安全的。我会分析是否可以用它替换我的逻辑（MySQL关于它的文档几乎不能更混乱）。您应该将您的评论扩展成一个合适的答案。声明LOCK TABLES无法与事务很好地配合，并提供解决方法非常有用的信息。 - Álvaro González

@Frank Heikens - 我已经创建了一个实际的答案，这样问题就可以被标记为已回答。 - Álvaro González

Answer 1

显然，LOCK TABLES 无法与事务很好地协作。一种解决方法是将其替换为SELECT .... FOR UPDATE。您不需要任何特殊语法（可以使用常规的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 提供。）

- Álvaro González

---

请注意，SELECT ... FOR UPDATE 仍然允许其他数据库会话从表中读取数据。我希望这可以帮助某些人在依赖此功能之前注意到这一点。 - Lars Gyrup Brink Nielsen