PHP小码农

Appearance

REPEATABLE READ ​

PostgreSQL 的 REPEATABLE READ 不是简单地把 READ COMMITTED 的「语句级快照」换成「事务级快照」，它的实现是快照隔离（Snapshot Isolation, SI）。这带来一个比 SQL 标准要求更强的特性：PG 的 REPEATABLE READ 还能防止幻读。代价是写冲突不再是静默重检查，而是直接报错 40001，应用必须能重试。

前置知识

阅读本节前，建议先了解：READ UNCOMMITTED / READ COMMITTED、MVCC 概述、事务 ID 与快照

基础概念 ​

事实说明
PG 的 RR 实现是快照隔离（SI）语义上比 SQL 标准的 RR 更强
快照在事务中第一条语句执行时获取而不是 BEGIN 时（惰性分配）
整个事务共用同一个快照事务内所有语句看到完全一致的数据视图
杜绝不可重复读同一行读多少次都一样
还能防幻读比 SQL 标准对 RR 的最低要求更强
写冲突会报 40001could not serialize access due to concurrent update
不会静默丢失更新冲突以错误形式暴露，而非被覆盖
不能防写偏斜SI 的固有限制，需 SERIALIZABLE
冲突后必须回滚整个事务不能只重试失败的那条语句

等级定位对比：

异常READ COMMITTEDREPEATABLE READ（SI）SERIALIZABLE（SSI）
脏读不可能不可能不可能
不可重复读可能不可能不可能
幻读可能不可能不可能
丢失更新（写冲突）可能（静默）不可能（报 40001）不可能（报 40001）
写偏斜可能可能不可能
读偏斜可能不可能不可能

语法与用法 ​

text
-- 事务级设置（必须在事务中任何查询之前）
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
COMMIT;

-- 或直接在 BEGIN 时指定
BEGIN ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;   -- 只读长查询

-- 会话级
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 查看
SHOW transaction_isolation;

最简可运行示例：

sql
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;   -- 整个事务内固定不变
SELECT balance FROM accounts WHERE id = 1;
COMMIT;
text
BEGIN
 balance
---------
 1000.00
(1 row)

 balance
---------
 1000.00
(1 row)

COMMIT
sql
-- 环境变量方式（会话级）
SET default_transaction_isolation = 'repeatable read';
SHOW transaction_isolation;
text
SET
 transaction_isolation
-----------------------
 repeatable read
(1 row)

详细说明 ​

快照隔离：事务级一致性视图 ​

SI 的核心是一个事务从头到尾只用一个快照。这个快照在事务内第一条语句执行时确定，之后无论别的会话提交了什么，你都只看到快照时点已提交的数据：

text
会话 A（REPEATABLE READ）：
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders;   -- 语句 1：此时确定快照，得到 100
-- 会话 B 插入 10 条订单并提交
SELECT count(*) FROM orders;   -- 语句 2：仍是 100（快照未变）
COMMIT;
text
-- 会话 A 输出
BEGIN
 count
-------
   100
(1 row)

 count
-------
   100
(1 row)

COMMIT

对比 READ COMMITTED 的同一段代码，第二次会读到 110。这个差异对「需要一致视图」的场景至关重要：报表、对账、跨表核对、备份导出。

sql
-- 观察快照的固定性：对比两条不同时间点的语句
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT now(), pg_current_snapshot() AS snap;
SELECT pg_sleep(1);
SELECT now(), pg_current_snapshot() AS snap;
COMMIT;
text
BEGIN
              now              |        snap
-------------------------------+--------------------
 2026-09-28 10:00:00.111111+08 | 732401:732405:
(1 row)

 pg_sleep
----------

(1 row)

              now              |        snap
-------------------------------+--------------------
 2026-09-28 10:00:01.222222+08 | 732401:732405:
(1 row)

COMMIT

注意两行的 now() 不同（它是语句开始时间），但 pg_current_snapshot() 完全一样——这正是「事务级快照」的直接证据。

不可重复读被彻底消除 ​

因为快照固定，同一行在事务内读多少次结果都相同：

text
会话 A（REPEATABLE READ）：
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- 1000.00
-- 会话 B 把余额改成 500 并提交
SELECT balance FROM accounts WHERE id = 1;   -- 仍 1000.00
COMMIT;
text
-- 会话 A 输出
BEGIN
 balance
---------
 1000.00
(1 row)

 balance
---------
 1000.00
(1 row)

COMMIT
text
-- 会话 B 在此期间执行
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;   -- 提交成功，但 A 看不到

两个事务在自己的视图里都是「对的」，只是看到的世界不同。这是 SI 的本质：并行执行但各自独立的一致视图。

幻读也被防止：PG 比 SQL 标准更强 ​

SQL 标准允许 REPEATABLE READ 出现幻读（只有 SERIALIZABLE 必须杜绝）。PostgreSQL 的 RR 基于 SI，范围查询的结果集也受快照保护，因此幻读不会发生：

text
会话 A（REPEATABLE READ）：
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders WHERE amount > 1000;   -- 5
-- 会话 B 插入一条 amount = 2000 的订单并提交
SELECT count(*) FROM orders WHERE amount > 1000;   -- 仍 5（无幻读）
COMMIT;
text
-- 会话 A 输出
BEGIN
 count
-------
     5
(1 row)

 count
-------
     5
(1 row)

COMMIT

这是 PostgreSQL 相对许多数据库（包括某些配置下的 MySQL、Oracle）的实际优势，在 PG 里「需要防幻读」不需要一路升到 SERIALIZABLE。

sql
-- 副作用：REPEATABLE READ 下 UPDATE 也只看快照内的行
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE orders SET reviewed = true WHERE amount > 1000;
-- 只会更新快照内的那些行；B 在快照后插入的行不会被本次 UPDATE 触及
COMMIT;
text
BEGIN
UPDATE 5
COMMIT

写冲突检测：40001 与「首个提交者获胜」 ​

SI 遇到两个事务修改同一行时采用首个提交者获胜（first-committer-wins）规则：

text
会话 A（RR）：
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- 拿锁，未提交

会话 B（RR）：
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;    -- 阻塞，等待 A

会话 A：COMMIT;   -- 提交成功

会话 B：-- 解除阻塞后检查发现：目标行在 B 的快照之后被修改过
        -- 直接报错，不尝试重检查
text
-- 会话 B 输出
BEGIN
ERROR:  could not serialize access due to concurrent update

这就是与 READ COMMITTED 最大的区别：

行为READ COMMITTEDREPEATABLE READ
冲突时阻塞后取最新版本重检查 WHERE，通常成功直接报 40001，不重检查
语义「基于最新数据执行」「要么整个事务基于快照成功，要么失败」
应用负担小必须实现重试

注意报错时的细节：40001 之后整个事务进入 aborted 状态，必须 ROLLBACK 后重试整个事务，不能只重试那一条 UPDATE。

sql
-- 错误！40001 之后事务已废，不能继续
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
SELECT 1;
text
ERROR:  current transaction is aborted, commands ignored until end of transaction block

重试模式：REPEATABLE READ 的必备配套 ​

既然 40001 是正常现象而非异常故障，应用必须实现「整事务重试 + 退避」：

python
# Python + psycopg3：REPEATABLE READ 的完整重试模板
import psycopg, time, random
from psycopg import errors

def transfer(from_id: int, to_id: int, amount: float, max_attempts: int = 5) -> None:
    for attempt in range(max_attempts):
        try:
            with psycopg.connect(DSN) as conn:          # 每次重试都用新连接/新事务
                with conn.transaction():
                    conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
                    conn.execute(
                        "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                        (amount, from_id),
                    )
                    conn.execute(
                        "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                        (amount, to_id),
                    )
            return
        except errors.SerializationFailure:            # SQLSTATE 40001
            if attempt == max_attempts - 1:
                raise
            # 指数退避 + 抖动，避免重试风暴
            time.sleep((2 ** attempt) * 0.01 + random.random() * 0.01)
    raise RuntimeError("unreachable")
java
// JDBC：捕获 40001 并整事务重试
for (int attempt = 0; attempt < 5; attempt++) {
    try {
        conn.setAutoCommit(false);
        try (Statement st = conn.createStatement()) {
            st.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ");
        }
        // ... 业务语句 ...
        conn.commit();
        return;
    } catch (SQLException e) {
        conn.rollback();
        if (!"40001".equals(e.getSQLState())) throw e;   // 非串行化失败直接抛出
        Thread.sleep(10L * (1L << attempt));             // 退避后重试
    }
}
sql
-- 也可以用 PL/pgSQL 在数据库内做有限重试
DO $$
DECLARE
  v_attempt int := 0;
BEGIN
  LOOP
    v_attempt := v_attempt + 1;
    BEGIN
      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
      UPDATE accounts SET balance = balance - 100 WHERE id = 1;
      UPDATE accounts SET balance = balance + 100 WHERE id = 2;
      EXIT;                          -- 成功则退出循环
    EXCEPTION WHEN serialization_failure THEN
      IF v_attempt >= 5 THEN RAISE; END IF;
      -- 注意：在 PL/pgSQL 中异常处理依赖子事务，此处需在新语句块中重试
    END;
  END LOOP;
END $$;

实践中更推荐在应用层做重试：数据库内重试受 PL/pgSQL 异常处理语义限制（异常处理块内的事务状态复杂），而应用层可以清晰地控制连接、退避与超时。

只读长查询：REPEATABLE READ 的最佳场景 ​

「需要一致视图且不写数据」是最适合 RR 的场景，例如生成报表、跨表对账、导出快照：

sql
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
-- 下面所有查询共享同一个快照，结果互相自洽
SELECT count(*) FROM orders;
SELECT sum(amount) FROM orders;
SELECT count(*) FROM order_items;
SELECT sum(amount) FROM order_items;   -- 与上面 orders 的合计一致
COMMIT;
text
BEGIN
 count
-------
 10000
(1 row)

    sum
-----------
 1234567.89
(1 row)

 count
-------
 35000
(1 row)

    sum
-----------
 1234567.89
(1 row)

COMMIT

READ ONLY 有两个好处：一是语义明确，二是 PG 可以为只读事务跳过 XID 分配，减少开销。

sql
-- 只读事务里误写会直接报错
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
INSERT INTO orders (amount) VALUES (1);
text
ERROR:  cannot execute INSERT in a read-only transaction

pg_export_snapshot()：把快照共享给其它会话 ​

有时需要让多个会话看到完全相同的快照，例如并行导出、分布式一致性读取。pg_export_snapshot() 导出一个快照标识，其它会话用 SET TRANSACTION SNAPSHOT 导入：

sql
-- 会话 A：导出快照（必须在有快照的事务内）
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
text
BEGIN
 pg_export_snapshot
--------------------
 00000003-0000001E-1
(1 row)
sql
-- 会话 B：导入同一个快照，即可看到与 A 完全一致的数据
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001E-1';
SELECT count(*) FROM orders;   -- 与 A 的视图一致
COMMIT;
text
BEGIN
SET
 count
-------
 10000
(1 row)

COMMIT

约束条件：

  • 导出快照的事务必须保持打开，直到所有导入者都已导入。
  • SET TRANSACTION SNAPSHOT 必须在导入事务的任何查询之前执行。
  • 事务必须已是 REPEATABLE READ 或 SERIALIZABLE 级别。
sql
-- 错误！必须在任何查询之前设置快照
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 1;
SET TRANSACTION SNAPSHOT '00000003-0000001E-1';
text
ERROR:  SET TRANSACTION SNAPSHOT must be called before any query

写偏斜（write skew）：SI 的致命盲区 ​

SI 能防「同一行」的写冲突，但防不住「不同行但相关」的写冲突，这就是写偏斜（write skew）。经典例子是「医生值班」：要求任何时候至少有一名医生在岗。

text
会话 A（RR）：
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE shift = 'night';   -- 2（有 Alice、Bob）
-- A 认为「还有 1 个人在岗，我可以下班」
DELETE FROM oncall WHERE doctor = 'Alice';
-- 未提交

会话 B（RR，同时）：
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE shift = 'night';   -- 也是 2
-- B 也认为「还有 1 个人在岗，我可以下班」
DELETE FROM oncall WHERE doctor = 'Bob';
COMMIT;   -- 成功（删的是不同行，无写冲突）

会话 A：COMMIT;   -- 也成功！
结果：oncall 表里 0 个人，违反「至少 1 人在岗」的约束
text
-- 会话 A 输出
BEGIN
 count
-------
     2
(1 row)

DELETE 1
COMMIT        -- 成功，但业务约束已被破坏

关键点：两个事务写的是不同的行（Alice 与 Bob），SI 的「同一行冲突检测」完全看不到问题。要杜绝写偏斜，只有三条路：

sql
-- 方案 1（推荐）：升级到 SERIALIZABLE，由 SSI 检测 rw 依赖环
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM oncall WHERE shift = 'night';
DELETE FROM oncall WHERE doctor = 'Alice';
COMMIT;
-- 其中一个事务会收到 40001，重试后能看到正确状态
sql
-- 方案 2：用物化冲突点，让两个事务争抢同一行
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM shift_locks WHERE shift = 'night' FOR UPDATE;   -- 显式锁同一行
SELECT count(*) FROM oncall WHERE shift = 'night';
DELETE FROM oncall WHERE doctor = 'Alice';
COMMIT;
sql
-- 方案 3：用数据库约束把不变式固化（本例可用排班表 + 外键计数）
ALTER TABLE oncall
  ADD CONSTRAINT oncall_shift_not_empty
  CHECK (shift <> 'night') NOT VALID;
-- 更实用的做法：把「至少一人在岗」建模为不可删除的「值班长」行 + 唯一约束
python
# 方案 1 的配套重试（写偏斜场景下 40001 是常态）
try:
    with conn.transaction():
        cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
        cur.execute("SELECT count(*) FROM oncall WHERE shift = 'night'")
        if count <= 1:
            raise BusinessError("不能在岗人数不足时下班")
        cur.execute("DELETE FROM oncall WHERE doctor = %s", (doctor,))
except errors.SerializationFailure:
    retry()

与 READ COMMITTED 的对比 ​

对比维度READ COMMITTEDREPEATABLE READ
快照获取时机每条语句事务内第一条语句
视图一致性语句级事务级
不可重复读可能不可能
幻读可能不可能
写冲突处理阻塞 + 重检查（通常成功）报 40001（必须重试）
并发吞吐更高略低（冲突时会失败重试）
应用复杂度低中（需重试框架）
长事务对 VACUUM 影响有有（且更明显，快照更久）
典型用途默认 CRUD一致性读、对账、报表、导出
sql
-- 同一段代码在两种级别下的不同命运
-- READ COMMITTED：两条 UPDATE 都可能成功（累计生效）
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- REPEATABLE READ：若期间 id=1 被别的事务改过，报 40001
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
text
BEGIN
UPDATE 1
COMMIT

BEGIN
ERROR:  could not serialize access due to concurrent update

实战示例 ​

场景：银行日终对账。要求「总账与分户账在同一时点读取，两者必须相等」。

第 1 步：准备数据

sql
DROP TABLE IF EXISTS ledger_entries, accounts_balance;

CREATE TABLE accounts_balance (
  id      bigint PRIMARY KEY,
  owner   text NOT NULL,
  balance numeric(12,2) NOT NULL
);

CREATE TABLE ledger_entries (
  id       bigserial PRIMARY KEY,
  account_id bigint NOT NULL REFERENCES accounts_balance(id),
  amount   numeric(12,2) NOT NULL
);

INSERT INTO accounts_balance (id, owner, balance) VALUES
  (1, 'Alice', 1000.00),
  (2, 'Bob',     800.00);

INSERT INTO ledger_entries (account_id, amount) VALUES
  (1, 1000.00),
  (2,  800.00);
text
DROP TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2

第 2 步：用 REPEATABLE READ 做一致对账

sql
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;

-- 两个查询共享同一快照，必然相等
SELECT sum(balance) AS balance_total FROM accounts_balance;
SELECT sum(amount)  AS ledger_total  FROM ledger_entries;

COMMIT;
text
BEGIN
 balance_total
---------------
       1800.00
(1 row)

 ledger_total
--------------
      1800.00
(1 row)

COMMIT

第 3 步：对比 READ COMMITTED 下可能出现的错账

sql
BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY;

SELECT sum(balance) AS balance_total FROM accounts_balance;
-- 此刻另一个事务完成了一次转账并提交（两个表都被修改）
SELECT sum(amount)  AS ledger_total  FROM ledger_entries;

COMMIT;
text
BEGIN
 balance_total
---------------
       1800.00
(1 row)

 ledger_total
--------------
      1800.00
(1 row)

COMMIT

数值上本例相等，但逻辑上没有保证——两个 sum() 来自不同时刻，只要中间有提交，就可能读到不一致的组合。这正是对账必须用 RR 的原因。

第 4 步：复现写冲突并重试

text
会话 A（RR）：
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts_balance SET balance = balance - 100 WHERE id = 1;

会话 B（RR）：
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts_balance SET balance = balance - 50 WHERE id = 1;
-- 阻塞

会话 A：COMMIT;

会话 B：-- 报 40001，需回滚后重试整个事务
text
-- 会话 B 输出
ERROR:  could not serialize access due to concurrent update

第 5 步：清理

sql
DROP TABLE ledger_entries, accounts_balance;
text
DROP TABLE

注意事项 ​

  • 快照在第一条语句时才取，不是 BEGIN 那一刻。 BEGIN 后长时间不执行语句，快照并未开始；这会影响对「快照时点」的预期。
sql
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- 此时还没有快照
SELECT pg_sleep(10);
-- 第一条查询执行时快照才确定
SELECT count(*) FROM orders;
COMMIT;
  • 40001 不是故障，是契约的一部分。 必须实现重试，否则随机失败会变成业务可用性问题。错误！ 把 40001 当作「系统错误」直接抛给用户。
sql
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- 冲突后整个事务作废，只能 ROLLBACK 重来
  • 40001 后不能只重试失败的那条语句。 事务已 aborted，所有后续语句报 25P02。
sql
-- 错误！
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
UPDATE accounts SET balance = balance - 50 WHERE id = 1;   -- 又一次报错
  • REPEATABLE READ 防不住写偏斜。 需要「多个相关行合起来满足不变式」的场景，必须用 SERIALIZABLE 或显式加锁。
sql
-- 错误！以为 RR 能保证「至少一人在岗」
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE shift = 'night';   -- 2
DELETE FROM oncall WHERE doctor = 'Alice';
COMMIT;   -- 与另一个事务同时提交后，可能在岗人数变 0
  • 长 RR 事务对 VACUUM 的拖累更久。 事务级快照意味着 xmin 在整个事务期间不变，死元组无法回收。
sql
-- 监控长 RR 事务
SELECT pid, state, now() - xact_start AS age, left(query, 50)
FROM   pg_stat_activity
WHERE  xact_start IS NOT NULL
ORDER  BY xact_start
LIMIT  10;
  • SET TRANSACTION SNAPSHOT 必须在任何查询之前，且必须处于 RR / SERIALIZABLE 事务中。
sql
-- 错误！
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 1;
SET TRANSACTION SNAPSHOT '00000003-0000001E-1';

最佳实践 ​

  • 只读一致性场景优先用 REPEATABLE READ READ ONLY：报表、对账、导出、跨表校验。这是 RR 性价比最高的用法。
sql
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT sum(balance) FROM accounts;
SELECT sum(amount)  FROM transfers;
COMMIT;
  • 把所有涉及「读—算—写」的写事务统一到 RR 并配套重试框架，让丢失更新从「静默」变成「显式可重试」。
sql
-- 关键点：计算基于事务内固定快照，冲突时 40001 让应用重来
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;
  • 重试带指数退避与次数上限，避免高冲突下形成重试风暴；重试必须幂等或整事务重放。
python
for attempt in range(5):
    try:
        run_transaction()
        break
    except errors.SerializationFailure:
        time.sleep((2 ** attempt) * 0.01 + random.random() * 0.01)
else:
    raise
  • 事务内不要做外部调用，RR 下事务越长，写冲突概率与重试成本越高。
sql
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE orders SET status = 'paid' WHERE id = 42;
COMMIT;   -- 立刻提交，然后才发通知
  • 需要「多行不变式」时不要停在 RR，直接用 SERIALIZABLE，或用显式锁构造冲突点。
sql
-- 用显式锁把写偏斜变成写冲突（RR 下也能生效）
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM shift_state WHERE shift = 'night' FOR UPDATE;
-- 检查并修改相关行
COMMIT;

下一节

继续学习：SERIALIZABLE

参考链接 ​