mysql delete key锁_并发delete+insert duplicate-key冲突导致死锁

死锁案例

简化后的死锁场景如下

环境： mysql5.7,事务隔离级别REPEATABLE-READ

表结构

CREATE TABLE `t4` (

`a` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`a`)

) ENGINE=InnoDB

数据：

mysql> select * from t4;

+---+

| a |

+---+

| 1 |

+---+

并发事务

T1

T2

begin；

begin

delete from t4 where a = 1;//ok, 0 rows affected

delete from t4 where a = 1; //wating,被阻塞

insert into t4 values(1);//Query OK, 1 row affected (0.01 sec)

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

从上面可以看出，并发事务执行delete，T2等待，T1再执行insert相同value的数据时出现死锁。

死锁分析

死锁原因

insert时如果数据出现duplicate key，会加LOCK_S锁，类型为NEXT-KEY LOCK，此时事务2已经在申请record lock X锁，在申请队