MySQL：使用自动提交的LOCK TABLES与START TRANSACTION有什么区别？

Question

在MySQL文档中有一条我不理解的语句:

正确使用LOCK TABLES和UNLOCK TABLES与事务表（如InnoDB表）的方法是使用SET autocommit = 0（而不是START TRANSACTION）开始事务，然后锁定表，并且直到显式提交事务之前不调用UNLOCK TABLES。(https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html)

即使在文档中进行了大量搜索并研究“autocommit”和“START TRANSACTION”的描述，我仍然不理解为什么要使用autocommit而不是START TRANSACTION。 有任何想法吗？谢谢

- Anumi

Answer 1

LOCK TABLES隐式地提交一个事务，具体可以参考https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

因此，如果您按照以下顺序进行操作，它将不会达到您的意图：

BEGIN;
/* I think I'm in transaction 1 */
LOCK TABLES ...;
/* but that implicitly committed transaction 1 */

UPDATE blab blah ...;
/* I think I'm still in transaction 1, but this update caused 
   a new transaction 2 to begin and autocommit */ 
UPDATE yadda yadda ...;
/* I think I'm still in transaction 1, but this update caused 
   a new transaction 3 to begin and autocommit */ 
COMMIT;

使用事务的目的是当您需要运行多个更新时，但希望所有更新的结果要么一起提交，要么根本不提交。

上面的示例未将两个更新原子地一起提交。它们各自拥有自己的自动提交事务。因此，一个可能成功，但另一个可能失败。

建议按照以下步骤操作：

SET autocommit=0;
LOCK TABLES ...;
/* that implicitly committed any outstanding transaction, but that's OK */

UPDATE blab blah ...;
/* because autocommit=0, a new DML statement implicitly starts a new transaction */ 
UPDATE yadda yadda ...;
/* the new transaction is still open */
COMMIT;

UNLOCK TABLES;

这个提交同时原子性地更新了两个。

为什么不只是使用LOCK TABLES然后BEGIN来启动一个事务呢？

https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html说：

开始一个事务（例如，使用START TRANSACTION）隐式地提交了任何当前的事务并释放了现有的表锁。

这对我来说似乎很奇怪。显式的BEGIN（或START TRANSACTION）会释放表锁，但是隐式启动事务却不会？在我看来，这个特性充满了WTF。但这就是文档记录的。

- Bill Karwin

---

谢谢Bill，这让我更清楚了。（很抱歉由于我的声望问题，我无法为您的答案点赞。） - Anumi

@Michael-sqlbot，我很少使用表锁——它们可能会妨碍对表的并发访问。但是它们的使用是为了绕过InnoDB的乐观锁定。您可以使用一个LOCK TABLES语句原子地锁定多个表。这将是避免死锁的有效方法，但它会序列化对表的访问。非常感谢您阅读我的书！ - Bill Karwin

谢谢Bill。非常好的回答。 - SeaBiscuit

开始一个事务（例如，使用START TRANSACTION）会隐式提交任何当前的事务并释放现有的表锁 - @Bill Karwin，这是否意味着我们每个会话只能有一个活动事务？提前致谢。 - user19481364

@Daniel，是的，对于MySQL和大多数其他SQL数据库来说，这是正确的。我使用过InterBase及其开源分支Firebird，它们允许在一个会话中打开多个事务，但据我所知，这是唯一的例外。 - Bill Karwin

Answer 2

• 永远不要（好吧，我没有听说过这种情况）在InnoDB中使用LOCK TABLES。 LOCK TABLES就像一把大锤子，它可能会严重减慢您的系统。

• 对于某些应用程序，autocommit=ON是可以的。

• 对于某些应用程序，请使用BEGIN（START TRANSACTION）和COMMIT - 记得将它们清晰地配对。

• 不要使用autocommit=OFF，因为很容易忘记执行COMMIT。

• 请记住，DDL语句隐式地COMMIT。（直到MySQL 8.0。）

- Rick James

---

即使在数据库 session_set_save_handler() -> write() 中，也需要 (LOCK TABLES session WRITE / UNLOCK TABLES)。 - r_a_f

@r_a_f - 我根本不使用PHP的“会话”——它无法允许在多个服务器上扩展客户端。此外，我也不想因为在“会话”机制中持有某些东西太久而冒失超时的风险。 - Rick James