大家好,我是树哥。
在前面的文章「MySQL 不同隔离级别,都使用了什么锁?」里,我们得出结论:在「读未提交」和「读已提交」隔离级别下,都只会使用记录锁,不会用间隙锁和 Next-Key 锁。而对于「可重复读」隔离级别来说,会使用记录锁、间隙锁和 Next-Key 锁。
那么 MySQL 啥时候会用记录锁,啥时候会用间隙锁,啥时候又会用 Next-Key 锁呢?今天我们就来做一些测试,弄清楚这个问题。
文章思维导图
影响因素
在开始之前,我们需要声明的是:本文所有测试及结论的前提均是在「可重复读」隔离级别下,以及 Innodb 存储疫情下。
根据网上资料,我们大概可以知道,影响其使用哪种行级锁的因素有:
- 索引类型(聚簇索引、唯一二级索引、普通二级索引)
- 匹配类型(精确匹配、唯一匹配、范围匹配)
- 事务隔离级别
- 是否开启 Innodb_locks_unsafe_for_binlog 系统变量
- 记录是否被标记删除
- 具体的执行语句类型(SELECT、INSERT、DELETE、UPDATE)
为了让文章相对易懂一些,我准备重点测试索引类型与匹配类型两个影响因素。对于其他的影响因素,我将不做改动。例如:事务隔离级别固定为「可重复读」,Innodb_locks_unsafe_for_binlog 固定为 false。而第 5、6 点相对来说简单一些,则我们会简单带过。
针对上面几个影响因素,我们指定了几个测试实验,分别是:
- 聚簇索引 + 精确匹配
- 聚簇索引 + 范围匹配
- 唯一二级索引 + 精确匹配
- 唯一二级索引 + 范围匹配
- 普通二级索引 + 精确匹配
- 普通二级索引 + 范围匹配
聚簇索引 + 精确匹配
为了测试「聚簇索引 + 精确匹配」下加锁的类型,我们采用如下的测试方法。
事务 A 执行下面命令:
执行 show engine innodb status\G; 查看锁信息如下图所示。
可以看到,其是对 id 为 2 的索引加了一个记录锁。
此时事务 B 执行下面命令:
执行之后,我们会发现事务 B 阻塞住了。
那如果聚簇索引的值找不到对应的记录呢,将会是一个什么样的结果呢?
我们再来测试一下,开始之前记得将事务 A 和 B 回滚恢复。
事务 A 执行下面命令,其中 id 为 5 的记录是不存在的:
执行 show engine innodb status\G; 查看锁信息如下图所示。
可以看到,其加了一个间隙锁,该间隙锁应该是 (2, 50) 这个范围。
我们可以通过在事务 B 执行如下命令来测试下间隙锁的范围。
由此我们可以得出结论:「聚簇索引 + 精确匹配」,如果能够定位到唯一一条存在的记录,那么其会使用记录锁。如果该记录不存在,那么则会使用间隙锁。
聚簇索引 + 范围匹配
事务 A 执行下面命令:
执行 show engine innodb status\G; 查看锁信息如下图所示。
可以看到,事务 A 一共加了 3 个锁,其中 1 个记录锁,2 个 Next-Key 锁。其中 1 个记录锁是对 id 为 2 的索引加的锁,Next-Key 锁是对 (2, 50] 和 (50, 正无穷) 这两个区间加的锁。
在事务 B 执行下面命令可以验证间隙锁的加锁区间:
这里我们思考一下,如果范围匹配的值并不存在,那么会是什么情况呢?
即事务 A 执行如下语句,其中 id 为 5 的记录是不存在的。
执行 show engine innodb status\G; 查看锁信息如下图所示。
可以看到,其实加了 2 个 Next-Key 锁,锁的范围应该是 (2, 50) 和 [50, + 无穷)。
此时事务 B 执行下面命令,应该都会阻塞。
由此我们可以得出结论:「聚簇索引 + 范围匹配」,会使用「记录锁 + 间隙锁 + Next-Key 锁」。
唯一二级索引 + 精确匹配
事务 A 执行下面命令:
执行 show engine innodb status\G; 查看锁信息如下图所示。
可以看到,其加的行级锁是 2 个记录锁,应该是 price = 10 这条索引记录的锁。
此时,如果在事务 B 执行下面命令:
执行之后,我们会发现事务 B 阻塞住了。
由此我们可以得出结论:唯一二级索引与聚簇索引非常类似,都只有一个唯一值,都是使用记录锁。
唯一二级索引 + 范围匹配
事务 A 执行下面命令:
执行 show engine innodb status\G; 查看锁信息如下图所示。
可以看到,事务 A 一共有 5 个行锁,其中 3 个 Next-Key 锁, 2 个记录锁。大致可以猜测出两个记录锁分别是 price 为 30 和 60 的记录锁。3 个 Next-Key 锁则是 (10, 30)、(30,60)、(60, 正无穷)三个范围。
为了验证我们上面的结论,我们在事务 B 执行下面命令,每条 SQL 都会阻塞住:
执行之后,我们会发现事务 B 阻塞住了。
由此我们可以得出结论:「唯一二级索引 + 范围匹配」,会使用「记录锁 + 间隙锁 + Next-Key 锁」。
普通二级索引 + 精确匹配
事务 A 执行下面命令:
执行 show engine innodb status\G; 查看锁信息如下图所示。
可以看到,其不仅有一个记录锁,还有一个间隙锁。这里可以猜测记录锁是 apple 索引的记录锁,而间隙锁则是 (负无穷,orange) 的间隙锁。
我们可在事务 B 执行如下命令验证一下:
之所以二级索引的精确匹配会有间隙锁,是因为二级索引可能匹配到多个。因此当匹配到一个的时候,会继续往后匹配,直到匹配到一个不符合的记录,随后就会以该不符合的记录(这里是 orange)作为值做一个间隙锁。
由此我们可以得出结论:「普通二级索引 + 精确匹配」,会使用「记录锁 + 间隙锁 + Next-Key 锁」。
普通二级索引 + 范围匹配
事务 A 执行下面命令:
执行 show engine innodb status\G; 查看锁信息如下图所示。
从上图可以看到起一共有 2 个记录锁,3 个 Next-Key 锁。其中 2 个记录锁应该是 orange 和 perl 两个记录,3 个 Next-Key 锁,应该是 (apple, orange]、[orange, perl)、[perl, 正无穷)。
我们可在事务 B 执行如下命令验证一下:
可以看到「普通二级索引 + 范围匹配」与「普通二级索引 + 精确匹配」结果是类似的。
我们可以得出结论:「普通二级索引 + 范围匹配」,会使用「记录锁 + 间隙锁 + Next-Key 锁」。
总结
我们做了这么多个测试,虽然有 3 种索引类型(聚簇索引、唯一二级索引、普通二级索引)和 2 种匹配类型(精确匹配、范围匹配),它们两两组合可以得出 6 种情况,再加上查询的值是否存在,可能有更多的可能性。但是我们发现它们的结构都非常类似,基本上都跟查找的记录是否存在,以及查找的记录是否是唯一的相关。
由此,我们大致可以得出结论:
- 如果查找的记录是唯一且存在的,那么只会使用记录锁,而不会使用间隙锁或 Next-Key 锁。
- 如果查找的记录不唯一或者不存在,那么就会使用 Next-Key 锁和间隙锁。
通过这次测试,我们大概知道了加锁的一些原则,但实际上 Innodb 的关于加锁的源码还是比较复杂的。有一篇文章讲得还是比较好的,本文可以说是做了一些简化,有兴趣的朋友可以自行阅读看看:完整版:Innodb 到底是怎么加锁的。
参考资料
- 完整版:Innodb 到底是怎么加锁的
- 【锁】MySQL 间隙锁 - 阿里云开发者社区
- MySQL next-key lock 加锁范围是什么?- SegmentFault 思否