MySQL 八股文之 MVCC 实现原理（多图易懂）

原创 OnTheRoad 2022-11-22

1158

1. MVCC 概述

MVCC（Multi-Version Concurrency Control）是 InnoDB 引擎在事务隔离级别 Read Committed、Repeatable Read 实现并发事务的依据。通过记录数据的多个历史版本来替代锁。以实现事务间的隔离效果，保证多事务的非阻塞读。

快照读与当前读

当前读：读取的是记录的最新版本数据，读取时需要保证其他事务不能修改当前记录，会对读取的行进行加锁。当前读的常见 SQL 语句：
- 加共享锁：SELECT … LOCK IN SHARE MODE
- 加排它锁：SELECT … FOR UPDATE、UPDATE、INSERT、DELETE
快照读：读取的是记录的可见版本，有可能是数据的历史版本，不会对读取的行进行加锁（非阻塞读）。如简单的 SELECT 语句都是快照读。InnoDB 通过 MVCC 机制实现快照读。

2. MVCC 的核心

表的隐藏列：DB_TRX_ID（记录操作当前数据的事务ID）、DB_ROLL_PTR（记录上个版本数据的地址，指向 undo log）。
undo log：记录数据各版本的修改历史，即“版本链”。
Read View：读视图，用于判断哪些数据版本对当前 SELECT 可见。

2.1. 隐藏列

在内部，InnoDB 为存储的每行数据，增加了如下 3 个字段：

DB_TRX_ID（6 字节）：称为”事务 ID“，标记 insert 或 update 该行数据的最后一个事务的事务 ID。此外，delete 操作在 InnoDB 内部被视为 update。InnoDB 通过标记行中的特殊位（bit）来表示”已删除“。
DB_ROLL_PTR（7 字节）：称为”回滚指针（roll pointer）“，用于指向 undo tablespace 中回滚段（rollback segment）的一条 undo log 记录。若数据行被更新，则该指针指向的 undo log 中包含重建更新之前该行数据所需的信息。回滚段中包含 insert undo logs 和 update undo logs。 Insert undo logs 只在事务回滚时需要，一旦事务提交就可以被丢弃（discarded ）。update undo logs 除了事务回滚时需要之外，也用于构建 InnoDB 一致性读。在一致性读中，需要 update undo logs 中的信息来构建早期版本的数据行。
DB_ROW_ID（6字节）：称为”行 ID“，是 MySQL 实例中全局（单个表内，可能不连续）分配的单调递增的值。即作为无主键表的隐式主键。当表中存在仅由 单个整型列 构成的 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，则可在 SELECT 语句中使用 _rowid 来引用索引列的值。即 _rowid 实际为索引列的别名。

以上 3 个隐藏字段，可通过 ibd2sdi（MySQL 8.0 开始提供）工具来查看。

2.1.1. 显式 _rowid

当表中存在仅由 单个整型列 构成的 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，则 _rowid 隐藏列实际为索引列的引用。可在 SELECT 语句中使用 _rowid 来查询索引列的值。这种 _rowid 称为“显式_rowid”。

_rowid 需要表具有的 3 个必备要素：

1. 单列索引
2. 数据类型为整型
3. 索引类型为 `PRIMARY KEY` 或 `UNIQUE NOT NULL` 索引

以下示例中，可以通过 SELECT 语句直接查询的 _rowid，称为 ”显式 _rowid“。

## 1. 单列整型主键的表中，包含隐藏列 _rowid
mysql> CREATE TABLE t_pk(id INT PRIMARY KEY, name VARCHAR(32)) SELECT id, name FROM (VALUES ROW(1,'one'), ROW(2,'two')) AS v(id, name);

mysql> SELECT id, name, _rowid FROM t_pk;
+----+------+--------+
| id | name | _rowid |
+----+------+--------+
|  1 | one  |      1 |
|  2 | two  |      2 |
+----+------+--------+
2 rows in set (0.00 sec)

## 2. 单列整型非空唯一索引的表中，包含隐藏列 _rowid
mysql> CREATE TABLE t_unik(id INT NOT NULL, name VARCHAR(32), UNIQUE KEY(id)) SELECT id, name FROM (VALUES ROW(3,'three'), ROW(4,'four')) AS v(id, name);

mysql> SELECT id, name, _rowid FROM t_unik;
+----+-------+--------+
| id | name  | _rowid |
+----+-------+--------+
|  3 | three |      3 |
|  4 | four  |      4 |
+----+-------+--------+
2 rows in set (0.00 sec)

## 3. 不满足 `单个整型列` 构成的 `主键` 或 `非空唯一索引` 时，表中没有 _rowid 列。
mysql> CREATE TABLE t_c_unik(id VARCHAR(8) NOT NULL, name VARCHAR(32), UNIQUE KEY(id)) SELECT id, name FROM (VALUES ROW('a','three'), ROW('b','four')) AS v(id, name);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT id, name, _rowid FROM t_c_unik;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'

2.1.2. 隐式 _rowid

当表中未设置主键时，InnoDB 会为数据行生成一个 6 字节长度（最大为 $2^{48}$-1，即 281474976710655）的无符号数（dictsys.row_id）作为 _rowid 列的值，只是这个 _rowid 无法显式通过 SELECT 获取。此种情况的 _rowid 称为 ”隐式 _rowid“。此_rowid 也是表的隐式主键。

分配的无符号数由 InnoDB 变量 dictsys.row_id 在 MySQL 实例中全局分配（由所有无主键的表共享）。当自增到最大值 $2^{48}$-1 后，会重新复位从 0 开始。当表中出现相同的 _rowid 时，新插入的数据会根据 _rowid 覆盖掉原有的旧数据。现象类似于根据 _rowid 进行更新覆盖。

_rowid 导致的数据覆盖

以下过程在 CentOS 7.9 中的 MySQL 5.7.39 中测试通过。

安装 gdb 工具，可用于修改全局变量 dictsys.row_id 的值

## 1. 安装 gdb 工具
[root@mysql8 ~]# yum install -y gdb

## 2. 获取 mysqld 的 PID，这里为 3431
mysql> system ps -ef|grep mysqld
root      2649     1  0 11:01 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mysql/database/ --pid-file=/mysql/pid/mysql.pid
mysql     3431  2649  0 11:01 ?        00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mysql/database --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mysql/mysql-log/mysql.err --open-files-limit=65535 --pid-file=/mysql/pid/mysql.pid --socket=/mysql/socket/mysql.sock --port=3306

gdb 根据 mysqld 进程 PID（3431），修改 dictsys.row_id 的值

## 3. 根据 mysqld 的 PID，将 dictsys.row_id 设置为 1
[root@mysql8 ~]# gdb -p 3431 -ex 'p dict_sys->row_id=1' -batch

<省略部分输出内容>
[New LWP 3432]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
0x00007f8eeea31ddd in poll () from /lib64/libc.so.6
$1 = 1
[Inferior 1 (process 3431) detached]

创建无主键的测试表

mysql> CREATE TABLE t_nopk( id INT, name VARCHAR(32) );
mysql> INSERT INTO t_nopk( id, name) VALUES(1001,'ShangHai'), (1002,'BeiJing'),(1003,'GuangZhou');

mysql> SELECT * FROM t_nopk;
+------+-----------+
| id   | name      |
+------+-----------+
| 1001 | ShangHai  |
| 1002 | BeiJing   |
| 1003 | GuangZhou |
+------+-----------+
3 rows in set (0.00 sec)

因 dictsys.row_id = 1，所以插入的 3 条数据对应的 _rowid 分别为 1、2、3。

将全局变量 dictsys.row_id 置为 281474976710656。

[root@mysql8 ~]# gdb -p 3431 -ex 'p dict_sys->row_id=281474976710656' -batch

<省略部分输出内容>
[New LWP 3432]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
0x00007f8eeea31ddd in poll () from /lib64/libc.so.6
$1 = 281474976710656
[Inferior 1 (process 3431) detached]

插入新数据

mysql> INSERT INTO t_nopk(id, name) VALUES(1004,'天津'),(1005,'沈阳'),(1006,'东莞');

mysql> SELECT * FROM t_nopk;
+------+-----------+
| id   | name      |
+------+-----------+
| 1004 | 天津      |
| 1005 | 沈阳      |
| 1006 | 东莞      |
| 1003 | GuangZhou |
+------+-----------+
4 rows in set (0.00 sec)

当 dictsys.row_id = 1，向无主键表插入数据时，InnoDB 将 dictsys.row_id = 1 的值作为数据行的隐式主键，并逐行自增。因此，第一次插入的 3 行数据的 _rowid 值分别为 1、2、3；
当 dictsys.row_id = 281474976710656（即 $2^{48}$），已超过最大值 $2^{48}$-1。InnoDB 在插入数据时将 dictsys.row_id 复位为 0。因此，第二次插入的 3 行数据的 _rowid 值分别为 0、1、2；
第二次插入的 3 行数据与第一次插入的 3 行数，_rowid 出现了重复值（即 1、2）。于是，InnoDB 在第二次插入数据时，根据 _rowid 的值覆盖了第一次插入的 2 行数据（图中蓝色箭头部分）。

2.2. Undo log 版本链

在 InnoDB 引擎中，当对数据执行 DML 操作之前，会将改前数据复制一份至 undo log 中。并更新数据行的 DB_ROLL_PTR 字段为 undo log 中该数据副本的地址。同一数据的多个不同版本的副本，则构成了“版本链”。

当使用 DELETE 语句删除数据行时，并不会立即从数据库中物理删除数据。而是，将数据的标志位标记为"已删除"，来表示删除该行数据。同时，将前镜像置于 undo log 中，形成版本链。被标记为“已删除”的数据，仅在丢弃（discard）因 DELETE 而产生的 update undo logs时，才会物理删除相应的数据行及其索引记录。这种删除操作称为 ”清除“。速度很快，数据删除顺序通常与执行的 DELETE 语句顺序相同。

INSERT 产生的 undo log，只在事务回滚时需要，当事务提交后，相应的 undo log 可被立即删除；而 UPDATE/DELETE 操作产生的 undo log，不仅在事务回滚时需要，在 MVCC 快照读时也需要。InnoDB 引擎在确保没有活动的事务引用该 undo log 后，才会将 undo log 删除。

2.3. Read View

ReadView（读视图）是 SQL 执行快照读时，MVCC 提取数据的依据，记录并维护系统当前活跃（未提交）事务的 ID。ReadView 中包含 4 个核心字段：

字段	含义
m_ids	当前系统中活跃（未提交）的事务 ID 集合
min_trx_id	min{活跃事务 ID}
max_trx_id	预分配事务 ID，即为 “max{活跃事务 ID} + 1”
creator_trx_id	ReadView 创建者（Current）的事务 ID

2.3.1. 版本链数据访问规则

trx_id 为 undo log 版本链中的事务 ID，将 trx_id 与 ReadView 中的各字段按如下步骤进行判断是否可见：

若被访问版本 trx_id == creator_trx_id，说明该数据版本由当前事务（creator_trx_id）修改，则可以访问该版本。
若被访问版本 trx_id < min_trx_id，说明数据版本已提交（非活跃事务），可以访问该版本。
若被访问版本 trx_id > max_trx_id，说明该事务（trx_id）是在创建 ReadView 之后才开启的，不可以访问该版本。
若被访问版本 min_trx_id <= trx_id <= max_trx_id，且 trx_id 不在 m_ids 中，说明创建 ReadView 时，该数据已提交（非活跃事务），可以访问该版本数据。

以上规则，可通俗地简述为：

1. 版本链中的 DB_TRX_ID，不在 未提交的事务 ID 数组 中，且 DB_TRX_ID < max_trx_id，则数据版本对当前事务可见。
2. 版本链中的 DB_TRX_ID == creator_trx_id，说明数据由当前事务（creator_trx_id），则数据版本对当前事务可见。

3. MVCC 实例（ReadView 规则解析）

根据不同的隔离级别，生成 ReadView 的时机不同：

Read Committed：事物中每次 SELECT 数据时，都会重生成新的 ReadView。确保读取的数据为已经提交的数据。
Repeatable Read：只在第一次 SELECT 数据时，生成一个 ReadView，后续 SELECT 数据都复用该 ReadView，确保前后读取的数据一致。但是，若两次 SELECT 之间插入当前读（如DELETE、UPDATE等），则当前读之后的 SELECT 会重新创建 ReadView。

3.1. 实例 1：事务中首次 SELECT

如图所示，在 9：00 时，事务120 中第一次执行 SELECT 查询数据。针对隔离级别 Read Committed 与 Repeatable Read 会创建相同的 ReadView 。ReadView 如下：

min_trx_id=108,          # 即 min{m_ids}
m_ids=[105,108,120],     # 未提交事务的 ID 数组
max_trx_id=121,          # 预分配的事务 ID，即 max{m_ids} + 1
creator_trx_id=120       # ReadView 创建者事务 ID

版本链中 DB_TRX_ID=101 的数据版本符合”DB_TRX_ID 不在 m_mids（未提交事务ID数组）中，且 DB_TRX_ID < max_trx_id”。因此，该版本（DB_TRX_ID=101）数据 NAME=张三 对 事务120 可见。

3.2. 实例2：事务中第二次 SELECT

如图所示，在 10：00 时，事务 108 已提交修改。事务120 中第二次执行 SELECT 查询数据。

针对隔离级别 Read Committed，会在执行 SELECT 时重建 ReadView。

min_trx_id=105,         # 即 min{m_ids}
m_ids=[105,120],        # 未提交事务的 ID 数组
max_trx_id=121,         # 预分配的事务 ID，即 max{m_ids} + 1
creator_trx_id=120      # ReadView 创建者事务 ID

版本链中 DB_TRX_ID=108 的数据版本符合”DB_TRX_ID 不在 m_mids（未提交事务ID数组）中，且 DB_TRX_ID < max_trx_id”。因此，该版本（DB_TRX_ID=108）数据 NAME=王五 对 事务120 可见。

针对隔离级别 Repeatable Read，会复用首次 SELECT 的 ReadView。

min_trx_id=108,         # 即 min{m_ids}
m_ids=[105,108,120],    # 未提交事务的 ID 数组
max_trx_id=121,         # 预分配的事务 ID，即 max{m_ids} + 1
creator_trx_id=120      # ReadView 创建者事务 ID

3.3. 实例3：事务中 UPDATE 后的 SELECT

如图所示，在 11：00 时，事务 105 已提交修改。事务120 中先执行 UPDATE（当前读），后执行 SELECT 查询数据。针对隔离级别 Read Committed 与 Repeatable Read 会创建相同的 ReadView 。ReadView 如下：

min_trx_id=120,         # 即 min{m_ids}
m_ids=[120],            # 未提交事务的 ID 数组
max_trx_id=121,         # 预分配的事务 ID，即 max{m_ids} + 1
creator_trx_id=120      # ReadView 创建者事务 ID

版本链中 DB_TRX_ID=120 的数据版本符合”DB_TRX_ID == creator_trx_id，说明数据由当前事务（creator_trx_id=120）所修改”。因此，该版本（DB_TRX_ID=120）数据 NAME=小明 对 事务120 可见。

mysql 事务隔离级别数据库事务 mvcc mvcc原理

最后修改时间：2022-11-23 19:52:11

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

MySQL 八股文（共3篇）

图文深入浅出 MySQL 八股文