Mysql死锁场景三(普通insert并发插入)

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

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列为普通索引
数据库隔离级别:RC(注意,隔离级别变成RC了)
数据库版本:mysql 5.7.21

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


image.png

1.死锁场景描述

1.1场景说明

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

1.2场景描述(与场景二一样,只是sql没有了on duplicate key)

在唯一索引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);

会话2:
start transaction;
insert into t values(51,36,62,63);

会话3:
start transaction;
insert into t values(52,36,62,63);

这个时候会话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 S waiting。这个意思是等待共享的next-key锁。因为会话1持有了新插入记录的锁,会话2,3等待在这条记录上加共享的next-key锁。

1.3.2 图2分析

死锁信息表明,会话2,3都在等待插入意向锁,会话3显示持有了lock mode S locks gap before rec(共享的间隙锁)。会话2,3都持有了4把锁,会话2也应该持有了相同的间隙锁。死锁跟场景二一样了,插入意向锁被gap锁阻塞了(相互阻塞)。与场景二唯一的区别是,等待的是共享的next-key锁(场景二排他的),会话1回滚后,拿到的是共享的gap锁(场景二排他的)。

1.3.3 隔离级别改为REPEATABLE-READ

大家可以尝试一下,结果是一模一样的

1.3.4 会话1修改为删除一条记录

这个场景可以修改一下,会话1删除1条已经存在的记录,会话2,3分别插入相同的记录。比如:
会话1: delete from t where c2=31;
会话2:insert into t values(32,31,322,332);
会话3:insert into t values(33,31,323,333);
会话1:提交
也会有概率地出现死锁。原因差不太多,删除的时候,会锁住c2=31的记录,阻塞会话2,3获取next-key锁。一旦会话1提交,会话2,3都会拿到next-key锁,发现记录删除了,重新扫描,变成获取gap锁了(这里有一定的概率,因为有可能一个会话执行比较快,直接拿完gap锁和插入意向锁了,另外一个会话还在拿gap锁的路上)。都去获取插入意向锁的时候,互相等待对方释放gap锁
隔离级别为RC或者RR都会有概率出现死锁

1.3.5 会话2,3sql加上on duplicate key

会话2:insert into t values(32,31,322,332) on duplicate key update c3=322;
会话3:insert into t values(33,31,323,333) on duplicate key update c3=323;
这样不会有死锁,查看锁等待信息,可以看到也是等待获取next-key锁,会话1提交后,也会发生锁迁移,但是还是去获取next-key锁,这样会话2,3就只有一个能够进入。这里比较奇怪,为什么没有变成去获取gap锁,没想通

2.如何规避

跟场景二一样

推荐阅读更多精彩内容