MySQL 5.7 insert on duplicate key死锁问题

5,830 阅读2分钟

背景

参考链接:InnoDB can still deadlock on just INSERT…ON DUPLICATE KEY

在对不同的记录进行INSERT...ON DUPLICATE KEY操作时,理论上是不会存在死锁问题的,但在5.7.18等版本中,该操作却可能导致死锁,这篇文章主要分析产生的原因。

原因分析

数据表和例子

假如数据表Test的结构如下:

Id Uniq
1 1
2 2
6 6

其中Id为主键,Uniq为唯一索引,执行如下操作:

Transaction 1:
start transaction;
insert into Test values(3,3) on duplicate key update id=3,uniq=3;

Transaction 2:
start transaction;
insert into Test values(4,4) on duplicate key update id=4,uniq=4;

Transaction 3:
start transaction;
insert into Test values(5,5) on duplicate key update id=5,uniq=5;

原因分析

理论上这三条记录的主键值和唯一索引值都不一样,他们插入的时候都会获取(2,6)区间的Insert Intention Lock,但由于各自插入的主键值和唯一索引没有冲突,所以实际是可以正常插入的的,不会发生思索。

那么为什么MySQL的部分版本这种情况下会发生死锁了?

https://bugs.mysql.com/bug.php?id=52020

This Bug#52020 (accidental re-introduction of Bug#7975 in MySQL 5.1) affects tables that do not contain a secondary index.

There is another bug that affects tables with secondary indexes:

Bug#50413 insert on duplicate key update sometimes writes binlog position incorrectly

Despite the title, Bug#50413 can occur even if MySQL replication or binlog is not used. That bug has was fixed in MySQL 5.7.4. 
The fix is that when we encounter a duplicate key in the clustered index or in any unique secondary index during an INSERT, 

we will acquire gap locks in the not-yet-checked secondary indexes as well. In this way, the INSERT will already have acquired some locks for the ON DUPLICATE KEY UPDATE part,

thus avoiding some potential deadlocks.

When there are multiple unique indexes, the execution of ON DUPLICATE KEY UPDATE can be ambiguous, which makes it tricky for statement-based replication.

注意上面叙述中的关键一条:Despite the title, Bug#50413 can occur even if MySQL replication or binlog is not used. That bug has was fixed in MySQL 5.7.4. The fix is that when we encounter a duplicate key in the clustered index or in any unique secondary index during an INSERT,开发者为了解决一个Bug,在Insert数据时,除了给主键加Insert Intention Lock,还会给唯一索引加Gap Lock,但这就带来了这篇文章描述的问题。

当我们上文的三个事务插入时,他们除了都想在(2,6)区间加Insert Intention Lock之外,还想在(2,6)区间上加Gap Lock,由于Insert Intention LockGap Lock冲突,这才导致了死锁。