将共享升级到互斥锁时避免MySQL死锁 [英] Avoiding MySQL deadlock when upgrading shared to exclusive lock
问题描述
我正在使用MySQL 5.5.我注意到在并发情况下发生了特殊的死锁，并且我认为这种死锁不应该发生.
I'm using MySQL 5.5. I've noticed a peculiar deadlock occurring in a concurrent scenario, and I don't think this deadlock should occur.
使用两个同时运行的mysql客户端会话，像这样重现:
Reproduce like this, using two mysql client sessions running simultaneously:
mysql会话1 :
create table parent (id int(11) primary key);
insert into parent values (1);
create table child (id int(11) primary key, parent_id int(11), foreign key (parent_id) references parent(id));
begin;
insert into child (id, parent_id) values (10, 1);
-- this will create shared lock on parent(1)
mysql会话2 :
begin;
-- try and get exclusive lock on parent row
select id from parent where id = 1 for update;
-- this will block because of shared lock in session 1
mysql会话1 :
-- try and get exclusive lock on parent row
select id from parent where id = 1 for update;
-- observe that mysql session 2 transaction has been rolled back
mysql会话2 :
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
从show engine innodb status报告的信息是这样的:
The information reported from show engine innodb status is this:
------------------------
LATEST DETECTED DEADLOCK
------------------------
161207 10:48:56
*** (1) TRANSACTION:
TRANSACTION 107E67, ACTIVE 43 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 13074, OS thread handle 0x7f68eccfe700, query id 5530424 localhost root statistics
select id from parent where id = 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E67 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000107e65; asc ~e;;
2: len 7; hex 86000001320110; asc 2 ;;
*** (2) TRANSACTION:
TRANSACTION 107E66, ACTIVE 52 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 12411, OS thread handle 0x7f68ecfac700, query id 5530425 localhost root statistics
select id from parent where id = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E66 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000107e65; asc ~e;;
2: len 7; hex 86000001320110; asc 2 ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E66 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000107e65; asc ~e;;
2: len 7; hex 86000001320110; asc 2 ;;
*** WE ROLL BACK TRANSACTION (1)
您可以看到事务(1)不显示任何已获取的S或X锁；它只是试图获取独占锁而被阻止.据我所知，既然没有周期，那么在这种情况下就不会出现僵局.
You can see that transaction (1) doesn't show any S or X locks already acquired; it's just blocked trying to acquire an exclusive lock. Since there's no cycle, there shouldn't be a deadlock in this situation, as I understand it.
这是一个已知的MySQL错误吗?有其他人遇到吗?使用了什么解决方法?
Is this a known MySQL bug? Have other people encountered it? What workarounds were used?
这些是我们可以采取的可能步骤:
These are the possible steps forward we could take:
- 减少对外键的使用(在我们的生产场景中，我们仅软删除引用表中的行，但是很讨厌)
- 预先获取排他锁，而不是隐式共享锁(这会降低我们的并发吞吐量)
- 更改我们的逻辑，这样我们就不再需要在同一事务中对父级进行排他锁，而这会增加子行(危险和困难)
- 将我们的MySQL版本更改为不显示此行为的版本
我们是否还在考虑其他选项?
Are there other options we're not considering?
推荐答案
This is a long standing bug which you can read more from: This bug report
这是MySQL级表锁定中的问题.
This is a problem in MySQL-level table locking.
在InnoDB内部，FOREIGN KEY约束检查可能会读取(或， 带有ON UPDATE或ON DELETE子句，写入)父表或子表.
Internally inside InnoDB, a FOREIGN KEY constraint check may read (or, with ON UPDATE or ON DELETE clause, write) parent or child tables.
通常，表访问受以下锁控制: 1. MySQL元数据锁定 2. InnoDB表锁 3. InnoDB记录锁
Normally, table access is governed by the following locks: 1. MySQL meta-data lock 2. InnoDB table lock 3. InnoDB record locks
所有这些锁将保留到事务结束.
All these locks are held until the end of the transaction.
在某些模式下会跳过InnoDB表和记录锁，但是 不在外键检查期间.死锁是因为MySQL 仅为显式获取的表获取元数据锁 在SQL语句中提到.
The InnoDB table and record locks are skipped in certain modes, but not during foreign key checks. The deadlock is caused because MySQL acquires the meta-data lock only for the table(s) that are explicitly mentioned in the SQL statements.
我想一种解决方法是访问孩子(或父母) 交易开始时的表格，有问题的外汇之前 按键操作.
I guess that a workaround could be to access the child (or parent) tables at the start of the transaction, before the problematic FOREIGN KEY operation.
阅读讨论内容和回复内容
Read the discussion and it's reply's
这篇关于将共享升级到互斥锁时避免MySQL死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
