详解 MySQL 中的记录锁、间隙锁、临键锁
前言
上篇文章我们介绍了 MySQL 中的各种锁知识，其中最重要的属于行锁范围的 记录锁（Record Locks）、间隙锁（Gap Locks）、临键锁（Next-Key Locks）由于细节较多，所以放到本篇文章单独详解。
注意 RR 级别下才有间隙锁、临键锁，所以本篇文章使用数据库环境为 MySQL 8.0.32 InnoDB 引擎，RR 隔离级别。
事务、锁信息的查看
为了更详细的了解一条 SQL 语句具体被上了什么锁，MySQL 给我们提供了三张内置表，用来记录事务的相关信息以及与事务绑定的锁信息。我们以下面的示例数据分别查看
CREATE TABLE `cash_repay_apply` (
`id` int NOT NULL AUTO_INCREMENT,
`memberId` int NOT NULL,
`repayNo` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 初始化数据
INSERT INTO `tcbiz_ins`.`cash_repay_apply` (`id`, `memberId`, `repayNo`) VALUES (1, 10000, 'TQYHKN202405302909821');
INSERT INTO `tcbiz_ins`.`cash_repay_apply` (`id`, `memberId`, `repayNo`) VALUES (4, 10001, 'TQYHKN202405302909822');
INSERT INTO `tcbiz_ins`.`cash_repay_apply` (`id`, `memberId`, `repayNo`) VALUES (6, 10002, 'TQYHKN202405302909823');
分别在两个客户端窗口 A/B 执行以下 SQL
BEGIN;
select * from cash_repay_apply where id = 6 for update ;
然后观察以下三张表记录
- information_schema.INNODB_TRX
- performance_schema.data_locks
- performance_schema.data_locks_wait
INNODB_TRX
information_schema.INNODB_TRX 这张表存储了当前的所有事务信息。包括事务id、开始时间、事务状态、隔离级别等。
执行以上 SQL 之后查看数据为下表（列举部分字段）
| trx_id（事务id） | trx_state（事务状态） | trx_started（开始时间） | trx_requested_lock_id（正在等待的锁id） | trx_isolatiion_level（隔离级别） |
|---|---|---|---|---|
| 25188 | LOCK WAIT | 2024-05-29 14:08:13 | 140289646597584:210:4:4:140290107440672 | REPEATABLE READ |
| 25187 | RUNNING | 2024-05-29 14:08:11 | NULL | REPEATABLE READ |
trx_state = RUNNING 表明事务正在运行，trx_state = LOCK WAIT 则表明事务正在等待获取锁。
data_locks
performance_schema.data_locks 记录了当前所有事务中的锁信息。包括持有的锁、等待获取的锁。
执行以上 SQL 之后查看当前数据库的锁信息如下（列举部分字段）。
| ENGINE_LOCK_ID | ENGINE_TRANSACTION（事务id） | LOCK_TYPE（锁类型） | INDEX_NAME(索引列) | LOCK_MODE（锁模式） | LOCK_STATUS（锁状态） | LOCK_DATA（锁定的数据） |
|---|---|---|---|---|---|---|
| 140289646597584:1272:140290106098864 | 25188 | TABLE | PRIMARY | IX | GRANTED | NULL |
| 140289646597584:210:4:4:140290107440672 | 25188 | RECORD | PRIMARY | X,REC_NOT_GAP | WAITING | 6 |
| 140289646595208:1272:140290106095792 | 25187 | TABLE | PRIMARY | IX | GRANTED | NULL |
| 140289646595208:210:4:4:140290107426848 | 25187 | RECORD | PRIMARY | X,REC_NOT_GAP | GRANTED | 6 |
这张表的信息很重要。其中 LOCK_TYPE 代表当前锁是行级别还是表级别。当前 LOCK_TYPE = RECORD 时，LOCK_MODE 有以下几种值
- LOCK_MODE = X ：代表临键锁
- LOCK_MODE = X,GAP ：代表间隙锁
- LOCK_MODE = X,REC_NOT_GAP ： 代表记录锁
- LOCK_MODE = X,GAP,INSERT_INTENTION： 代表插入意向锁（正在申请被间隙锁锁住的区间的插入意向锁）
- LOCK_MODE = X,INSERT_INTENTION： 代表插入意向锁（正在申请被临键锁锁住的区间的插入意向锁）
data_locks_wait
我们在 performance_schema.data_locks_wait 表可以看到等待中的锁的详细信息。
执行以上 SQL 之后，查看 performance_schema.data_locks_wait 表记录。（列举部分字段）
| REQUESTING_ENGIN_LOCK_ID（正在请求锁的锁id） | REQUESTING_ENGINE_TRANSACTION_ID（请求的事务id） | BLOCKING_ENGINE_LOCK_ID（当前正持有锁的锁id） |
|---|---|---|
| 140289646597584:210:4:4:140290107440672 | 25188 | 140289646595208:210:4:4:140290107426848 |
可以看到 B 窗口的事务 25188 正在等待获取 事务id = 25187 持有的锁。
准备测试数据
我们准备一些测试数据来为验证三种不同的行锁做准备。初始化数据
CREATE TABLE `cash_repay_apply` (
`id` int NOT NULL AUTO_INCREMENT,
`member_id` int NOT NULL,
`repay_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `member_id` (`member_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 数据初始化
INSERT INTO `tcbiz_ins`.`cash_repay_apply` (`id`, `member_id`, `repay_no`) VALUES (1, 1, 'TQYHKN20242231038123');
INSERT INTO `tcbiz_ins`.`cash_repay_apply` (`id`, `member_id`, `repay_no`) VALUES (3, 2, 'TQYHKN20242231038112');
INSERT INTO `tcbiz_ins`.`cash_repay_apply` (`id`, `member_id`, `repay_no`) VALUES (6, 20, 'TQYHKN20242231038100');
INSERT INTO `tcbiz_ins`.`cash_repay_apply` (`id`, `member_id`, `repay_no`) VALUES (9, 3, 'TQYHKN20242231038102');
INSERT INTO `tcbiz_ins`.`cash_repay_apply` (`id`, `member_id`, `repay_no`) VALUES (20, 25, 'TQYHKN2024223103810z');
INSERT INTO `tcbiz_ins`.`cash_repay_apply` (`id`, `member_id`, `repay_no`) VALUES (21, 20, 'TQYHKN2024223103810x');
了解了三张内置表的作用以及相关重要字段，接下来我们根据不同的查询类型分析 SQL 语句具体加了什么锁。注意以下的所有测试都以锁定读的方式进行。
唯一索引查询
等值查询
使用唯一索引等值查询时，被查询的索引值存不存在，会决定对索引加什么样的锁。
记录存在
执行下面的 SQL 查询一条存在的记录
BEGIN;
select * from cash_repay_apply where id = 6 for update ;
查看 data_locks 表，可以看到
| LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|
| TABLE | IX | GRANTED | NULL |
| RECORD | X,REC_NOT_GAP | GRANTED | 6 |
意向锁不关注，可以看到行锁 LOCK_MODE = X,REC_NOT_GAP 这代表记录锁。LOCK_DATA = 6，锁定的索引是 id = 6 ，没毛病。
记录不存在
我们再来查询一条记录不存在的数据，执行以下 SQL
BEGIN;
select * from cash_repay_apply where id = 7 for update ;
查看 data_locks 表可以看到
| 窗口 | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|
| 窗口A | TABLE | IX | GRANTED | NULL |
| 窗口A | RECORD | X,GAP | GRANTED | 9 |
可以看到 LOCK_MODE = X,GAP ，这代表间隙锁。再看 LOCK_DATA = 9 ，那这个间隙锁锁定的范围是啥？不知道，表里的信息太过于简单，实践是检验真理的唯一标准。我们另开一个 窗口B 分别插入不同的 主键id，来测试这个间隙范围。
先插入主键为 id = 7 的记录
BEGIN;
INSERT into cash_repay_apply VALUES (7,3,'TQYHKTEST1234');
执行后发现事务一直等待，id = 7 的记录无法插入。我们再查询 data_locks 表发现行锁多了两条记录
| 窗口 | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|
| 窗口 B | TABLE | IX | GRANTED | NULL |
| 窗口 B | RECORD | X,GAP,INSERT_INTENTION | WAITING | 9 |
| 窗口 A | TABLE | IX | GRANTED | NULL |
| 窗口 A | RECORD | X,GAP | GRANTED | 9 |
再插入主键为 id = 8 的记录发现得到的结果和插入 id = 7 的记录是一样的，也会阻塞，锁信息也相同。
我们再尝试插入 id = 5 ，id = 10 的记录发现均可以成功插入。
BEGIN;
INSERT into cash_repay_apply VALUES (5,3,'TQYHKTEST1234');
INSERT into cash_repay_apply VALUES (10,3,'TQYHKTEST1234');
到这里我们基本上可以得出结论，当前窗口 A select * from cash_repay_apply where id = 7 for update 这条语句加的间隙锁范围是 (6,9)。
为了精确，我们再插入 id = 6 和 id = 9 的记录，这两条主键记录是表中已存在的，执行以下 SQL 之后数据库会报错 Duplicate entry '6' for key cash_repay_apply。
BEGIN;
INSERT into cash_repay_apply VALUES (6,3,'TQYHKTEST1234');
INSERT into cash_repay_apply VALUES (9,3,'TQYHKTEST1234');
记录不存在（上界）
我们再来测试另一种场景。执行下面的 SQL
BEGIN;
select * from cash_repay_apply where id = 23 for update ;
表中最大已存在的索引值为 id = 21，我们等值锁定读 id = 23 。查看 data_locks
| 窗口 | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|
| 窗口 A | TABLE | IX | GRANTED | NULL |
| 窗口 A | RECORD | X | GRANTED | supremum pseudo-record |
这里 LOCK_MODE = X 表明加了临键锁。但是比较奇怪的是 LOCK_DATA = supremum pseudo-record。我们来看看官网 Next-Key Locks 的描述
For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.
翻译
对于最后一个间隙，临键锁锁定最大索引值到正无穷这个区间，supremum pseudo-record 代表伪记录，可以理解成代表 +∞ 的一个虚拟值。因此对于这种特殊情况的临键锁来说，锁定的其实是 （最大索引，+∞）。
虽然 LOCK_MODE = X 显示的是临键锁，但是实际作用上，它是一个间隙锁的作用，锁定间隙为 (21,+∞)。篇幅有限，这里就不用插入语句来演示了。
记录不存在（下界）
上面我们测试了唯一索引等值查询条件值大于表中已存在索引的最大值（这里读起来可能会拗口）。我们再来测试唯一索引等值查询条件值小于已存在索引的最小值。执行下面的 SQL
BEGIN;
select * from cash_repay_apply where id= -1 for update ;
然后查看 data_locks 表
| 窗口 | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|
| 窗口 A | TABLE | IX | GRANTED | NULL |
| 窗口 A | RECORD | X,GAP | GRANTED | 1 |
可以看到这里加的是一个间隙锁，锁定间隙为 (-∞,1)。
结论
当使用唯一索引等值查询数据库中不存在的记录，此时会加间隙锁，间隙左区间是当前查询条件值在表中索引值往小寻找的第一个存在的值（没找到就是 -∞）。间隙右区间是当前查询条件值在表中索引值往大寻找的第一个已存在的值（没找到就是 +∞）。
存在一种特殊情况是当间隙范围是表中最大索引值到 +∞ 时，此时 data_locks 中标记是临键锁，但实际上作用和间隙锁是一致的。
范围查询
条件开区间
使用唯一索引进行范围查询时。查询条件是否包含 = 加的锁也会不同。执行下面的 SQL
BEGIN;
select * from cash_repay_apply where id > 6 and id < 20 for update ;
然后观察 data_locks 表
| 窗口 | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|
| 窗口 A | TABLE | IX | GRANTED | NULL |
| 窗口 A | RECORD | X | GRANTED | 9 |
| 窗口 A | RECORD | X,GAP | GRANTED | 20 |
可以看到由于 6~20 之间存在一个数据库已存在的索引 id = 9 。这里 (6,9] 是临键锁，(9,20) 是间隙锁。分别执行下面两条插入语句可以测试间隙锁、临键锁的范围
-- 处于锁定范围，无法插入
BEGIN;
INSERT into cash_repay_apply VALUES (7,3,'TQYHKTEST1234');
INSERT into cash_repay_apply VALUES (12,3,'TQYHKTEST1234');
-- 不在锁定范围，可以插入
INSERT into cash_repay_apply VALUES (5,3,'TQYHKTEST1234');
INSERT into cash_repay_apply VALUES (23,3,'TQYHKTEST1234');
再执行一条 update 语句修改 id = 9 的这条记录，测试临键锁锁定索引 id = 9 本身
-- 阻塞，等待获取id = 9 的记录锁
BEGIN;
update cash_repay_apply set member_id = 6 where id = 9;
当使用唯一索引进行范围查询，并且条件范围是开区间（不包含 ">=" 和 "<="）查询，会对每一个扫描到的值加临键锁。
条件存在闭区间
执行下面的 SQL
BEGIN;
select * from cash_repay_apply where id >= 6 and id < 20 for update ;
查询 data_locks 记录
| 窗口 | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|
| 窗口 A | TABLE | IX | GRANTED | NULL |
| 窗口 A | RECORD | X,REC_NOT_GAP | GRANTED | 6 |
| 窗口 A | RECORD | X | GRANTED | 9 |
| 窗口 A | RECORD | X,GAP | GRANTED | 20 |
上面的 SQL 加锁信息为
- 记录锁：
id = 6 - 临键锁：
(6,9] - 间隙锁：
(9,20)
再执行下面的 SQL
BEGIN;
select * from cash_repay_apply where id >= 6 and id <= 20 for update ;
当条件范围两边都是闭区间时，再查询 data_locks 表
| 窗口 | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|
| 窗口 A | TABLE | IX | GRANTED | NULL |
| 窗口 A | RECORD | X,REC_NOT_GAP | GRANTED | 6 |
| 窗口 A | RECORD | X | GRANTED | 9 |
| 窗口 A | RECORD | X | GRANTED | 20 |
可以看到在条件是 < 和 <= 时，如果 <= 的 "=" 命中存在的索引，那么最后一段锁就是临键锁，未命中就是间隙锁。
间隙锁的右边界依然是找到大于等于条件 (>=20) 的第一个已存在的索引值（如果大于等于条件 (>=20) 的第一个已存在的索引值不存在，那么间隙右边界就是 supremum pseudo-record ，此时是临键锁）。
对于 >= 的查询，如果 "=" 条件命中已存在的索引，那么该索引会被加上记录锁。其他被扫描的索引会加临键锁。
我们再验证一种只有 <= 的情况，执行以下 SQL
BEGIN;
select * from cash_repay_apply where id <= 212 for update ;
查看 data_locks 表
| 窗口 | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|
| 窗口 A | TABLE | IX | GRANTED | NULL |
| 窗口 A | RECORD | X | GRANTED | supremum pseudo-record |
| 窗口 A | RECORD | X | GRANTED | 1 |
| 窗口 A | RECORD | X | GRANTED | 3 |
| 窗口 A | RECORD | X | GRANTED | 6 |
| 窗口 A | RECORD | X | GRANTED | 9 |
| 窗口 A | RECORD | X | GRANTED | 20 |
| 窗口 A | RECORD | X | GRANTED | 21 |
可以看到全是临键锁，由于表中不存在大于条件值 212 的索引，所以最后一段是到正无穷的临键锁。
那么我们再验证一种只有 >= 的情况，执行以下 SQL
BEGIN;
select * from cash_repay_apply where id >= 9 for update ;
观察 data_locks 表
| 窗口 | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|
| 窗口 A | TABLE | IX | GRANTED | NULL |
| 窗口 A | RECORD | X,REC_NOT_GAP | GRANTED | 9 |
| 窗口 A | RECORD | X | GRANTED | supremum pseudo-record |
| 窗口 A | RECORD | X | GRANTED | 20 |
| 窗口 A | RECORD | X | GRANTED | 21 |
可以看到除了条件值 id = 9 为记录锁，其他都是临键锁。
结论
使用唯一索引进行范围查询条件，所扫描到的索引都会被加临键锁，以下几种情况除外。
>=查询中"="命中已存在的索引，该索引会被加记录锁<查询，数据库中小于条件的第一个已存在索引到大于条件的第一个已存在索引之间会被加上间隙锁。
普通索引查询
当使用普通索引查询的时候，加锁规则也和等值、范围查询有关系。
等值查询
记录不存在
执行以下 SQL
BEGIN;
select * from cash_repay_apply where member_id = 19 for update ;
查询 data_locks 表记录
| 窗口 | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|---|
| 窗口 A | NULL | TABLE | IX | GRANTED | NULL |
| 窗口 A | member_id | RECORD | X,GAP | GRANTED | 20,6 |
注意此时我们发现与唯一索引查询时不同的是 LOCK_DATA = 20,6。上面使用主键索引时这里的 LOCK_DATA 都是单个值，现在多了一个值用逗号隔开，不难发现这个 6 正是 member_id = 20 的普通索引对应的 主键 id。
我们根据 LOCK_DATA 中的 20，结合前面的结论，可以知道这里 member_id 的间隙锁范围是 (3,20)。然后我们执行 SQL 插入 member_id 在 (3,20) 之间的值发现都会被阻塞，这是意料之中的结果。
下面我们再执行以下 SQL 插入 member_id = 3 和 member_id = 20 的值。按道理说这不属于间隙锁 (3,20) 的范围之内，应当不会被阻塞。
BEGIN;
-- 下面的 SQL 不会阻塞
INSERT into cash_repay_apply(id,member_id,repay_no) VALUES (8,20,'TQYHKTEST1234');
INSERT into cash_repay_apply(id,member_id,repay_no) VALUES (11,20,'TQYHKTEST1234');
INSERT into cash_repay_apply(id,member_id,repay_no) VALUES (7,3,'TQYHKTEST1234');
INSERT into cash_repay_apply(id,member_id,repay_no) VALUES (4,3,'TQYHKTEST1234');
-- 下面的 SQL 会阻塞
INSERT into cash_repay_apply(id,member_id,repay_no) VALUES (5,20,'TQYHKTEST1234');
INSERT into cash_repay_apply(id,member_id,repay_no) VALUES (2,20,'TQYHKTEST1234');
INSERT into cash_repay_apply(id,member_id,repay_no) VALUES (10,3,'TQYHKTEST1234');
INSERT into cash_repay_apply(id,member_id,repay_no) VALUES (12,3,'TQYHKTEST1234');
但是实际情况却是有些可以成功，有些则会阻塞。我们知道普通索引也是建立在一颗索引树上，只不过这颗索引树的叶子结点存储的是 主键 id 的值。我们画出当前测试数据的 member_id 索引树如下图。
我们发现上面插入 member_id = 3 的阻塞的 SQL，都是主键 id > 9 的。member_id = 20 的阻塞的 SQL ，都是主键 id < 6 的。从这里不难发现，这里真正的间隙锁范围是 (member_id = 3 & id = 9,member_id = 20 & id = 6)。
也就是说当插入进来的记录索引如果在 B+TREE 上分裂之后位于下图中两条红色竖箭头之间的位置，都是属于间隙锁位置，无法插入。
到这里我们应该能理解了，为什么 member_id 间隙锁的范围是 (3,20)，插入 member_id = 3 和 member_id = 20 的数据有时候还会阻塞，因为这还得再评估 主键id 的值。
记录存在
执行以下 SQL
BEGIN;
select * from cash_repay_apply where member_id = 3 for update ;
查看 data_locks 表
| 窗口 | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|---|
| 窗口 A | NULL | TABLE | IX | GRANTED | NULL |
| 窗口 A | member_id | RECORD | X | GRANTED | 3,9 |
| 窗口 A | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 9 |
| 窗口 A | member_id | RECORD | X,GAP | GRANTED | 20,6 |
这条 SQL 执行后事务加的行锁的情况是
member_id索引(2,3]的临键锁PRIMARY主键索引id = 9的记录锁member_id索引(9,20)的间隙锁
然后我们再结合 member_id 索引树大致图来看
其实原理是一样的，只要插入的索引经过 B+TREE 分裂后位置处于间隙范围内，都不允许插入。除了间隙锁外，对 (2,3] 的临键锁限制了 member_id = 3 的修改，主键记录锁 id = 9 限制了这条记录的修改。
范围查询
使用非唯一索引范围查询，执行以下 SQL
BEGIN;
select * from cash_repay_apply where member_id > 0 and member_id < 26 for update ;
查看 data_locks 表记录
可以看到 非唯一索引范围查询，所有扫描到的 member_id 索引都会被加临键锁，所指向的 主键 id 都会被加记录锁。
未使用索引查询
执行以下 SQL
BEGIN;
select * from cash_repay_apply where repay_no = '1' for update ;
我们知道测试表中 repay_no 是没有索引的，查询 data_locks 记录
可以看到表中所有的主键索引都被加了临键锁。
当使用非索引字段锁定读，或者锁定读未走到索引的情况下会给所有的主键加上临键锁，这就相当于表锁了。
结语
我知道这篇文章很乏味，很无聊，也很难读。别说读者，就连我自己写完之后再次读文章中的语句都会很迷惑。但是没有办法，这种实践性的东西场景又多，实在难归纳出一套能够覆盖所有场景的结论。总之自己多用实际 SQL 实践就能理解了。
另外其实对于这些锁的细节我们作为研发来说几乎是用不到的，至少工作快五年的我确实没有用过，平时都是写业务代码，顶多业务代码中会用到一个 SELECT ... FOR UPDATE 锁定读。最近也是公司在裁员，所以没事复习复习相关技术栈。分享一下心得。
如果这篇文章对你有帮助，记得点赞加关注！你的支持就是我继续创作的动力！
转载自:https://juejin.cn/post/7374783392674643994