1、环境说明

MySQL5.6.33，隔离级别是RR。表结构及数据：

Create table t1(id int not null primary key auto_increment,c1 int,c2 int,c3 int, unique key(c1),unique key(c2)); insert into t1(c1,c2,c3) values(1,3,4),(6,6,10),(9,9,14);

2、测试用例

session1session2session3begin;begin;begin;

insert into t1 (c1,c2,c3) values(4,4,1);

   

insert into t1 (c1,c2,c3) values(4,4,2);

   

insert into t1 (c1,c2,c3) values(4,4,3);

commit;   

Update t1 set c3=5 where c1=4;

   

Update t1 set c3=5 where c1=4;

  deadlock;

3、死锁日志

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2018-07-07 06:27:15 a347bb90 *** (1) TRANSACTION: TRANSACTION 7973, ACTIVE 43 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s) MySQL thread id 4, OS thread handle 0xa34acb90, query id 75 localhost root updating Update t1 set c3=5 where c1=4 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 18 page no 4 n bits 72 index `c1` of table `yzs`.`t1` trx id 7973 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 4; hex 80000006; asc ;; *** (2) TRANSACTION: TRANSACTION 7974, ACTIVE 33 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 320, 2 row lock(s) MySQL thread id 5, OS thread handle 0xa347bb90, query id 76 localhost root updating Update t1 set c3=5 where c1=4 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 18 page no 4 n bits 72 index `c1` of table `yzs`.`t1` trx id 7974 lock mode S Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 4; hex 80000006; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 18 page no 4 n bits 72 index `c1` of table `yzs`.`t1` trx id 7974 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 4; hex 80000006; asc ;; *** WE ROLL BACK TRANSACTION (2)

4、分析死锁日志

从死锁日志上可以看到：

TRANSACTION 7973：

    Update t1 set c3=5 where c1=4语句在等待二级索引c1上(4,6)上的X类型的记录锁（lock_mode X locks rec but not gap）

TRANSACTION 7974：

    拥有二级索引c1上(4,6)上S类型的next key锁(lock mode S),等待申请(4,6)上的记录锁(lock_mode X locks rec but not gap)

但从死锁日志上只看到两个update语句互相等待，不知道业务逻辑场景的话，很难找到原因。

注：这里留下疑问，为什么主键是6呢，不是4？这个和自增键有关，关于自增值这里不做过多考虑，感兴趣的自行测试分析。

5、加锁原理

1）关于insert唯一键加锁时重复键判断加S类型next-key锁的加锁原理见之前博客：

          https://blog.csdn.net/yanzongshuai/article/details/79326637

          以及https://blog.csdn.net/yanzongshuai/article/details/79301868

     注意，这里发生重复键加S 类型next key锁时，不论是什么隔离级别，都会加这样的锁。

2）关于隐式锁转换显式锁流程见之前博客：

        https://blog.csdn.net/yanzongshuai/article/details/79306514

        https://blog.csdn.net/yanzongshuai/article/details/79254031

        https://blog.csdn.net/yanzongshuai/article/details/79252679

3）关于update加锁原理见之前博客：

       https://blog.csdn.net/yanzongshuai/article/details/80870949

       https://blog.csdn.net/yanzongshuai/article/details/80870957

       https://blog.csdn.net/yanzongshuai/article/details/80872095

6、解析

     1）session1执行insert into t1 (c1,c2,c3) values(4,4,1);实际上是没有加任何锁的。

     2）session2执行insert into t1 (c1,c2,c3) values(4,4,2);二级索引(4)和session1的发生冲突，使session1的隐式锁转换成显式锁；发生唯一冲突，则对(4)加S类型的next key锁，此时session1已经加了X锁，发生等待；

     3）session3执行insert into t1 (c1,c2,c3) values(4,4,3);同理，等待session1释放二级索引c1(4)上的X锁，申请S类型的next key锁。

     4）session1执行commit后，session2和session3报错：ERROR 1062 (23000): Duplicate entry '4' for key 'c1'，同时会申请到S类型的next key锁。

     5）session2执行Update t1 set c3=5 where c1=4;从之前博客

          https://blog.csdn.net/yanzongshuai/article/details/80872095

          可知在search阶段会对二级索引记录(4)申请X类型的记录锁。session3已拥有S类型next key锁，所以发生等待；

     6）session3再执行Update t1 set c3=5 where c1=4;同理，会申请X类型的记录锁，等待session2释放其S类型next key锁。此时发生死锁。

7、解决方法

杨奇龙老师解释可以使用使用insert on duplicate key语句来代替原来的insert语句。这2个语句的加锁不一样，感兴趣的可以研究下。

8、参考

https://mp.weixin.qq.com/s/96CDhpgu5uUQ7qKYhKgt3w