InnoDB SELECT ... FOR UPDATE语句锁定表中的所有行 [英] InnoDB SELECT ... FOR UPDATE statement locking all rows in a table

问题描述

启用了InnoDB插件的MySQL Server版本5.1.41.我有以下三个发票表:发票，invoice_components和invoice_expenses.表发票具有invoice_id主键. invoice_components和invoice_expenses都链接到具有invoice_id作为非唯一foreign_key的表发票(每个发票可以具有多个组件和多个支出).两个表都有此外键的BTREE索引.

MySQL Server version 5.1.41 with InnoDB plugin enabled. I have the following three tables for invoices: invoices, invoice_components and invoice_expenses. Table invoices has invoice_id primary key. Both invoice_components and invoice_expenses are linked to table invoices with invoice_id as a non-unique foreign_key (each invoice can have more than one component and more than one expense). Both tables have a BTREE index for this foreign key.

我有以下交易:

交易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; 

对于第一个事务，一切正常，选择并锁定了行.

Everything works ok for the first transaction and the rows are selected and locked.

交易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; try restarting transaction.

The second transaction returns ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction for the third query.

当我尝试选择... FOR UPDATE其他发票及其组成和费用时，也会发生同样的情况.似乎第一笔交易已锁定invoice_expenses表中的所有行.任何想法为什么会发生这种情况?

The same happens when I try to SELECT ... FOR UPDATE other invoices and their components and expenses. It seems the first transaction has locked all the rows in invoice_expenses table. Any ideas why this is happening?

其他信息

事务2在第三个事务1查询之后开始.服务器上没有其他用户，连接或事务.

Transaction 2 starts after the third query of transaction 1. There are no other users, connections or transactions on the server.

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

The problem occurs in the default REPEATABLE READ transaction isolation level. It is fixed by changing to READ COMMITTED level. This is a solution but it still doesn't explain why the problem is occurring with invoice_expenses and not with invoice_components.

推荐答案

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

I suspect it has to do with gap locks and next-key locks and the differences in the behaviour of REPEATABLE READ :

摘录来自MySQL文档: SET TRANSACTION语法

The excerpts are from MySQL docs: SET TRANSACTION syntax

对于锁定读取(使用FOR UPDATE或LOCK IN SHARE MODE进行选择的SELECT)，UPDATE和DELETE语句，锁定取决于该语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件.对于具有唯一搜索条件的唯一索引，InnoDB仅锁定找到的索引记录，而不锁定其前的空白.对于其他搜索条件，InnoDB使用 gap锁或next-key(gap加索引记录)锁来锁定扫描的索引范围，以阻止其他会话插入该范围所覆盖的间隙. >

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.

和读取已提交:

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

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 system变量的状态以及更改此变量的设置时是否发生了相同的锁定.

Perhaps OP can tell us the status of innodb_locks_unsafe_for_binlog system variable and if the same locking occurs when this variable's setting is changed.

此外，如果相同的锁定发生在没有顺序ID的情况下，例如18和20或18和99

Also, if same locking happens with not sequential ids, like 18 and 20, or 18 and 99

这篇关于InnoDB SELECT ... FOR UPDATE语句锁定表中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！