Software Systems Atlas

中文

English

中文

English

主题

第 5 卷 / 第 12 章 / 第 1 课

12.1 PostgreSQL MVCC、快照与隔离级别 ​

元数据卡

  • 前置：第 11 章事务与可串行化
  • 关键词：MVCC、快照、可见性、Read Committed、Repeatable Read、SSI、VACUUM
  • 代码语言：SQL（PostgreSQL）

MVCC 让普通读选择合适的行版本，减少与写入互相等待。两个事务写同一行、DDL、外键检查和显式加锁读仍需锁或冲突检测。本页使用 PostgreSQL 16+，账户表沿用 11.1；实验开始前把两个账户余额恢复到 500.00 和 200.00，并先结束其他练习事务。

SQL 标准规定的是最低保证 ​

现代 SQL 标准除了脏读、不可重复读和幻读，还用 serialization anomaly 描述无法对应任何串行顺序的结果。

隔离级别脏读不可重复读幻读序列化异常
Read Uncommitted可出现可出现可出现可出现
Read Committed禁止可出现可出现可出现
Repeatable Read禁止禁止标准允许可出现
Serializable禁止禁止禁止禁止

这张表是最低要求，不是所有产品的精确行为表。PostgreSQL 接受四个名称，但 Read Uncommitted 按 Read Committed 执行；它的 Repeatable Read 也不出现幻读，却仍可能出现写偏斜等序列化异常。

PostgreSQL 如何保存行版本 ​

PostgreSQL 的堆元组包含创建它的事务标识 xmin，以及删除或替换它的事务标识 xmax 等头部信息。UPDATE 通常不是原地覆盖全部旧内容，而是创建新元组版本，并让旧版本记录自己已被替换。

text
旧版本: xmin=80, xmax=105, balance=500
新版本: xmin=105, xmax=0, balance=400

这张图只是概念模型。能否看到某个版本，还要结合当前事务自身、事务提交状态、快照中的活跃事务集合以及提示位等信息，不能只比较两个整数。xmax 还可能记录加锁事务或 MultiXact，非零并不等于该行已经被删除；需结合元组标志和事务状态判断。

快照可抽象为三类边界：

  • 拍摄快照时仍活跃的事务；
  • 更早且已经结束的事务；
  • 快照之后才取得事务号的事务。

读取器据此排除未提交或“来自未来”的版本，并保留对当前快照仍有效的旧版本。

Read Committed：每条语句一张新快照 ​

PostgreSQL 默认是 Read Committed。普通 SELECT 在语句开始时取得快照，同一条语句执行期间不会突然看到后来提交的行；下一条语句会取得新快照，因此可能看到新提交。

sql
BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT balance FROM account WHERE account_id = 1;
-- 并发事务提交了修改
SELECT balance FROM account WHERE account_id = 1;
-- 第二条语句可以看到新值

COMMIT;

修改语句还多一层语义：UPDATE、DELETE 或 SELECT ... FOR UPDATE 找到的目标行若已被并发事务修改，会等待对方结束。对方提交后，PostgreSQL 可能在新版本上重新检查 WHERE 条件。这让单行原子增量工作良好，但也意味着复杂条件更新不能简单理解为“整条语句只看一张完全静止的数据库图片”。

Repeatable Read：首次实际语句建立事务快照 ​

在 PostgreSQL 中，Repeatable Read 的快照通常在事务里的第一条非事务控制语句开始时建立，不是执行 BEGIN 的那个瞬间。

sql
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM account WHERE account_id = 1;
-- 此处建立并使用事务快照

SELECT balance FROM account WHERE account_id = 1;
-- 仍使用同一快照，也能看见本事务自己的修改

COMMIT;

若该事务尝试修改一个在快照建立后已被别的事务提交修改的目标行，PostgreSQL 可能报：

text
ERROR: could not serialize access due to concurrent update
SQLSTATE: 40001

应用必须回滚并从事务开头重试。Repeatable Read 提供稳定快照，但不能保证所有成功事务等价于某个串行次序。

写偏斜：稳定快照仍会破坏集合约束 ​

假设值班表要求至少一人在线：

sql
CREATE TABLE guard (
  guard_id bigint PRIMARY KEY,
  on_duty boolean NOT NULL
);

INSERT INTO guard (guard_id, on_duty)
VALUES (1, true), (2, true);

按此顺序执行可构造依赖：A 和 B 都 BEGIN REPEATABLE READ；两边先各自查询 SELECT count(*) FROM guard WHERE on_duty 得到 2；A 将 guard_id=1 改为 false 并提交；B 将 guard_id=2 改为 false 并提交；新事务查询应得到 0。改用 Serializable 前先将两行恢复为 true，并保留相同交错；至少一次尝试应遇到 40001，而不是两次都成功。

两个 Repeatable Read 事务都看到两人在线，各自只把自己改为离线。它们写入不同行，没有直接写：写冲突，因而可能都提交，最终在线人数为零。这就是写偏斜。

单行 CHECK 无法表达“全表至少一行满足条件”。可选方案包括：

  • 使用 Serializable，并正确重试 40001；
  • 把约束集中到一行可锁定的聚合记录；
  • 显式锁住代表该业务集合的稳定对象；
  • 重新建模，让不变量可由唯一约束或外键表达。

Serializable：SSI 检测危险依赖 ​

PostgreSQL Serializable 在 Repeatable Read 的快照语义上增加 Serializable Snapshot Isolation。它跟踪读写依赖，在可能形成不可串行结果时中止某个事务。

SIReadLock 是用于检测谓词读写依赖的标记，不是普通阻塞锁。它可能以元组、页或关系粒度出现，粒度还会为控制内存而提升。不要把它描述成“所有读都加锁并阻塞写”。

Serializable 的关键接口语义是：

  • 参与维护不变量的事务都采用相应 Serializable 协议时，成功提交的集合具有串行效果；
  • 某次尝试可能以 40001 失败；
  • 必须重试整个事务；
  • SERIALIZABLE READ ONLY DEFERRABLE 可等待安全快照，适合某些长只读报表。

旧版本为什么需要清理 ​

只要仍有快照可能看到旧元组，数据库就不能回收它。事务结束且版本不再对任何相关快照可见后，VACUUM 才能把空间标为可复用；普通 VACUUM 通常不把文件尾部之外的空间归还操作系统。

持有旧快照的长事务（包括某些 idle in transaction 会话）会阻碍回收；不是所有空闲事务都始终持有同一张快照。这些保留边界可能造成死元组积累、表和索引膨胀，并拖延事务号冻结。排查膨胀时不能只调 autovacuum，还要先找持有旧快照的会话。

动手观察 ​

用两个独立连接比较 Read Committed 与 Repeatable Read。会话 B 开启 autocommit，并且不要在 B 外层再包一个未提交的事务；代码按注释分发到对应连接：

sql
-- 会话 A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM account WHERE account_id = 1;

-- 会话 B
UPDATE account SET balance = balance + 10 WHERE account_id = 1;

-- 回到会话 A
SELECT balance FROM account WHERE account_id = 1;
COMMIT;

从余额 500 开始，Repeatable Read 的 A 应两次读到 500，提交后新查询读到 510。重置余额再用 Read Committed，A 应依次读到 500、510。要观察冲突，请另开一轮并在 A 的 COMMIT 前执行 UPDATE：若 B 的更新已提交，Repeatable Read 下 A 应收到 40001；若 B 尚未结束，A 先等待，之后是否失败取决于 B 提交还是回滚。

参考 ​

下一节换到 InnoDB。它同样使用 MVCC，却把旧版本主要放在 undo 记录里，而且普通快照读与加锁读的观察点并不相同。

Built with VitePress | Software Systems Atlas

Copyright 2026 Steven Yang