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

Question

MySQL 服务器版本 5.1.41，启用了 InnoDB 插件。我有以下三个发票表：invoices、invoice_components 和invoice_expenses。表发票有invoice_id 主键。发票_组件和发票_费用都链接到表发票，发票_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 exited;尝试为第三个查询重新启动事务。

当我尝试选择...更新其他发票及其组成部分和费用时，也会发生同样的情况。似乎第一个事务已锁定invoice_expenses 表中的所有行。有什么想法为什么会发生这种情况吗？

其他信息

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

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

Answer 1

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

摘录自MySQL文档： SET TRANSACTION 语法

对于锁定读取（SELECT with FOR UPDATE 或 LOCK IN SHARE MODE）、UPDATE 和 DELETE 语句，锁定取决于该语句是使用具有唯一搜索条件的唯一索引，还是使用范围类型搜索条件。对于具有唯一搜索条件的唯一索引，InnoDB 仅锁定找到的索引记录，而不锁定其之前的间隙。对于其他搜索条件，InnoDB 会锁定扫描的索引范围，使用间隙锁或下一个键（间隙加索引记录）锁来阻止其他会话插入该范围所覆盖的间隙。

和已提交读取：

注意：在 MySQL 5.1 中，如果使用 READ COMMITTED 隔离级别或启用了 innodb_locks_unsafe_for_binlog 系统变量，则除了外键约束检查和重复键检查之外，没有 InnoDB 间隙锁定 。此外，在 MySQL 评估 WHERE 条件后，不匹配行的记录锁将被释放。

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

另外，如果相同的锁定发生在不连续的 ID 上，例如 18 和 20 或 18 和 99

Answer 2

“对于搜索遇到的索引记录，SELECT ... FROM ... FOR UPDATE 会阻止其他会话执行 SELECT ... FROM ... LOCK IN SHARE MODE 或在某些事务隔离级别中读取。一致性读取将忽略任何锁定设置在读取视图中存在的记录上”

可以通过 select for update 应用哪些特定锁定，以便其他会话无法读取锁定的记录？

Answer 3

您正在使用交易；自动提交不会禁用事务，它只是使事务在没有显式开始事务的语句末尾自动提交。

发生的情况是，其他一些线程在某些记录上持有记录锁（您正在更新表中的每条记录！）太长时间，并且您的线程超时。

您可以通过在事件发生后发出“SHOW ENGINE INNODB STATUS”来查看事件的更多详细信息。最好在安静的测试机上进行此操作。