MySQL innoDB 间隙锁产生的死锁问题 | 京东云技术团队
背景
线上经常偶发死锁问题,当时处理一张表,也没有联表处理,但是有两个 mq 入口,并且消息体存在一样的情况,频率还不是很低,这么一个背景,我非常容易怀疑到,两个消息同时近到这一个事务里面导致的,但是是偶发的,又模拟不出来什么场景会导致死锁,只能进行代码分析,问题还原的方式去排查问题。
业务代码简化成下面
也就是说先 update ,select , insert 这么一个顺序
表中存在 dm_code ,erp 唯一索引
如果不存在索引 第一行 update 会导致行锁升级为表锁,反而不会导致问题出现,但是并发太差
结论
先说结论:
重点: 无论哪个事务 insert,两个事务必须都 update 完成,只要满足这个条件,两个 insert 执行的时候就会报死锁
原因:我先按照自己的理解解释下:
innodb 的行锁,存在间隙锁,为啥要去有索引,如果没有索引,第一个 update 就直接进行了表锁,这样导致另外一个事务无法进入,就只能进行等待了。
有索引的情况下:
两个事务都执行 update,都拿到了[当前值,+∞) 的锁(记录锁+间隙锁),(update 的时候,无数据命中)
第一个 insert 时,希望等待另外一个事务释放锁。第二个事务希望第一个事务释放锁,因此出现了死锁问题
相关知识梳理
InnoDB 有三种行锁的算法:
1.Record Lock:是加在索引记录上的。
2.Gap Lock(间隙锁):对索引记录间的范围加锁,或者加在最后一个索引记录的前面或者后面
3.Next-Key Lock:前两种锁的结合,锁定一个范围,并且锁定记录本身,主要目的是解决幻读的问题。
间隙锁主要是防止幻象读,用在 Repeated-Read(简称 RR)隔离级别下。在 Read-Commited(简称 RC)下,一般没有间隙锁(有外键情况下例外,此处不考虑)。间隙锁还用于 statement based replication
间隙锁有些副作用,如果要关闭,一是将会话隔离级别改到 RC 下,或者开启 innodb_locks_unsafe_for_binlog(默认是 OFF)。
间隙锁(无论是 S 还是 X)只会阻塞 insert 操作。
RR 隔离级别
session2
上面都报错:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这个证明 id (3,5)都被间隙锁锁住了
(3,5) 区间之外都可以执行 insert,delete 操作
可以看到,delete k=5 的记录阻塞了 k=3、4、5、6、7、8、9 记录的插入操作,事实上,除了对于 k=5 这条记录上 record lock 之外,innoDB 对于 delete 和 update 在辅助索引(非主键索引)上的条件时会对扫过的记录上间隙锁,为了防止幻读,会锁住 k=5 这条记录的前面一条记录(id=2,k=2)到后面一条记录(id=10,k=10)之间的区间,即锁住 k 在区间(2,10)的范围(如果没有后一条记录,一直锁到正无穷),至于在边界 k=2 及 k=10 上,由于索引内是按照主键排序的,不会锁住(id<2,k=2)但是会锁住(id>2,k=2),同理不会锁住(id>10,k=10)但是会锁住(id<10,k=10).
由于索引内是按照主键排序的,不会锁住(id<2,k=2)但是会锁住(id>2,k=2),同理不会锁住(id>10,k=10)但是会锁住(id<10,k=10).
值得注意的是,delete 和 update 在唯一索引(primary key/unique key)上更新存在的记录时只会上行级记录锁(record key),而在唯一索引上更新不存在的记录时同辅助索引一样会上间隙锁;在上例中,delete id=5 只会在(id=5,k=5)这条记录上上 X 锁,而 delete id=7 却会锁住(id>5&&id<10)这个区间。
线上问题还原
重点: insert 之前两个回话都执行完 update
解决办法:
避免更新或者删除不存在的记录,虽然更新存在的记录也会产生间隙锁,但是间隙锁锁住的范围会更小;
更新不存在的记录会锁住意想不到的区间范围,极其容易导致死锁问题
这些仅仅是解决问题的一个小的技巧,不能从根本上解决问题,如果想从根本上解决就从代码级别上加锁,这样避免了这种问题,但是同时并发就小了,根据自己的实际情况进行定夺方案
作者:京东零售 吴法刚
来源:京东云开发者社区 转载请注明来源
版权声明: 本文为 InfoQ 作者【京东科技开发者】的原创文章。
原文链接:【http://xie.infoq.cn/article/6708351c2d469991aed1d87a7】。文章转载请联系作者。
评论