mysql - InnoDB SELECT ... FOR UPDATE 语句锁定表中的所有行

Question

启用了 InnoDB 插件的 MySQL 服务器版本 5.1.41。我有以下三个发票表:invoices、invoice_components 和 invoice_expenses。表 invoices 有 invoice_id 主键。 invoice_components 和 invoice_expenses 都链接到表 invoices，并将 invoice_id 作为非唯一的外键(每张发票可以有多个组件和多个费用)。两个表都有这个外键的 BTREE 索引。

我有以下交易:

事务 1

START TRANSACTION; 
SELECT * FROM invoices WHERE invoice_id = 18 FOR UPDATE; 
SELECT * FROM invoice_components WHERE invoice = 18 FOR UPDATE; 
SELECT * FROM invoice_expenses WHERE invoice = 18 FOR UPDATE; 

第一笔交易一切正常，行被选中并锁定。

交易 2

START TRANSACTION; 
SELECT * FROM invoices WHERE invoice_id = 19 FOR UPDATE; 
SELECT * FROM invoice_components WHERE invoice = 19 FOR UPDATE; 
SELECT * FROM invoice_expenses WHERE invoice = 19 FOR UPDATE; 

第二个事务返回ERROR 1205 (HY000): Lock wait timeout exceeded;尝试为第三个查询重新启动事务。

当我尝试 SELECT ... FOR UPDATE 其他发票及其组成部分和费用时，也会发生同样的情况。似乎第一笔交易锁定了 invoice_expenses 表中的所有行。知道为什么会这样吗？

附加信息

事务 2 在事务 1 的第三次查询之后开始。服务器上没有其他用户、连接或事务。

问题出现在默认的REPEATABLE READ 事务隔离级别。它通过更改为 READ COMMITTED 级别来修复。这是一个解决方案，但仍然无法解释为什么问题出现在 invoice_expenses 而不是 invoice_components。

Answer 1

我怀疑这与间隙锁和下一键锁以及可重复读取行为的差异有关:

摘录自 MySQL 文档: SET TRANSACTION syntax

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

和已提交阅读:

Note: In MySQL 5.1, if the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.

也许 OP 可以告诉我们 innodb_locks_unsafe_for_binlog 系统变量的状态，以及当这个变量的设置改变时是否发生相同的锁定。

此外，如果相同的锁定发生在非顺序 ID 上，例如 18 和 20，或者 18 和 99