间隙锁加锁算法分析
引言
在进行死锁分析时，如果看 MySQL 的死锁日志云里雾里，那可能是我们对 MySQL 的加锁规则不甚了解，从而感觉分析无从下手。只能看代码，改 SQl 碰运气。有一句话说的好：我也不知道为什么这么改就好了，反正它已经能工作了！
本文主要基于网上相关文章整理验证而出，目前 MySQL 最新版是 8.3，本文相关 Case 在下列环境中验证通过：
- MariaDB 10系列：<= 10.4.33 (2024/02/07)，对标 MySQL 5.7
- MySQL 5.x系列：<= MySQL 5.7.44（2023/12/16)
- MySQL 8.x系列：<= MySQL 8.0.13（2018/12/29）
PS：网上比较好的教程是丁奇 2018年《MySQL 实战 45 讲》，经确认，MySQL 超过 8.0.13 版本后，其中部分 Case 失效（案例三：主键索引范围锁）。生产环境中使用 MySQL 5.7 比较多，但是 8.0.13 中的锁分析功能更好用。故为了更好的理解和分析间隙锁，本文结果输出使用 MySQL 8.0.13，经初步验证，其加锁表现和 MySQL 5.7 以及 MariaDB 10.4.33一致。
锁类型
任何语句执行前，都需要获取表级意向锁以表明后续要获取什么类型的行锁：
- 意向共享锁（IS）：表锁，指示事务稍后需要对表中的行加共享锁（S），获取行锁（S）之前，必须先获取 IS 表锁
- 意向独占锁（IX）：表锁，指示事务稍后需要对表中的行加独占锁（X），获取行锁（X）之前，必须先获取 IX 表锁
InnoDB 是行锁引擎，针对某一行加的锁只有2种：
- 共享锁（S）：行锁，允许持有该锁的事务读取一行，如果此时其他事务对该行请求 S 锁成功，请求 X 锁被阻塞
- 独占锁（X）：行锁，允许持有该锁的事务更新或删除行，此时不管其他事务对该行请求 S 锁还是 X 锁都被阻塞
下文介绍的纪录锁（Record Locks）、间隙锁（Gap Locks）、临键锁（Next-Key Locks）和插入意向锁（Insert Intention Locks）都是具体的锁算法，注意区分。
加锁算法
- 临键锁（Next-Key Locks）：临键锁是索引记录上的记录锁（Record Locks）和索引记录之前的间隙上的间隙锁（Gap Locks）的组合。InnoDB 执行行级锁定的方式是，当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁（S）或独占锁（X），在索引记录之前的间隙上设置间隙锁（Gap Locks），记录锁可以防止其他事务更新或删除行，间隙锁可以防止其他事务在记录之前插入。假设索引包含值 10、11、13、20，该索引可能的 Next-Key 锁涵盖以下区间，其中圆括号表示排除区间端点，方括号表示包含端点（左开由闭）：
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
- 记录锁（Records Locks）：始终锁定索引上的一条记录，即使表中的列表没有索引。对于这种情况，InnoDB 创建一个隐藏的聚集索引（Clustered Index）并使用该索引进行记录锁定。
- 间隙锁（Gap Locks）：间隙锁是对索引记录之间间隙的锁定，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁定。例如 select c1 from t where c1 between 10 and 20 for update; 因为该范围内所有现有值之间的间隙已被锁定，故可防止其他事务将 c1=15 的值插入到 t 表中，无论该表中是否已存在列 c1=15 的值。
- 插入意向锁（Insert Intention Locks）：插入意向锁是一种间隙锁，在执行 INSERT 插入之前设置。此锁表明插入意图，插入同一索引间隙的多个事务如果插入位置不同，则无需互相等待。假设存在值为 4 和 7 的索引记录，2个事务分别尝试插入值 5 和 6 ，在获得插入行上的排他锁之前，都需要使用插入意向锁锁定 4 和 7 之间的间隙，但因为插入的行不冲突，所以不会互相阻塞。
关于加锁算法，具体参考：锁的种类
索引类型
如下建表语句：
CREATE TABLE `metadata` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`object_id` char(26) NOT NULL COMMENT '对象id',
`parent_id` char(26) NOT NULL COMMENT '父对象id',
`path` varchar(1024) NOT NULL COMMENT '路径',
`object_type` int(11) DEFAULT NULL COMMENT '元数据类型。1: 文件夹 2:目录',
PRIMARY KEY (id),
UNIQUE KEY (object_id),
KEY `idx_path`(path(320)),
-- 注意，利用联合索引的最左匹配原则，idx_parentId 完全是多余的，这里只是为了演示
KEY `idx_parentId` (parent_id),
KEY `idx_parent_id_object_type`(parent_id, object_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='元数据表'
涵盖的索引类型有：
- 主键索引：列 id，全表唯一且自增，最常见的索引
- 唯一索引：列 object_id，唯一不允许重复
- 前缀索引：索引名 idx_path，列 path，只对路径的前 320 字符建立索引
- 普通索引：索引名 idx_parentId，列 parent_id
- 联合索引：索引名 idx_parent_id_object_type，包含列 parent_id 和 object_type
在物理存储方面又分为：
- 聚簇索引：主键索引（或没有主键索引，第一个唯一索引），和行数据存储在一起，参考：Clustered and Secondary Indexes
- 二级索引：除了主键索引之外的索引，只存储了主键的值，查询数据时需要回表再根据主键找对应的行数据。
- 覆盖索引：执行查询操作时，所需的数据可以直接从索引文件中获取，而无需查询数据文件
根据物理存储方式的不同，InnoDB 的加锁行为会有一些不同，具体如下：
- 使用 聚簇索引（主键索引） 时，因为和行数据存储在一起，故根据 id 查询时，只会锁定聚簇索引（主键索引或没有主键索引，第一个唯一索引），即 INDEX_NAME 为 PRIMARY，共1把锁：
-- 锁定主键索引中 id=1 的行
$ select * from metadata where id=1 for update;
-- 锁定的 INDEX_NAME 是 PRIMARY（主键索引）
$ select ENGINE_TRANSACTION_ID,EVENT_ID,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------------------+----------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | EVENT_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+----------+------------+-----------+---------------+-------------+-----------+
| 2103 | 32 | NULL | TABLE | IX | GRANTED | NULL |
| 2103 | 32 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-----------------------+----------+------------+-----------+---------------+-------------+-----------+
- 使用 二级索引（唯一索引、普通索引）时，需要通过主键值回表查询数据，故会先锁定二级索引上的索引项，然后再锁定主键索引上的索引项，即 INDEX_NAME 为 PRIMARY 和 二级索引 object_id，共2把锁：
-- 先锁定唯一索引 object_id=a 的索引项，再根据 object_id=a 索引项的索引值(id)，锁定对应id主键的索引项
$ select * from metadata where object_id="a" for update;
-- INDEX_NAME 是 object_id（唯一索引）和 PRIMARY（主键索引）
$ select ENGINE_TRANSACTION_ID,EVENT_ID,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------------------+----------+------------+-----------+---------------+-------------+------------------------------+
| ENGINE_TRANSACTION_ID | EVENT_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+----------+------------+-----------+---------------+-------------+------------------------------+
| 2111 | 40 | NULL | TABLE | IX | GRANTED | NULL |
| 2111 | 40 | object_id | RECORD | X,REC_NOT_GAP | GRANTED | 'a ' |
| 2111 | 40 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-----------------------+----------+------------+-----------+---------------+-------------+------------------------------+
- 使用 覆盖索引 时，如果是加共享锁(S, lock in share mode)则只锁定二级索引（共1把锁），如果是加独占锁(X)，则先锁定二级索引，然后再锁定主键索引，共2把锁。
-- 共享锁，只锁二级索引
$ select id from metadata where object_id="a" lock in share mode;
$ select ENGINE_TRANSACTION_ID,EVENT_ID,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------------------+----------+------------+-----------+---------------+-------------+------------------------------+
| ENGINE_TRANSACTION_ID | EVENT_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+----------+------------+-----------+---------------+-------------+------------------------------+
| 421747628210032 | 13 | NULL | TABLE | IS | GRANTED | NULL |
| 421747628210032 | 13 | object_id | RECORD | S,REC_NOT_GAP | GRANTED | 'a ' |
+-----------------------+----------+------------+-----------+---------------+-------------+------------------------------+
-- 独占锁，同时锁二级和聚簇索引
$ select id from metadata where object_id="a" for update;
$ select ENGINE_TRANSACTION_ID,EVENT_ID,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------------------+----------+------------+-----------+---------------+-------------+------------------------------+
| ENGINE_TRANSACTION_ID | EVENT_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+----------+------------+-----------+---------------+-------------+------------------------------+
| 2112 | 17 | NULL | TABLE | IX | GRANTED | NULL |
| 2112 | 17 | object_id | RECORD | X,REC_NOT_GAP | GRANTED | 'a ' |
| 2112 | 17 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-----------------------+----------+------------+-----------+---------------+-------------+------------------------------+
加锁过程
select、update 和 delete 语句
加锁的基本单位是临键锁（Next-Key Locks），是记录锁（Record Locks）和间隙锁（Gap Locks）的组合。在某些情况下，InnoDB为了减少锁冲突，降低加锁粒度以提升并发性能，会由 Next-Key Locks 退化成记录锁或者间隙锁，即由加2把锁变成加1把锁。
临键锁（Next-Key Locks）= 记录锁（Record Locks）+ 间隙锁（Gap Locks）
在丁奇大佬《MySQL 实战 45 讲》 中总结了2个原则，2个优化和1个Bug，可以总结加锁过程：
- 原则 1：加锁的基本单位是 Next-Key Locks，是前开后闭区间（左不包含，右包含）。
- 原则 2：查找过程中访问到的对象才会加锁。
- 优化 1：索引上的等值查询，给唯一索引（主键索引也是唯一索引）加锁的时候，Next-Key Locks 退化为行锁。
- 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，Next-Key Locks 退化为间隙锁。
- 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
从上述总结中，我们可以看到，只有在唯一索引 + 等值查询 + 记录存在的情况下，临键锁（Next-Key Locks）才会退化成记录锁（Record Locks），等值查询记录不存在时，则不管是那种索引，都会退化成间隙锁。
根据索引类型，进一步总结其加锁规则如下：
- 根据查询条件，扫描索引，无论什么情况下，InnoDB 会往前扫描到第一个不满足条件的行为止
- 等值查询
- 唯一索引（包括主键索引，即聚簇索引）
- 记录存在：临键锁退化成记录锁。只需要确保记录无法被其他事务更新或删除，因为有唯一索引，其他事务插入相同记录值时会报错，此时无需再加间隙锁。如果是唯一索引，还会对主键索引加记录锁，即加2把锁，参考case1
- 记录不存在：临键锁退化成间隙锁。只需锁定该记录值所在的间隙，以阻止其他事务插入，参考case2
- 非唯一索引（普通索引，即二级索引）
- 记录存在：因为是普通索引，可以插入重复的值，故需要同时锁定记录本身、记录之前的间隙和记录之后的间隙（不太准确，但好理解），因为Inodb不知道重复值会插入到何处。又因为加锁单位是临键锁，故最终会加2个临键锁。同时，对该值对应的主键记录加记录锁，即加3把锁，参考case9
- 记录不存在：临键锁退化成间隙锁，参考case10
- 覆盖索引（指 lock in share mode，如果是加独占锁，规则同非唯一索引）
- 记录存在：只加覆盖索引的记录锁，不涉及回表查询，故无需访问主键数据，参考case13
- 记录不存在：同上，退化成普通索引的间隙锁，没有记录，所以也不会有主键索引加间隙锁一说，参考case14
- 唯一索引（包括主键索引，即聚簇索引）
- 范围查询
- 唯一索引：对所有符合查询条件的记录和记录之前的间隙加锁，即 Next-Key Locks，如果是 “>=” 或 “<=” 该记录存在，则只对该记录加记录锁，记录之前的间隙不加锁，锁是加在主键索引上，参考case11
- 非唯一索引：对所有符合查询条件的记录和记录之前的间隙加锁，即 Next-Key Locks，同时对命中二级索引对应的主键索引加记录锁。即临键锁加在二级索引上，记录锁加在主键索引上，参考case9
PS1：上述规则在 MySQL 8.0.13 验证通过，使用上文“索引类型”中的 metadata 表、数据和相关索引
PS2：不知道 X,REC_NOT_GAP,GAP,IX 是什么含义，参考之前的文章：锁的种类 中附录一节。
上面看文字有点抽象，我们来看具体的 SQL 语句和 MySQL 的输出。
首先给 metadata 表插入如下2条数据：
-- metadata 表数据(id 自增主键，object_id 唯一索引，parent_id 普通索引)
+----+-----------+-----------+--------+-------------+
| id | object_id | parent_id | path | object_type |
+----+-----------+-----------+--------+-------------+
| 1 | a | 001 | gns:// | 1 |
| 3 | c | 1 | gns:// | 1 |
+----+-----------+-----------+--------+-------------+
1）主键索引（聚簇索引）
-- case1: 等值存在，退化成记录锁
$ select * from metadata where id=1 for update;
| 2146 | 18 | NULL | TABLE | IX | GRANTED | NULL |
| 2146 | 18 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
-- case 2: 等值不存在，阻止1-3之间插入，退化成间隙锁
$ select * from metadata where id=2 for update;
| 2151 | 33 | NULL | TABLE | IX | GRANTED | NULL |
| 2151 | 33 | PRIMARY | RECORD | X,GAP | GRANTED | 3 |
-- case 3: 等值不存在，阻止3-无穷大插入，仍然是临键锁，锁定无穷大本身和前一个间隙
$ select * from metadata where id=2 for update;
| 2153 | 39 | NULL | TABLE | IX | GRANTED | NULL |
| 2153 | 39 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
-- case 3: 范围查询存在（实际中不建议这样加锁，锁定的记录太多，性能低下）
$ select * from metadata where id >= 1 for update;
| 2154 | 43 | NULL | TABLE | IX | GRANTED | NULL |
| 2154 | 43 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
| 2154 | 43 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
| 2154 | 43 | PRIMARY | RECORD | X | GRANTED | 3 |
-- case 4: 范围查询不存在
$ select * from metadata where id > 3 for update;
| 2155 | 47 | NULL | TABLE | IX | GRANTED | NULL |
| 2155 | 47 | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record |
-- case5: 范围查询大于小于（PS：理论上只需要加间隙锁，这里8.3以后优化了，算是5.7和8.0.13的一个Bug？）
$ select * from metadata where id > 1 and id < 3 for update;
| 2158 | 61 | NULL | TABLE | IX | GRANTED | NULL |
| 2158 | 61 | PRIMARY | RECORD | X | GRANTED | 3 |
2）唯一索引
-- case9: 等值查询，记录存在，根据原则2访问的都要加锁，需要回表查，故主键索引记录也要加锁
$ select * from metadata where object_id="a" for update;
| 2184 | 94 | NULL | TABLE | IX | GRANTED | NULL |
| 2184 | 94 | object_id | RECORD | X,REC_NOT_GAP | GRANTED | 'a ' |
| 2184 | 94 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
-- case10: 等值查询，记录不存在，中间插入，即向右遍历时且最后一个值不满足等值条件的时候
$ select * from metadata where id=2 for update;
| 2182 | 85 | NULL | TABLE | IX | GRANTED | NULL |
| 2182 | 85 | object_id | RECORD | X,GAP | GRANTED | 'c ' |
-- case11: 等值查询，记录不存在，在索引的最后插入，加的临键锁，没有退化成间隙锁，MySQL 8.3 解决了该问题
$ select * from metadata where id=4 for update;
| 2183 | 89 | NULL | TABLE | IX | GRANTED | NULL |
| 2183 | 89 | object_id | RECORD | X | GRANTED | supremum pseudo-record |
3）非唯一索引（二级索引）
-- case6: 等值查询，记录存在，2把临键锁 + 1把记录锁
$ select * from metadata where parent_id="1" for update;
| 2176 | 61 | NULL | TABLE | IX | GRANTED | NULL |
| 2176 | 61 | idx_parentId | RECORD | X | GRANTED | supremum pseudo-record |
| 2176 | 61 | idx_parentId | RECORD | X | GRANTED | '1 ', 3 |
| 2176 | 61 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
-- case7: 等值查询，记录不存在，退化成间隙锁
$ select * from metadata where parent_id="002" for update;
| 2186 | 102 | NULL | TABLE | IX | GRANTED | NULL |
| 2186 | 102 | idx_parentId | RECORD | X,GAP | GRANTED | '1 ', 3 |
-- case8: 范围查询，记录存在
$ select * from metadata where parent_id>"0" for update;
| id | object_id | parent_id | path | object_type |
+----+-----------+-----------+--------+-------------+
| 1 | a | 001 | gns:// | 1 |
| 3 | c | 1 | gns:// | 1
| ENGINE_TRANSACTION_ID | EVENT_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+----------+--------------+-----------+---------------+-------------+---------------------------------+
| 2173 | 51 | NULL | TABLE | IX | GRANTED | NULL |
| 2173 | 51 | idx_parentId | RECORD | X | GRANTED | supremum pseudo-record |
| 2173 | 51 | idx_parentId | RECORD | X | GRANTED | '001 ', 1 |
| 2173 | 51 | idx_parentId | RECORD | X | GRANTED | '1 ', 3 |
| 2173 | 51 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
| 2173 | 51 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
-- case9: 范围查询，记录不存在
$ select * from metadata where parent_id>"2" for update;
| 2174 | 55 | NULL | TABLE | IX | GRANTED | NULL |
| 2174 | 55 | idx_parentId | RECORD | X | GRANTED | supremum pseudo-record |
4）覆盖索引（加共享锁时）
-- case13: 等值查询，记录存在，无需回表，共享锁(S)只加一把锁
$ select id from metadata where object_id="a" lock in share mode;
| 421747628210032 | 128 | NULL | TABLE | IS | GRANTED | NULL |
| 421747628210032 | 128 | object_id | RECORD | S,REC_NOT_GAP | GRANTED | 'a ' |
-- case14: 等值查询，记录不存在，且在中间位置，即向右遍历时且最后一个值不满足等值条件的时候，退化成间隙锁
$ select id from metadata where object_id="b" lock in share mode;
| 421747628210032 | 144 | NULL | TABLE | IS | GRANTED | NULL |
| 421747628210032 | 144 | object_id | RECORD | S,GAP | GRANTED | 'c ' |
-- case15: 等值查询，记录不存在，索引的最后位置，仍然是临键锁，未退化
$ select id from metadata where object_id="d" lock in share mode;
| 421747628210032 | 148 | NULL | TABLE | IS | GRANTED | NULL |
| 421747628210032 | 148 | object_id | RECORD | S | GRANTED | supremum pseudo-record |
-- case16: 范围查询，记录存在
$ select id from metadata where object_id>="a" lock in share mode;
| id |
+----+
| 1 |
| 3 |
| ENGINE_TRANSACTION_ID | EVENT_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
| 421747628210032 | 132 | NULL | TABLE | IS | GRANTED | NULL |
| 421747628210032 | 132 | object_id | RECORD | S | GRANTED | supremum pseudo-record |
| 421747628210032 | 132 | object_id | RECORD | S | GRANTED | 'c ' |
| 421747628210032 | 132 | object_id | RECORD | S | GRANTED | 'a ' |
insert 语句
可重复读级别下为了解决幻读问题，innodb 新增了间隙锁，通过锁定一个间隙来阻止其他事务的插入，我们来看看这个过程。
首先，事务1在读取 id 大于 1 的行：
$ begin;select * from metadata where id>1 lock in share mode;
+----+-----------+-----------+--------+-------------+
| id | object_id | parent_id | path | object_type |
+----+-----------+-----------+--------+-------------+
| 3 | c | 1 | gns:// | 1 |
+----+-----------+-----------+--------+-------------+
因为有一条符合的记录，所以 InnoDB 加了2个临建锁 (1,3] 和 (3,+supernum]：
$ select ENGINE_TRANSACTION_ID,EVENT_ID,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------------------+----------+------------+-----------+-----------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | EVENT_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+----------+------------+-----------+-----------+-------------+------------------------+
| 421747628212816 | 24 | NULL | TABLE | IS | GRANTED | NULL |
| 421747628212816 | 24 | PRIMARY | RECORD | S | GRANTED | supremum pseudo-record |
| 421747628212816 | 24 | PRIMARY | RECORD | S | GRANTED | 3 |
+-----------------------+----------+------------+-----------+-----------+-------------+------------------------+
事务2准备插入 id=2 的新数据，预期其应该变成阻塞状态：
begin;insert into metadata values(2, "d", "c", "gns://", 1);
再次查看当前加的锁：
$ select ENGINE_TRANSACTION_ID,EVENT_ID,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------------------+----------+------------+-----------+------------------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | EVENT_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+----------+------------+-----------+------------------------+-------------+------------------------+
| 2269 | 23 | NULL | TABLE | IX | GRANTED | NULL |
| 2269 | 23 | PRIMARY | RECORD | X,GAP,INSERT_INTENTION | WAITING | 3 |
| 421747628212816 | 24 | NULL | TABLE | IS | GRANTED | NULL |
| 421747628212816 | 24 | PRIMARY | RECORD | S | GRANTED | supremum pseudo-record |
| 421747628212816 | 24 | PRIMARY | RECORD | S | GRANTED | 3 |
+-----------------------+----------+------------+-----------+------------------------+-------------+------------------------+
我们看到，2269 事务就是执行 insert 语句的事务，从 LOCK_MODE 中的 X,GAP,INSERT_INTENTION 可以看出是加的插入意向锁（Insert Intention Locks），LOCK_STATUS 是 WAITING 状态表明在等待获取锁，范围的右边是 3，结合已有的数据，加锁的间隙应该是 (1,3)。
因为事务1已加了临建锁 (1,3]，也就是对 (1,3) 间隙和 id=3 的纪录同时加锁。在 加锁的种类 一文中，我们介绍了插入意向锁和间隙锁是冲突的，所以事务2进入阻塞状态。
另外，从输出的结果中，我们可以看到在当前读语句中，都先获取了表级的 IX 或者 IS 锁（上文中有介绍）。
加锁过程案例分析
准备 MySQL 数据
创建如下表：
CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_c` (`c`)
) ENGINE=InnoDB;
其中，id 列是主键索引（唯一索引），c 是普通索引，d 没有索引，是普通的列。
准备如下初始数据：
insert into test values
(5, 5, 5),
(10, 10, 10),
(15, 15, 15),
(20, 20, 20),
(25, 25, 25);
上述有7个区间：
（-∞，5]（5，10]（10，15]（15，20]（20，25]（25，+supernum]
其中圆括号表示排除区间端点，方括号表示包含端点（左开右闭）。
Case1: 唯一索引等值查询
记录存在：退化成记录锁
| 事务A | 事务B | 事务C |
|---|---|---|
| begin; select * from test where id = 15 for update; |
||
| insert into test values(15,15,15); (ERROR 1062 (23000): Duplicate entry) |
||
| update test set c=c+1 where id=10; (Blocked) |
||
| commit |
事务A加锁变化过程如下：
- 根据原则1，加锁单位是 Next-Key Locks，所以事务 A 的加锁范围是 (10,15]
- 同时根据优化1，索引上的等值查询，给唯一索引加锁的时候，Next-Key Locks 退化为行锁，记录存在，因此最终加锁的是 15
所以，事务B插入 id=15 的记录会因为唯一约束导致错误而失败，这也是为什么会退化成记录锁的原因。同样的，事务C修改 id=15 这行也是不可以的。
记录不存在：退化成间隙锁
| 事务A | 事务B | 事务C |
|---|---|---|
| begin; select * from test where id = 13 for update; |
||
| insert into test values(12,12,12); (Blocked) |
||
| update test set c=c+1 where id=10; (OK) |
||
| commit |
事务A加锁变化过程如下：
- 根据原则1，加锁单位是 Next-Key Locks，所以事务 A 的加锁范围是 (10,15]
- 同时根据优化2，索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，Next-Key Locks 退化为间隙锁，右边的是15，不满足id=13的条件，因此最终加锁的范围是 (10,15)
所以，事务B要往这个间隙插入 id=12 的值会被锁住，但是事务 C 修改 id=10 这行是可以的。
Case2: 唯一索引范围查询
| 事务A | 事务B | 事务C |
|---|---|---|
| begin; select * from test where id>=10 and id<11 for update; |
||
| insert into test values(8,8,8); (OK) insert into test values(13,13,13); (Blocked) |
||
| update set d=d+1 where id=15; (Blocked) |
||
| commit |
虽然逻辑上事务A的语句等价于：
select * from test where id=10 for update;
但实际上事务A的加锁过程和上面的语句并不一样，具体如下：
- 根据原则1，加锁单位是 Next-Key Locks，对 (5,10] 加锁
- 但是需要先找到 id=10 的行，根据优化1，主键 id 上的等值条件，退化成行锁，故只对 id=10 这一行加锁。
- 继续往后进行范围查找，直到 id=15 这一行停下来，因此需要加 Next-Key Lock (10,15]
所以，事务A锁的是主键索引 id=10 的行和 (10,15]。故事务B插入id=8能成功，插入13阻塞。事务C因为id=15的主键被锁住，故也会阻塞。
Case3: 普通索引等值查询
记录存在
| 事务A | 事务B | 事务C | 事务D |
|---|---|---|---|
| begin; select * from test where c = 15 for update; |
|||
| insert into test values(16,16,16); (Blocked) |
|||
| update test set c=c+1 where id=15; (Blocked) |
|||
| update test set c=c+1 where id=20; (OK) |
|||
| commit |
事务A的加锁过程如下：
- 根据原则1，对 15 所在区间 (10,15] 加 Next-Key Locks
- 因为c是普通索引，因此访问这一条记录是不能马上停下来的，需要向右遍历，查到c=20才放弃，根据原则2，访问到的都需要加锁，因此 (15,20] 也要加 Next-Key Locks
- 同时符合优化2：等值判断，向右遍历，最后一个值不满足 c=15 条件，因此退化成间隙锁 (15,20)
- 因为需要回表查询，根据原则2，访问到的对象都需要加锁，所以还需要对 id=15 的主键加锁
此时事务A加的锁为c索引的 (10,15] 和 (15,20)，以及 id=15 的主键记录锁。事务B准备在区间 (15,20) 插入新记录，所以会阻塞。事务C因为主键 id=15 已被加锁，故更新时也会阻塞，事务D可以执行，因为事务A只对 (15,20) 加了间隙锁。
记录不存在
| 事务A | 事务B | 事务C |
|---|---|---|
| begin; select * from test where c = 14 for update; |
||
| insert into test values(13,13,13); (Blocked) |
||
| update test set c=c+1 where id=15; (OK) |
||
| commit |
事务A的加锁过程如下：
- 根据原则1，对区间 (10,15] 加 Next-Key Locks
- 根据优化2，因为 id=14 的记录不存在，故退化成间隙锁 (10,15)
所以，事务B往这个间隙插入肯定会被阻塞，事务C可以执行。
覆盖索引
| 事务A | 事务B | 事务C |
|---|---|---|
| begin; select id from test where c = 10 lock in share mode; |
||
| insert into test values(13,13,13); (Blocked) |
||
| update test set d=d+1 where id=5; (OK) |
||
| commit |
事务A的加锁过程如下：
- 加锁基本单位是 Next-Key Locks，对区间 (5,10] 加锁
- 根据优化2，还需要继续向右遍历，直到第一个不满足条件的值，因此还需要对 (10,15) 加锁
- 因为id的值可以直接从 c 的索引文件中加载，不需要访问主键记录，故不会对主键加锁
所以，事务B插入 c=13 的记录位于 (10,15) 的间隙从而阻塞，而事务C成功。如若使用 lock for update 独占锁(X)，系统会认为你接下来要更新数据，所以会对主键索引上满足条件的行加锁，则会导致事务C被阻塞，无法更新。
Case4: 普通索引范围查询
这里 c 是普通索引：
| 事务A | 事务B | 事务C |
|---|---|---|
| begin; select * from test where c>=10 and c<11 for update; |
||
| insert into test values(8,8,8); (Blocked) |
||
| update set d=d+1 where id=15; (Blocked) |
||
| commit |
事务A加锁过程如下：
- 根据原则1，加 Next-Key Locks (5,10]
- 因为 c 是普通索引，所以在开始找到 id=10 的记录后，不会退化成行锁
- 继续往后扫描，找到 id=15 的记录不满足条件，故对 (10, 15] 加 Next-Key Locks
所以，事务B和事务C都被阻塞。
Case5: 无索引查询
| 事务A | 事务B | 事务C | 事务D |
|---|---|---|---|
| begin; select * from test where d = 15 for update; |
|||
| insert into test values(16,16,16); (Blocked) |
|||
| update test set c=c+1 where id=15; (Blocked) |
|||
| update test set c=c+1 where id=20; (Blocked) |
|||
| commit |
事务A的加锁过程如下：
- 由于 d 没有索引，故 MySQL 进行全表扫描
- 根据原则1，加锁基本单位是 Next-Key Locks，因为所有记录都被访问，而 d 又没有索引，InnoDB 的锁又一定是加在索引上，故只能对所有主键加上 Next-Key Locks，即 (-∞,5], (5,10], (10,15], (15,20], (20,25], (25, supremum pseudo-record]
最终，InnoDB对所有行都加上了 Next-Key Locks，相当于实现了表锁的效果，其他事务的任何当前读操作都会阻塞（select * from table 快照读不影响）。
延申: limit 语句加锁
具体请参考：为什么我只改一行的语句，锁这么多？：案例七：limit 语句加锁
总结
加锁的基本单位是临键锁（Next-Key Locks）= 记录锁（Record Locks）+ 间隙锁（Gap Locks），在某些情况下，InnoDB为了减少锁冲突，降低加锁粒度以提升并发性能，会由 Next-Key Locks 退化成记录锁或者间隙锁，即由加2把锁变成加1把锁。
在丁奇大佬《MySQL 实战 45 讲》 中总结了2个原则，2个优化和1个Bug，可以总结加锁过程：
- 原则 1：加锁的基本单位是 Next-Key Locks，是前开后闭区间（左不包含，右包含）。
- 原则 2：查找过程中访问到的对象才会加锁。
- 优化 1：索引上的等值查询，给唯一索引（主键索引也是唯一索引）加锁的时候，Next-Key Locks 退化为行锁。
- 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，Next-Key Locks 退化为间隙锁。
- 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
从上述总结中，我们可以看到，只有在唯一索引 + 等值查询 + 记录存在的情况下，临键锁（Next-Key Locks）才会退化成记录锁（Record Locks），等值查询记录不存在时，则不管是那种索引，都会退化成间隙锁。
最后，再总结一下几种加锁算法：
- 记录锁（Record Locks）：始终锁定索引上的一条记录，即使表中的列表没有索引。
- 间隙锁（Gap Locks）：间隙锁是对索引记录之间间隙的锁定，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁定。以防止其他事务在间隙中插入新的索引记录，解决幻读问题。
- 临键锁（Next-Key Locks）：同时锁定记录和间隙，实际是记录锁（Record Locks）和 间隙锁（Gap Locks）的结合。
- 插入意向锁（Insert Intention Locks）：插入意向锁是一种间隙锁，在执行 INSERT 插入之前设置。插入意向锁之间不冲突，但是和间隙锁是冲突的。间隙锁可以重复获取，所以在“不存在插入，存在返回”的场景下会出现死锁，具体参考死锁案例中的介绍。
联系作者
因本人水平有限，而 MySQL 又比较复杂，文章难免出现错误，故相关 MySQL 文章虽然我会发布到多个平台和公众号，但是文章最终的修正请以 github 的这个仓库为准：https://github.com/xmcy0011/ToBeTopGo-Gitbook
期待您的 issues，共同进步。
当然，如果您有任何关于 MySQL 的疑问，欢迎留言或在仓库提 issues。
作者简介：一线Coder，公众号《Go和分布式IM》运营者，开源项目： CoffeeChat 、interview-golang 发起人 & 核心开发者，终身程序员。
参考
- 官网文档：InnoDB Locking
- 官方文档：Phantom Rows（幻读）
- 官方文档：Clustered and Secondary Indexes
- 官方文档：CREATE TABLE Statement
- 官方文档：CREATE INDEX Statement
- 官方文档：How MySQL Uses Indexes
- 为什么我只改一行的语句，锁这么多？
- MYSQL（04）-间隙锁详解
- MySQL 是怎么加锁的？
- 记一次 innodb 间隙锁导致的死锁分析
- MySQL锁系列（一）之锁的种类和概念
- how engine innodb status 输出结果解读
- 36 | 记一次线上SQL死锁事故：如何避免死锁？
- 索引常见面试题
- MySQL记录锁、间隙锁、临键锁（Next-Key Locks）加锁过程