PostgreSQL / 系统课时

掌握 Repeatable Read 语义

掌握 Repeatable Read 语义

Repeatable Read 解决的是“同一事务里前后两次读取结果会变”的问题。PostgreSQL 会让事务持续使用同一份 MVCC 快照,因此不可重复读和幻读都不会出现;但稳定快照不等于可串行化,跨多行的业务约束仍可能被写偏差破坏。

学习目标

  • 说明 Repeatable Read 的事务级快照在何时建立,以及它能屏蔽哪些并发变化
  • 用两个会话验证幻读被避免、同一行并发更新会触发事务中止
  • 识别写偏差,并判断何时应升级到 Serializable 或显式加锁

快照为何能保持稳定

在 PostgreSQL 的 Repeatable Read 中,快照不是在 BEGIN 本身固定,而是在事务执行第一条非事务控制的数据访问或修改语句时建立。之后的普通查询持续基于这份快照,同时仍能看到本事务自己的修改;其他事务在此后提交的新版本不会突然进入当前事务的可见范围。PostgreSQL 的实现因此比 SQL 标准对 Repeatable Read 的最低要求更强:不可重复读和幻读都不会发生,但序列化异常仍可能发生。:contentReference[oaicite:0]{index=0}

Repeatable Read 事务级快照与并发分支

先创建两组最小数据:rr_items 用于观察查询结果集合,rr_doctors 用于验证“至少一名医生值班”这一跨行约束。

sql
DROP TABLE IF EXISTS rr_items;
DROP TABLE IF EXISTS rr_doctors;

CREATE TABLE rr_items (
    id integer PRIMARY KEY,
    category text NOT NULL
);

INSERT INTO rr_items (id, category)
VALUES (1, 'x'), (2, 'x');

CREATE TABLE rr_doctors (
    doctor text PRIMARY KEY,
    on_call boolean NOT NULL
);

INSERT INTO rr_doctors (doctor, on_call)
VALUES ('alice', true), ('bob', true);

预期输出

text
DROP TABLE
DROP TABLE
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 2

如果表此前不存在,DROP TABLE IF EXISTS 还可能额外打印 NOTICE,不影响后续实验。

用两个会话验证稳定快照

先在会话 A 开启 Repeatable Read,并执行第一次查询以建立快照。

sql
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) AS x_count
FROM rr_items
WHERE category = 'x';

预期输出

text
BEGIN
 x_count
---------
       2
(1 row)

保持会话 A 不提交,在会话 B 插入一条满足同一条件的新记录并提交。

sql
BEGIN;

INSERT INTO rr_items (id, category)
VALUES (3, 'x');

COMMIT;

预期输出

text
BEGIN
INSERT 0 1
COMMIT

回到会话 A,再执行完全相同的查询。新记录已经提交,但不属于 A 的固定快照,因此结果仍为 2。

sql
SELECT count(*) AS x_count
FROM rr_items
WHERE category = 'x';

COMMIT;

预期输出

text
 x_count
---------
       2
(1 row)

COMMIT

新开一个事务再次查询时才会看到 3 行。这里不仅没有“同一行变了”的不可重复读,也没有“满足条件的行集合变多”的幻读。

同一行并发更新为何会中止

固定快照并不意味着可以覆盖快照之后出现的新版本。会话 A 先读取 id = 1,让该事务记住旧版本。

sql
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT id, category
FROM rr_items
WHERE id = 1;

预期输出

text
BEGIN
 id | category
----+----------
  1 | x
(1 row)

会话 B 修改同一行并提交。

sql
UPDATE rr_items
SET category = 'y'
WHERE id = 1;

预期输出

text
UPDATE 1

随后会话 A 再尝试修改这行。由于目标行已被快照之后提交的事务改变,PostgreSQL 不能在 Repeatable Read 中把两个版本悄悄合并,会中止当前事务。:contentReference[oaicite:1]{index=1}

sql
UPDATE rr_items
SET category = 'z'
WHERE id = 1;

ROLLBACK;

预期输出

text
ERROR:  could not serialize access due to concurrent update
ROLLBACK

收到这类 40001 序列化失败后,应回滚并从事务起点重新执行;只重试最后一条 UPDATE 会丢失前面读取与判断所依赖的快照一致性。

写偏差是 Repeatable Read 的关键边界

写偏差发生在两个事务读取了相同约束,但最终修改的是不同记录。假设业务规则要求 rr_doctors 中至少保留一名值班医生。会话 A 与 B 都在自己的快照里看到两人值班,于是分别把自己改为休息。

会话 A 执行并暂不提交:

sql
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) AS on_call_count
FROM rr_doctors
WHERE on_call;

UPDATE rr_doctors
SET on_call = false
WHERE doctor = 'alice';

预期输出

text
BEGIN
 on_call_count
---------------
             2
(1 row)

UPDATE 1

会话 B 并发执行并提交:

sql
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) AS on_call_count
FROM rr_doctors
WHERE on_call;

UPDATE rr_doctors
SET on_call = false
WHERE doctor = 'bob';

COMMIT;

预期输出

text
BEGIN
 on_call_count
---------------
             2
(1 row)

UPDATE 1
COMMIT

此时会话 A 也执行 COMMIT,两个事务修改的是不同的行,因此都可能成功。最后检查约束:

sql
SELECT count(*) AS on_call_count
FROM rr_doctors
WHERE on_call;

预期输出

text
 on_call_count
---------------
             0
(1 row)

这就是“每个事务单独看都合理,合起来却不可能对应任何正确串行执行”的写偏差。若正确性依赖跨多行、范围查询或“先检查再写入”的业务约束,Repeatable Read 不能仅靠稳定快照兜底;优先考虑 Serializable,并为 40001 做整事务重试,或明确锁住所有参与约束判断的记录。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

掌握 Repeatable Read 语义

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通