死锁案例

简化后的死锁场景如下

环境： 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提交，这就造成了相互等待。

加锁分析

1. T2 delete语句需要等待记录上的X锁；
2. 普通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) 

参考

InnoDB 事务锁系统简介

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

http://hedengcheng.com/?p=844