死锁案例二

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、测试用例

session1 session2 session3
begin; 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;

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

相关文章
相关标签/搜索
每日一句
    每一个你不满意的现在,都有一个你没有努力的曾经。
本站公众号
   欢迎关注本站公众号,获取更多程序园信息
开发小院