关于innodb:InnoDB-的并发控制锁与-MVCC

原文：https://nullwy.me/2023/07/innodb-locking/
如果感觉我的文章对你有用，请随便赞叹

目前支流数据库事务的并发管制实现，如 MySQL InnoDB、PostgreSQL、Oracle，都应用两阶段封闭 2PL 与 MVCC 技术，但具体实现细节上存在差别。InnoDB 是在以封闭技术为主体的状况下，用 MVCC 技术辅助实现读 - 写、写 - 读操作的并发。PostgreSQL 的并发控制技术是以 MVCC 技术为主，封闭技术为辅。本文次要关注 InnoDB 事务的并发管制实现。

并发管制，是数据库系统的 ACID 个性中的隔离性（Isolation)）的保障。所谓隔离性，就是事务的执行不应受到其余并发执行事务的烦扰，事务的执行看上去应与其余事务是隔离的。被隔离的执行，等价于事务的某种串行执行，或者说，它等价于一个没有并发的执行。保障串行性可能只容许极小的并发度，采纳较弱隔离性，能带来更高的并发度，是并发事务的正确性和性能之间的斗争。

晚期各大数据库厂商实现并发管制时多采纳基于封闭的并发控制技术，所以在基于封闭的技术背景下，才在 ANSI SQL-92 规范中提出了四种隔离级别：未提交读（Read Uncommitted）、己提交读（Read Committed）、可反复读（Repeatable Read）、可串行化（Serializable）（附注：为了书写简便本文将各个隔离级别顺次缩写为 RU、RC、RR、SER）。ANSI SQL-92 规范的四种隔离级别，是依据三种读异常现象（phenomena）定义的，隔离级别和异常现象的关系如下：

隔离级别	P1 脏读	P2 不可反复读	P4 幻读
Read Uncommitted	可能	可能	可能
Read Committed	防止	可能	可能
Repeatable Read	防止	防止	可能
Serializable	防止	防止	防止

ANSI SQL-92 规范文档对三种读异常现象的定义原文如下 [ref]：

The isolation level specifies the kind of phenomena that can occur during the execution of concurrent SQL-transactions. The following phenomena are possible:
1) P1 (“Dirty read”): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.
2) P2 (“Non-repeatable read”): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.
3) P3 (“Phantom”): SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search condition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.

除了脏读、不可反复读和幻读这 3 种读数据异样外，还有写数据异样，即脏写和失落更新。各个异样的含意如下：

P0 脏写（Dirty Write）：事务 T1 写某数据项，并且未提交或回滚，事务 T2 也写该数据项，而后事务 T1 或事务 T2 回滚，回滚导致另外一个事务的批改被连带回滚。脏写异样会导致事务无奈回滚，原子性无奈失去保障，所以全副隔离级别下都应该防止。脏写也能够叫回滚失落。
P1 脏读（Dirty Read）：读到了其余事务还未提交的数据。
P2 不可反复读（Non-Repeatable）：事务 T1 读取某数据项，事务 T2 批改 update 或删除 delete 该数据项，事务 T1 再次读取该数据项，后果不同。
P3 幻读（Phantom）：事务 T1 读取满足某条件的数据项汇合，事务 T2 生成新的满足该条件的数据项，事务 T2 再次读取满足该条件的数据项汇合，后果不同。
P4 失落更新（Lost Update）：事务 T1 读取某数据项，事务 T2 更新该数据项并提交，事务 T1 疏忽事务 T2 的更新，间接基于最后的读取数据项做更新并提交，导致事务 T2 的更新失落。失落更新也能够叫笼罩失落。

各个异样的读写操作序列的简化符号示意如下 [Berenson 1995]：

P0: w1[x]...w2[x]...(c1 or a1)                 事务 T2 脏写
A1: w1[x]...r2[x]...(a1 and c2 in any order)   事务 T2 脏读，r2[x] 为脏读
A2: r1[x]...w2[x]...c2...r1[x]...c1            事务 T1 不可反复读，两次 r1[x] 后果不同
A3: r1[P]...w2[y in P]...c2...r1[P]...c1       事务 T1 幻读，两次 r1[P] 后果不同
P4: r1[x]...w2[x]...w1[x]...c1                 事务 T2 更新失落，c1 导致 w2[x] 失落

其中 w1[x] 示意事务 T1 写入记录 x，r1[x] 示意事务 T1 读取记录 x，c1 示意事务 T1 提交，a1 示意事务 T1 回滚，r1[P] 示意事务 T1 依照谓词 P 的条件读取若干条记录，w1[y in P] 示意事务 T1 写入记录 y 满足谓词 P 的条件。

Berenson 的论文评判了 ANSI SQL-92 规范的异样定义。ANSI SQL-92 规范的异样的定义存在歧义，能够严格解释，也能够宽松解释，A1、A2 和 A3 的符号示意为严格解释，按严格解释，某些非凡的异样无奈囊括，所以举荐宽松解释。依照规范的定义，容易引起误会的是，在排除 P1 脏读、P2 不可反复、P3 幻读这三种读异样后就会失去可串行化隔离级别，然而事实并非如此。规范没有定义 P0 脏写和 P4 更新失落异样。另外，基于 MVCC 技术实现的快照隔离（Snapshot Isolation），能防止规范定义的 P1 脏读、P2 不可反复、P3 幻读，并且防止 P0 脏写和 P4 更新失落，但还存在写偏序（Write Skew）异样。

不可反复读和幻读的区别：

不可反复读对于事务 T2 的写操作是更新或删除操作，而幻读对于事务 T2 的写操作是插入（插入的新数据满足条件）或更新（使不满足条件的数据在更新后满足条件）操作。
对于幻读景象中事务 T2 的操作，如果操作是对现有数据的更新或删除操作，则表明这样的操作等同于不可反复读，即是在多个行数据上进行更新或删除，即在多个行数据上批量化重演了不可反复读景象。
不可反复读和幻象最大的区别就是前者只须要“锁住”（思考）曾经读过的数据，而幻读须要 对“还不存在的数据“做出预防 。不可反复读景象中事务 T2 着眼于 对现有数据进行操作 ；而幻读景象中事务 T2 着眼于对新增 （或不在锁定范畴内曾经存在的数据上做更新后而得的数据满足了谓词条件）数据。

异样由并发抵触引起，对应关系如下：

写写抵触：P0 脏写、P4 失落更新
写读抵触：P1 脏读
读写抵触：P2 不可反复读、P3 幻读

晚期各大数据库厂商实现并发管制时多采纳基于封闭的并发控制技术，所以在基于封闭的技术背景下，才在 ANSI SQL 规范中提出了四种隔离级别。基于锁的并发控制技术的加锁形式与隔离级别的关系表 [Berenson 1995]：

隔离级别	写锁	数据项的读锁	谓词的读锁
Read Uncommitted	长写锁	无锁要求	无锁要求
Read Commited	长写锁	短读锁	短谓词锁
Repeatable Read	长写锁	长读锁	短谓词锁
Serializable	长写锁	长读锁	长谓词锁

阐明：

短锁（short duration lock），以后正在执行的语句持有锁，语句执行结束锁被开释。长锁（long duration lock），当锁被持有后，直到事务提交之后才被开释。
RU 隔离级别，阻止 P0，长写锁
RC 隔离级别，阻止 P0、P1，长写锁 + 短读锁 + 短谓词锁
RR 隔离级别，阻止 P0、P1、P4、P2，长写锁 + 长写锁 + 短谓词锁
SER 隔离级别，阻止 P0、P1、P4、P2、P3，长写锁 + 长写锁 + 长谓词锁

基于锁的并发管制下，隔离级别和异常现象的关系：

隔离级别	P0 脏写	P1 脏读	P4 失落更新	P2 不可反复读	P4 幻读
Read Uncommitted	防止	可能	可能	可能	可能
Read Committed	防止	防止	可能	可能	可能
Repeatable Read	防止	防止	防止	防止	可能
Serializable	防止	防止	防止	防止	防止

各个隔离级别在基于锁的并发控制技术下的具体的实现阐明（参考自腾讯李海翔的《数据库事务处理的艺术》第 2 章）：

基于锁的并发管制，读 - 读操作能够并发执行，但读 - 写、写 - 读、写 - 写操作无奈并发执行，阻塞期待。MVCC 联合封闭技术，使得读-写、写-读操作互不阻塞，即只有写-写操作不能并发，并发度被进步到 75%，这就是 MVCC 被广为应用的起因。

InnoDB 的并发管制以封闭技术为主，MVCC 技术为辅助。让咱们先看下 InnoDB 的封闭技术。

InnoDB 存储引擎实现两种规范的 行级锁 模式，共享锁（读锁）和排他锁（写锁）[doc]：

共享锁（shared lock，S）：容许事务读一行数据。
排他锁（exclusive Lock，X）：容许事务删除或更新一行数据。

如果事务 T1 持有行 r 上的共享锁（S），则来自某个不同事务 T2 的对行 r 上的锁的申请将按如下形式解决：

T2 对 S 锁的申请能够立刻被授予。因而，T1 和 T2 持有 r 上的锁。
T2 对 X 锁的申请不能立刻被授予。

如果事务 T1 持有行 r 上的排他锁（X），则某个不同事务 T2 对 r 上任一类型的锁的申请无奈立刻被授予。相同，事务 T2 必须期待事务 T1 开释其对行 r 的锁定。

共享锁和排他锁的兼容性：

待申请 \ 已持有	共享锁 S	排他锁 X
共享锁 S	兼容	抵触
排他锁 X	抵触	抵触

辨别共享锁（读锁）和排它锁（写锁）后，读锁与读锁的并发可被容许进行，并发能力得以进步。

对于 update、delete 和 insert 语句，InnoDB 会主动给波及数据集加排他锁（X）；对于一般 select 语句，InnoDB 不会加任何锁（SERIALIZABLE隔离级别下除外）；事务能够通过以下语句显式给查问 select 显式加共享锁或排他锁：

共享锁（S）：select ... for share
排他锁（X）：select ... for update

当初让咱们来试验下共享锁和排他锁。创立 tbl 表，并增加表数据：

create table tbl 
  (a int, b int, c int, d int, primary key(a), unique key(b), key(c));
insert into tbl values
  (10, 10, 10, 10), (20, 20, 20, 20), (30, 30, 30, 30), 
  (40, 40, 40, 40), (50, 50, 50, 50), (60, 60, 60, 60), 
  (70, 70, 70, 70), (80, 80, 80, 80), (90, 90, 90, 90),
  (100, 100, 100, 100);

InnoDB 的排它锁示例，如下：

事务 1	事务 2
mysql> begin;	mysql> begin;
— 在 a = 10 的索引记录上增加排他锁 mysql> select * from tbl where a = 10 for update;
	— 阻塞，获取 a = 10 的排他锁超时 mysql> update tbl set b = 42 where a = 10; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	— 阻塞，获取 a = 10 的排他锁超时 mysql> update tbl set b = 42 where a >= 10;
	— 阻塞，获取 a = 10 的排他锁超时 mysql> delete from tbl where a = 10;
	— 阻塞，获取 a = 10 的排他锁超时 mysql> select * from tbl where a = 10 for update;
	— 更新胜利，能够取得其余记录的排他锁 mysql> update tbl set b = 42 where a = 20;
mysql> commit;
	— 更新胜利，在事务 1 开释锁后，其余事务能够获取排他锁 mysql> update tbl set b = 42 where a = 10;

InnoDB 的共享锁示例，如下：

事务 1	事务 2
mysql> begin;	mysql> begin;
— 在 a = 10 的索引记录上增加共享锁 mysql> select * from tbl where a = 10 for share;
	— 获取 a = 10 的共享锁胜利 mysql> select * from tbl where a = 10 for share;
	— 阻塞，获取 a = 10 的排他锁超时 mysql> update tbl set b = 42 where a = 10;
mysql> commit;
	— 更新胜利，在事务 1 开释锁后，其余事务能够获取排他锁 mysql> update tbl set b = 42 where a = 10;

InnoDB 存储引擎反对多粒度锁定（multiple granularity locking），这种锁定容许事务在行级上的锁和表级上的锁同时存在。为了反对在不同粒度上进行加锁操作，InnoDB 存储引擎反对一种额定的锁形式，称之为意向锁（Intention Lock）。意向锁是将锁定的对象分为多个档次，意向锁意味着事务心愿在更细粒度上进行加锁。

若将上锁的对象看成一棵树，那么对最上层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先须要对粗粒度的对象上锁。如果须要对页上的记录 r 进行上 X 锁，那么别离须要对数据库 A、表、页上意向锁 IX，最初对记录 r 上 X 锁。若其中任何一个局部导致期待，那么该操作须要期待粗粒度锁的实现。

在一个对象加锁之前，该对象的全副先人节点均加上了意向锁。心愿给某个记录加锁的事务必须遍历从根到记录的门路。在遍历树的过程中，该事务给各节点加上意向锁。

举例来说，假如在表 1 的记录 r 上持有 X 锁，表 1 上必然持有 IX 锁。如果其余事务想在表 1 上加 S 表锁或 X 表锁，但与已有 IX 锁不兼容，所以该事务须要期待。再举例，假如表 1 持有 S 锁，如果其余事务想在表 1 的记录 r 上加 X 锁，须要先取得表 1 的 IX 锁，但与已有 S 锁不兼容，所以该事务须要期待。有了意向锁之后，就能疾速判断行锁和表锁之间是否兼容。

InnoDB 存储引擎反对意向锁设计比拟简练，其意向锁即为 表级别的锁，两种意向锁 [doc]：

动向共享锁（IS）：事务打算给数据行加行共享锁（S），事务在给一个数据行加共享锁（S）前必须先获得该表的 IS 锁。
动向排他锁（IX）：事务打算给数据行加行排他锁（X），事务在给一个数据行加排他锁（X）前必须先获得该表的 IX 锁。

IS、IX、S、X 锁的兼容性：

待申请 \ 已持有	IS	IX	S	X
IS	兼容	兼容	兼容	抵触
IX	兼容	兼容	抵触	抵触
S	兼容	抵触	兼容	抵触
X	抵触	抵触	抵触	抵触

兼容关系：各种意向锁（IS、IX）之间全副兼容，动向共享锁 IS 和共享锁 S 兼容，共享锁 S 和共享锁 S 兼容，其余抵触。

SQL 语句能够分为数据定义语言（DDL）、数据管制语言（DCL）、数据查询语言（DQL）、数据操纵语言（DML）四种类型的语句，前两种语句，波及的对象在数据之上，所以加锁的范畴，通常是表级，对应表级锁。后两种语句操作的对象是数据，加锁的范畴，通常是数据级，这就对应行级锁。

InnoDB 行锁分为 3 种类型 [doc]：

记录锁（record lock）：对索引记录项加锁。
间隙锁（gap lock）：间隙锁，对索引记录项之间的“间隙”、笫一条记录前的“间隙”或最初一条记录后的“间隙“加锁。锁定一个范畴，但不蕴含索引记录自身。
next-key 锁（next-key Lock）：前两种锁的组合，记录锁 + 间隙锁，锁定一个范畴，并且锁定索引记录自身。（中文世界有时将 next-key lock 翻译为“临键锁”）

如果索引上蕴含 10, 20, 30, 40, 50 这些记录，那么可能的 next-key 锁的锁区间（interval），如下：

(- 无穷, 10]     即，间隙锁 (- 无穷, 10) + 记录锁 10。区间为，左开右闭区间
(10, 20]       即，间隙锁 (10, 20) + 记录锁 20
(20, 30]       即，间隙锁 (20, 30) + 记录锁 30
(30, 40]       即，间隙锁 (30, 40) + 记录锁 40
(40, 50]       即，间隙锁 (40, 50) + 记录锁 50
(50, + 无穷]     即，间隙锁 (50, + 无穷)

最初一个锁区间 (50, + 无穷]，对应的是上界伪记录（supremum pseudo-record），不是实在存在的记录。这个锁区间用于避免在最大值 50 之后插入记录。

记录锁总是会去锁住索引记录，如果 InnoDB 存储引擎表在建设的时候没有设置任何一个索引，那么这时 InnoDB 存储引擎会应用隐式的主键来进行锁定。

MySQL 默认的事务隔离级别是可反复读（REPEATABLE-READ），如果把事务隔离级别改成已提交读（READ-COMMITTED），间隙锁会被禁用。禁用间隙锁后，幻读异样会呈现，因为其余事务能够在间隙中插入新行。InnoDB 的间隙锁，就是为了解决幻读异样而引入的。对于幻读异样，参见官网文档 doc。

RR 隔离级别下，InnoDB 的锁通常应用 next-key 锁。然而，在惟一索引（和主键索引）上的等值查问 ，next-key 锁进化为记录锁，间隙锁并不需要，即仅锁住索引自身，而不是范畴。 如果在惟一索引（和主键索引）上做范畴查问，间隙锁仍然须要。官网文档形容如下 [doc]：

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)

间隙锁是“纯抑制性的”，间隙锁惟一的作用就是为了避免其余事务的插入到间隙中。间隙锁和间隙锁之间是互不抵触的，所以间隙共享 S 锁和间隙排他 X 锁没有任何区别。

另外，还有一种锁叫 插入意向锁（insert intention lock），基于间隙锁，专门用于 insert 操作。在执行 insert 操作时，须要先申请获取插入意向锁，也就是说，须要先查看以后插入地位上的下一条记录上是否持有间隙锁，如果被间隙锁锁住，则锁抵触，插入被阻塞。多个事务做 insert 操作，被雷同的间隙锁阻塞，如果插入的值各不相同，这些事务的 insert 操作之间不阻塞。

所以，间隙锁与插入意向锁的兼容关系是，已持有的间隙锁与待申请的插入意向锁抵触，而插入意向锁之间兼容，在一个间隙锁锁上能够有多个意向锁期待。

IS、IX、X、S 锁和记录锁、间隙锁、next-key 锁的关系：

IS、IX、S、X 是锁模式（lock mode）（源码层面上对应 lock_mode 枚举）。
记录锁、间隙锁、next-key 锁、插入意向锁是行锁类型（record lock type）。
每一种行锁类型，都蕴含 IS、IX、S、X 锁模式，如共享的记录锁、排他的记录锁、共享的间隙录锁、排他的间隙锁等。

MySQL 8.0 之前，information_schema 库提供 innodb_trx、innodb_locks 和 innodb_lock_waits 三张表，用来监控事务和诊断潜在的锁问题，具体介绍能够参见官网 5.7 文档 doc。

innodb_trx：以后事务表
innodb_locks：锁期待中的锁信息表
innodb_lock_waits：锁期待中的事务表

在 MySQL 8.0 之前，要想取得以后已持有的锁信息，须要开启参数 innodb_status_output_locks 并且执行命令 show engine innodb status，具体介绍能够参见官网文档“15.17 InnoDB Monitors”，doc。

MySQL 8.0 开始，innodb_locks 表和 innodb_lock_waits 表，被 performance_schema 库的 data_locks 表和 data_lock_waits 表代替。其中值得注意的不同点是，新的 data_locks 表，同时蕴含了已持有的锁和申请中的锁的信息，这样查看以后已持有的锁信息更加不便。相干 SQL 示例：

-- 查问全副锁信息
select * from performance_schema.data_locks \G
-- 查问全副记录锁的锁信息
select * from performance_schema.data_locks where LOCK_TYPE = 'RECORD' \G
-- 查问期待中的锁信息
select * from performance_schema.data_locks where LOCK_STATUS = 'WAITING' \G
-- 查问锁期待中的事务
select * from performance_schema.data_lock_waits \G
-- 应用 sys 库的 innodb_lock_waits 视图
-- 查问锁期待中的事务
select * from sys.innodb_lock_waits \G

命令 show engine innodb status 的输入和 data_locks 表的对应关系，能够参考文章 link。

本文的全副案例采纳的 MySQL 版本为 8.0.30。MySQL 的默认事务隔离级别是 REPEATABLE-READ（可反复读），事务隔离级别能够通过零碎变量 transaction_isolation 管制。

-- 事务隔离级别，默认为可反复读（Repeatable Read）mysql> select @@global.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

tbl 表的数据如下：

mysql> select * from tbl;
+-----+------+------+------+
| a   | b    | c    | d    |
+-----+------+------+------+
|  10 |   10 |   10 |   10 |
|  20 |   20 |   20 |   20 |
|  30 |   30 |   30 |   30 |
|  40 |   40 |   40 |   40 |
|  50 |   50 |   50 |   50 |
|  60 |   60 |   60 |   60 |
|  70 |   70 |   70 |   70 |
|  80 |   80 |   80 |   80 |
|  90 |   90 |   90 |   90 |
| 100 |  100 |  100 |  100 |
+-----+------+------+------+
10 rows in set (0.00 sec)

SQL 语句：

select * from tbl where a = 10 for update;

data_locks 表中的行锁数据：

mysql> select * from performance_schema.data_locks where LOCK_TYPE = 'RECORD' \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4816436360:185:4:3:140408965390368
ENGINE_TRANSACTION_ID: 56664
            THREAD_ID: 367
             EVENT_ID: 22
        OBJECT_SCHEMA: testdb
          OBJECT_NAME: tbl
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140408965390368
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10

加锁状况：

在 a = 10 的索引记录上增加排他记录锁（X,REC_NOT_GAP）

其余 SQL 语句的加锁状况（通过查 data_locks 表确认）：

-- 在 a = 10 的索引记录上增加共享记录锁（S,REC_NOT_GAP）select * from tbl where a = 10 for share;

-- 在 a = 10 的索引记录上增加排他记录锁（X,REC_NOT_GAP）update tbl set b = 42 where a = 10;

-- 在 a = 10 的索引记录上增加排他记录锁（X,REC_NOT_GAP）delete from tbl where a = 10;

加锁与锁抵触 SQL 演示：

事务 1	事务 2
mysql> begin;	mysql> begin;
— 在 a = 10 的索引记录上增加排他记录锁 mysql> select * from tbl where a = 10 for update;
	— 阻塞，因为 a = 10 上存在排他记录锁 mysql> select * from tbl where a = 10 for update; — 阻塞，因为 a = 10 上存在排他记录锁 mysql> insert into tbl (a) values (10);
	— 插入胜利 mysql> insert into tbl (a) values (9); — 插入胜利 mysql> insert into tbl (a) values (11);
mysql> rollback;	mysql> rollback;