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锁，在申请队列中了，事务1再加NEXT-KEY LOCK S锁则需要等待事务2提交，这就造成了相互等待。

加锁分析

T2 delete语句需要等待记录上的X锁；

普通insert时，其加锁过程为先在插入间隙上获取插入意向锁，插入数据后再获取插入行上的排它锁。由于这里T1 insert的数据检测到duplicate key，会加S锁(如果扫描到的记录被标记删除时，也会加S 锁)，且针对主键索引加LOCK_ORDINARY类型的记录锁(NEXT-KEY LOCK)，而NEXT-KEY LOCK与record互斥 。

另外，如果delete的where子句没有满足条件的记录，而对于不存在的记录 并且在RR级别下，delete加锁类型为gap lock。可以参考我的这篇博客https://my.oschina.net/hebaodan/blog/1835966

在RC级别测试上述场景无死锁发生。

死锁解决

避免出现按某个key delete后又插入相同key的场景，规避insert数据duplicate key的问题

如果非特殊需求修改为非唯一索引

通过其他手段如分布式锁实现串行化处理

附：死锁日志

*** (1) TRANSACTION:

TRANSACTION 11074, ACTIVE 11 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 10, OS thread handle 123145442168832, query id 160 localhost 127.0.0.1 root updating

delete from t4 where a = 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 68 page no 3 n bits 72 index PRIMARY of table `aliyun`.`t4` trx id 11074 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32

0: len 4; hex 80000001; asc ;;

1: len 6; hex 000000002b41; asc +A;;

2: len 7; hex 2e000001dc13c4; asc . ;;

*** (2) TRANSACTION:

TRANSACTION 11073, ACTIVE 40 sec inserting

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 9, OS thread handle 123145442725888, query id 161 localhost 127.0.0.1 root update

insert into t4 values(1)

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 68 page no 3 n bits 72 index PRIMARY of table `aliyun`.`t4` trx id 11073 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32

0: len 4; hex 80000001; asc ;;

1: len 6; hex 000000002b41; asc +A;;

2: len 7; hex 2e000001dc13c4; asc . ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 68 page no 3 n bits 72 index PRIMARY of table `aliyun`.`t4` trx id 11073 lock mode S waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32

0: len 4; hex 80000001; asc ;;

1: len 6; hex 000000002b41; asc +A;;

2: len 7; hex 2e000001dc13c4; asc . ;;

*** WE ROLL BACK TRANSACTION (1)

参考