并发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)
参考
https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html
