MySQL中间隙锁的加锁机制_间隙锁怎么加-优快云博客

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

文章目录

背景

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

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

实验环境的准备

前置条件

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

MySQL的版本：5.7.24
事务的隔离级别：RR可重复读
测试使用存储引擎为innodb存储引擎

准备建表语句

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

序号	字段名称	字段类型	字段注释	索引类型
1	id	int	表的主键	聚簇索引
2	name	varchar	姓名	N/A
3	age	int	年龄	非唯一索引
4	phone	varchar	手机号	唯一索引
5	remark	varchar	备注信息	N/A

建表语句如下：

CREATE TABLE `userinfo` (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄，普通索引列',
  `phone` varchar(255) DEFAULT NULL COMMENT '手机，唯一索引列',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_userinfo_phone` (`phone`) USING BTREE COMMENT '手机号码，唯一索引',
  KEY `idx_user_info_age` (`age`) USING BTREE COMMENT '年龄，普通索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

准备初始化数据

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

INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (0, 'mayun', 20, '0000', '马云');
INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (5, 'liuqiangdong', 23, '5555', '刘强东');
INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (10, 'mahuateng', 18, '1010', '马化腾');
INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (15, 'liyanhong', 27, '1515', '李彦宏');
INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (20, 'wangxing', 23, '2020', '王兴');
INSERT INTO `userinfo`(`id`, `name`, `age`, `phone`, `remark`) VALUES (25, 'zhangyiming', 38, '2525', '张一鸣');

最后的测试环境

准备好表和初始化数据之后，我们的测试环境就准备好了，最后的测试环境如下：
在这里插入图片描述

间隙锁的结构

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

主键索引id列上的间隙锁结构如下图所示：
非唯一索引age列上的间隙锁结构如下所示：因为是非因为索引，所以索引中的值可以重复出现，所以在图中没有标记每一个间隙可能出现的值，用三个点代替显示。
唯一索引phone列上的间隙锁如下所示：

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

索引的结构

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

id主键索引结构
age非唯一索引结构
phone唯一索引结构

间隙锁加锁规则

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

查询过程中访问到的对象才会加锁。
加锁的基本单位是next-key lock（前开后闭）。
等值查询上MySQL的优化：索引上的等值查询，如果是唯一索引，next-key lock会退化为行锁，如果不是唯一索引，需要访问到第一个不满足条件的值，此时next-key lock会退化为间隙锁。
范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止。

实验部分

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

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

/*根据主键查询，给行增加X锁*/
select * from userinfo where id = 5 for update;

/*根据唯一索引列查询，给行增加X锁*/
select * from userinfo where phone = '5555' for update;

/*根据非唯一索引列查询，给行增加X锁*/
select * from userinfo where age = 23;

/*根据普通列查询，给行增加X锁*/
select * from userinfo where name = 'liuqiangdong';

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

/*查看正在运行的事务*/
select * from information_schema.innodb_trx;

/*查看当前的锁信息*/
select * from information_schema.innodb_locks;

/*查看锁等待的信息*/
select * from information_schema.innodb_lock_waits;

RR级别+主键索引列

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

/*根据主键查询，给行增加X锁*/
select * from userinfo where id = 5 for update;

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

不能删除id=5的这一行数据。id=5的行已经被当前事务给增加了X锁，所以其他事务将不能查询、修改、删除这一行数据。
不能新插入一个id=5的行。表中已经存在主键id=5的行了，所以其他事务不能再次增加一个id=5的行。所以就可以避免在id=5这个条件下再次查询的时候出现多行数据而产生幻读的现象。
不能修改id=5的这一行数据。id=5的行已经被当前事务给增加了X锁，所以其他事务将不能查询、修改、删除这一行数据。
不能把其他行的id值改为5。这一行是主键索引，也是唯一所以，它的值不能重复。所以其他事务不能把一个id!=5的行改为id=5的行。所以就可以避免在id=5这个条件下再次查询的时候出现多行数据而出现幻读的现象。

此时增加的锁也只有主键索引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锁的时候，这里解释一下为什么成功，为什么失败。

第5步中，是通过id=0这个条件去给id=0的这一行数据增加S共享锁，where条件中的id是主键列，也是一个唯一索引列，所以在查询的时候，可以直接通过主键索引定位到对应的行。所以直接找到了id=0的这一行，同时发现这一行上面没有其他X所存在，所以增加S锁成功。
第6步中，是通过phone=0000这个条件去给id=0的行增加S锁，这里之所以失败的原因如下：
1. phone是一个varchar类型的唯一索引，在给它赋值的时候，如果我们赋值不是varchar类型的数据，MySQL为了避免SQL直接出现错误，会尝试进行隐式转换，把数据库中的phone列使用函数转换为和你赋值的类型一致的数据再进行等值判断。
2. 对索引列使用函数操作，会导致查询的时候不走索引，索引对当前查询SQL语句失效了，此时的phone的索引就不会被使用，所以此时在查询数据的时候就是走全表扫描。
3. 在全表扫描的时候，扫描到第一个phone=0000的行并不会停止，因为此时phone列的唯一索引没有使用，所以要继续扫描，判断表中每一行的phone列的值是否为0000。根据前面说的加锁规则，在尝试加锁的时候，会对所有扫描过的对象增加对应的锁。所以在扫描到id=5的行的时候，尝试给这一行增加S锁。
4. 然而，id=5的这一行的索引记录，已经被左侧的事务给增加了一个X锁，因为S锁和X锁不能共存，所以此时给id=5的索引记录加S锁失败。
5. 所以步骤6就被阻塞住了，等超过事务默认的最大等待阈值就会退出。
第7步中，执行成功了的原因正式因为我们在给phone列赋值的时候，使用了正确的varchar类型的'0000'，所以在查询表中数据的时候，可以使用到唯一索引，直接定位到对应的主键索引上面的值，从而可以对id=0的索引记录加锁成功。不需要走全表扫描就可以找到对应的行。
第8步中，被阻塞的原因是因为它要给id=5的索引记录增加S锁，它可以通过主键索引直接定位到要加S锁的索引记录行，不需要走全表扫描。找到id=5的行后尝试给它加S锁，但是发现这个行已经被左侧的事务给增加了X锁。S锁和X锁不能共存，所以右侧的事务给id=5的行增加S锁失败。

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

RR级别+唯一索引列

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

/*根据唯一索引列查询，给行增加X锁*/
select * from userinfo where phone = '5555' for update;

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

先根据phone列上面的唯一索引找到索引值为’5555’索引记录，因为是唯一索引，所以找到5555的索引记录后就停止搜索了，索引树上只有一条5555的记录。
然后再根据这个索引记录上面存储的主键索引的值5去主键索引上面查找需要查询的行记录。
根据加锁规则中提到的：只会在扫描到的对象上增加锁。所以会在主键索引id=5的索引记录上增加X锁，也会在唯一索引phone='5555’的索引记录上增加一个X锁。

此时加锁的情况如下所示：
在这里插入图片描述

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

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

在第3步执行完成的之后，在非主键索引(非聚簇索引、二级索引)phone中的phone='5555’索引记录行增加了X锁，同时在主键索引(聚簇索引)id中id=5的索引记录行增加了X锁。
第4步中查询语句使用了覆盖索引的功能，我们只查询的phone这一列的值，在非主键索引上就包含了我们要查询的结果，所以这个查询不会去查询主键索引，只会在非主键索引上搜索查找。但是它增加S锁和X锁都失败了，说明这个phone='5555’索引记录上已经被增加了X锁。
在第5不执行完成后，左侧的事务就回滚了，所以在第3步中增加的两把X锁都被释放掉了。
所以在第6步和第7步中，再次尝试给phone='5555’的非主键索引记录行增加S锁或X锁的时候，都加锁成功了。
此时就已经说明了在通过非主键索引且是唯一索引列，在给表增加X锁的时候，除了会对主键索引对应的记录行增加X锁之外，还会在非聚簇索引的索引记录上增加X锁。
在第11步中，我们做了和第3不类似的操作，但是和第3步的区别在于：此时我们给表增加的是S锁而不是第3步中的X锁。
因为S锁和S锁是共享的，所以在右侧事务中的第12步中，给phone='5555’的索引记录增加S锁，成功。
因为S锁和X锁不能共存，所以在右侧事务中的第13步中，给phone='5555’的索引记录增X锁，加锁失败。而当左侧事务执行完第14步回滚事务的操作之后，此时phone列上面的phone值为’5555’的索引记录的S锁已经被释放，所以做右侧事务中，再次尝试给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锁。

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

RR级别+非唯一索引列

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

select * from userinfo where age = 23 for update;

加锁情况如下图所示：
在这里插入图片描述

这里需要说明几点：

根据非唯一索引加锁规则，此次增加的间隙锁有：(20,23]、(23,27]。对于非唯一索引来说，当扫描到最后一个边界age=27的时候，临键锁退化为间隙锁。所以此时的锁为间隙锁：(20,27)，不包含左右两边的边界值20和27。
行锁有：在age=23的非主键索引记录上有两把，因为有两个age=23索引记录。同时在id=5和id=20的主键索引记录上也有两把X锁，
只要当age的取值范围为(20,27)的时，任何记录行都不能插入成功。因为age上面的间隙锁阻止了数据的插入。
只要当age的值不属于(20,27)，且不等于20也不等于27的时候，任何记录行都可以插入成功。前提是待插入的数据行中的id的值符合主键id的唯一性和phone的值符合phone唯一索引的唯一性。
当age=20的时候，并不是说，id的值可以使用任意表中不存在的主键值作为待插入的主键值。此时id的值必须为小于0的数，不能大于0。举例说明：当待插入的数据中的age=20, id=1，那么在非唯一索引age上面存这一行索引记录的时候，会在上图中的蓝色方框20后面在创建一个age=20的记录，还需要为这个非唯一索引下面村上主键索引(聚簇索引)的值：1，此时的1比表中已经存在的主键值0大，所以它会放在0的后面，如上图中所示此时的1会占用被锁住的索引间隙。所以此时不能插入成功。能插入成功的为：age=20, id=-1或age=20, id=-2这样的组合。
当age=27的时候，同理，id的值需要大于15才可以插入成功。比如：age=27, id=16或者age=27, id=17这样的组合。如果此时插入一个age=27, id=14的数据行，你设想一下会怎么存放这个索引记录，索引中已经存在一个age=27, id=15的记录了，此时再来一个age=27, id=14的记录，那这个id=14的记录必须排在id=15的前面。而此时id=14的位置是被间隙锁锁住的。所以它是插入失败的。
上面凡是红色的背景或红色箭头的区域都是有锁的区域，也就是这样的数据不能被插入成功。

实验前的预测：
在这里插入图片描述

实验截图：
在这里插入图片描述

实验使用到的SQL语句：

insert into userinfo (id, name, age, phone, remark) values(1, 'name1', 20, 'phone1', 'id=1导致被阻塞，改为id<1的值就可以成功');
insert into userinfo (id, name, age, phone, remark) values(-1, 'name-1', 21, 'phone-1', 'age=21导致被阻塞，改为age<21或age>27就可以成功');
insert into userinfo (id, name, age, phone, remark) values(1, 'name1', 21, 'phone1', 'age=21导致被阻塞，只要20<age<27，不管其他列是什么值，都会被阻塞');
insert into userinfo (id, name, age, phone, remark) values(-1, 'name-1', 20, 'phone-1', '可以插入成功');
insert into userinfo (id, name, age, phone, remark) values(14, 'name14', 27, 'phone14', 'id=14导致被阻塞，改为id>15就可以插入成功');
insert into userinfo (id, name, age, phone, remark) values(16, 'name16', 26, 'phone16', 'age=26导致被阻塞，改为age<20或age>26就可以成功');
insert into userinfo (id, name, age, phone, remark) values(14, 'name14', 26, 'phone14', 'age=26导致被阻塞，只要20<age<27，不管其他列是什么值，都会被阻塞');
insert into userinfo (id, name, age, phone, remark) values(16, 'name16', 27, 'phone16', '可以插入成功');

RR级别+普通字段列

如果是使用如下的一个普通字段来加锁，会是什么情况呢？

select * from userinfo where name = 'liuqiangdong' for update;

此时加锁的情况是这样的：因为name是一个普通的列，上面没有任何索引可以使用，所以根据这个条件进行搜索数据的时候，会进行全表扫描的操作，当搜索到第一个name='liuqiangdong’的数据行的时候，不会马上停下来，因为后面可能还有很多个name='liuqiangdong’的数据行，所以要进行全表扫描。根据前面我们说的加锁规则，凡是扫描到的对象都要加锁，所以此时全表中的每一个主键索引记录上都有一个X锁。同时为了防止幻读的凡是，会对这个表上所有的主键索引的间隙也都增加上间隙锁。

加锁情况示意图如下：
在这里插入图片描述