InnoDB SELECT ... FOR UPDATE 语句锁定表中的所有行
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。
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.
I have the following transactions:
transaction 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.
transaction 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;
The second transaction returns ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
for the third query.
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?
Additional info
Transaction 2 starts after the third query of transaction 1. There are no other users, connections or transactions on the server.
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我怀疑这与间隙锁和下一个键锁以及可重复读取的行为差异有关:
摘录自MySQL文档: SET TRANSACTION 语法
和已提交读取:
也许OP可以告诉我们innodb_locks_unsafe_for_binlog系统变量的状态,以及当该变量的设置更改时是否会发生相同的锁定。
另外,如果相同的锁定发生在不连续的 ID 上,例如
18
和20
或18
和99
I suspect it has to do with gap locks and next-key locks and the differences in the behaviour of REPEATABLE READ :
The excerpts are from MySQL docs: SET TRANSACTION syntax
and READ COMMITTED :
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.Also, if same locking happens with not sequential ids, like
18
and20
, or18
and99
“对于搜索遇到的索引记录,SELECT ... FROM ... FOR UPDATE 会阻止其他会话执行 SELECT ... FROM ... LOCK IN SHARE MODE 或在某些事务隔离级别中读取。一致性读取将忽略任何锁定设置在读取视图中存在的记录上”
可以通过 select for update 应用哪些特定锁定,以便其他会话无法读取锁定的记录?
"For index records the search encounters, SELECT ... FROM ... FOR UPDATE blocks other sessions from doing SELECT ... FROM ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads will ignore any locks set on the records that exist in the read view"
What are those certain locks which can be applied with select for update so that other sessions cannot read locked record?
您正在使用交易;自动提交不会禁用事务,它只是使事务在没有显式
开始事务
的语句末尾自动提交。发生的情况是,其他一些线程在某些记录上持有记录锁(您正在更新表中的每条记录!)太长时间,并且您的线程超时。
您可以通过在事件发生后发出“SHOW ENGINE INNODB STATUS”来查看事件的更多详细信息。最好在安静的测试机上进行此操作。
You are using a transaction; autocommit does not disable transactions, it just makes them automatically commit at the end of the statements that do not have an explicit
start transaction
on them.What is happening is, some other thread is holding a record lock on some record (you're updating every record in the table!) for too long, and your thread is being timed out.
You can see more details of the event by issuing a "SHOW ENGINE INNODB STATUS" after the event. Ideally do this on a quiet test-machine.