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

已于 2024-04-23 16:28:39 修改
阅读量1.1k 收藏 13
点赞数 23
分类专栏： 学会MySQL 后端开发 文章标签： mysql 间隙锁 独占锁 记录锁 mysql锁
于 2024-04-23 16:11:28 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xmcy001122/article/details/137505632
版权

间隙锁加锁算法分析

引言

在进行死锁分析时，如果看 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 发起人 & 核心开发者，终身程序员。

参考

Go和分布式IM
关注
专栏目录
MySQL——数据库锁原理
庄小焱
12-10 1508
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。例如，操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素，为了保证一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性。人们认为行级锁总会增加开销。实际上，只有当实现本身会增加开销时，行级锁才会增加开销。InnoDB存储引擎不需要锁升级，因为一个锁和多个锁的开销是相同的。InnoDB存储引擎锁的实现和Oracle数据库非常类似，提供一致性的
Mysql 索引与优化 加锁处理分析
robinjun的专栏
09-28 783
Mysql 入门 (robinjun) 摘要 1 Mysql介绍 1.1 Mysql简介 定义：MySQL 是一款优秀的开源小型关系数据库系统。 历史： 1.       1979年，始于一款报表工具、数据引擎。 2.       1996年，发布 MySQL1.0 版本。 3.       2000年，成立 MySQL AB公司。 4.       2008年，Sun 以
MySQL笔记（六）MySQL间隙锁的加锁规律
Liu_G的博客
08-07 1063
MySQL笔记（六）MySQL的锁机制及使用场景MySQL的锁机制及使用场景 在下面的场景中的时候，我们总是需要使用到MySQL的锁 MySQL的锁机制及使用场景
深度解析：MySQL中的间隙锁【含代码示例】
一个做过前端开发的产品经理，经历过睿智产品的折磨导致脱发之后，励志要翻身"农奴"把歌唱，一边打入敌人内部一边持续提升自己，为我们广大开发同胞谋福祉，坚决抵制睿智产品折磨我们码农兄弟！
06-27 2896
间隙锁，顾名思义，锁定的是两个索引记录之间的“间隙”，即两个值之间不存在的实际记录空间。这种机制确保了在事务执行期间，其他事务无法在这个范围内插入新的记录，从而维护了事务隔离性，特别是对于可重复读（Repeatable Read）隔离级别。
mysql间隙锁与行锁分析与处理
最新发布
CSYZA的博客
08-08 879
间隙锁的定义：对于键值在条件范围内但并不存在的记录，即“间隙（GAP）”，InnoDB 会对这个“间隙”加锁。间隙锁的作用：防止幻读。
锁--06---间隙锁-加锁规则---11个案例
时光里的博客
04-08 617
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录间隙锁加锁规则前提:间隙锁 是在==可重复读隔离(RR)级别==下才会生效的加锁规则11个案例数据准备: ==RR级别==唯一索引(id)：(-∞,0]，(0,5]，(5,10]，(10,15] ,(15,20] ,(20,25] ,（25,+supremum]非唯一索引(col1): (-∞,0]，(0,5]，(5,10]，(10,15] ,(15,20] ,(20,25] ,（25,+supremum]案例一：唯一索引等值查询.
MySQL-间隙锁-加锁规则
泰山与水
09-19 1664
MySQL 后面的版本可能会改变加锁策略，本文只适用版本： 5.x 系列 <=5.7.24 8.0 系列 <=8.0.13 间隙锁在可重复读隔离级别下才有效，以下规则只描述了间隙锁： 原则 1：加锁的基本单位是 next-key lock，next-key lock 是前开后闭区间 原则 2：查找过程中访问到的对象才会加锁。这和锁的普适概念，竞争的资源才会加锁同理 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁 优化 2：索引上的等值查
mysql 区间锁
zhaoyangjian724的专栏
11-18 1121
SESSION A: mysql> select * from t100 where id >100 for update; +-----+-------+ | id | value | +-----+-------+ | 110 | a | | 120 | a | +-----+-------+ 2 rows in set (0.31 sec) SESSION B: my
深入理解MySQL锁机制与性能优化：详解记录锁、间隙锁、临键锁及慢SQL查询分析
搬砖的小熊猫的博客
07-24 1512
Read View 保存了当前事务开启时所有活跃（还没有提交）的事务列表，换个角度，可以理解为 Read View 保存了不应该让这个事务看到的其他的事务 ID 列表。第三种情况，当我们使用了范围查询，不仅仅命中了 Record 记录，还包含了 Gap 间隙，在这种情况下我们使用的就是临键锁，它是 MySQL 里面默认的行锁算法，相当于记录锁加上间隙锁。越靠上，表示查询效率越高。第一种情况，当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁。
mysql事务两个表加锁顺序_MySQL事务与锁详解
weixin_29189363的博客
01-25 1261
文章目标1、 掌握事务的特性与事务并发造成的问题2、事务一致性问题解决方案3、MVCC的原理4、锁的分类、行锁的原理、行锁的算法1 什么是数据库的事务？1.1 事务的典型场景在项目里面，什么地方会开启事务，或者配置了事务？无论是在方法上加注解，还 是配置切面比如下单，会操作订单表，资金表，物流表等等，这个时候我们需要让这些操作都 在一个事务里面完成。当一个业务流程涉及多个表的操作的时候，我们希望它...
MySql——锁的类型及锁带来的问题（脏读等）
JAVA那些事
12-13 999
1 什么是锁 1.1 锁的概述 在生活中锁的例子多的不能再多了，从古老的简单的门锁，到密码锁，再到现在的指纹解锁，人脸识别锁，这都是锁的鲜明的例子，所以，我们理解锁应该是非常简单的。 再到MySQL中的锁，对于MySQL来说，锁是一个很重要的特性，数据库的锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性，这样才能保证在高并发的情况下，访问数据库的时候，数据不会出现问题。 1.2 锁的两...
MySQL间隙锁
laim3的博客
06-21 281
MySQL范围锁 MySQL加锁的基本单位是next-key lock. 只有访问到的对象才会加锁 索引上的等值查询，唯一索引退化为行锁，非唯一索引遍历到最后一个不满足条件的值退化为gap lock 主键范围锁例子 表t, id为主键索引，初始表记录id(10,20,30) select * from t where id >= 10 and id < 20 for update; 加锁范围为 id[10],id(10,20] update t where id = 10 或 id =
MySQL间隙锁有什么作用？怎么使用？
学亮编程手记
11-27 748
间隙锁的作用是防止其他事务在同一个间隙内插入新的数据，从而避免了幻读（Phantom Read）问题。幻读指的是在同一个事务中多次执行相同的查询，但结果集却不一致，因为其他事务插入了新的数据。间隙锁是基于索引范围的锁，它锁定了索引范围内的间隙（两个索引值之间的空间），而不是具体的数据行。总结起来，间隙锁是MySQL中一种用于保护数据一致性的锁机制，通过锁定索引范围内的间隙，防止其他事务在该间隙内插入新的数据。因此，在实际使用中，需要根据具体的业务需求和情况，合理选择使用间隙锁来保护数据的一致性。
mysql间隙锁
johnhuster的专栏
07-10 695
间隙锁存在需要满足下面几个条件： （1）索引级别在RR或之上，针对RC以及RU级别是不存在的 （2）非聚簇索引才会存在 然而索引类型的不同也会影响间隙锁锁住数据的范围，唯一索引会锁住上面的范围，但是常规索引则会针对上下返回都会锁住； 在唯一索引的情况下操作： Session1： update t set a=600 where x=500; Session2： insert i...
21 间隙锁加锁规则
luojias的博客
01-22 1869
间隙锁加锁规则，注意临键锁是间隙锁和行锁的总称，实际上临键锁就是行锁加间隙锁，实际上是先加间隙锁，再加行锁，这两个是分开独立的。lock in share mode想要保证搜索的数据不被修改，必须避免覆盖索引，锁是加在索引上的，只有被访问到的索引才会加锁。2.普通索引等值查询，最后一个不满足的，临键锁退化为间隙锁。其实读提交隔离级别在外键场景下还是有间隙锁，相对比较复杂，1.唯一索引的范围查询会查询到第一个不满足条件的值为止。1.等值查询的时候，给唯一索引加锁退化为行锁。两个原则，两个优化，一个bug。
MySQL间隙锁问题
追梦人的博客
01-03 8564
间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。 最近用户反馈说系统老是出现insert时，等待超时了，最后发现是insert间隙锁！间隙锁是innodb中行锁的一种， 但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读，但是它会把锁定范围扩大，有时候也会给我们带来麻烦
【mysql45讲】InnoDB间隙锁与加锁规则
乾坤未定，你我皆是黑马
09-16 1619
一、间隙锁的概念 1.间隙锁的作用 解决幻读问题 2.间隙锁的意思 间隙锁，锁的就是两个值之间的间隙。 3.间隙锁的冲突关系 间隙锁只跟“向间隙中插入记录”存在冲突关系。 4.next-key-lock 间隙锁和行锁合称next-key-lock。每个next-key-lock是左开右闭区间。 比如有两条记录a和b，执行语句select * from t for update时，要把整个表锁起来，就形成了三个next-key-lock: (-∞，a],(a,b],(b,supernum]。 ps:supe
mysql区间锁导致的问题
木子希的博客
08-15 730
前几天产线出BUG,记录下来 数据库使用mysql,引擎使用innodb 执行insert语句的时候产线日志报如下错误 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 从错误来看,锁等待超时。找到insert语句找到对应表,结合当时系统情况推断是另一个跑批在操作对应表.sql语句如下 delete...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值