学会MySQL(2)——间隙锁加锁算法分析-每日运维

引言

在进行死锁分析时，如果看 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：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

最后，再总结一下几种加锁算法：

记录锁（Record Locks）：始终锁定索引上的一条记录，即使表中的列表没有索引。
间隙锁（Gap Locks）：间隙锁是对索引记录之间间隙的锁定，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁定。以防止其他事务在间隙中插入新的索引记录，解决幻读问题。
临键锁（Next-Key Locks）：同时锁定记录和间隙，实际是记录锁（Record Locks）和间隙锁（Gap Locks）的结合。
插入意向锁（Insert Intention Locks）：插入意向锁是一种间隙锁，在执行 INSERT 插入之前设置。插入意向锁之间不冲突，但是和间隙锁是冲突的。间隙锁可以重复获取，所以在“不存在插入，存在返回”的场景下会出现死锁，具体参考死锁案例中的介绍。

联系作者

因本人水平有限，而 MySQL 又比较复杂，文章难免出现错误，故相关 MySQL 文章虽然我会发布到多个平台和公众号，但是文章最终的修正请以 github 的这个仓库为准：ToBeTopGo-Gitbook

期待您的 issues，共同进步。

当然，如果您有任何关于 MySQL 的疑问，欢迎留言或在仓库提 issues。

参考

官网文档：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）加锁过程

学会MySQL(2)——间隙锁加锁算法分析

引言

锁类型

加锁算法

索引类型

加锁过程

select、update 和 delete 语句

insert 语句

加锁过程案例分析

准备 MySQL 数据

Case1: 唯一索引等值查询

记录存在：退化成记录锁

记录不存在：退化成间隙锁

Case2: 唯一索引范围查询

Case3: 普通索引等值查询

记录存在

记录不存在

覆盖索引

Case4: 普通索引范围查询

Case5: 无索引查询

延申: limit 语句加锁

总结

联系作者

参考

相关文章

发布评论取消回复

爱可生开源社区

服务器开放端口，设置服务器开放端口的步骤

远程桌面连接不上，解决远程桌面连接不上

Oracle 导出CSV工具-sqluldr2

学会MySQL(2)——间隙锁加锁算法分析

引言

锁类型

加锁算法

索引类型

加锁过程

select、update 和 delete 语句

insert 语句

加锁过程案例分析

准备 MySQL 数据

Case1: 唯一索引等值查询

记录存在：退化成记录锁

记录不存在：退化成间隙锁

Case2: 唯一索引范围查询

Case3: 普通索引等值查询

记录存在

记录不存在

覆盖索引

Case4: 普通索引范围查询

Case5: 无索引查询

延申: limit 语句加锁

总结

联系作者

参考

相关文章

发布评论 取消回复

爱可生开源社区

服务器开放端口，设置服务器开放端口的步骤

远程桌面连接不上，解决远程桌面连接不上

Oracle 导出CSV工具-sqluldr2

发布评论取消回复