开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第11天，点击查看活动详情

InnoDB 中加行锁的规则是比较复杂的，看了多篇文章也没能十分明确地掌握规则，只能随着不断地应用、分析来一点点加深体会。本文是学习行锁加锁规则的学习笔记，以备不时之需。

01-MDL 操作都会加什么锁

在之前介绍锁和幻读中提到过各种类型的锁，特别是间隙锁。今天这篇文章中，我们会介绍一下 MDL 操作都会加什么类型的锁？在此之前，我们需要明确以下几点：

加锁的对象是索引
加锁的基本单位是 next-key 锁，是左开右闭的区间，是间隙锁（左开右开）和记录锁的组合体
由于两阶段锁协议，锁在需要时申请，在事务提交时释放

MDL 一般分为 INSERT \ DELETE \ UPDATE \ SELECT：

INSERT，一般不需要锁，通过 DB_TRX_ID 实现的隐式锁。具体的情况我们在下一节详细分析。
DELETE，需要定位到要删除的记录。
- 表上的 IX 锁
- 根据使用到的索引不同，加的锁也不一样
UPDATE，区分三种不同的情况：
- 修改的列，占用空间不变化。
- 修改的列，占用空间发生变化（增大、减小）。
- 修改的列的主键发生变化。
SELECT，区分两种情况：
- 快照读，不需要加锁；
- 当前读（锁定读），SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE，读取的是记录的“最新”状态，多个事务之间需要通过锁来控制。

下面我们来详细介绍下前面几种 MDL 操作加的锁信息。我们将使用 users 表来演示并验证分析结果。 performance_schema.data_locks 中的记录中包含了许多与锁无关地列，在后面的结构中，我删去了这些列，只保留了与本章内容相关的内容。

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_email` varchar(32) DEFAULT NULL,
  `phone_number` varchar(16) DEFAULT NULL,
  `city` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;

01.1-`INSERT` 时的隐式锁

情形一

如果插入的表中目前没有任何锁，插入时，只需要在表级别上加 IX 锁。例如：

-- T1
INSERT INTO users(user_id, user_email, phone_number, city) VALUES (1, 'everett@example.net', '(368) 403-1593', 'New Faye Kiehn town');
/*
*************************** 1. row ***************************
ENGINE_TRANSACTION_ID: T1
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*/

从锁信息中可以看到，只在 users 上增加了 TABLE 级别的 IX 锁。

情形二

如果情形一种插入的记录，另一个事务：

立即使用 SELECT ... LOCK IN SHARE MODE 尝试获取记录的 S 锁，或使用 SELECT ... FOR UPDATE 尝试获取记录的 X 锁，当前事务会被阻塞（block）。否则，会造成脏写。
立即使用 UPDATE ... 尝试获取记录的 X 锁，当前事务会阻塞；否则，会造成脏写。

我们通过两个事务 T1 和 T2 来验证一下上述说法。

-- T2
BEGIN;
SELECT * FROM users LOCK IN SHARE MODE;

/*
*************************** 1. row ***************************
ENGINE_TRANSACTION_ID: T2
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IS         -- T2 申请的 IS 锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE_TRANSACTION_ID: T2
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: S          -- T2 创建的 S 锁，WAITING 状态说明阻塞了，等待 T1 提交，释放 id = 1 的记录 X 锁
          LOCK_STATUS: WAITING 
            LOCK_DATA: 1
*************************** 3. row ***************************
ENGINE_TRANSACTION_ID: T1
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX                -- T1 插入数据时创建的 IX 锁信息
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE_TRANSACTION_ID: T1
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP     -- 注意：T2 为 T1 创建的 X 锁信息
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
 */

对于聚簇索引中的记录来说，每条记录中有一个隐藏的列 DB_TRX_ID，表示当前事务的 ID。如果其他事务（例如前面例子中的 T2）想对这条记录添加 S 锁或 X 锁时，会先检查该记录的 DB_TRX_ID（前面例子中的 T1）是否是活跃事务。若是，则为其（T1）创建一个 X 锁结构，并将该结构的 is_waiting 设置为 false。然后，为自己（T2）创建一个锁结构，并将该结构的 is_waiting 设置为 true。

对于二级索引中的记录来说，每条记录中并不包含 DB_TRX_ID 列。但在页的 Page Header 处存在一个 PAGE_MAX_TRX_ID 属性，表示对该页面做改动的最大事务 ID。如果 PAGE_MAX_TRX_ID 小于当前活跃的最小事务 ID，则说明 PAGE_MAX_TRX_ID 代表的事务已经提交了。否则，应该根据记录中的主键 ID，回表到聚簇索引上，按照聚簇索引的规则再操作一遍。

上述过程就是插入可能产生的“隐式锁”。锁并不由 INSERT 直接产生，而是由后续尝试对刚插入的记录请求 S 锁或 X 锁的事务创建。

情形三

如果即将插入的间隙已经被其他事务（下面的 T1）加了间隙锁或 next-key 锁，INSERT 语句（下面的 T2）会阻塞，并在该间隙上加一个插入意向锁（INSERT_INTENTION）。插入意向锁实际上是内存中的一个锁结构，用于等待其他事务释放对应的间隙锁或 next-key 锁。

-- T1
BEGIN ;
SELECT * FROM users LOCK IN SHARE MODE ;

/*
*************************** 1. row ***************************
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record  -- 由于表中没有数据，所以 (-infinum, + supremum) 加锁，即锁全表。
 */

-- T2
BEGIN ;
INSERT INTO users(user_id, user_email, phone_number, city) VALUES (1, 'everett@example.net', '(368) 403-1593', 'New Faye Kiehn town');

/*
*************************** 1. row ***************************
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: WAITING                 -- 这里可以看到，插入意向锁，且处于等待状态
            LOCK_DATA: supremum pseudo-record  -- 由事务 T1 的 SELECT ... LOCK IN SHARE MODE 持有
 */

01.2-`DELETE` 时的锁

删除记录时，会在表上增加 IX 锁。

根据使用的索引不同，增加锁也不同。

使用主键删除时，在聚簇索引上增加记录 X 锁

DELETE FROM users WHERE user_id = 1; 
/*
ENGINE_TRANSACTION_ID: 114542
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP   -- 记录 X 锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
*/

没有索引时，通过间隙锁锁住全表，(-infinum, 1], (1, +supremum)。

DELETE FROM users WHERE user_email = 'everett@example.net';
/*
*************************** 2. row ***************************
ENGINE_TRANSACTION_ID: 114548
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
ENGINE_TRANSACTION_ID: 114548
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
 */

使用二级索引时，会先将二级索引锁住，(-infimum, |'everett@example.net', 1|], (|'everett@example.net', 1|, +supremum)，然后在聚簇索引上用记录 X 锁住要删除的记录。

-- 先建立一个索引 
ALTER TABLE ADD INDEX idx_user_email(user_email);

DELETE FROM users WHERE user_email = 'everett@example.net';
/*
*************************** 2. row ***************************
ENGINE_TRANSACTION_ID: 114565
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: idx_user_email
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
ENGINE_TRANSACTION_ID: 114565
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: idx_user_email
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'everett@example.net', 1
*************************** 4. row ***************************
ENGINE_TRANSACTION_ID: 114565
        OBJECT_SCHEMA: demodb
          OBJECT_NAME: users
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP  -- 局促索引上的记录 X 锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1 
*/

01.3-`UPDATE` 时的锁

情形一，列占用空间不发生变化

会在表上增加表级别 IX 锁。

根据使用的索引不同，加锁的数量也不同。与 DELETE 时加的锁一样。

情形二，列占用空间不发生变化

先定位到记录，这个过程与上述场景一一致。将记录彻底删除后，再插入一个新记录，这个过程看作是隐式锁，这与 INSERT 过程一致。

情形三，修改的列的主键发生变化

相当于原记录删除后，再新插入一条记录。看作是 DELETE 和 INSERT 的组合。

01.4-`SELECT` 时的锁

SELECT 时加的锁，我们将通过《MySQL 实战45讲》讲到加锁规则时使用得几个场景来验证。课程中给出了加锁规则，并针对每条规则给出了一个场景。我们将通过 InnoDB 锁日志来验证一下这些场景。

02-InnoDB 行锁规则

《MySQL 实战45讲》中林晓斌大佬总结的加锁规则，两个“原则”、两个“优化”和一个“bug”。

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。原则 2：查找过程中访问到的对象才会加锁。优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

借用课程中的表结构 t 和数据：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

INSERT INTO t VALUES (0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

首先，明确一下。快照读（SELECT）时是不需要加锁的，以下讨论的场景都是当前读（SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE）。

02.1-唯一索引等值查询

根据要查询的记录存在、不存在，可分为两种情况：

存在，会加表级别的意向锁，会在聚簇索引上增加记录锁，如果使用唯一二级索引，还会在二级索引上加记录锁。例如：

-- 将索引 c 修改为唯一索引
ALTER TABLE t DROP INDEX c;
ALTER TABLE t ADD UNIQUE c(c);

BEGIN; -- T1
UPDATE t SET d = d + 1 WHERE c = 5;

/*
*************************** 1. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX              -- 表上的意向锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: c
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP    -- 唯一索引上的记录锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
*************************** 3. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP    -- 聚簇索引上的记录锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
 */

根据原则 1，加锁的单位是 next-key 锁，即 (0, 5]。根据优化 1，退化为行锁。 2. 不存在，会加表级别的意向锁，会在索引上加间隙锁。例如：

BEGIN; -- T1
UPDATE t SET d = d + 1 WHERE c = 7;
/*
*************************** 1. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX              -- 表级别的意向锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: c
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP           -- 索引 c 上的间隙锁，(5, 10)
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10
 */

根据原则 1，加锁的单位是 next-key 锁，即 (5, 10]。根据优化二，如果 next-key 退化为间隙锁 (5, 10)。

02.2-非唯一索引等值查询

与唯一索引等值查询类似，分两中情况：

存在时，表级的意向锁，非唯一索引上的间隙锁（主要是因为非唯一性，所以需要找到第一个不满足的索引）

BEGIN ; -- T1
UPDATE t SET d = d + 1 WHERE c = 5;
/*
*************************** 1. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: c
            LOCK_TYPE: RECORD
            LOCK_MODE: X                  -- (0, 5] c 上的 next-key lock
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5, 5
*************************** 3. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP    -- 聚簇索引上的记录锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
*************************** 4. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: c
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP          -- (5, 10) c 上的间隙锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10, 10
4 rows in set (0.00 sec)
 */

首先，会在索引 c 上增加 (0, 5] next-key 锁。因为，c 不是唯一索引，需要向右遍历，到 c = 10 时终止。根据原则 2，扫描到的都加锁，所以加 (5, 10] next-key lock。再根据优化 2，退化为 (5, 10)。最后，因为要修改 d 的值，在聚簇索引上增加记录锁，防止其他事务修改。

不存在时。表级别的意向锁，索引上的间隙锁。例如：

BEGIN ; -- T1
UPDATE t SET d = d + 1 WHERE c = 7;

/*
*************************** 1. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: c
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP       -- (5, 10) 间隙锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10, 10
 */

根据原则 1，在 c 上增加 next-key lock (5, 10]。在根据优化 2，退化为间隙锁 (5, 10)。

02.3-唯一索引范围查询

BEGIN ; -- T1
SELECT * FROM t WHERE id >= 10 AND id < 11 FOR UPDATE ;

/*
*************************** 1. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10
*************************** 3. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 15
 */

根据 WHERE 子句条件，先扫描到 id=10 的记录，加上 (5, 10] next-key 锁，由于优化 1，退化为记录锁。然后继续向右扫描，扫描到 id = 15 的记录，加上 (10, 15] next-key 锁。

02.4-非唯一索引范围查询

BEGIN ; -- T1
SELECT * FROM t WHERE c >= 10 AND c < 11 FOR UPDATE ;

/*
*************************** 1. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: NULL
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: c
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10, 10
*************************** 3. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: c
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 15, 15
*************************** 4. row ***************************
        OBJECT_SCHEMA: mydb
          OBJECT_NAME: t
           INDEX_NAME: PRIMARY
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10
 */

与在唯一索引上不同的是，第一个 (5, 10] 的锁并不会退化到行锁。所以，在索引 c 上就有两个 next-key 锁，(5, 10]、(10, 15]。

02.5-不使用索引的查询

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

MySQL「11」InnoDB 行锁规则

01-MDL 操作都会加什么锁

01.1-`INSERT` 时的隐式锁

情形一

情形二

情形三

01.2-`DELETE` 时的锁

01.3-`UPDATE` 时的锁

情形一，列占用空间不发生变化

情形二，列占用空间不发生变化

情形三，修改的列的主键发生变化

01.4-`SELECT` 时的锁

02-InnoDB 行锁规则

02.1-唯一索引等值查询

02.2-非唯一索引等值查询

02.3-唯一索引范围查询

02.4-非唯一索引范围查询

02.5-不使用索引的查询

refs

MySQL「11」InnoDB 行锁规则

01-MDL 操作都会加什么锁

01.1-INSERT 时的隐式锁

情形一

情形二

情形三

01.2-DELETE 时的锁

01.3-UPDATE 时的锁

情形一，列占用空间不发生变化

情形二，列占用空间不发生变化

情形三，修改的列的主键发生变化

01.4-SELECT 时的锁

02-InnoDB 行锁规则

02.1-唯一索引等值查询

02.2-非唯一索引等值查询

02.3-唯一索引范围查询

02.4-非唯一索引范围查询

02.5-不使用索引的查询

refs

01.1-`INSERT` 时的隐式锁

01.2-`DELETE` 时的锁

01.3-`UPDATE` 时的锁

01.4-`SELECT` 时的锁