mysql - 共享锁升级为独占锁避免MySQL死锁

Question

我正在使用 MySQL 5.5。我注意到在并发场景中发生了一个特殊的死锁，我认为这种死锁不应该发生。

像这样重现，使用同时运行的两个 mysql 客户端 session :

mysql session 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 session 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 session 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 session 2:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

show engine innodb status报告的信息是这样的:

------------------------
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 锁；它只是在尝试获取独占锁时被阻止。既然没有循环，按照我的理解，这种情况不应该出现死锁。

这是一个已知的 MySQL 错误吗？其他人遇到过吗？使用了哪些解决方法？

这些是我们可以采取的可能的前进步骤:

• 减少我们对外键的使用(在我们的生产场景中，我们只软删除引用表中的行，但是很恶心)
• 预先获取独占锁而不是隐式共享锁(会降低我们的并发吞吐量)
• 改变我们的逻辑，这样我们就不再需要在添加子行的同一事务中对父级进行独占锁定(有风险且困难)
• 将我们的 MySQL 版本更改为不表现出这种行为的版本

还有我们没有考虑的其他选择吗？

Answer 1

这是一个长期存在的错误，您可以从以下网址了解更多信息:This bug report

This is a problem in MySQL-level table locking.

Internally inside InnoDB, a FOREIGN KEY constraint check may read (or, with ON UPDATE or ON DELETE clause, write) parent or child tables.

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.

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.

阅读讨论和它的回复