MySQL死锁排查,原来我一直没懂。。。

原创
04/20 14:14
阅读数 81


喜大普奔,微信给我的公众号开了留言功能!!!有缘看到这篇文章的朋友,可以留个言互动下,谢谢~

最近线上偶发MySQL的死锁异常,发现原来很多理论都只背了个结论,细节都是魔鬼。

比如,MySQL在RR级别用gap lock防止幻读,RC级别就没有gap lock吗?

不妨来一起看看,MySQL的死锁问题有哪些你不了解的细节。

1、死锁信息

1.1 数据库基本信息

  • 版本:MySQL 5.7

  • 隔离级别:  READ-COMMITTED

  • 表结构:

1.2 死锁日志



死锁日志分析
1)事务1

  • HOLDS THE LOCK(S) : 该事务持有两个S锁,其中一个锁在索引idx_displaydataidMX4TYZIKTKSZCAABAAAAAAY8$f$w_4 位置上

  • WAITING FOR THIS LOCK TO BE GRANTED : 该事务在等待索引idx_displaydataidMX4TYXYKTJ6VKAABAAAAADY8$m$462位置上,等待一个X锁

2)事务2

  • HOLDS THE LOCK(S) : 该事务持有两个S锁,其中一个锁在索引idx_displaydataidMX4TYXYKTJ6VKAABAAAAADY8$m$462 位置上

  • WAITING FOR THIS LOCK TO BE GRANTED : 该事务在等待索引idx_displaydataidMX4TYZIKTKSZCAABAAAAAAY8$f$w_4一个X锁

死锁原因看起来比较清楚,锁互斥且循环等待,造成了死锁。

1.3 死锁疑点

随着我仔细分析上面的日志,发现又不是那么简单,或者说有几个疑点困惑:

  • Question1: gap before rec 表示一个间隙锁,我们数据库的隔离级别是 RC,怎么还有间隙锁?

  • Question2: gap before rec insert intention好像叫插入意向锁,到底是个啥?

  • Question3: INSERT语句,到底有几把锁?为什么会获得S锁?

2、死锁答疑

2.1 为什么RC级别下还有间隙锁?

网上很多博客视频都会说RC级别下间隙锁会失效,然后搬出官方文档的原话:

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated).

但是,官方文档后面还有一句:

In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

意思是RC级别下间隙锁会用于外键和唯一键检查。

2.2 插入意向锁到底是什么?

查阅了官方文档,我们可以了解到,插入意向锁(Insert Intention Locks
)其实是一种特殊的gap lock,在行插入前,要获取这个锁(所以这个锁是在行排它锁之前获取)。

假设存在值为 4 和 7 的索引记录,尝试插入值 5 和 6 的两个事务,在获取插入行上的排它锁之前,使用插入意向锁锁定间隙,即在(4,7)上加 gap lock

但是这两个事务不会互相冲突等待

但是如果这个区间已经存在其他普通 gap lock(比如其他事务用select for update 或者 select in share mode获取了gap lock),则插入意向锁会被阻塞。

注意,这也是我们常说的gap lock能够避免幻读的原因,可以阻止INSERT获取插入意向锁

如果多个事务插入相同数据导致唯一冲突,则在重复的索引记录上加读锁,这个我们后面再详细介绍。

简单来说,插入意向锁的属性为:

  • 它不会阻塞其他任何锁;

  • 它本身仅会被gap lock阻塞

2.3 INSERT到底有几把锁

1)普通INSERT

  • 先加插入意向锁,插入意向锁之间不冲突。比如4,7两行之间,可以同时插入5、6两行。

  • 插入成功后,加对应行锁。

2)INSERT唯一索引冲突

INSERT的时候,发现唯一索引冲突,触发duplicate-key error 后,会先获取到一个next-key读锁。

session A第二次插入时,发生唯一键冲突的时候,并不只是简单地报错返回,还在冲突的索引上加了锁。

一个 next-key lock 就是由它右边界的值定义的。这时候,session A 持有索引 c 上的 (5,10]共享 next-key 读锁,所以session B插入时也被阻塞了。

总结一下:

  • 通常INSERT语句,先加插入意向锁,插入成功后,获得行锁,排它锁

  • 在INSERT之前,先通过插入意向锁,判断是否可以插入(仅会被gap lock阻塞)

  • 当插入唯一冲突时,在重复索引上添加next-key读锁

事务1 插入成功未提交,获取了排它锁,但是事务1最终可能会回滚,所以其他重复插入事务不应该直接失败,这个时候他们改为申请读锁。

3、总结下INSERT几种经典死锁

3.1 模式一:唯一索引并发写入回滚

  • session A插入,获得行写锁;

  • session B、C插入时,发现唯一索引冲突,同时请求next-key读锁,锁排队;

  • session A回滚,释放行写锁,session B、C同时获得next-key读锁

  • session B、C尝试插入,需要获取插入意向锁,互斥等待,触发死锁

3.2 模式二:唯一索引并发删除插入

  • session A 拿到行写锁(delete from where 正常情况是获取next-key锁,只有当唯一索引命中时会变成行锁)

  • sessionB/C发现唯一索引冲突,触发duplicate-key error 后,同时请求next-key读锁,锁排队;

  • session A commit后,删除成功,释放行写锁,sessionB/C 获得next-key读锁

  • session B、C 尝试插入,需要获取插入意向锁,互斥等待,触发死锁    

3.3 模式三:唯一索引并发删除后插入

  • session A的delete from 拿到行写锁

  • session B的delete from 希望获取行写锁,等待

  • session A的insert 唯一索引冲突,希望获取next-key读锁,锁排队,并且排在B的后面,形成死锁

4、总结下加锁原则

这里还有一个加锁原则比较重要,一个SQL到底要加哪些锁。

查阅了网上一些资料,做了一个总结,具体案例就不展开了:

  • MySQL的锁是加在索引上的

  • 查询过程中访问到的索引对象才会加锁(没有索引就可能锁全表)

  • 加锁的基本单位是next-key lock(前开后闭)

  • 等值查询上MySQL的优化:索引上的等值查询,如果是唯一索引,next-key lock会退化为行锁,如果不是唯一索引,需要访问到第一个不满足条件的值,此时next-key lock会退化为间隙锁

  • 范围查询:无论是否是唯一索引,范围查询都需要访问到不满足条件的第一个值为止

5、死锁优化建议

  • 避免大事务,尽量拆小

  • 避免 经典死锁模式

  • 批量操作尽量排序后,按相同顺序插入或者删除

  • 尽量使用普通索引而不是唯一索引,即使使用唯一索引,也应该尽量避免重复插入

  • 可以考虑使用RC隔离级别加binlog_format=row模式,而不是RR隔离级别







往期热门笔记合集推荐:


原创:阿丸笔记(微信公众号:aone_note),欢迎 分享,转载请保留出处。

没有留言功能的悲伤,扫描下方二维码「加我」聊些有的没的吧~

                                                                              觉得不错,就点个  再看 吧👇



本文分享自微信公众号 - 阿丸笔记(aone_note)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部