PHP小码农

Appearance

脏读 / 不可重复读 / 幻读 ​

并发异常（concurrency anomaly）是理解隔离级别的钥匙：SQL 标准正是用「哪些异常必须被禁止」来定义四个隔离级别的。本节逐一剖析脏读、不可重复读、幻读三种经典异常，并补充读偏斜、丢失更新、写偏斜这三种同样致命的异常，用双会话时间线在 PostgreSQL 18 中真实复现它们，最后给出「什么场景选什么级别」的决策表。

前置知识

阅读本节前，建议先了解：READ UNCOMMITTED / READ COMMITTED、REPEATABLE READ

基础概念 ​

SQL 标准定义了四种「现象（phenomenon）」，并用它们来划分隔离级别的下限：

异常英文SQL 标准的定义
脏读dirty read一个事务读取了并发未提交事务写入的数据
不可重复读nonrepeatable read一个事务重新读取之前读过的数据，发现它被另一个已提交事务修改了
幻读phantom read一个事务重新执行返回行集的查询，发现行集因另一个最近提交的事务而改变
串行化异常serialization anomaly一组事务成功提交的结果，与这些事务按任一顺序逐个执行的结果都不一致

除此之外，数据库文献还用另外三种异常描述 MVCC 类系统的行为：

异常英文一句话定义
读偏斜read skew同一事务内不同读取看到数据库的不同版本，整体不自洽
丢失更新lost update两个事务读同一值再各自写回，后写覆盖先写，先写静默丢失
写偏斜write skew各事务单独看都满足业务约束，并发提交后合起来破坏了约束

PostgreSQL 的实际防护能力（注意与 SQL 标准表格的差异）：

异常READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLE
脏读不可能不可能不可能不可能
不可重复读可能可能不可能不可能
幻读可能可能不可能不可能
读偏斜可能可能不可能不可能
丢失更新可能可能不可能（报 40001）不可能（报 40001）
写偏斜可能可能可能不可能

这张表有两个「反直觉」之处，正是 PostgreSQL 的独特之处：

  1. READ UNCOMMITTED 一列和 READ COMMITTED 完全相同——PostgreSQL 内部只有三种隔离级别，READ UNCOMMITTED 被映射为 READ COMMITTED。
  2. REPEATABLE READ 已经能防幻读——SQL 标准允许 RR 出现幻读（只要求禁止不可重复读），PostgreSQL 的实现比标准更强。

被禁止的现象用「不可能」表示，未被禁止的用「可能」表示。「可能」不等于「一定发生」——异常需要特定的并发时序才会出现，所以它是最难排查的一类 bug。

语法与用法 ​

复现并发异常的标准套路是双会话交错执行：一个会话负责「写」，另一个会话负责「读」，通过控制提交时机把异常逼出来。

text
-- 通用模板

会话 A（写方）                     会话 B（读方）
──────────────────────────────    ──────────────────────────────
BEGIN;                            BEGIN ISOLATION LEVEL <级别>;
<修改数据>                        <读取数据>          ← 第一次读
                                   ─── 此刻 A 提交 ───
COMMIT;                           <读取相同数据>      ← 第二次读
                                   COMMIT;

-- 若两次读到的值不同 → 出现不可重复读
-- 若第二次读到了 A 未提交的值 → 出现脏读
-- 若第二次读到的行集变了 → 出现幻读

最小可运行示例（不可重复读，READ COMMITTED）：

sql
-- 会话 A
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;
sql
-- 会话 B（在 A 的 COMMIT 之前读到 1000，之后读到 500）
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;   -- 1000
SELECT balance FROM accounts WHERE id = 1;   -- 500（同一事务内变了）
COMMIT;
text
 balance
---------
 1000.00
(1 row)

 balance
---------
  500.00
(1 row)

把会话 B 的级别换成 REPEATABLE READ，同样的时序下两次读都返回 1000.00，不可重复读消失。

详细说明 ​

脏读（dirty read）：PostgreSQL 中根本不可能 ​

SQL 标准对脏读的定义是「一个事务读取了并发未提交事务写入的数据」。它是所有异常里最危险的一种，因为那个未提交的写入随时可能被回滚，读到的数据等于从未存在过。

PostgreSQL 基于 MVCC（多版本并发控制），所有写操作产生的都是新版本行，未提交的新版本对其他事务不可见。因此脏读在 PostgreSQL 的任何隔离级别下都不可能发生——包括被映射为 READ COMMITTED 的 READ UNCOMMITTED。

时刻会话 A会话 B
t1BEGIN;
t2UPDATE accounts SET balance = 0 WHERE id = 1;
t3BEGIN;
t4SELECT balance FROM accounts WHERE id = 1; → 1000（读不到 0）
t5ROLLBACK;
t6SELECT balance FROM accounts WHERE id = 1; → 1000
sql
-- 会话 A：改了但故意不提交
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
-- 此时不提交、不回滚
sql
-- 会话 B：任何隔离级别都读不到 A 的未提交值
BEGIN ISOLATION LEVEL READ UNCOMMITTED;   -- PG 内部按 READ COMMITTED 处理
SELECT balance FROM accounts WHERE id = 1;
COMMIT;
text
 balance
---------
 1000.00
(1 row)

如果会话 A 最终 ROLLBACK，会话 B 从头到尾看到的都是正确的 1000.00，没有被污染。这一点上 PostgreSQL 比 SQL 标准更安全：标准允许 READ UNCOMMITTED 出现脏读，PostgreSQL 直接不提供这种能力。

不可重复读（nonrepeatable read）：READ COMMITTED 的常见坑 ​

不可重复读指「同一事务内两次读同一行，结果不同」。它的根因是 READ COMMITTED 的语句级快照：每条语句开始时取一次新快照，所以两条语句之间提交的事务会被后一条语句看到。

这类 bug 在业务代码里非常隐蔽，典型形态是「先查一次判断，再查一次使用」：

时刻会话 A（写方）会话 B（读方，READ COMMITTED）
t1BEGIN;
t2SELECT balance ... WHERE id=1; → 1000
t3BEGIN; UPDATE ... SET balance=500 WHERE id=1; COMMIT;
t4SELECT balance ... WHERE id=1; → 500
t5COMMIT;
sql
-- 会话 B：同一事务内两次读，值却变了
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- 中间会话 A 提交了一次 UPDATE
SELECT balance FROM accounts WHERE id = 1;
COMMIT;
text
 balance
---------
 1000.00
(1 row)

 balance
---------
  500.00
(1 row)

同时读两行时问题更严重，会出现「读偏斜」（见下文）。要消除不可重复读，把会话 B 的级别改成 REPEATABLE READ 即可：快照在事务的第一条语句时获取，之后整个事务都看这一个版本。

sql
-- 修复：升级到 REPEATABLE READ
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- 1000
SELECT balance FROM accounts WHERE id = 1;   -- 仍是 1000
COMMIT;

幻读（phantom read）：行集而不是行的变化 ​

幻读关注的是行集而不是单行：同一事务内两次执行同一个范围查询，第二次多出（或少了）满足条件的行。它通常由并发事务的 INSERT 或 DELETE 引起。

sql
-- 场景：统计某个时段的订单数
-- 会话 B
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*) FROM orders WHERE created_at >= '2026-10-01';
-- 中间会话 A 插入了一条 2026-10-01 的订单并提交
SELECT count(*) FROM orders WHERE created_at >= '2026-10-01';
COMMIT;
text
 count
-------
  1200
(1 row)

 count
-------
  1201
(1 row)
时刻会话 A（写方）会话 B（读方，READ COMMITTED）
t1BEGIN;
t2SELECT count(*) ...; → 1200
t3BEGIN; INSERT INTO orders ...; COMMIT;
t4SELECT count(*) ...; → 1201（幻影行出现）
t5COMMIT;

PostgreSQL 的 REPEATABLE READ 能防幻读，这是它与 SQL 标准的重要差异。标准只要求 RR 禁止不可重复读，允许幻读；PostgreSQL 用快照隔离实现 RR，事务全程用同一个快照，所以第二次范围查询不会看到新插入的行。

sql
-- 修复：REPEATABLE READ 下幻读消失
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders WHERE created_at >= '2026-10-01';   -- 1200
SELECT count(*) FROM orders WHERE created_at >= '2026-10-01';   -- 仍是 1200
COMMIT;

需要警惕的是：防住幻读不等于防住写偏斜。RR 能让「读到的行集」稳定，但两个事务分别对同一行集做检查再各自写入不同行时，仍可能破坏业务约束（详见写偏斜小节）。

读偏斜（read skew）：一个事务看到两个时刻的数据库 ​

读偏斜是不那么出名但危害更大的一种异常：它不一定重复读同一行，而是读取不同数据项时看到了数据库的不同版本，导致整体不自洽。经典案例是转账期间读余额：

text
初始状态：账户 1 = 500，账户 2 = 500，合计 1000

事务 T1（转账）：从账户 2 转 100 到账户 1
  账户 1 → 600
  账户 2 → 400

事务 T2（对账，READ COMMITTED）：
  ① 读账户 1  → 500     （T1 提交前）
  ─── T1 提交 ───
  ② 读账户 2  → 400     （T1 提交后）
  合计 = 900  ← 凭空少了 100！
时刻会话 A（转账）会话 B（对账，READ COMMITTED）
t1BEGIN;
t2UPDATE accounts SET balance = balance + 100 WHERE id = 1;
t3UPDATE accounts SET balance = balance - 100 WHERE id = 2;
t4BEGIN;
t5SELECT balance FROM accounts WHERE id = 1; → 500（旧版本）
t6COMMIT;
t7SELECT balance FROM accounts WHERE id = 2; → 400（新版本）
t8COMMIT; → 合计 900
sql
-- 会话 B：两次读各自取新快照，读到不一致的中间状态
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;   -- 500
SELECT balance FROM accounts WHERE id = 2;   -- 400
COMMIT;
text
 balance
---------
  500.00
(1 row)

 balance
---------
  400.00
(1 row)

读偏斜在 REPEATABLE READ 及以上被彻底消除——整个事务共用一个快照，两次读必然互相一致。

sql
-- 修复：快照隔离下对账结果自洽
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- 要么 (600,400)，要么 (500,500)，绝无 (500,400)
SELECT balance FROM accounts WHERE id = 2;
COMMIT;

对于「逻辑备份」「跨表校验」「报表快照」这类场景，读偏斜是无法接受的，必须使用 REPEATABLE READ 或更高的级别。

丢失更新（lost update）：后写覆盖先写 ​

丢失更新的形态是：两个事务读取同一个值，各自计算后写回，后者覆盖前者，前者的修改被静默丢弃。它是「读—算—写」模式的经典陷阱。

text
初始：库存 stock = 10

会话 A：读 stock=10，判断 10 > 0，写 stock = 10 - 1 = 9
会话 B：读 stock=10，判断 10 > 0，写 stock = 10 - 1 = 9   ← A 的扣减丢失了
最终：9（正确应为 8），卖出了 2 件却只扣了 1 件
时刻会话 A（READ COMMITTED）会话 B（READ COMMITTED）
t1BEGIN;
t2SELECT stock FROM items WHERE id=1; → 10
t3BEGIN;
t4SELECT stock FROM items WHERE id=1; → 10
t5UPDATE items SET stock=9 WHERE id=1;
t6COMMIT;
t7UPDATE items SET stock=9 WHERE id=1;（覆盖）
t8COMMIT; → 最终 9
sql
-- 反例：应用层算好值再写回，丢失更新
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT stock FROM items WHERE id = 1;         -- 10
UPDATE items SET stock = 9 WHERE id = 1;      -- 应用层计算 10-1
COMMIT;

有三种正确写法：

sql
-- 写法 1（推荐）：让数据库自己做相对更新，天然无丢失更新
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
sql
-- 写法 2：先把行锁住，再读再写
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT stock FROM items WHERE id = 1 FOR UPDATE;   -- 阻塞并发修改
UPDATE items SET stock = stock - 1 WHERE id = 1;
COMMIT;
sql
-- 写法 3：升级到 REPEATABLE READ，冲突会以 40001 暴露（而不是静默丢失）
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT stock FROM items WHERE id = 1;
UPDATE items SET stock = 9 WHERE id = 1;   -- 若被并发修改过则报错，需重试
COMMIT;
text
ERROR:  could not serialize access due to concurrent update

注意 REPEATABLE READ 下的丢失更新不是「被静默覆盖」，而是变成显式的 40001 错误——问题被暴露出来，但仍然需要应用重试。

写偏斜（write skew）：每个事务单独看都合法 ​

写偏斜是快照隔离（Snapshot Isolation）的致命盲区，也是 REPEATABLE READ 与 SERIALIZABLE 的分水岭。它的特征极其反直觉：每个事务单独执行都是正确的，但并发提交后业务约束被破坏。

最经典的例子是「医生值班」：约束是「night 班至少 1 人在岗」。

text
初始：night 班有 Alice 和 Bob 两人

会话 A：BEGIN; SELECT count(*) ... WHERE shift='night';  -- 2，> 1，可以下班
        DELETE FROM oncall WHERE doctor='Alice';
会话 B：BEGIN; SELECT count(*) ... WHERE shift='night';  -- 也是 2，也可以下班
        DELETE FROM oncall WHERE doctor='Bob';

两个事务都提交成功 → night 班 0 人在岗，约束被破坏
时刻会话 A（REPEATABLE READ）会话 B（REPEATABLE READ）
t1BEGIN;
t2SELECT count(*) ...; → 2
t3BEGIN;
t4SELECT count(*) ...; → 2（自己的快照）
t5DELETE ... doctor='Alice';
t6COMMIT;
t7DELETE ... doctor='Bob';
t8COMMIT; → 约束被破坏
sql
-- 会话 A 与 B 在 REPEATABLE READ 下都会成功，约束被破坏
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE shift = 'night';   -- 2
DELETE FROM oncall WHERE doctor = 'Alice';           -- 另一个会话删 Bob
COMMIT;

为什么 REPEATABLE READ 拦不住？因为两个事务写的是不同的行（Alice 与 Bob），不存在写写冲突，行锁根本不冲突；它们只是都「读到了 2」这个已经被对方改变的事实。丢失更新是「写同一行」的冲突，写偏斜是「写不同行但读同一范围」的冲突。

只有 SERIALIZABLE 能拦住它——SSI 追踪读写依赖，发现 rw 依赖环后中止其中一方：

sql
-- 修复：SERIALIZABLE 下必有一方报 40001
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM oncall WHERE shift = 'night';
DELETE FROM oncall WHERE doctor = 'Alice';
COMMIT;
text
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

如果业务约束能用唯一约束或 CHECK 表达，优先用约束固化，比 SERIALIZABLE 更便宜（详见「最佳实践」）。

PostgreSQL 各隔离级别防护能力：为什么比 SQL 标准更强 ​

把 PostgreSQL 的实际行为与 SQL 标准的最低要求并排放：

异常SQL 标准要求禁止的级别PostgreSQL 实际禁止的级别
脏读READ COMMITTED 及以上所有级别（含 READ UNCOMMITTED）
不可重复读REPEATABLE READ 及以上REPEATABLE READ 及以上
幻读SERIALIZABLEREPEATABLE READ 及以上
串行化异常SERIALIZABLESERIALIZABLE

两处「超额交付」的原因：

① READ UNCOMMITTED 被映射为 READ COMMITTED。 PostgreSQL 只实现了三种内部隔离级别。MVCC 架构下不存在「读未提交版本」的能力，所以干脆把 READ UNCOMMITTED 映射为 READ COMMITTED：

sql
-- READ UNCOMMITTED 实际得到的是 READ COMMITTED
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;
COMMIT;
text
 transaction_isolation
-----------------------
 read committed
(1 row)

② REPEATABLE READ 用快照隔离实现，顺带防住了幻读。 SQL 标准只要求 RR 禁止不可重复读；PostgreSQL 的 RR 是快照隔离，事务全程一个快照，幻读也就自然不可能发生。这是标准明确允许的增强（标准规定的是「最低要求」，更高保证是允许的）：

sql
BEGIN ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;
COMMIT;
text
 transaction_isolation
-----------------------
 repeatable read
(1 row)

需要强调的是：「更强」不等于「可以升级使用」。REPEATABLE READ 引入了 40001 重试成本，不会凭空变成免费的 SERIALIZABLE——它仍然防不住写偏斜。选择级别时要看「业务不变式的形态」，而不是「哪个更强」。

复现异常的方法论：双会话时间线 ​

排查并发 bug 的关键能力是「稳定复现」。推荐一套可重复的方法论：

  1. 固定数据起点：每次复现前把测试数据重置到已知状态。
  2. 两个独立连接：用两个 psql 会话（或两个数据库客户端），不要用同一连接。
  3. 用时间线表格记录：把每一步操作与观察结果写下来，交错顺序就是异常的本质。
  4. 控制提交时机：异常通常出现在「读—写—提交」的交错点上，需要人为停顿。
  5. 逐级验证：在同一时序下依次跑 READ COMMITTED / REPEATABLE READ / SERIALIZABLE，观察异常是否消失。
sql
-- 第 1 步：准备可复现的数据
DROP TABLE IF EXISTS anomaly_demo;
CREATE TABLE anomaly_demo (id int PRIMARY KEY, val int NOT NULL);
INSERT INTO anomaly_demo VALUES (1, 100);
text
DROP TABLE
CREATE TABLE
INSERT 0 1
sql
-- 第 2 步：会话 A 改而不提交（用于验证脏读）
BEGIN;
UPDATE anomaly_demo SET val = 999 WHERE id = 1;
-- 保持这个事务不结束
sql
-- 第 3 步：会话 B 在不同级别下读取，记录结果
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT val FROM anomaly_demo WHERE id = 1;   -- 100，不是 999
COMMIT;
text
 val
-----
 100
(1 row)

用 pg_stat_activity 可以观察两个会话的实时状态，确认它们确实处在不同的交错点：

sql
SELECT pid, state, now() - xact_start AS xact_age, left(query, 40) AS query
FROM   pg_stat_activity
WHERE  backend_type = 'client backend'
  AND  xact_start IS NOT NULL
ORDER  BY xact_start;
text
 pid  |        state        | xact_age |                  query
------+---------------------+----------+------------------------------------------
 7312 | idle in transaction | 00:00:12 | UPDATE anomaly_demo SET val = 999 WHERE
 7345 | active              | 00:00:01 | SELECT val FROM anomaly_demo WHERE id =
(2 rows)

把每次复现的时间线表格与 SQL 序列一起保存下来，就是最好的 bug 报告与回归测试用例。

异常的检测、重试与工程化处理 ​

不同异常需要不同的工程对策，把处理方式固化到框架层比在每个业务函数里手写更可靠：

异常检测方式工程对策
脏读PostgreSQL 中不存在无需处理
不可重复读 / 读偏斜业务数据对不上账只读事务用 REPEATABLE READ READ ONLY
幻读范围统计结果抖动REPEATABLE READ 及以上
丢失更新计数/库存对不上相对更新 / FOR UPDATE / 升级级别
写偏斜约束被破坏但找不到并发写SERIALIZABLE + 重试，或改用约束
串行化失败捕获 SQLSTATE 40001重试整个事务（指数退避）
死锁捕获 SQLSTATE 40P01重试整个事务，并统一加锁顺序

40001 与 40P01 都属于「可重试错误」，重试必须覆盖整个事务：

python
import time, random, psycopg
from psycopg import errors

RETRYABLE = (errors.SerializationFailure, errors.DeadlockDetected)

def run_with_retry(conn, fn, max_attempts: int = 5):
    for attempt in range(max_attempts):
        try:
            with conn.transaction():
                return fn(conn)
        except RETRYABLE:
            if attempt == max_attempts - 1:
                raise
            delay = min(0.05 * (2 ** attempt), 1.0)
            time.sleep(delay + random.uniform(0, delay / 2))

40002（完整性约束违例）、40003（语句完成状态未知）虽然也在 SQLSTATE Class 40 里，但语义不同，不能无脑重试：

sql
-- 属于 Class 40（事务回滚）的常见错误码
-- 40001  serialization_failure        ← 可重试
-- 40P01  deadlock_detected            ← 可重试
-- 40002  transaction_integrity_constraint_violation  ← 一般是业务错误
-- 40003  statement_completion_unknown ← 需人工核对

在 PostgreSQL 里定位「谁的并发导致了我的异常」，可以查统计视图的冲突计数：

sql
SELECT datname,
       xact_commit,
       xact_rollback,
       deadlocks,
       pg_stat_get_db_conflict_all(oid) AS all_conflicts
FROM   pg_stat_database
WHERE  datname = current_database();
text
 datname | xact_commit | xact_rollback | deadlocks | all_conflicts
---------+-------------+---------------+-----------+---------------
 mydb    |      204512 |          2103 |         4 |            37
(1 row)

xact_rollback 与 all_conflicts 持续增长，说明重试率偏高，应检查事务是否过长、读范围是否过大。

隔离级别选型决策表 ​

把「业务不变式的形态」作为第一判据，而不是「习惯用哪个级别」：

业务特征推荐级别关键理由
普通 CRUD、单行更新READ COMMITTED（默认）语句级原子性足够，无重试成本
x = x + n 类型的相对更新READ COMMITTED由数据库计算，天然无丢失更新
一致性报表、对账、逻辑备份REPEATABLE READ READ ONLY需要消除读偏斜的稳定快照
长事务中反复读同一批数据REPEATABLE READ事务级快照，视图稳定
「读—算—写」同一行REPEATABLE READ 或 FOR UPDATE冲突以 40001 暴露而非静默丢失
「读—算—写」多个不同行SERIALIZABLE写偏斜只有 SSI 能防
排班、占座、额度分配SERIALIZABLE 或唯一约束典型写偏斜场景
极高吞吐的计数器READ COMMITTED重试成本会让强隔离得不偿失
只读从库上的长查询REPEATABLE READ READ ONLY与主库写事务互不干扰

一句话原则：能用约束或单语句表达的规则，就不要靠隔离级别去保证；只有无法用约束表达的跨行不变式，才上 SERIALIZABLE。

sql
-- 能用唯一约束表达的规则，优先用约束（便宜、可靠、无重试）
ALTER TABLE bookings ADD CONSTRAINT bookings_room_slot_uniq UNIQUE (room_id, slot);
sql
-- 无法用约束表达的跨行不变式，才用 SERIALIZABLE + 重试
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM oncall WHERE shift = 'night';
-- 业务判断后决定是否 DELETE
COMMIT;

实战示例 ​

场景：订单库存扣减。先复现「丢失更新导致的超卖」，再逐级验证三种隔离级别的实际效果。

第 1 步：准备数据

sql
DROP TABLE IF EXISTS items;

CREATE TABLE items (
  id    int PRIMARY KEY,
  name  text    NOT NULL,
  stock int     NOT NULL CHECK (stock >= 0)
);

INSERT INTO items VALUES (1, '限量商品', 10);
text
DROP TABLE
CREATE TABLE
INSERT 0 1

第 2 步：单会话正常扣减（相对更新的正确写法）

sql
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
SELECT * FROM items WHERE id = 1;
text
UPDATE 1
 id |   name   | stock
----+----------+-------
  1 | 限量商品 |     9
(1 row)

第 3 步：双会话在 READ COMMITTED 下复现「应用层读—算—写」的丢失更新

text
时刻   会话 A                              会话 B
────   ────────────────────────────────    ────────────────────────────────
t1     BEGIN;
t2     SELECT stock ...;              → 9
t3                                         BEGIN;
t4                                         SELECT stock ...;          → 9
t5     UPDATE items SET stock=8 ...;
t6     COMMIT;
t7                                         UPDATE items SET stock=8 ...;
t8                                         COMMIT;
       ── 两次扣减只生效一次，stock = 8，静默超卖 1 件 ──
sql
-- 反例（会话 A 与 B 都执行这段，级别为 READ COMMITTED）
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT stock FROM items WHERE id = 1;        -- 9
-- 应用层算出 9-1=8
UPDATE items SET stock = 8 WHERE id = 1;     -- 后提交者覆盖前者
COMMIT;
text
BEGIN
 stock
-------
     9
(1 row)

UPDATE 1
COMMIT

第 4 步：改为相对更新，问题消失

sql
-- 正确写法：计算与写入在同一条语句内，由数据库保证原子性
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;
text
BEGIN
UPDATE 1
COMMIT

第 5 步：验证 REPEATABLE READ 下「读—算—写」会报 40001 而不是静默丢失

sql
-- 会话 A 与 B 并发执行；A 先提交，B 报错
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT stock FROM items WHERE id = 1;
UPDATE items SET stock = stock - 1 WHERE id = 1;
COMMIT;
text
BEGIN
 stock
-------
     8
(1 row)

ERROR:  could not serialize access due to concurrent update

应用捕获 40001 后重试整个事务，第二轮就能看到对方已提交的结果：

sql
-- 重试：新事务快照能看到对方的结果
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT stock FROM items WHERE id = 1;   -- 7（对方已扣减）
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;
text
BEGIN
 stock
-------
     7
(1 row)

UPDATE 1
COMMIT

第 6 步：验证 CHECK 约束是最后一道防线

sql
-- 人为把库存压到 0 后再扣减，约束会拦住超卖
UPDATE items SET stock = 0 WHERE id = 1;
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;   -- 条件不满足，0 行
UPDATE items SET stock = stock - 1 WHERE id = 1;                 -- 绕过条件，触发约束
text
UPDATE 1
UPDATE 0
ERROR:  new row for relation "items" violates check constraint "items_stock_check"
DETAIL:  Failing row contains (1, 限量商品, -1).

第 7 步：清理

sql
DROP TABLE items, anomaly_demo;
text
DROP TABLE

注意事项 ​

  • 不要用「一定不会发生」的心态依赖 READ COMMITTED。 它的「可能」只表示需要特定时序，而高并发下这种时序每天都在发生。错误！ 用 SELECT 判断后再 UPDATE，认为单用户测试通过就没问题。

  • READ UNCOMMITTED 在 PostgreSQL 中不等于真正的读未提交。 它会静默变成 READ COMMITTED。错误！ 为了「性能」显式写 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED——既拿不到语义差异，也不会带来性能收益。

sql
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;   -- read committed
COMMIT;

  • REPEATABLE READ 不是「更强的 READ COMMITTED」那么简单。 它带来 40001 重试契约，且仍然防不住写偏斜。不推荐在没准备好重试框架时盲升级别。

  • SERIALIZABLE 需要重试，不是「设了就没事」。 它把并发正确性变成「可能失败但可重试」的工程问题。错误！ 把 40001 当致命错误返回 500 给用户。

  • 只重试失败语句是错的。 事务在错误后进入 aborted 状态，后续语句全部报 25P02。必须整段 ROLLBACK 后重试整个事务。

sql
-- 错误！
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE items SET stock = stock - 1 WHERE id = 1;   -- 40001
SELECT 1;   -- ERROR: current transaction is aborted

  • 「读到一致快照」和「结果可串行化」是两件事。 REPEATABLE READ 保证前者，只有 SERIALIZABLE 保证后者。判断业务要求的是「数据一致」还是「结果正确」。

  • 序列（sequence）不回退不参与事务。 nextval 的推进立即对其他事务可见且不随回滚撤销，用序列号做「无间隙编号」的假设本身就是错的。

sql
SELECT nextval('orders_id_seq');   -- 即使事务回滚，这个值也不会被退回

最佳实践 ​

  • 默认留在 READ COMMITTED，只在有明确理由时升级。 大多数 CRUD 与相对更新在这里既正确又最快。

  • 把「读—算—写」改写成单条语句或相对更新，这是消除丢失更新最便宜的方式。

sql
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
  • 只读的一致性查询统一用 REPEATABLE READ READ ONLY，彻底消除读偏斜与不可重复读，且不会产生写冲突。对延迟敏感的报表可进一步加 DEFERRABLE。
sql
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT count(*) FROM orders;
SELECT sum(amount) FROM orders;
COMMIT;
  • 能用约束表达的规则一律用约束（UNIQUE / CHECK / 外键），它比任何隔离级别都便宜、可靠、且无需重试。
sql
ALTER TABLE bookings ADD CONSTRAINT bookings_room_slot_uniq UNIQUE (room_id, slot);
  • SERIALIZABLE 与重试框架必须成对出现，并保证重试路径幂等（唯一键 + ON CONFLICT、业务幂等键）。
sql
INSERT INTO payments (order_id, amount, idempotency_key)
VALUES (42, 100.00, 'order-42-attempt')
ON CONFLICT (idempotency_key) DO NOTHING;
  • 把并发异常写成回归测试。 用两个连接按时间线复现，作为 CI 的一部分，防止后续改动悄悄把级别降回去。
sql
-- 回归测试骨架：断言 REPEATABLE READ 下两次读结果相同
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT val FROM anomaly_demo WHERE id = 1;
-- 并发会话在此提交一次 UPDATE
SELECT val FROM anomaly_demo WHERE id = 1;   -- 必须与第一次相同
COMMIT;
  • 监控 40001 / 40P01 与 xact_rollback 的增长率，把它当作容量与设计的反馈信号，而不是当作噪音。
sql
SELECT datname, xact_rollback, deadlocks, pg_stat_get_db_conflict_all(oid) AS conflicts
FROM   pg_stat_database
WHERE  datname = current_database();

下一节

继续学习：快照隔离与可串行化异常

参考链接 ​