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}

先创建两组最小数据:rr_items 用于观察查询结果集合,rr_doctors 用于验证“至少一名医生值班”这一跨行约束。
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);预期输出
DROP TABLE
DROP TABLE
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 2如果表此前不存在,DROP TABLE IF EXISTS 还可能额外打印 NOTICE,不影响后续实验。
用两个会话验证稳定快照
先在会话 A 开启 Repeatable Read,并执行第一次查询以建立快照。
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) AS x_count
FROM rr_items
WHERE category = 'x';预期输出
BEGIN
x_count
---------
2
(1 row)保持会话 A 不提交,在会话 B 插入一条满足同一条件的新记录并提交。
BEGIN;
INSERT INTO rr_items (id, category)
VALUES (3, 'x');
COMMIT;预期输出
BEGIN
INSERT 0 1
COMMIT回到会话 A,再执行完全相同的查询。新记录已经提交,但不属于 A 的固定快照,因此结果仍为 2。
SELECT count(*) AS x_count
FROM rr_items
WHERE category = 'x';
COMMIT;预期输出
x_count
---------
2
(1 row)
COMMIT新开一个事务再次查询时才会看到 3 行。这里不仅没有“同一行变了”的不可重复读,也没有“满足条件的行集合变多”的幻读。
同一行并发更新为何会中止
固定快照并不意味着可以覆盖快照之后出现的新版本。会话 A 先读取 id = 1,让该事务记住旧版本。
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT id, category
FROM rr_items
WHERE id = 1;预期输出
BEGIN
id | category
----+----------
1 | x
(1 row)会话 B 修改同一行并提交。
UPDATE rr_items
SET category = 'y'
WHERE id = 1;预期输出
UPDATE 1随后会话 A 再尝试修改这行。由于目标行已被快照之后提交的事务改变,PostgreSQL 不能在 Repeatable Read 中把两个版本悄悄合并,会中止当前事务。:contentReference[oaicite:1]{index=1}
UPDATE rr_items
SET category = 'z'
WHERE id = 1;
ROLLBACK;预期输出
ERROR: could not serialize access due to concurrent update
ROLLBACK收到这类 40001 序列化失败后,应回滚并从事务起点重新执行;只重试最后一条 UPDATE 会丢失前面读取与判断所依赖的快照一致性。
写偏差是 Repeatable Read 的关键边界
写偏差发生在两个事务读取了相同约束,但最终修改的是不同记录。假设业务规则要求 rr_doctors 中至少保留一名值班医生。会话 A 与 B 都在自己的快照里看到两人值班,于是分别把自己改为休息。
会话 A 执行并暂不提交:
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';预期输出
BEGIN
on_call_count
---------------
2
(1 row)
UPDATE 1会话 B 并发执行并提交:
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;预期输出
BEGIN
on_call_count
---------------
2
(1 row)
UPDATE 1
COMMIT此时会话 A 也执行 COMMIT,两个事务修改的是不同的行,因此都可能成功。最后检查约束:
SELECT count(*) AS on_call_count
FROM rr_doctors
WHERE on_call;预期输出
on_call_count
---------------
0
(1 row)这就是“每个事务单独看都合理,合起来却不可能对应任何正确串行执行”的写偏差。若正确性依赖跨多行、范围查询或“先检查再写入”的业务约束,Repeatable Read 不能仅靠稳定快照兜底;优先考虑 Serializable,并为 40001 做整事务重试,或明确锁住所有参与约束判断的记录。