Mysql死锁场景二(insert on duplicate key)

数据准备(与其他场景一样):

create table t(c1 int primary key, c2 int, c3 int, c4 int, unique index i_c2(c2), index i_c3(c3));

insert into t values (10, 11, 12, 13), (20, 21, 22, 23), (30, 31, 32, 33), (40, 41, 42, 43);

表名t,c1列为主键,c2列为唯一索引,c3列为普通索引
数据库隔离级别:RR
数据库版本:mysql 5.7.21

锁阻塞示意图(后续分析的时候,用得到):


image.png

1.死锁场景描述

1.1场景说明

重复提交数据,比如前端提交按钮重复点击了多次,后端也没有控制

1.2场景描述

在唯一索引c2的间隙(31,41)插入3条相同记录。会话1插入1条c2=36的记录,会话2插入相同数据,会话3插入相同数据,会话1回滚,会话2,3形成了死锁。(如果会话1提交,不会形成死锁)

会话1:
start transaction;
insert into t values(50,36,52,53) on duplicate key update c2=36;

会话2:
start transaction;
insert into t values(51,36,62,63) on duplicate key update c2=36;

会话3:
start transaction;
insert into t values(52,36,62,63) on duplicate key update c2=36;

这个时候会话2,会话3都阻塞了,我们可以查看一下锁信息
另外开启一个会话4

show engine innodb status;


image.png

暂不分析,后面一起分析

会话1:
rollback;
这个时候可以看到会话2,3形成了死锁

会话4查看死锁信息:
show engine innodb status;


image.png

1.3死锁分析

1.3.1 图1分析

会话2,3被会话1阻塞的时候,会话2,3都线上lock_mode X waiting。这个意思是等待排他的next-key锁。因为会话1持有了新插入记录的锁,会话2,3等待在这条记录上加next-key锁。

1.3.2 图2分析

死锁信息表明,会话2,3都在等待插入意向锁,会话3显示持有了lock_mode X locks gap before rec(间隙锁)。会话2,3都持有了4把锁,会话2也应该持有了相同的间隙锁。(这里mysql没有显示完整,不知道为什么,可能编写这个代码的程序员偷懒了)。死锁跟场景一一样了,插入意向锁被gap锁阻塞了(相互阻塞)。这里比较有意思的一点是,看图1的时候,会话2,3明明都在等待的是lock_mode X waiting(next-key锁),为什么最后都持有的是gap锁呢。其实,这里发生了锁迁移,因为等待的记录不见了,需要迁移到最近的一条记录上来。具体我就不表了,可以参考:http://mysql.taobao.org/monthly/2016/06/01/

1.3.3 隔离级别改为read-committed

大家可以尝试一下,结果肯定出乎你的意料。居然跟repeatable-read隔离级别的结果一模一样。
会话2,3也都会被会话1阻塞,而且也都是lock_mode X waiting(next-key)锁。为什么也会申请gap锁,这里是唯一索引引起的。具体可以参考:***,(这里找不到比较好的介绍的文章,如果你资源,欢迎提供)。总之,大家记住,RC隔离级别也会存在gap锁,大部分场景就是唯一索引检查的时候。
接着,往下的逻辑就跟RR隔离级别一样了,发送了锁迁移,然后申请插入意向锁就悲剧了。
总之,如果RC隔离级别出现了死锁,并且死锁信息里面还有gap锁,先去看看你的表上是否有唯一索引

2.如何规避

这个就简单了,本来重复提交的数据就不应该流到数据层,应用层就掐掉。

推荐阅读更多精彩内容