MySQL 之隔离级别：可重复读

站长

2022年09月16日 11:56 · 阅读数 35

鄙人希望能写一些对萌新有所帮助的文章，若有谬误，还望不吝赐教。

在 MySQL 中，当我们将隔离等级明确为可重复读*（实际上是 MySQL 的默认事务隔离级别），接着运行一个事务，再开启另一个事务：

A 事务首次查询；B 事务接着新增；A 事务随后包含 B 事务的新行进行更新，并再次查询；结果出现变化，这似乎没有避免直觉上的幻读。

既然如此，本篇文章就是来探究 MySQL 可重复读时，可解决的幻读到底是怎样的幻读？

声明：任何 SQL 系统中，可重复读的隔离级别都不要求解决幻读。我们只是在探寻：MySQL 为了在这个层级解决幻读*（或部分幻读），做了哪些工作。

什么是幻读

首先是直觉定义：当某个事务未完成前，尽管可能有其他事务在执行，但我在本事务每次读取的结果，应当是一致的。

但随后我们阅读 Wikipedia 的定义：当读取的 WHERE 条件并未加范围锁时，由于另一事务对该范围的数据进行增删改操作，导致本事务在两次读取时，结果不一致的情况。

会发现一个明显的差异：当 WHERE 没有加范围锁，这句话意味着什么：

如果要规避幻读，则必须加范围锁——锁死本事务用到的一切数据——降低性能瓶颈；
有没有加范围锁或通过类似机制来保障本事务的相关数据一致性，成为幻读的一大判断依据。

理清了定义，我们来看看 MySQL 做了哪些工作。

MySQL 的一些机制

首先，MySQL 在执行读取时，会发生两种读取方式。

快照读（一致性的非锁定读取）

根据 MySQL 的定义，在我们进行查询时，由 InnoDB 向查询呈现数据库某个时间点的快照，从而达成 一致性的非锁定读取机制。

而当隔离级别升格到可重复读时（MySQL 的事务默认隔离级别），整个事务过程中，都以事务开始时所用的快照为准。

试试 MySQL 官方给的栗子：

# 事件A
SET autocommit=0;

# 事件B
SET autocommit=0;

# 事件A
SELECT * FROM t;
# 空结果

# 事件B
INSERT INTO t VALUES (1, 2);

# 事件A
SELECT * FROM t;
# 空结果

# 事件B
INSERT INTO t VALUES (1, 2);

# 事件A
SELECT * FROM t;
# 空结果

# 事件B
COMMIT;

# 事件A
SELECT * FROM t;
# 空结果

COMMIT;

SELECT * FROM t;
# 有结果

听起来一口气就解决了幻读问题，但我们继续阅读更多信息：

尽管一致性读取听起来很好，但对某些 DML 语句会发生不一样的效果：

首先，快照适用于事务中的 SELECT 语句，当 A 事务更新了 由其他事务提交的、在事务期间产生 的新行，即便它们并不在快照内，也会变得可见。

除此之外，当你在 DML 语句中运行一些子语句时：

默认情况下，InnoDB 使用更健壮的锁来处理这些语句，并且这些读取语句像是活跃在已提交读的级别一样，尽管仍处在同一个事务，每个语句的读取也会导致设置和更新自己的快照。

让我们将官方的栗子稍作修改，假设两个列为 id 和 val：

# 事件A
SET autocommit=0;

# 事件B
SET autocommit=0;

# 事件A
SELECT * FROM t;
# 一个结果

# 事件B
INSERT INTO t VALUES (2, 3);

# 事件A
SELECT * FROM t;
# 一个结果

# 事件B
COMMIT;

# 事件A
SELECT * FROM t;
# 一个结果

INSERT INTO t VALUES (3, 4);
SELECT * FROM t;
# 两个结果，快照的一条 + 新增一条

UPDATE t SET val = 0 WHERE id > 0;
# 注意语句的影响行数
SELECT * FROM t;
# 三个结果——因为 DML 语句影响（发现）了一些额外的行，快照被更新了，事务尚未提交的一条也包含在内

这里还有一个 MVVC（多版本并发控制）的概念，有兴趣可以自行研究：InnoDB Multi-Versioning - MySQL Doc

（因为 MVVC 涉及到更多其他内容，至少目前我对其知之甚少，无法在这里描述它的概念和实现情况——每个现代化的数据库系统都有基于自身定位的 MVVC 实现）

当前读（锁定读取）

针对快照读的问题，锁定读取则提供了锁机制，以供你安全的去操作/即将操作目标数据。

共享锁：SELECT ... FOR SHARE，其他事务可以读取这些数据，但不能修改它们。
更新锁：SELECT ... FOR UPDATE，锁定这些数据相关的行、索引和关联索引信息，保证在共享锁和其他隔离级别中，均无法修改本事务相关的数据。

有一些小小的注意事项：

仅有禁用自动提交才能使用锁定读取；
子查询并不会被关联锁定，如果你想锁定子查询的数据，记得将对应的子语句也放到里面；
如果不想等待其他事务结束锁，可以使用 SELECT ... FOR ... SKIP LOCKED，这会忽略掉被锁定的行，返回剩余结果；或者是 SELECT ... FOR ... NOWAIT，直接返回被锁的错误。
在事务结束时，所有锁定读取的锁都会被释放。

最后留一个栗子：

# 事件A
SET autocommit=0;

# 事件B
SET autocommit=0;

# 事件A
SELECT * FROM t FOR SHARE;
# 一个结果，没有 WHERE 条件，SHARE 将锁加到全表

# 事件B
INSERT INTO t VALUES (2, 3);
# 被阻塞

间隙锁

行锁保证了修改的一致性，而当数据进行新增行为时，行锁就无能为力了。

间隙锁（Gap Lock）用于锁住范围的索引，保证目标位置关联的间隙牢固，于是，当我们想要新增 val = 5 的行时，只需要锁住 5 左右的索引，即可保证新增的一致性。

# 事件A
SET autocommit=0;

# 事件B
SET autocommit=0;

# 事件A
SELECT * FROM t WHERE id = 5 FOR SHARE;# 这会锁定 5，没有范围的成本
# 或
# SELECT * FROM t WHERE id > 4 FOR SHARE;# 这会锁定 5 ~ ∞ 的范围

# 事件B
INSERT INTO t VALUES (5, 3);

# 事件A
INSERT INTO t VALUES (5, 12);
# 成功

稍微补充：如果我们对 ID = 5 做操作时，会直接走记录锁（锁定某一个记录），毕竟没必要 Gap 了。

我们确认了间隙锁对索引的行为，如果条件列根本不属于索引，会发生什么？

If id is not indexed or has a nonunique index, the statement does lock the preceding gap.

如果 id 列不具备索引或只有非唯一索引，均会锁定邻近范围，这是文档的解释。这里就不留代码了，大家有兴趣可以去尝试一下。

Next-Key 锁

当我们将间隙锁和记录锁组合使用，就得到了 Next-Key 锁。

可重复读级别中，InnoDB 会采用这个锁，从而保证相关的增改将阻塞其他事务的同目标增改，当此时仅有我们能进行该操作时，自然规避了幻读。

最后，让我们回到最初的栗子：

# 事件A
SET autocommit=0;

# 事件B
SET autocommit=0;

# 事件A
UPDATE t SET val = 0 WHERE id > 0;
# 成功，同时 Next-Key 锁锁住所有 WHERE 条件相关的记录

# 事件B
INSERT INTO t VALUES (2, 3);
# 被阻塞

结论

最终可知，MySQL 的可重复读隔离级别，解决的是同类读时，产生的幻读问题，但并未解决非同类读导致的幻读问题——当然咱们阅读百科也能看到：对于可重复读级别的定义上，规避幻读不是该级别必须处理的事项。

引用

本文参考或引用以下资料，在此致谢：

Repeatable-read isolation violated in UPDATE - MySQL Bug Report Consistent Nonlocking Reads - MySQL Document Innodb中的事务隔离级别和锁的关系 - 美团技术团队数据库内核月报－ 2017 / 06 - 阿里云PolarDB-数据库内核组 Isolation (database systems) - Wikipedia《高性能 MySQL》 - O'Reilly 系列丛书

引申：还有几个共享/排他/意向/锁，各有用处，我英文不是太好，看的有些累了（和本文主题好像也不是太相关），大家有兴趣自取哈：InnoDB Locking - MySQL Document（如果有关系，请留言，我会补充一下它们的概念和与本文的关系所在）