MySQL中间隙锁的加锁机制

微信搜索“coder-home”或扫一扫下面的二维码，关注公众号，第一时间了解更多干货分享，还有各类视频教程资源。扫描它，带走我

间隙锁是MySQL在RR可重复读隔离级别下用来修复幻读才引入的一种锁，间隙锁也只有在RR可重复读隔离级别下才会存在，如果是在RC读已提交隔离级别下，是没有间隙锁的存在的。另外，我们也知道，幻读这种现象也只有在当前读的时候才会发生，在一致性快照读的情况下是没有幻读现象的。

那么间隙锁到底是怎么样工作的？它是如何保证在当前读的时候，不会出现幻读现象的呢？接下来让我们一起剖解分享一下间隙的加锁的机制是怎么样的。

接下来的实验是在如下的环境下开始的：

下面的实验使用的userinfo表结果如下表格所示：其中用户的年龄age字段是可能存在多个用户的年龄相同的情况，所以这个age列上有非唯一索引；而所有用户的手机号码不会重复，所以这个phone列上面有一个唯一索引；姓名name列和备注remark列上面没有任何索引就是两个普通的字段。

建表语句如下：

下面实验中使用到的测试数据如下：

准备好表和初始化数据之后，我们的测试环境就准备好了，最后的测试环境如下：

针对上面我们准备表结构和插入的测试数据，目前userinfo表中有三个间隙锁，分别在主键id列上，非唯一索引age列，唯一索引phone列。它们的间隙锁的分布情况分别如下。根据各个索引列上面的值，把索引切分为不同的区间段。

我们要知道，MySQL中的行锁和间隙锁是锁定就是对应的索引。行锁锁定的行所在的主键索引，非主键索引列上面的锁也是锁定对应的非主键索引。间隙锁也是锁定索引，他们不是锁定行，也不是锁定某个列，是锁定对应的索引。

上面的表插入数据之后，在id主键索引上会有一个B+Tree的索引结构。在age非唯一索引和phone唯一索引两列上，会有两个B+Tree索引结构。他们的结构如下图所示：

id主键索引结构

age非唯一索引结构

phone唯一索引结构

这里先简单总结一下间隙锁加锁的一些规则，然后我们根据规则去逐步验证这些规则。

我们针对以下几个SQL语句来分析一下具体间隙锁的加锁的范围是什么。这几个SQL分别使用不同的列来作为where条件来筛选表中第2条数据。

根据where条件中各个列的类型，我们可以分为如下4类SQL。下面的四个语句都会自动给表增加上对应的行锁之外，如果有必要增加对应的间隙锁，也会增加上间隙锁来避免幻读的发生，而如果不需要间隙锁就可以避免幻读的发生，那么MySQL就不会自动增加上对应的间隙锁只要对应的行锁就可以了。

实验的过程中，还需要密切观察下面三个表中的内容，这里记录的MySQL在执行期间正在允许的事务、锁、锁等待等信息。从这里三张表中，可以看到每一个阻塞是因为什么阻塞的，被哪些锁阻塞的。

在可重复读隔离级别下，通过主键索引去查询数据尝试增加X锁的时候，使用如下的SQL语句

此时会在主键索引上索引值为5的记录上增加X锁，此时不需要使用其他间隙锁就可以避免幻读的发生。因为主键索引是唯一索引，当锁住这一行数据后，其他事务将不能做如下操作：

此时增加的锁也只有主键索引id=5这一个行锁，锁结构如下：

综上几点可以确定，当使用主键索引进行查询数据增加X锁的时候，是可以避免幻读的发生，此时不需要间隙锁的参与就可以避免幻读。其他事务可以正常的对userinfo表进行除id=5之外增删改查操作。比如插入一个id=6的数据行，删除id=0的数据行，修改id=20的行等操作。

实验截图：

通过如下的实验，可以看出，在左侧的事务给表userinfo增加了id=5的行锁之后，右侧的事务仍然可以对表中其他数据行和间隙进行增删改查。说明左侧的事务只增加了id=5的行锁，没有间隙锁的存在。

下面我们再来看一个因为索引失效而走全表扫描的例子，与此同时下面的这个例子也能从一定程度上说明左侧的事务只对id=5这一行增加了一个X锁，没有其他间隙锁或行锁的存在。

上面截图中的第5，6，7，8在尝试加S锁的时候，这里解释一下为什么成功，为什么失败。

所以：在我们平时开发的时候，写SQL语句的时候，一定要格外的注意给where条件后面的字段赋值的时候，一定要根据对应的字段类型进行赋值。切不要让MySQL使用隐式转换的功能导致索引失效而走全表扫描。这样我们会有一种错觉：为啥我加了索引了，查询还是很慢。你要保证：增加了索引，并且SQL语句查询的时候真正使用到了索引，这样才会对你的SQL性能有提升。

当我们尝试通过一个唯一索引列去给表增加X锁的时候，会使用如下的SQL，它会给表增加那些锁呢？

此时查询数据的过程是这样的：先查唯一索引，然后再回表查询主键索引，然后从主键索引上返回查询的结果。具体流执行流程如下：

此时加锁的情况如下所示：

疑问1：如何证明通过select * from userinfo where phone='5555' for update增加X锁的时候，在索引列phone='5555’的索引记录上增加了X锁了？通过如下实验可以证明这个结论：

针对上图中的各个实验步骤，简单做如下说明。

疑问2：为什么要在主键索引id=5这个记录上也增加X锁？如果并发的一个SQL，是通过主键索引来删除数据，SQL语句为：delete from userinfo where id = 5;。 此时，如果update语句没有将主键索引上的记录加锁，那么并发的delete就会感知不到前面的update语句的存在，违背了同一记录上的更新/删除需要串行执行的原则。

疑问3：通过疑问2，我们知道在唯一索引phone上面增加了X锁，那么我们前面的实验一中，通过select * from userinfo where id = 5 for update增加X锁的时候，有没有在索引列phone='5555’的索引记录上增X锁呢？

我们的实验截图如下：

注意：目前我只能确定phone='5555’的索引记录上一定是没有X锁，但是有没有S锁目前还不能确定，因为如果通过id=5 for update加X锁后，如果在phone='5555’的非主键索引的索引记录上增加一个S锁，在右侧的事务中，也是可以获得S锁的，因为S锁和S锁是可以共存的。事实上，我们也确实在右侧的事务中获得了phone='5555’索引记录上的S锁。索引我不确定在左侧的事务中，是否给phone='5555’的索引记录上增加了S锁。

如果大家有办法可以证明这一点，也希望大家给我留言你的证明方式。

通过非唯一索引列age来增加X锁的SQL语句如下，当执行完成如下语句之后，表userinfo会有哪些锁产生呢？

加锁情况如下图所示：

这里需要说明几点：

实验前的预测：

实验截图：

实验使用到的SQL语句：