MySQL: InnoDB: 锁（locking）

[dushaoshuai]

一些准备

为了更好地观察锁，需要做一些提前准备：

了解 InnoDB 的一些机制

了解事务、隔离级别、B+ 树等。

确保 Performance Schema 已启用

执行以下语句查看 performance_schema 变量的值，1 说明 Performance Schema 已启用，0 说明未启用：

> SELECT @@GLOBAL.performance_schema;
+-----------------------------+
| @@GLOBAL.performance_schema |
+-----------------------------+
|                           1 |
+-----------------------------+

如果是未启用的话，可以参考文档将 Performance Schema 打开。打开后，Performance Schema 存储引擎会操作 performance_schema 数据库里的表，给这些表填充数据。

对几个表的表结构有基本了解

table/view	information
INFORMATION_SCHEMA.INNODB_TRX	事务
PERFORMANCE_SCHEMA.data_locks	事务和锁
PERFORMANCE_SCHEMA.data_lock_waits	事务正在等待的锁
sys.innodb_lock_waits	事务正在等待的锁

确认事务的隔离级别

需要事先建立这样一个认知：REPEATABLE READ 和 READ COMMITTED 两种隔离级别下的锁有不同表现：

• READ COMMITTED 隔离级别下，间隙锁（gap lock）被禁用（只用于外键约束检查和 duplicate-key 检查）^1

因此，如果发现加锁情况不是自己所预期的那样，可以检查一下隔离级别：

> SELECT @@SESSION.transaction_isolation;
+--------------------------------+
| @@SESSION.transaction_isolation |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+
1 row in set (0.035 sec)

设置隔离级别为自己想要的，例如 REPEATABLE READ：

> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.061 sec)

准备一张表

CREATE TABLE `locks` (
  `unique` bigint NOT NULL COMMENT '测试在唯一索引上加锁',
  `index` int NOT NULL DEFAULT '0' COMMENT '测试在非唯一索引上加锁',
  `no_index` varchar(20) DEFAULT '' COMMENT '测试无索引列加锁',
  PRIMARY KEY (`unique`) COMMENT '唯一索引',
  KEY `index` (`index`) COMMENT '非唯一索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试 InnoDB 加锁'

INSERT INTO `locks` VALUES
    (1, 30, 'a'),
    (5, 30, 'c'),
    (6, 20, 'g'),
    (15, 15, 'b'),
    (25, 24, 'x'),
    (26, 30, 'c');

锁

共享锁和互斥锁

共享锁（shared (S) locks）允许持有锁的事务进行读操作。

互斥锁（exclusive (X) locks）允许持有锁的事务进行写操作（更新/删除）。

不同事务对相同对象加锁时，锁的兼容性如下：

	S	X
S	Compatible	Conflict
X	Conflict	Conflict

以下为了方便，使用 S 表示共享锁，使用 X 表示互斥锁。

行级锁和表级锁

InnoDB 支持多种粒度的锁：表级锁和行级锁。并且允许在同一张表上同时出现表级锁和行级锁。

行级锁按照锁的排他性可以分为 S 和 X 锁，兼容性同上所示。

表级锁也有 S 和 X 锁，同时为了支持多种粒度的锁并存，又有意向锁（intention locks）。意向锁是表级锁，作用是表明有事务（一个或多个）有意向锁定表中的行（可能已经加了锁了，也可能正要去加锁）。意向锁在表级锁和行级锁之间起到一个协调的作用，他告诉那些想要给整张表加锁的事务：现在有其他事务正在给表中的行加行锁，你可能要等等哦（具体等不等要看锁之间的兼容性）。

意向锁按照排他性可以分为意向共享锁（intention shared lock (IS)）和意向排他锁（intention exclusive lock (IX)）。

• IS 锁表明有事务有意向给表中的行加 S 锁，例如 SELECT ... FOR SHARE 语句会给表加 IS 锁
• IX 锁表明有事务有意向给表中的行加 X 锁，例如 SELECT ... FOR UPDATE 语句会给表加 IX 锁

个人经验，一般实践中不会主动给整张表加锁，因此下面不会再讨论表级别的 S 和 X 锁。

意向锁之间是相互兼容的，因为一个事务锁定表中的某几行并不妨碍另一个事务锁定表中的另外几行。

	IS	IX
IS	Compatible	Compatible
IX	Compatible	Compatible

观察行级锁和意向锁

在 READ COMMITTED 隔离级别下观察（因为没有 gap lock，减少干扰）。

在一个连接中，执行以下语句：

> START TRANSACTION;
Query OK, 0 rows affected (0.099 sec)

> SELECT * FROM `locks` WHERE `no_index` = 'c' FOR UPDATE;
+--------+-------+----------+
| unique | index | no_index |
+--------+-------+----------+
|      5 |    30 | c        |
|     26 |    30 | c        |
+--------+-------+----------+
2 rows in set (0.033 sec)

> COMMIT;
Query OK, 0 rows affected (0.034 sec)

在以上语句中 COMMIT 之前，在另一个连接中查看锁：

> SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
|              69475311 | db_1          | locks       | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|              69475311 | db_1          | locks       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 5         |
|              69475311 | db_1          | locks       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 26        |
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.034 sec)

• 在 locks 表上有一个表级锁，IX 锁
• 有两个行级锁，X 锁，锁住两条记录（这里暂时省略一些细节）

行级锁

行级锁按照锁定范围和作用不同，可以分为

• （索引）记录锁（record lock）
• 间隙锁（gap lock）
• next-key lock
• 插入意向锁（insert intention lock）

记录锁

记录锁锁定索引记录。如果一个表没有用户定义的索引，InnoDB 会自动创建一个隐藏的 clustered index 并在这个索引上加锁。

观察记录锁

（一些操作的细节如上，不再赘述）

在 READ COMMITTED 隔离级别下，一条这样的语句：

> SELECT * FROM `locks` WHERE `index` = 30 FOR SHARE;
+--------+-------+----------+
| unique | index | no_index |
+--------+-------+----------+
|      1 |    30 | a        |
|      5 |    30 | c        |
|     26 |    30 | c        |
+--------+-------+----------+
3 rows in set (0.034 sec)

加锁情况如下：

> SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
|       422068543825776 | db_1          | locks       | NULL       | TABLE     | IS            | GRANTED     | NULL      |
|       422068543825776 | db_1          | locks       | index      | RECORD    | S,REC_NOT_GAP | GRANTED     | 30, 1     |
|       422068543825776 | db_1          | locks       | index      | RECORD    | S,REC_NOT_GAP | GRANTED     | 30, 5     |
|       422068543825776 | db_1          | locks       | index      | RECORD    | S,REC_NOT_GAP | GRANTED     | 30, 26    |
|       422068543825776 | db_1          | locks       | PRIMARY    | RECORD    | S,REC_NOT_GAP | GRANTED     | 1         |
|       422068543825776 | db_1          | locks       | PRIMARY    | RECORD    | S,REC_NOT_GAP | GRANTED     | 5         |
|       422068543825776 | db_1          | locks       | PRIMARY    | RECORD    | S,REC_NOT_GAP | GRANTED     | 26        |
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
7 rows in set (0.035 sec)

• 除了一个 IS 锁是表锁外，其余都是记录锁
• 6 个记录锁 LOCK_MODE 都是 S,REC_NOT_GAP，强调这些锁都是记录锁，不涉及间隙锁
• 3 个记录锁锁定了非唯一索引 index 的 3 条索引记录：30, 1，30, 5，30, 26
• 同时观察到，3 个记录锁锁定了主键索引的 3 条索引记录：1，5，26

间隙锁

间隙锁在索引记录之间的间隙上加锁。

间隙锁的唯一作用是阻止其他事务向间隙中插入，避免幻行（phantom rows）的发生。间隙锁是相互兼容的，共享间隙锁和排他间隙锁在功能上完全相同，不同事务可以在同一个间隙上持有相互冲突的锁。

READ COMMITTED 隔离级别下，间隙锁被禁用，只用于外键约束检查和 duplicate-key 检查。这是一种在性能和并发之间的权衡。

观察间隙锁

同样的语句：

> SELECT * FROM `locks` WHERE `unique` = 7 FOR SHARE;
Empty set (0.033 sec)

在不同隔离级别下加锁不同。

REPEATABLE READ 隔离级别下的间隙锁

> SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
|       422068543825776 | db_1          | locks       | NULL       | TABLE     | IS        | GRANTED     | NULL      |
|       422068543825776 | db_1          | locks       | PRIMARY    | RECORD    | S,GAP     | GRANTED     | 15        |
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
2 rows in set (0.034 sec)

• 为了阻止其他事务向主键索引的记录 6 和 15 之间的间隙 (6, 15) 中插入，一个间隙锁（S,GAP）锁住了这个间隙

READ COMMITTED 隔离级别下的间隙锁

> SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
|       422068543825776 | db_1          | locks       | NULL       | TABLE     | IS        | GRANTED     | NULL      |
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
1 row in set (0.040 sec)

• 没有间隙锁

next-key lock

next-key lock 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。

观察 next-key lock

同样的语句：

> SELECT * FROM `locks` WHERE `unique` BETWEEN 3 AND 12 FOR UPDATE;
+--------+-------+----------+
| unique | index | no_index |
+--------+-------+----------+
|      5 |    30 | c        |
|      6 |    20 | g        |
+--------+-------+----------+
2 rows in set (0.048 sec)

在不同隔离级别下加锁不同。

REPEATABLE READ 隔离级别下

> SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
|              69341463 | db_1          | locks       | NULL       | TABLE     | IX        | GRANTED     | NULL      |
|              69341463 | db_1          | locks       | PRIMARY    | RECORD    | X         | GRANTED     | 5         |
|              69341463 | db_1          | locks       | PRIMARY    | RECORD    | X         | GRANTED     | 6         |
|              69341463 | db_1          | locks       | PRIMARY    | RECORD    | X,GAP     | GRANTED     | 15        |
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
4 rows in set (0.037 sec)

• 行级锁有 3 个，2 个 next-key lock（X 后面为空），1 个间隙锁（X,GAP）
• 两个 next-key lock 锁在主键索引的记录 5 和 6 上，锁住的区间为 (1, 5] 和 (5, 6]
• 一个间隙锁锁住的区间为 (6, 15)，这里没有必要锁住 15

READ COMMITTED 隔离级别下

> SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
|              69499232 | db_1          | locks       | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|              69499232 | db_1          | locks       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 5         |
|              69499232 | db_1          | locks       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 6         |
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.035 sec)

• 只有 5 和 6 两个主键索引记录被加了记录锁，不涉及间隙锁

performance_schema.data_locks 表的 LOCK_MODE 字段

辨别记录锁、间隙锁、next-key lock：

(lock)	LOCK_MODE
record	*,REC_NOT_GAP
gap	*,GAP
next-key	*

• 这里 * 表示 S 或者 X

在 REPEATABLE READ 隔离级别下做进一步观察

对非唯一索引列加锁

语句：

> SELECT * FROM `locks` WHERE `index` = 24 FOR UPDATE;
+--------+-------+----------+
| unique | index | no_index |
+--------+-------+----------+
|     25 |    24 | x        |
+--------+-------+----------+
1 row in set (0.040 sec)

产生的锁为：

> SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
|              69504257 | db_1          | locks       | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|              69504257 | db_1          | locks       | index      | RECORD    | X             | GRANTED     | 24, 25    |
|              69504257 | db_1          | locks       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 25        |
|              69504257 | db_1          | locks       | index      | RECORD    | X,GAP         | GRANTED     | 30, 1     |
+-----------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.040 sec)

• 非唯一索引 index 被锁住的区间为 (20, 24] 和 (24, 30)
• 主键的索引记录 25 上也被加了记录锁

对无索引列加锁

语句：

> SELECT * FROM `locks` WHERE `no_index` = 'a' FOR UPDATE;
+--------+-------+----------+
| unique | index | no_index |
+--------+-------+----------+
|      1 |    30 | a        |
+--------+-------+----------+
1 row in set (0.038 sec)

产生的锁为：

> SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
|              69501380 | db_1          | locks       | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
|              69501380 | db_1          | locks       | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
|              69501380 | db_1          | locks       | PRIMARY    | RECORD    | X         | GRANTED     | 1                      |
|              69501380 | db_1          | locks       | PRIMARY    | RECORD    | X         | GRANTED     | 5                      |
|              69501380 | db_1          | locks       | PRIMARY    | RECORD    | X         | GRANTED     | 6                      |
|              69501380 | db_1          | locks       | PRIMARY    | RECORD    | X         | GRANTED     | 15                     |
|              69501380 | db_1          | locks       | PRIMARY    | RECORD    | X         | GRANTED     | 25                     |
|              69501380 | db_1          | locks       | PRIMARY    | RECORD    | X         | GRANTED     | 26                     |
+-----------------------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
8 rows in set (0.034 sec)

• 所有的主键索引记录上都加了 next-key lock
• supremum pseudo-record 是一个伪记录，大于表里所有已有的主键索引记录
• 这里其实相当于把整张表都锁上了

插入意向锁（Insert Intention Locks）

插入意向锁是一种间隙锁，作用是：多个事务向同一个索引间隙中插入时，如果插入的位置不同，则不必相互等待。

// TODO: 插入意向锁能观察到吗？

其他锁

AUTO-INC Locks 是一种特殊的表级锁，和 AUTO_INCREMENT 有关。

参见

• 15.7.1 InnoDB Locking
• MySQL Glossary - intention lock
• 15.7.2.1 Transaction Isolation Levels
• 13.3.7 SET TRANSACTION Statement

进一步阅读

• 15.7.3 Locks Set by Different SQL Statements in InnoDB
• 15.7.5 Deadlocks in InnoDB
• 间隙锁和向间隙中插入