幻读和不可重复读的区别及PG vs MySQL实现机制对比，两派又打起来了

digoal2025年10月02日约 20 分钟

本文将详细介绍幻读和不可重复读的区别, 同时对比MySQL和PG在解决幻读方面的具体实现机制. 最后分享一下两者的串行事务隔离级别的实现机制.

一、幻读和不可重复读的概念区别
二、PostgreSQL 如何在RR隔离级别模式下解决幻读
三、MySQL 如何在RR隔离级别模式下解决幻读
四、对比MySQL和PostgreSQL解决幻读的本质区别
五、为什么mysql 针对select only和select for update要使用两种实现机制来解决幻读?
六、mysql 防止的幻读和SQL标准中定义的幻读有什么区别?
七、对比pg和mysql的SERIALIZABLE隔离级别
八、Serializable解决问题场景示例

一、幻读和不可重复读的概念区别

幻读（Phantom Read）和不可重复读（Non-repeatable Read）是数据库事务隔离级别中讨论的两种并发问题。它们都与事务在读取数据时，其他事务同时修改数据而引发的数据不一致有关，但它们影响的数据范围和表现形式有所不同。

不可重复读 (Non-repeatable Read)

定义：
在一个事务内，两次或多次查询同一条数据记录，发现该记录的值不同。这是因为在第一次查询之后、第二次查询之前，另一个事务修改了这条记录并提交了。

关注点：修改（UPDATE）和单条记录的值。

例子：

事务 A 开始，查询 ID 为 10 的用户余额，结果是 500 元。
事务 B 开始，将 ID 为 10 的用户余额修改为 1000 元，并提交。
事务 A 再次查询 ID 为 10 的用户余额，结果变成了 1000 元。

结果： 事务 A 在两次读取同一条记录时，得到了不一致的结果。

幻读 (Phantom Read)

定义：
在一个事务内，两次执行同一个范围查询（例如：WHERE condition），发现第二次查询的结果集里多了一些或少了一些记录。这是因为在第一次查询之后、第二次查询之前，另一个事务插入（INSERT）或删除了（DELETE）符合查询条件的记录，并提交了。

关注点：新增/删除（INSERT/DELETE）和多条记录的集合（记录的数量）。

例子：

事务 A 开始，查询年龄大于 30 的员工数量，结果是 50 人。
事务 B 开始，插入了 5 个年龄为 40 岁的新员工，并提交。
事务 A 再次查询年龄大于 30 的员工数量，结果变成了 55 人。

结果： 事务 A 在两次执行相同的查询时，查询到的记录数量不同，就像出现了“幻影”一样。

总结和核心区别

特性	不可重复读 (Non-repeatable Read)	幻读 (Phantom Read)
关注的变化	值被修改 (UPDATE)	数量/集合发生变化 (INSERT/DELETE)
对象	同一条已经存在的数据记录	符合查询条件的多条数据记录（集合）
隔离级别	读已提交 (Read Committed) 级别下可能出现	可重复读 (Repeatable Read) 级别下仍可能出现 (取决于数据库实现，如 MySQL 的默认设置解决了幻读)

简单来说：

不可重复读：记录还在，但内容变了（UPDATE）。
幻读：记录的数量变了（INSERT 或 DELETE），像看到了“新出现”或“消失”的记录。

在标准的 SQL 隔离级别中，可重复读 (Repeatable Read) 级别可以防止不可重复读，但不一定能防止幻读。而最高的隔离级别——串行化 (Serializable) 可以解决这两种并发问题。不过，很多数据库（如 MySQL 默认的 Repeatable Read 级别）通过使用 Next-Key Locks 或其他机制，实际上也解决了幻读问题。

二、PostgreSQL 如何在RR隔离级别模式下解决幻读

PostgreSQL 的 MVCC (Multi-Version Concurrency Control) 机制在 可重复读 (Repeatable Read, RR) 隔离级别下之所以能够同时解决不可重复读和幻读问题，关键在于其对 事务快照（Transaction Snapshot） 的严格和一致性使用。

核心机制：事务快照 (Transaction Snapshot)

在 PostgreSQL 的 RR 模式下，一个事务（称之为）启动时，会生成一个事务快照。这个快照记录了系统中所有已提交的、未提交的、以及正在进行的事务的状态。

快照的时间点： 这个快照是在 第一次执行数据修改语句（SELECT, INSERT, UPDATE, DELETE）时创建的。
快照的可见性规则： 在整个生命周期内，它所能看到的数据版本都必须符合这个快照的规则：

只能看到在快照创建时已经提交的数据版本。
忽略所有在快照创建后才提交的事务所做的任何更改。

正是这个“快照隔离”的特性，使得 RR 模式在 PostgreSQL 中比 SQL 标准所定义的 RR 级别更强大，从而解决了不可重复读和幻读。

解决不可重复读 (Non-Repeatable Read)

不可重复读关注的是同一条记录的值被另一个事务修改。

步骤	事务 A (RR 模式)	事务 B (修改并提交)
1.	启动，并执行第一次 `SELECT` (快照生成)	事务 A 看到记录 V1 版本。
2.	`UPDATE` 记录，将其修改为 V2 版本，并提交。	事务 A 的快照已经生成。
3.	执行第二次 `SELECT` 同一条记录	事务 A 仍然只能看到快照允许它看到的 V1 版本。

结论： 即使事务 B 已经修改并提交了数据，事务 A 由于持有旧的快照，会忽略事务 B 的更改，从而重复读取到相同的值，解决了不可重复读。

解决幻读 (Phantom Read)

幻读关注的是符合条件的记录集合被另一个事务新增或删除。

步骤	事务 A (RR 模式)	事务 B (插入/删除并提交)
1.	启动，并执行第一次范围 `SELECT` (快照生成)	事务 A 看到 10 条记录。
2.	`INSERT` 5 条新记录（或 `DELETE` 5 条记录），并提交。	事务 A 的快照已经生成。
3.	执行第二次范围 `SELECT` 相同的条件	事务 A 仍然只能看到快照允许它看到的那 10 条记录。

结论： 事务 B 插入或删除的新记录，其事务 ID 是在快照生成之后才提交的。根据 MVCC 的可见性规则，事务 A 在进行范围查询时会自动过滤掉这些“太新”的版本，因此两次查询到的记录集合数量保持一致，解决了幻读。

与 SQL 标准的区别

SQL 标准定义的 Repeatable Read 级别，通常只要求锁住你读取的数据行以防止修改，但对新的插入行（即幻读）并没有要求必须阻止。

PostgreSQL 的 MVCC 实现是基于快照隔离 (Snapshot Isolation) 的，它保证了事务开始后看到的数据集合是完全一致的，包括行值和行数，这是一种更强的隔离，因此能够同时解决不可重复读和幻读。

三、MySQL 如何在RR隔离级别模式下解决幻读

好的，MySQL 默认的事务隔离级别是 可重复读 (Repeatable Read, RR)。与标准的 SQL 规范不同，MySQL 的 RR 级别能够有效防止幻读。它主要通过其独特的 锁机制，特别是 Next-Key Locks（临键锁），来实现这一目标。

1. Next-Key Locks（临键锁）机制概述

MySQL 的 InnoDB 存储引擎在 RR 隔离级别下，执行范围查询（例如 SELECT ... WHERE age > 20）时，并不会只使用简单的行锁，而是使用一种结合了 行锁 (Record Lock) 和 间隙锁 (Gap Lock) 的复合锁：Next-Key Lock。

行锁 (Record Lock): 锁住实际存在的索引记录。
间隙锁 (Gap Lock): 锁住索引记录之间的间隙，以及第一个记录之前的间隙或最后一个记录之后的间隙。它锁住的是一个范围，而不是具体的记录。

Next-Key Lock 锁定的范围是从当前记录（不含）到下一条记录（含）的一个左开右闭区间。

2. Next-Key Locks 如何解决幻读

幻读发生在当一个事务两次执行范围查询时，另一个事务在该范围中插入或删除了新行并提交。Next-Key Locks 的作用就是防止在查询的间隙中进行插入或删除操作。

我们以一个包含 ID 索引的表 T 为例，其中已有的记录 ID 值为 10, 20, 30。

索引间隙	Next-Key Lock 锁定的范围
第一个间隙和记录 10
间隙和记录 20
间隙和记录 30
最后一个间隙（注意：最后一个间隙通常只用间隙锁）

幻读场景示例（使用 `SELECT FOR UPDATE` 或 `SELECT FOR SHARE`）

假设事务想要查询并锁定所有 ID 在之间的记录，以确保没有人在其中添加新员工。

步骤：

步骤	事务 A (RR 模式)	事务 B (插入操作)
1.	`BEGIN;`
2.	`SELECT * FROM T WHERE ID > 10 AND ID <= 30 FOR UPDATE;`	事务 A 获得了两个 Next-Key Locks：** 和 **。
3.	`INSERT INTO T (ID) VALUES (25);`	事务 B 阻塞！尝试插入 ID=25 的记录，但 25 落在的间隙内。事务 A 的 Next-Key Lock 锁住了这个间隙。
4.	等待超时或事务 A 提交/回滚	幻读被阻止。只要事务 A 不释放锁，事务 B 就无法插入新记录。
5.	`COMMIT;`	事务 A 释放所有锁。
6.	事务 B 才能执行插入操作

解析：

当执行 SELECT ... FOR UPDATE 时，它不仅锁定了 ID 为 20 和 30 的记录本身（行锁），还锁定了和这两个间隙（间隙锁）。
事务试图插入 ID 为 25 的记录。由于 25 位于锁定的间隙内，插入操作被阻塞。
因此，即使在稍后再次执行同样的查询，也不会看到 ID=25 这条新的幻影记录，因为这条记录根本无法在事务期间被插入。

3. 注意：纯查询 (SELECT) 与快照读 (Snapshot Read)

需要注意的是，在 MySQL 的 RR 模式下：

普通 SELECT 语句 (即快照读/Snapshot Read) 是通过 MVCC（多版本并发控制）机制实现的，它读取的是事务开始时的版本快照。

解决幻读： 快照读本身也可以防止幻读，因为它只看事务启动时已提交的数据。如果在启动后插入并提交了新行，的快照是看不到这行新数据的。

锁定读语句 (例如 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE) 是通过 Next-Key Locks 机制实现的，它读取的是最新数据并加锁。

解决幻读： Next-Key Locks 的作用是阻止其他事务在锁定的范围内进行 INSERT 或 DELETE，从而真正从物理上消除幻读发生的可能性，保证数据安全性和一致性。

总结来说，Next-Key Locks 是 MySQL InnoDB 在 RR 级别下，用于实现真正的、物理上的“可重复读”和“无幻读”的核心机制，特别是在涉及数据修改或显式锁定的场景中。

四、对比MySQL和PostgreSQL解决幻读的本质区别

好的，MySQL (InnoDB) 和 PostgreSQL 在可重复读 (Repeatable Read, RR) 隔离级别下都能解决幻读问题，但它们采用的本质机制和哲学是完全不同的。

MySQL (InnoDB) 解决幻读的本质：物理锁（Next-Key Locks）

MySQL 的 InnoDB 存储引擎在 RR 级别下，主要通过 锁（Locking） 机制来解决幻读，特别是 Next-Key Locks（临键锁） 。

特性	描述	核心原理
隔离级别	可重复读 (Repeatable Read, RR)
核心机制	Next-Key Locks (临键锁)，结合了行锁和间隙锁。
解决本质	阻塞其他事务的写入。事务 A 通过加锁，物理上阻止事务 B 在 A 查询的范围内进行 `INSERT` 或 `DELETE` 操作，从而保证 A 再次查询时记录数量不变。
影响	读写操作相互影响。锁定读（`SELECT ... FOR UPDATE`）会阻止其他事务在间隙内插入数据，降低了并发性。
何时生效	锁定读（`SELECT ... FOR UPDATE` 或 `SELECT ... FOR SHARE`）时，锁住索引记录及其间的间隙。
哲学	“宁可错杀，不可放过”。使用强力锁机制，在数据操作层面确保一致性。

简而言之： MySQL 是通过在事务进行范围查询时，把数据和数据之间的“空地”（间隙）都锁起来，让其他事务根本无法在那个范围里“偷偷摸摸”地添加或删除记录，从源头上消除了幻读的可能。

PostgreSQL 解决幻读的本质：快照隔离（Snapshot Isolation）

PostgreSQL 在 RR 级别下，主要通过 MVCC (多版本并发控制) 提供的 事务快照（Transaction Snapshot） 机制来解决幻读。

特性	描述	核心原理
隔离级别	可重复读 (Repeatable Read, RR)
核心机制	事务快照（Snapshot Isolation），基于 MVCC。
解决本质	隔离事务的可见性。事务 A 看不到事务 B 在 A 启动快照后所做的任何更改（包括插入、删除和修改），从而保证两次查询结果集的一致性。
影响	读写分离，并发性高。读操作不加锁，不阻塞写操作；写操作也不阻塞读操作。
何时生效	事务第一次执行查询时，生成一个固定的、全局的快照，并贯穿整个事务生命周期。
哲学	“眼不见为净”。允许其他事务修改数据，但对当前事务保持不可见，保障一致性。

简而言之： PostgreSQL 是通过在事务开始时， 给事务拍一张“照片”（快照） 。该事务后续所有的查询都只看这张“照片”上的数据。即使其他事务在后台插入了新记录并提交，这些新记录对当前的事务来说是“透明”或“隐形”的，自然也就不会发生幻读。

对比总结：本质的区别

特征	MySQL (Next-Key Locks)	PostgreSQL (Snapshot Isolation)
解决方式	物理阻止 (Locking)	逻辑隔离 (MVCC Snapshot)
关键工具	Next-Key Locks（间隙锁）	事务快照（Transaction Snapshot）
性能影响	在锁定读时，可能因为加锁而降低并发。	读操作不加锁，并发性高。
读操作	普通 `SELECT` 使用 MVCC 快照，锁定读使用 Next-Key Lock。	所有 `SELECT` 都使用 MVCC 快照。
事务冲突	阻塞 (事务 B 插入被阻塞，直到 A 释放锁)。	不阻塞 (事务 B 可以插入，但对 A 不可见)。
数据是否真实	锁定读能确保读取到最新且被锁定的数据。	总是读取事务开始时的旧版本数据。

五、为什么mysql 针对select only和select for update要使用两种实现机制来解决幻读?

MySQL InnoDB 针对普通 SELECT（快照读）和锁定读（SELECT ... FOR UPDATE）使用两种不同的机制来解决幻读，核心原因在于它们对数据一致性的要求和事务的目的不同，必须在隔离性和并发性之间做出权衡。

两种读取的目的和需求差异

读取类型	目的	核心需求	解决幻读的哲学
快照读 (`SELECT` only)	读取数据。用于报表、分析、简单查询等不涉及修改的场景。	高并发性。读操作不应阻塞写操作，事务应看到一个一致的历史版本。	逻辑隔离（眼不见为净）：允许其他事务插入数据，但本事务的快照屏蔽这些数据。
锁定读 (`SELECT ... FOR UPDATE`)	读取数据并准备修改/删除。用于业务逻辑中“先检查，后操作”的关键步骤。	绝对的实时一致性。确保在读取到提交之前，没有其他事务能修改或插入任何符合条件的数据。	物理阻止（宁可错杀，不可放过）：物理上阻止其他事务在查询范围内插入数据。

为什么不能只用一种机制？

1. 为什么快照读不能用 Next-Key Locks？

如果普通的 SELECT 也像锁定读那样使用 Next-Key Locks：

极大地牺牲并发性： 任何范围查询都会锁住查询结果之间的间隙。这意味着，当一个长事务在进行一个简单的 SELECT 查询时，可能会阻塞其他所有事务在该范围内的 INSERT 操作。数据库的并发能力将急剧下降。
违背 MVCC 的设计初衷： MVCC 的核心价值在于实现读写不冲突。让 SELECT 加锁会打破这一优势。

结论： 对于只读操作，使用 MVCC 快照隔离已经足够保证可重复读（即无幻读），同时还能保持高并发。

2. 为什么锁定读不能只用 MVCC 快照？

锁定读的目的是锁定你查到的数据，以确保接下来对这些数据的修改是基于一个未被篡改的、最新的状态。

无法保证数据的“存在性”： 幻读不仅仅是新行出现的问题，更严重的是在 SELECT ... FOR UPDATE 之后，业务逻辑可能依赖于这个范围内没有新数据。

例：检查表中是否有 ID 为 10 的记录，如果没有，就 INSERT 一条。如果只用 MVCC，事务可以在 SELECT 之后插入 ID=10 的记录并提交。当准备 INSERT 时，会因为主键冲突失败（写冲突）。

锁定范围不足： MVCC 快照只能锁定已存在的行，但无法锁定行与行之间的空隙。锁定读的目的就是为了防止其他事务往这些空隙里插入新行，从而保证业务逻辑的原子性。

结论： 锁定读是为了后续的写入或修改操作做准备。它需要强一致性和物理排他性，只有 Next-Key Locks 才能提供这种对整个范围的绝对保护。

总结

MySQL 在 RR 隔离级别下，采取了组合策略：

高并发场景 (快照读)： 使用 MVCC 保证逻辑上的无幻读（保证事务看到的数据版本不变）。
数据保护场景 (锁定读)： 使用 Next-Key Locks 保证物理上的无幻读（防止其他事务破坏事务依赖的范围）。

这种设计使得 MySQL 能够在提供标准 RR 隔离级别更高保证（无幻读）的同时，尽量在不同的使用场景下优化性能。

六、mysql 防止的幻读和SQL标准中定义的幻读有什么区别?

这是一个非常好的问题，它触及了数据库理论与实际实现之间的一个关键差异。

MySQL (InnoDB) 在其默认的 可重复读 (Repeatable Read, RR) 隔离级别下声称解决了幻读，但这与 SQL 标准中对幻读的定义以及该标准对 RR 级别应达到的隔离效果的定义存在微妙但重要的区别。

1. SQL 标准中幻读 (Phantom Read) 的定义

SQL 92 标准定义幻读为：

事务 重新执行一个返回行集合的查询，发现满足该查询条件的行集合发生了变化，因为另一个事务在此期间 插入或删除了符合条件的行并提交。

SQL 标准对 RR 隔离级别的定义：

SQL 标准认为，可重复读 (RR) 级别必须防止不可重复读，但可以容忍（允许发生）幻读。

标准哲学： RR 级别主要关注已存在的行的值不能被改变。它没有要求阻止新行的插入或删除。因此，在标准的 RR 级别下，一个事务两次查询同一个范围，可能会发现记录数量不同，这被认为是 RR 级别的正常现象。

2. MySQL (InnoDB) 实际防止的幻读

MySQL 的 InnoDB 存储引擎在实现 RR 隔离级别时，使用了比 SQL 标准更强的隔离保证，特别是通过 MVCC 快照读 和 Next-Key Locks 机制。

A. 对于普通 `SELECT`（快照读）

MySQL RR 级别下的普通 SELECT 查询，使用 MVCC 快照 机制，可以防止幻读：

机制： 事务启动时拍摄快照。事务插入的新行，即使提交了，其版本号也高于的快照时间点。
结果： 的查询会自动过滤掉插入的新行。
结论： 在 MVCC 快照读层面，MySQL 确实防止了 SQL 标准定义的幻读，因为它保证了两次 SELECT 看到的行集合是数量和内容都一致的。

B. 对于锁定读（`SELECT ... FOR UPDATE`）

在锁定读（涉及后续修改）的场景下，MySQL 使用 Next-Key Locks 来防止幻读：

机制： Next-Key Locks (行锁 + 间隙锁) 会物理上阻止任何其他事务在查询的范围内插入新行。
结果： 保证了事务依赖的范围绝对不会出现新的数据。
结论： 在锁定读层面，MySQL 提供了更强的保证，它不仅在逻辑上解决了幻读，还在物理上消除了幻读的可能性。

3. 本质区别总结：防范“写倾斜”

真正的区别不在于幻读的定义本身，而在于 RR 级别是否能防止更复杂的并发问题：**写倾斜 (Write Skew)**。

对比项	SQL 标准 RR 级别	MySQL (InnoDB) RR 级别
对幻读的观点	允许发生。	不允许发生（通过 MVCC 和锁实现）。
隔离的强度	弱于 MySQL。	强于 SQL 标准。
防止写倾斜	无法防止。	仍然无法完全防止。
更高隔离级别	串行化 (Serializable) 才能完全防止幻读和写倾斜。	串行化 (Serializable) 才能完全防止写倾斜。

关键点：

MySQL 的 RR 隔离级别虽然通过 Next-Key Locks 和 MVCC 解决了 SQL 标准定义的幻读，但它没有达到快照隔离 (Snapshot Isolation, SI) 甚至 串行化 (Serializable) 级别能提供的全面保护，因为它仍然可能出现 Write Skew 等**序列化异常 (Serialization Anomaly)**。

因此，MySQL 解决的幻读，是指解决了标准定义的、涉及行数量变化的简单异常，但如果应用程序需要最高级别的隔离性来防止所有并发写入异常（如写倾斜），则仍然需要升级到 SERIALIZABLE 隔离级别。

简单来说，MySQL 的 RR 级别比标准的 RR 强，但仍未达到 SERIALIZABLE 级别。

七、对比pg和mysql的SERIALIZABLE隔离级别

PostgreSQL 和 MySQL 的 `SERIALIZABLE` 隔离级别对比

SERIALIZABLE（串行化）是 SQL 标准中定义的最高事务隔离级别。它保证事务的并发执行结果与这些事务按某个顺序串行执行的结果一致。这意味着它能彻底消除所有并发异常，包括脏读、不可重复读、幻读，以及更复杂的如写倾斜 (Write Skew) 等**序列化异常 (Serialization Anomalies)**。

然而，PostgreSQL 和 MySQL 在实现 SERIALIZABLE 级别时，采用了截然不同的底层技术，这直接影响了它们的性能和行为。

PostgreSQL 的 `SERIALIZABLE` 机制：序列化快照隔离 (SSI)

PostgreSQL 在 9.1 版本引入了一种创新的机制来实现 SERIALIZABLE：**序列化快照隔离 (Serializable Snapshot Isolation, SSI)**。

特性	描述
底层原理	基于 MVCC 和乐观并发控制。
实现方式	不使用传统的两阶段锁 (2PL) 来阻止读写冲突。它在事务执行过程中不加任何读锁，而是跟踪事务读取和写入的数据集。
冲突检测	事务在执行期间，如果发现它所依赖的数据（即它读取的快照）在其他事务提交后被修改了，就会在提交时检测到序列化异常。
处理冲突	如果检测到异常，PostgreSQL 会回滚发生冲突的事务，并返回一个错误：`could not serialize access due to concurrent update`（`SQLSTATE 40001`）。
性能	高并发。读操作不阻塞写操作，事务只在提交时才检测并处理冲突，这通常比锁定整个操作范围的性能要好。

简而言之： PostgreSQL 的 SERIALIZABLE 是乐观的。它允许事务自由执行，假设不会冲突，直到提交时才检查是否破坏了序列化顺序。如果发现无法序列化，就回滚，将问题抛给应用程序重试。

更多参考:

《PostgreSQL 10.0 preview 功能增强 - 串行隔离级别预加锁阈值可控》
《PostgreSQL SERIALIZABLE ISOLATION LEVEL introduce》
《AI论文解读 | Serializable Snapshot Isolation in PostgreSQL》

MySQL 的 `SERIALIZABLE` 机制：严格的两阶段锁 (2PL)

MySQL 的 InnoDB 引擎遵循更传统、更保守的方式来实现 SERIALIZABLE 隔离级别。

特性	描述
底层原理	基于严格的两阶段锁 (Strict Two-Phase Locking, 2PL)。
实现方式	强制所有的 SELECT 语句都转化为锁定读 (`SELECT ... FOR SHARE`)，并使用 Next-Key Locks。
冲突阻止	事务在读取数据时就会加上共享锁（`S` 锁），在写入时加上排他锁（`X` 锁）。这些锁会阻止其他事务访问或修改相关数据或间隙。
处理冲突	如果试图获取持有的锁，它会阻塞并等待锁释放。如果等待超时或发生死锁，事务会被回滚。
性能	并发性较低。读操作会加锁并阻塞写操作，大大限制了并发度，但在事务结束时保证了不会回滚（除非是死锁）。

简而言之： MySQL 的 SERIALIZABLE 是悲观的。它使用强锁来预防所有并发冲突。任何读操作都会获取锁，并可能阻塞并发的写操作。

对比总结

特征	PostgreSQL `SERIALIZABLE` (SSI)	MySQL `SERIALIZABLE` (2PL)
核心机制	序列化快照隔离 (SSI)	严格的两阶段锁 (2PL) + Next-Key Locks
并发模型	乐观：先执行，提交时检查冲突。	悲观：通过加锁，提前阻止所有冲突。
SELECT 行为	仍然是快照读，不加读锁。	强制转换为锁定读 (`SELECT ... FOR SHARE`)。
冲突处理	冲突时回滚事务（`SERIALIZATION FAILURE`）。	冲突时阻塞等待（可能死锁回滚）。
读写冲突	极少阻塞，高并发。	读操作加共享锁，显著降低并发。
应用程序需求	必须实现事务重试机制。	应用程序不需要特别的重试机制（除非发生死锁）。

实际应用选择

如果您的应用是读多写少，并且可以容忍偶尔的事务回滚和重试，PostgreSQL 的 SSI 通常能提供更高的并发吞吐量。
如果您的应用对回滚非常敏感，或者需要最简单的编程模型（即不处理重试），那么 MySQL 的 2PL 是一个更直接的选择，尽管是以牺牲一些并发性为代价。

八、SERIALIZABLE解决问题场景的示例

当然，我们来看一个隔离级别如何解决并发问题的经典场景—— 写倾斜 (Write Skew) 。

写倾斜是（可重复读）或（快照隔离）级别无法阻止的一种复杂异常，但级别可以彻底解决。

解决写倾斜 (Write Skew) 问题的场景示例

场景：医疗值班安排

假设有一个医院的值班表，规定在任何时候，至少需要有一名医生在值班（即总值班人数必须）。

表名：`duty_roster`
`name` (VARCHAR)
`is_on_call` (BOOLEAN)

当前状态：

name	is_on_call
Alice	TRUE
Bob	TRUE
总值班人数	2

两个医生 Alice 和 Bob 都想下班，所以他们各自启动一个事务，试图将自己的 is_on_call 状态改为。

1. 故障：在级别下的写倾斜

在或级别下，事务和可能会成功提交，导致数据不一致（违反了“至少需要一人值班”的业务规则）。

时间	事务 A (Alice)	事务 B (Bob)	数据库状态	结果
t1	`BEGIN;`	`BEGIN;`	Alice=T, Bob=T	事务启动，获取快照和。
t2	`SELECT COUNT(*) FROM duty_roster WHERE is_on_call = TRUE;`	检查：2 人值班，安全。
t3	`SELECT COUNT(*) FROM duty_roster WHERE is_on_call = TRUE;`	检查：2 人值班，安全。
t4	`UPDATE duty_roster SET is_on_call = FALSE WHERE name = 'Alice';`	Alice=F, Bob=T	修改 Alice 状态。此操作对的快照不可见。
t5	`text{UPDATE duty_roster SET is_on_call = FALSE WHERE name = 'Bob';`	Alice=F, Bob=F	修改 Bob 状态。此操作对的快照不可见。
t6	`COMMIT;`	Alice=F, Bob=F	提交。
t7	`COMMIT;`	Alice=F, Bob=F	提交。

结果： 两个事务都成功了，但最终状态是0 人值班 ( )，违反了业务规则。这种异常就是写倾斜：两个事务都依赖了对方最终会保持不变的旧信息（快照），并基于此信息进行了写入，导致了不一致。

2. 解决方案：隔离级别

在隔离级别下，数据库会强制事务按顺序执行，从而避免上述冲突。

MySQL 解决方案 (悲观锁 2PL)

MySQL 会使用 Next-Key Locks 隐式锁定和查询到的所有行，并阻止并发写入。

时间	事务 A (Alice)	事务 B (Bob)	数据库状态	结果
t1	`BEGIN;`	`BEGIN;`	Alice=T, Bob=T	事务启动。
t2	`SELECT COUNT(*) FROM duty_roster WHERE is_on_call = TRUE FOR SHARE;`	锁定 Alice 和 Bob 两行（共享锁）。
t3	`SELECT COUNT(*) FROM duty_roster WHERE is_on_call = TRUE FOR SHARE;`	尝试锁定 Alice 和 Bob 两行成功 (共享锁兼容)。
t4	`UPDATE duty_roster SET is_on_call = FALSE WHERE name = 'Alice';`	Alice=F, Bob=T	尝试将 Alice 行的共享锁升级为排他锁。
t5	`UPDATE duty_roster SET is_on_call = FALSE WHERE name = 'Bob';`	Alice=F, Bob=T	尝试将 Bob 行的共享锁升级为排他锁阻塞（等待释放 Bob 行上的锁）。
t6	Alice=F, Bob=T	提交，释放所有锁。
t7	(T_B 的 UPDATE 锁升级成功)`UPDATE duty_roster SET is_on_call = FALSE WHERE name = 'Bob';`	Alice=F, Bob=F	重新评估更新失败或成功。如果 MySQL 严格执行，它会检测到死锁或在尝试重新评估提交后的语句时强制回滚。最简单的情况是在阻塞等待。

最终结果： 两个事务中只有一个能够顺利提交（另一个被阻塞或回滚），确保了至少有一人值班的业务规则。

PostgreSQL 解决方案 (乐观锁 SSI)

PostgreSQL 不使用阻塞锁，而是在提交时检测冲突：

时间	事务 A (Alice)	事务 B (Bob)	数据库状态	结果
t1	`BEGIN ISOLATION LEVEL SERIALIZABLE;`	`BEGIN ISOLATION LEVEL SERIALIZABLE;`	Alice=T, Bob=T	事务启动。
t2	`SELECT COUNT(*) FROM duty_roster WHERE is_on_call = TRUE;`	读取：2 人。
t3	`SELECT COUNT(*) FROM duty_roster WHERE is_on_call = TRUE;`	读取：2 人。
t4	`UPDATE duty_roster SET is_on_call = FALSE WHERE name = 'Alice';`	Alice=F, Bob=T	修改 Alice。
t5	`UPDATE duty_roster SET is_on_call = FALSE WHERE name = 'Bob';`	Alice=F, Bob=F	修改 Bob。
t6	`COMMIT;`	Alice=F, Bob=T	提交。成功。
t7	`COMMIT;`	Alice=F, Bob=T	尝试提交检测到冲突！依赖的 `COUNT` 结果（2）在提交后已失效。
结果	Alice=F, Bob=T	被强制回滚，返回 `SERIALIZATION FAILURE` 错误。

最终结果： 提交成功，被机制回滚。应用程序收到错误后，应重试事务。重试后的将看到提交后的数据（即只有 1 人），因此会阻止自己下班，从而保证了至少有一人值班。

级别通过强制实现序列化执行的等效性，成功解决了写倾斜问题。