运维首页 > 数据运维 > 学会MySQL(2)——间隙锁加锁算法分析

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

2024年 4月 23日 84.9k 0

knYixJM9SKKHvoU6gZxzww.jpg

引言

在进行死锁分析时，如果看 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 的这个仓库为准：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）加锁过程
爱可生开源社区
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

爱可生开源社区

这个人很懒，什么都没有留下～

文章2708 评论0