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

阿丸笔记

发布于 2024-04-23 14:29:13

5880

发布于 2024-04-23 14:29:13

文章被收录于专栏：阿丸笔记

最近线上偶发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_displaydataid的 MX4TYZIKTKSZCAABAAAAAAY8fw_4 位置上
WAITING FOR THIS LOCK TO BE GRANTED : 该事务在等待索引idx_displaydataid的MX4TYXYKTJ6VKAABAAAAADY8m462位置上，等待一个X锁

2）事务2

HOLDS THE LOCK(S) : 该事务持有两个S锁，其中一个锁在索引idx_displaydataid的 MX4TYXYKTJ6VKAABAAAAADY8m462 位置上
WAITING FOR THIS LOCK TO BE GRANTED : 该事务在等待索引idx_displaydataid的MX4TYZIKTKSZCAABAAAAAAY8fw_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会退化为间隙锁
范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止