MySQL事务与表锁定

Question

我需要使用表锁定（写入）并同时更新几个表，因此我需要同时进行事务处理，因为锁定不是事务安全的。

从MySQL文档中，我读到了以下内容：https://dev.mysql.com/doc/refman/5.6/en/lock-tables-and-transactions.html。

The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. For example, if you need to write to table t1 and read from table t2, you can do this:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have autocommit = 1, because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. InnoDB does not acquire the internal table lock at all if autocommit = 1, to help old applications avoid unnecessary deadlocks.

从这个页面的另一方面，我们有https://dev.mysql.com/doc/refman/5.6/en/commit.html。该页面介绍了MySQL中的提交操作。

To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

所以，如果使用START TRANSACTION禁用自动提交，则在表锁定部分为什么说正确的方法是使用SET autocommit = 0（而不是START TRANSACTION）开始事务。我有遗漏什么还是这两者之间存在矛盾？我可以在表锁定时使用START TRANSACTION吗？我正在使用InnoDB引擎。

谢谢。

- dav

---

为什么您觉得需要使用表级锁？行锁为什么不足够？ - Darwin von Corax

多个脚本应该并行工作，而select for update不可行（我猜你是这个意思），因为它们应该进行选择、更新和插入操作，所以整个表格应该被锁定，这样脚本才能同步工作。 - dav

Answer 1

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

• LOCK TABLES不支持事务，会在尝试锁定表之前隐式提交任何活动事务。
• 启动事务(例如使用START TRANSACTION)，会隐式提交任何当前的事务并释放已有的表锁。

- pjkozlowski