前言

事务中的锁一直是数据库中非常重要的部分，其中 MySQL 的 InnoDB 多粒度锁就包含意向锁 / 记录锁 / 间隙锁等，本文将以此展开事务的背后锁是如何运作的。

如何自己进行实验？

很好，又是这个经典的问题。

1
2
3
SELECT * FROM performance_schema.data_locks \G;  -- 查询所有锁
SELECT * FROM performance_schema.data_locks WHERE OBJECT_SCHEMA="{库名}" AND OBJECT_NAME="{表名}" AND INDEX_NAME="{索引名}" \G;  -- 查询指定 数据库-数据表-索引 下的锁
-- \G 表示以列表方式打印, 而非数据表方式, 当数据字段多而记录少时建议使用。

索引名中，自动生成索引为 GEN_CLUST_INDEX，主键索引为 PRIMARY，二级索引为用户定义索引。

当然，需要注意的是，只有在事务中才会持有锁，因此你的实验必须在事务中进行。另外，后面我们将介绍的 ​除意向锁以外的表级锁 都不会在这个列表中显示。


锁的分类

表级锁

本章节中标为 “特殊” 的锁不是事务体系的关键，将不在之后章节着重介绍。它们往往具有例外的生命周期。

表锁（特殊）

1
2
3
LOCK TABLES {表名} READ;  -- 为表添加读锁 (S)
LOCK TABLES {表名} WRITE; -- 为表添加写锁 (X)
UNLOCK TABLES;           -- 释放会话所有表锁

表锁不会由事务自动创建，因为事务拥有更好的解决方案。​表锁必须由用户创建，由用户释放（或者会话关闭）。 通常并不建议用户创建表锁，因为表锁粒度太大，它严重影响并发效率。

需要注意的是，大多数锁的生命周期都在事务结束时完成，但表锁比较特殊，其生命周期由用户定义。

元数据锁（特殊）

元数据锁（MDL）用于保护数据表结构，而非保护数据表数据。

  • 当一个 DML 语句发生，将自动对数据表加 MDL 读锁，语句结束即释放。
  • 当一个 DDL 语句发生，将自动对数据表加 MDL 写锁，语句结束即释放。

因此 MDL 锁保证表结构和表数据不会同时更改。

什么是 DML？什么是 DDL？

DML：Data Modify Language。只包含增删改查（CRUD）操作：INSERT / SELECT / UPDATE / DELETE。

DDL：Data Definition Language。主要包含对数据表结构操作：CREATE / DROP / ALTER / TRUNCATE。

AUTO-INC 锁（特殊）

AUTO-INC 是用于保护自增量的轻量级锁。当创建一个 AUTO_INCREMENT 字段时，数据表会为其创建自增量。当然 InnoDB 保证只存在一个 AUTO_INCREMENT 字段。

不过特别的是，AUTO-INC 锁在何时释放是可配置的：

  • innodb_autoinc_lock_mode = 0：语句结束释放。
  • innodb_autoinc_lock_mode = 1：INSERT ... VALUES 获取立即释放，INSERT ... SELECT 语句结束释放。
  • innodb_autoinc_lock_mode = 2：默认，获取立即释放。

语句结束释放与获取立即释放的区别在于，语句结束释放保证自增字段按插入时刻排序，而获取立即释放只保证唯一。我们通常并不需要严格保证自增字段排序。

意向锁

意向锁是 InnoDB 中非常重要的概念，它是一种表级锁，在事务申请行级锁前申请。意向锁主要针对表锁提出，​用于优化表锁的互斥判定 。

假设没有意向锁，当用户申请表锁时，由于表锁应该与行锁互斥，因此申请表锁时必须遍历此表所有行锁未被占用，这是非常耗时的。因此 InnoDB 提出了意向锁的概念，事务申请任何行锁时必须首先申请意向锁，这就使得用户申请表锁时只需检查意向锁即可。当然，我们提到不建议用户使用表锁，因此在一些习惯下意向锁不发挥作用。

需要注意的是，由于意向锁仅仅表达意向，因此其读写锁之间是互容的，而和表锁存在互斥关系，​详见 "锁间容斥关系" 。

与多数锁相同，意向锁的生命周期在事务结束时结束。

行级锁

行级锁仅在索引中有效，因此确保您的查询不会触发扫描；查询在非索引中将对全表加锁。

当然，事实上全表加锁也是行级锁，只是它将对所有行加锁。这也体现了充分使用索引的重要性。

记录锁

记录锁（Record Lock，Rec Not Gap）分为读写锁，它对一条记录加锁。除了在 SERIALIZABLE 中会使用读锁外，其他场景下读操作都是无锁化的。

事务 A 事务 B
BEGIN; BEGIN;
INSERT INTO student VALUES(5, “JamhusTao”, 18);
UPDATE student SET name=“Mike” WHERE id=5;
COMMIT; (Waiting…)
COMMIT;

在这个示例中，由于事务 A 对 id=5 持有记录写锁，因此事务 B 试图进入 id=5 记录锁等待。

与多数锁相同，记录锁的生命周期在事务结束时结束。

间隙锁

间隙锁仅作用于 REPEATABLE-READ / SERIALIZABLE，因为在前两种事务隔离级别中幻读并不急需解决。

间隙锁（Gap Lock）为解决幻读而提出，它用于禁止新记录被插入加锁间隙中。根据幻读的定义，它是在两次条件查询中失去了或新增了记录，失去记录可以通过记录锁保护，而新增记录则需要间隙锁保护。

事务 A 事务 B
BEGIN; BEGIN;
SELECT * FROM student WHERE id=7 FOR UPDATE; – 假设记录不存在
INSERT INTO student VALUES(10, “JamhusTao”, 18);
COMMIT; (Waiting…)
COMMIT;

在这个示例中，事务 A 将持有区间 id∈(5, +∞)id \in (5,~+\infty) 的间隙锁，而事务 B 试图插入因此需要等待。事实上我们即将介绍它在等待插入意向锁。

事实上间隙锁也有读写锁之分，但是这两者实际在表现上没有任何区别。与意向锁相似，间隙锁间互容，而它们将与即将介绍的插入意向锁互斥。与记录锁相同，间隙读锁只在 SERIALIZABLE 中使用。

与多数锁相同，间隙锁的生命周期在事务结束时结束。

临键锁

临键锁（Next-Key Lock）等价于记录锁与间隙锁之和，例如表示 id=10id=10 的记录锁和表示 id∈(5, 10)id \in (5,~10) 的间隙锁都会保存在记录 id=10id=10 上，当两者同时存在时就称该记录持有临键锁，表示 id∈(5, 10]id \in (5,~10]。

事实上，临键锁只是简单的对记录锁与间隙锁之和的称呼，例如在 performance_schema.data_locks 中临键锁为 X、记录锁为 X, REC_NOT_GAP、间隙锁为 X, GAP。在区间查询中，临键锁往往比提到的另外两种锁更常见，在之后的场景分析中它将频繁出现。

与多数锁相同，临键锁的生命周期在事务结束时结束。

插入意向锁

插入意向锁（Insert Intention Lock）​不是意向锁而是间隙锁 ，它用于表达插入意向，会与间隙锁互斥，其作用即为与间隙锁共同解决幻读问题。当插入操作执行时，首先获取插入意向锁，再插入并获取记录锁，这也是间隙锁示例中的完整工作解释。

我们在间隙锁的示例中 Waiting... 处尝试查看当前锁：

1
SELECT THREAD_ID, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks\G;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
*************************** 1. row ***************************
  THREAD_ID: 124
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX                      # 事务 B 的意向写锁
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 2. row ***************************
  THREAD_ID: 124
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP,INSERT_INTENTION  # 插入意向锁
LOCK_STATUS: WAITING                 # 等待中...
  LOCK_DATA: 10
*************************** 3. row ***************************
  THREAD_ID: 123
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX                      # 事务 A 的意向写锁
LOCK_STATUS: GRANTED
  LOCK_DATA: NULL
*************************** 4. row ***************************
  THREAD_ID: 123
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP                   # 产生的间隙锁
LOCK_STATUS: GRANTED
  LOCK_DATA: 10

另外，插入意向锁具有特殊的生命周期，插入成功即释放。

锁间容斥关系

(表级锁) 表读锁 (S) 表写锁 (X) 意向读锁 (IS) 意向写锁 (IX)
表读锁 (S) ✔️ ❌ ✔️ ❌
表写锁 (X) ❌ ❌ ❌ ❌
意向读锁 (IS) ✔️ ❌ ✔️ ✔️
意向写锁 (IX) ❌ ❌ ✔️ ✔️
(行级锁) 记录锁 (RS) 记录锁 (RX) 间隙锁 (GS / GX) 插入意向锁 (IIX)
记录锁 (RS, SE) ✔️ ❌ ✔️ ✔️
记录锁 (RX) ❌ ❌ ✔️ ✔️
间隙锁 (GS, SE / GX, RR+) ✔️ ✔️ ✔️ ❌
插入意向锁 (IIX) ✔️ ✔️ ❌ ✔️

（两表为不同事务间容斥关系，相同事务内任意锁互容；其中行级锁为相同记录容斥关系，不同记录间任意锁互容。表中 SE 表示该锁仅在 SERIALIZABLE 中存在，RR+ 表示该锁仅在 REPEATABLE-READ 或更高隔离级别中存在。）

​为什么没有表级锁和行级锁间关系？ 这是由于意向锁的提出使得表级锁容斥关系和行级锁容斥关系是完全分离的，它们之间的互斥判定都由意向锁间接完成。

​为什么表中没有临键锁？ 临键锁即为记录锁与间隙锁之和，可自行推导：NS(RS+GS), SE / NX(RX+GX), RR+\text{NS}\left(\text{RS}+\text{GS}\right),~\text{SE}~/~\text{NX}\left(\text{RX}+\text{GX}\right),~\text{RR}^+。


场景分析

仅讨论 REPEATABLE-READ 隔离级别下的加锁场景分析，其他隔离级别大同小异可自行研究。

首先我们明确一下后面我将使用的案例数据表长这样：

1
2
SHOW CREATE TABLE student \G;
SELECT * FROM student;
1
2
3
4
5
6
7
8
9
10
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1
2
3
4
5
6
7
8
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  5 | Mike         |   18 |
|  6 | JamhusTao    |   18 |
|  8 | John         |   23 |
| 10 | GreatLiangpi |   20 |
+----+--------------+------+

不同索引下的加锁分析

扫描场景分析

扫描场景下，写操作将为所有记录加临键锁，在用户视角下扫描操作将为 ​全表上锁 。

因此，​用户应该尽量避免扫描 发生。

如何确定扫描 / 索引

许多时候我们可以通过经验判定扫描 / 索引，例如：SELECT * FROM student; 是扫描，SELECT * FROM student WHERE age BETWEEN 18 AND 60 OR id > 0; 是扫描，SELECT * FROM student WHERE age BETWEEN 18 AND 60; 是索引。

但有时经验判断不一定准确，例如下面我们将要给出的就是一些难以经验判断的。我们可以使用 EXPLAIN 查询语句执行计划，从而避免扫描发生。

1
EXPLAIN SELECT age FROM student WHERE age BETWEEN 18 AND 60 AND id > 0 FOR UPDATE \G;
1
2
3
4
5
6
7
8
9
10
11
12
13
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: index    # 索引扫描
possible_keys: PRIMARY,age
          key: age
      key_len: 5
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where; Using index
1
EXPLAIN SELECT age FROM student WHERE age BETWEEN 18 AND 60 AND id > 10 FOR UPDATE \G;
1
2
3
4
5
6
7
8
9
10
11
12
13
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: range    # 区间索引
possible_keys: PRIMARY,age
          key: PRIMARY  # 主键索引
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 50.00
        Extra: Using where
1
EXPLAIN SELECT age FROM student WHERE age BETWEEN 18 AND 19 AND id > 0 FOR UPDATE \G;
1
2
3
4
5
6
7
8
9
10
11
12
13
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: range    # 区间索引
possible_keys: PRIMARY,age
          key: age      # 二级索引
      key_len: 9
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index

我们可以通过 EXPLAIN 中的 type 字段检查索引类型：(效率升序)

  • ALL：全表扫描。
  • index：索引扫描，扫描二级索引记录，用于覆盖索引场景。由于二级索引比聚簇索引信息少，它比全表扫描更快。
  • range：索引范围扫描。
  • ref：非唯一索引等值索引。
  • eq_ref：唯一索引等值索引，常用于联结。
  • const：唯一索引（含主键索引）等值索引。

聚簇索引分析

主键索引（PRIMARY KEY）和生成聚簇索引（GEN CLUST INDEX）均为聚簇索引。一张表只有一个聚簇索引，当主键索引创建时使用它作为聚簇索引，不存在主键索引时使用生成聚簇索引。

聚簇索引比较简单，会按照需要在索引上加锁。例如下面这个示例：

1
2
SELECT * FROM student WHERE id BETWEEN 6 AND 8 FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
2
3
4
5
6
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  6 | JamhusTao |   18 |
|  8 | John      |   23 |
+----+-----------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX             # 意向写锁
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP  # 聚簇索引记录锁
  LOCK_DATA: 6
*************************** 3. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X              # 聚簇索引临键锁
  LOCK_DATA: 8

可以看到这里我们持有 PRIMARY 索引下的锁，通常无法只对 GEN CLUST INDEX 局部加锁，因为用户不直接访问该字段。至于为什么持有这些锁，我们会在 [唯一 / 普通索引加锁分析](#唯一 / 普通索引加锁分析) 中介绍。

二级索引分析

除主键索引（PRIMARY KEY）和生成聚簇索引（GEN CLUST INDEX）以外的均为二级索引。

二级索引的特殊之处在于，二级索引被加锁后，主键索引的对应记录也会加锁。不过，值得注意的是，二级索引可能持有间隙锁，而对应的主键索引只持有记录锁，无需持有间隙锁。例如下面这个示例：

1
2
SELECT id, age FROM student WHERE age = 18 FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
2
3
4
5
6
+----+------+
| id | age  |
+----+------+
|  5 |   18 |
|  6 |   18 |
+----+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX             # 意向写锁
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X              # 二级索引临键锁
  LOCK_DATA: 18, 5          # age, PRIMARY
*************************** 3. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X              # 二级索引临键锁
  LOCK_DATA: 18, 6          # age, PRIMARY
*************************** 4. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP  # 聚簇记录锁
  LOCK_DATA: 5
*************************** 5. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP  # 聚簇记录锁
  LOCK_DATA: 6
*************************** 6. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP          # 二级索引间隙锁
  LOCK_DATA: 20, 10         # age, PRIMARY

​为什么聚簇索引需要持记录锁？ 我们知道，当使用二级索引时存在回表操作，即在二级索引完成查找后仍需查找聚簇索引，因此聚簇索引应持有记录锁。​在这里聚簇索引无需持有间隙锁 的原因是，间隙锁用于解决幻读问题，而聚簇索引作为回表索引无需解决这个问题，只需在二级索引解决。

唯一 / 普通索引加锁分析

什么是唯一索引？

唯一索引是指 UNIQUE KEY，同时主键索引（PRIMARY KEY）和生成聚簇索引（GEN CLUST INDEX）也是唯一索引；其余均为普通索引。另外，我们在创建 UNIQUE 字段时就会自动创建唯一索引。

唯一索引与普通索引在锁机制中的区别在于，唯一索引可以提供额外优化和使用更小范围的锁。后面我们将会进一步介绍。

唯一索引的等值查询

唯一索引的等值查询分为两种情况：

  • 记录存在，获取一个记录锁。
  • 记录不存在，获取一个间隙锁。

我们这里以唯一索引 name 为例，查找存在的键。（场景分析 中我们介绍了表结构）

1
2
SELECT * FROM student WHERE name="JamhusTao" FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
2
3
4
5
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  6 | JamhusTao |   18 |
+----+-----------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: name
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP
  LOCK_DATA: 'JamhusTao', 6
*************************** 3. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP
  LOCK_DATA: 6

我们来看这个示例，首先行级操作申请意向锁，然后由于唯一索引等值查找记录存在，对 name="JamhusTao" 加记录锁。至于聚簇索引，我们在 二级索引分析 中已介绍。

​为什么只需持有记录锁？ 我们讲到间隙锁是用来解决幻读问题的，而在唯一索引中由于键不允许重复，因此当唯一索引等值查找记录存在时，只需持有记录锁即可。我们也将注意到，非唯一索引仅持有间隙锁是不够的。

依然以唯一索引 name 为例，查找不存在的键。（场景分析 中我们介绍了表结构）

1
2
SELECT * FROM student WHERE name="Tao" FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
Empty set
1
2
3
4
5
6
7
8
9
10
11
12
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: name
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: supremum pseudo-record

我们来看这个示例，首先行级操作申请意向锁，然后由于唯一索引等值查找记录存在，对 supremum pseudo-record 加间隙锁。由于查找的记录不存在，因此无需对聚簇索引加锁。

​什么是 supremum pseudo-record？ 它是一个尾后伪记录。在我们查找 name="Tao" 后需要防止用户插入而产生幻读，由于 name="Tao" 大于其他所有记录，因此这个防插入间隙应该是 (Mike,+∞)(\text{Mike}, +\infty)。在 InnoDB 中，间隙锁被保存在其右边界字段，尾后伪记录也为此存在。

唯一索引的区间查询

唯一索引的区间查询也分为两种情况：

  • 开边界（< / >）
    • 左开边界：边界持有临键锁，其余区域持临键锁。
    • 右开边界：边界持有间隙锁，其余区域持临键锁。
  • 闭边界（<= / >=）
    • 闭边界不存在，同开边界。
    • 闭边界存在。
      • 左闭边界：边界持记录锁，其余区域持临键锁。
      • 右闭边界：边界持间隙锁，其余区域持临键锁。
  • 双边界
    • 范围内为空集，同 唯一索引的等值查询 的不存在场景。
    • 否则，同前面两种情况复合，中间区间持临键锁。
1
2
3
SELECT * FROM student WHERE id > 6 FOR UPDATE;
-- SELECT * FROM student WHERE id > 7 FOR UPDATE;  -- 结果相同
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
2
3
4
5
6
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  8 | John         |   23 |
| 10 | GreatLiangpi |   20 |
+----+--------------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: 10
*************************** 4. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: 8
1
2
3
SELECT * FROM student WHERE id < 8 FOR UPDATE;
-- SELECT * FROM student WHERE id < 7 FOR UPDATE;  -- 结果相同
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
2
3
4
5
6
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | Mike      |   18 |
|  6 | JamhusTao |   18 |
+----+-----------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: 5
*************************** 3. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: 6
*************************** 4. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP
  LOCK_DATA: 8
1
2
SELECT * FROM student WHERE id <= 7 FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
2
3
4
5
6
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | Mike      |   18 |
|  6 | JamhusTao |   18 |
+----+-----------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: 5
*************************** 3. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: 6
*************************** 4. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP
  LOCK_DATA: 8
1
2
SELECT * FROM student WHERE id >= 8 FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
2
3
4
5
6
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  8 | John         |   23 |
| 10 | GreatLiangpi |   20 |
+----+--------------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP
  LOCK_DATA: 8
*************************** 3. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: supremum pseudo-record
*************************** 4. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: 10
1
2
3
SELECT * FROM student WHERE id > 6 AND id < 8 FOR UPDATE;
-- SELECT * FROM student WHERE id = 7 FOR UPDATE;  -- 结果相同
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
Empty set
1
2
3
4
5
6
7
8
9
10
11
12
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP
  LOCK_DATA: 8
1
2
SELECT * FROM student WHERE id >= 6 AND id < 9 FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
2
3
4
5
6
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  6 | JamhusTao |   18 |
|  8 | John      |   23 |
+----+-----------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP  # 左闭边界存在
  LOCK_DATA: 6
*************************** 3. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X              # 中间区间临键锁
  LOCK_DATA: 8
*************************** 4. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP          # 右开区间
  LOCK_DATA: 10

这里我们依然可以从如何解决幻读的角度触发来解释这些机制，这里就不作一一解释。

普通索引的等值查询

普通索引的等值查询分为两种情况：

  • 记录存在，记录持有临键锁，后一记录持有间隙锁。
  • 记录不存在，持有间隙锁。
1
2
SELECT * FROM student WHERE age=18 FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
2
3
4
5
6
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | Mike      |   18 |
|  6 | JamhusTao |   18 |
+----+-----------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X              # 左边界临键锁
  LOCK_DATA: 18, 5
*************************** 3. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X              # 区间临键锁
  LOCK_DATA: 18, 6
*************************** 4. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY        # 聚簇索引, 上节介绍
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP
  LOCK_DATA: 5
*************************** 5. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY        # 聚簇索引, 上节介绍
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP
  LOCK_DATA: 6
*************************** 6. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP          # 右边界间隙锁
  LOCK_DATA: 20, 10

由于非唯一索引可重复，因此在等值查找时需要在其左右添加间隙锁，防止相同的值插入产生幻读。

1
2
SELECT * FROM student WHERE age=21 FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
Empty set
1
2
3
4
5
6
7
8
9
10
11
12
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X,GAP
  LOCK_DATA: 23, 8

普通索引的区间查询

普通索引的区间查询非常简单，不知道为什么 InnoDB 没有为其作任何优化。​任意的普通索引的区间查询都总是持必要的临键锁 ，这种情况下永远不持有单一的记录锁和间隙锁。

1
2
SELECT * FROM student WHERE age >= 20 AND age < 60 FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
2
3
4
5
6
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
| 10 | GreatLiangpi |   20 |
|  8 | John         |   23 |
+----+--------------+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X              # 间隙锁足矣, 但还是临键锁
  LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X              # 此处确实不能降级为记录锁
  LOCK_DATA: 20, 10
*************************** 4. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X
  LOCK_DATA: 23, 8
*************************** 5. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP
  LOCK_DATA: 10
*************************** 6. row ***************************
OBJECT_NAME: student
 INDEX_NAME: PRIMARY
  LOCK_TYPE: RECORD
  LOCK_MODE: X,REC_NOT_GAP
  LOCK_DATA: 8
1
2
SELECT * FROM student WHERE age > 20 AND age < 23 FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks \G;
1
Empty set
1
2
3
4
5
6
7
8
9
10
11
12
*************************** 1. row ***************************
OBJECT_NAME: student
 INDEX_NAME: NULL
  LOCK_TYPE: TABLE
  LOCK_MODE: IX
  LOCK_DATA: NULL
*************************** 2. row ***************************
OBJECT_NAME: student
 INDEX_NAME: age
  LOCK_TYPE: RECORD
  LOCK_MODE: X              # 间隙锁足矣, 但还是临键锁
  LOCK_DATA: 23, 8

我也不清楚普通索引的区间查询是出于何种考量总是持有临键锁。在我的理解中解决幻读右边界只需总是间隙锁即可，或者是我考虑不周还是？