主题
第 7 章 事务与隔离级别
「我转账给你 100 元，要么我账户少 100、你账户多 100；要么我们俩都没动。绝不允许钱凭空消失或凭空增加。」——这就是事务最朴素的承诺。
PostgreSQL 的事务是它最骄傲的特性之一：默认开启、完全 ACID、且在 SERIALIZABLE 级别下用业界领先的 SSI（Serializable Snapshot Isolation） 算法实现，这是 MySQL InnoDB 至今没有的。本章会把 ACID、隔离级别、SSI、保存点、两阶段提交一次性吃透。
7.1 导读：为什么事务是数据库的灵魂
一个会让你睡不着觉的故事
银行小张写了一段「转账」程序：
python
db.execute("UPDATE ch7_accounts SET balance = balance - 100 WHERE id = 1")
# ↑ 如果机器在这里突然断电……
db.execute("UPDATE ch7_accounts SET balance = balance + 100 WHERE id = 2")如果数据库没有事务，那么 Alice 的账户会少 100 元、Bob 的账户却没多 100 元，凭空蒸发的 100 元谁来背？答案是：程序员背、DBA 背、银行赔。
事务的作用就是把多条 SQL 打包成一个原子单元：要么全部成功（COMMIT），要么全部当作没发生（ROLLBACK）。
本章要解决的 5 个问题
- ACID 到底指什么？PostgreSQL 是怎么保证它的？
- 写完
BEGIN之后到底发生了什么？为什么 PG 出错后整个事务就「废了」？ - 「脏读、不可重复读、幻读、序列化异常」这 4 种并发问题分别长什么样？
- PG 的 4 个隔离级别（READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE）实际行为如何？为什么 PG 没有真正的 READ UNCOMMITTED？
- SSI 是什么神仙算法？为什么它比 MySQL 的「Repeatable Read + Gap Lock」更优雅？
7.2 ACID 详解：数据库的四大誓言
ACID 是 1983 年由 Andreas Reuter 和 Theo Härder 提出的事务四大特性。我们用「银行流水」作类比逐个拆解。
┌──────────────────────────────────────────────────────────────┐
│ A Atomicity 原子性 要么全做，要么全不做 │
│ C Consistency 一致性 事务前后数据库满足约束 │
│ I Isolation 隔离性 并发事务互相看不见对方的中间状态 │
│ D Durability 持久性 一旦 COMMIT 就永久落盘 │
└──────────────────────────────────────────────────────────────┘A — Atomicity 原子性
🍳 生活类比：煎一个荷包蛋。要么蛋出锅了端上桌（成功），要么从锅里铲出来扔掉（失败）。绝不可能「蛋的左半边端上桌、右半边还在锅里」。
PostgreSQL 通过 WAL（Write-Ahead Log，预写日志） 实现原子性：
- 每条修改在写入数据页前，先把「我要做什么」记录到 WAL 文件；
- 事务
COMMIT时，把 WAL 强制fsync到磁盘； - 如果在
COMMIT之前进程崩溃，重启后回放 WAL，把已提交的事务重做（REDO），未提交的事务自动当作不存在（PG 用pg_xact目录的事务状态位标记，未标记 COMMIT 的事务等价于 ABORT）。
sql
BEGIN;
UPDATE ch7_accounts SET balance = balance - 100 WHERE id = 1;
UPDATE ch7_accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 这中间任何一步失败 / 断电 / kill -9，账户都不会出现「钱蒸发」C — Consistency 一致性
📚 生活类比：会计的「资产 = 负债 + 所有者权益」永远要相等。每记一笔账，等式两边都要同时变。
一致性是数据始终满足业务规则与数据库约束：
- 数据库层面：主键、外键、
CHECK、UNIQUE、触发器、NOT NULL永远满足； - 应用层面：业务不变量（如「账户余额 ≥ 0」「订单总额 = SUM(明细)」）由开发者用约束 + 事务一起保证。
PG 在事务提交时强制检查所有「立即式」约束（IMMEDIATE）；可以通过 DEFERRABLE INITIALLY DEFERRED 让外键约束延迟到 COMMIT 时才检查。
sql
-- ch7_accounts 表有 CHECK (balance >= 0)
BEGIN;
UPDATE ch7_accounts SET balance = balance - 99999 WHERE id = 1;
-- ERROR: new row for relation "ch7_accounts" violates check constraint "ch7_accounts_balance_check"
ROLLBACK;I — Isolation 隔离性
🚪 生活类比：你和室友同时上厕所，每个厕所是个独立的隔间，看不到对方在干嘛。隔离级别就是「门上有几把锁、隔板有多高」。
隔离性是并发事务之间互相干扰的程度。完全隔离（SERIALIZABLE）= 事务串行执行；完全不隔离（READ UNCOMMITTED）= 你能看到别人没提交的脏数据。隔离级别是性能与正确性的折中，本章 7.5 节专题讨论。
D — Durability 持久性
🪨 生活类比：在石头上刻字 vs 在沙滩上写字。事务一旦
COMMIT，就该像「刻在石头上」——下一秒地震、断电、运维拔电源，重启后数据照样在。
PG 用「WAL + fsync + Checkpoint」三件套保证持久性：
┌─ 事务 COMMIT ────────────────────────────────────┐
│ │
│ 内存中修改数据页（脏页） │
│ │ │
│ ▼ │
│ 生成 WAL 记录到 wal_buffers │
│ │ │
│ ▼ │
│ COMMIT 时调用 fsync(pg_wal/...) ← 关键！ │
│ │ │
│ ▼ │
│ 返回客户端 "COMMIT" 成功 │
│ │
│ 数据页之后由 Checkpoint / Bgwriter 慢慢刷盘 │
│ （即使没刷到数据文件，崩溃恢复时也会重放 WAL） │
└──────────────────────────────────────────────────┘⚙️ 配置细节：
synchronous_commit = on（默认）才真的 fsync；改成off会异步落盘，性能高但断电会丢最近事务（一般丢 ≤ 600ms）。生产慎改。
7.3 事务控制 SQL 全家桶
7.3.1 显式事务
PG 的标准事务起点：
sql
BEGIN; -- 与 START TRANSACTION 完全等价
-- ...一堆 SQL...
COMMIT; -- 提交；可写 END;
-- 或
ROLLBACK; -- 回滚；可写 ABORT;实操示例（psql）：
text
learn_pg=# BEGIN;
BEGIN
learn_pg=*# UPDATE ch7_accounts SET balance = balance - 100 WHERE id = 1;
UPDATE 1
learn_pg=*# UPDATE ch7_accounts SET balance = balance + 100 WHERE id = 2;
UPDATE 1
learn_pg=*# COMMIT;
COMMIT注意提示符变化：=# → =*#（在事务中）→ =#。这是 psql 给的小贴士。
7.3.2 隐式事务（autocommit）
PG 默认 autocommit = on，即每条单独的 SQL 自动包裹一个事务：
sql
UPDATE ch7_accounts SET balance = balance - 100 WHERE id = 1;
-- ↑ 等价于 BEGIN; UPDATE...; COMMIT;这是新手最常踩的坑：以为「我没写 BEGIN，应该不会真的提交吧？」——错，PG 已经提交了。
📌 与 MySQL 的区别
项目 MySQL PostgreSQL autocommit 默认 ON ON 事务出错后行为 出错的语句被忽略，事务可继续执行 整个事务被标记为 aborted，必须 ROLLBACK 显式开始事务 START TRANSACTION或BEGIN同 DDL 是否可回滚 大部分 DDL 不可 回滚（隐式提交） 几乎全部 DDL 可回滚（神技！）
7.3.3 PG 「事务出错则全废」陷阱
这是 PG 比 MySQL 更严格的地方，必须背下来：
text
learn_pg=# BEGIN;
BEGIN
learn_pg=*# SELECT 1;
?column?
----------
1
(1 row)
learn_pg=*# SELECT * FROM nonexistent_table;
ERROR: relation "nonexistent_table" does not exist
LINE 1: SELECT * FROM nonexistent_table;
^
learn_pg=!# SELECT 2;
ERROR: current transaction is aborted, commands ignored until end of transaction block
learn_pg=!# COMMIT;
ROLLBACK注意第三个提示符 =!#（叹号），表示事务已废，后续任何 SQL 都会报「current transaction is aborted」。COMMIT 实际效果是 ROLLBACK。
✅ 解决方案有三：
- 事务里出错后立即
ROLLBACK； - 用 保存点 SAVEPOINT 把错误语句包起来；
- 在 psql 中开启
\set ON_ERROR_ROLLBACK interactive，让 psql 自动在每条语句前打保存点。
7.3.4 SAVEPOINT 保存点
💾 生活类比：玩 RPG 游戏走到一个存档点，往前打 BOSS 失败了可以「读档回到存档点」，而不是从游戏开头重新打。
sql
BEGIN;
INSERT INTO ch7_cart_items (user_id, product_id, qty) VALUES (10, 1, 1);
SAVEPOINT sp_step1;
INSERT INTO ch7_cart_items (user_id, product_id, qty) VALUES (10, 2, -5);
-- ERROR: new row for relation "ch7_cart_items" violates check constraint "ch7_cart_items_qty_check"
ROLLBACK TO SAVEPOINT sp_step1;
-- 之前的 sp_step1 之后的修改被撤销，但 sp_step1 之前的 INSERT 还在！
INSERT INTO ch7_cart_items (user_id, product_id, qty) VALUES (10, 2, 2);
COMMIT;
SELECT * FROM ch7_cart_items;
-- id | user_id | product_id | qty
-- ----+---------+------------+-----
-- 1 | 10 | 1 | 1
-- 2 | 10 | 2 | 2完整命令：
| 命令 | 作用 |
|---|---|
SAVEPOINT name | 在事务中打一个存档点 |
RELEASE SAVEPOINT name | 释放（不再可回滚到）某个存档点，但保留之后的修改 |
ROLLBACK TO SAVEPOINT name | 回到存档点，撤销之后的所有修改 |
底层原理：每个 SAVEPOINT 在 PG 内部创建一个子事务（sub-transaction），子事务有自己的 xid（事务 ID）。ROLLBACK TO 实际上是把这个子 xid 标记为 aborted，老元组继续有效，新元组对外不可见。
7.3.5 事务模式参数
sql
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
-- 或者
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;| 参数 | 含义 |
|---|---|
ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } | 设置当前事务隔离级别（仅本事务有效） |
READ ONLY / READ WRITE | 只读 / 读写 |
DEFERRABLE | PG 特色：仅 SERIALIZABLE READ ONLY 可用，让事务等到没有可能造成冲突的读写时再开始，永远不会被 SSI 回滚 |
DEFERRABLE 的妙用：跑长报表时设置
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE，可以拿到一致性快照，且不会因为别人的写入被回滚。
修改默认隔离级别（会话级 / 库级）：
sql
-- 当前会话
SET default_transaction_isolation = 'repeatable read';
-- 整个库（postgresql.conf 或 ALTER DATABASE）
ALTER DATABASE learn_pg SET default_transaction_isolation = 'repeatable read';7.3.6 两阶段提交 PREPARE TRANSACTION
跨库 / 跨服务的分布式事务（XA）需要「先准备、再统一提交」两步：
sql
BEGIN;
UPDATE ch7_accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'xact_001'; -- ← 第 1 阶段：准备
-- 此时事务被持久化但未提交，连接已可关闭
-- 在所有参与者都 PREPARE 成功后：
COMMIT PREPARED 'xact_001'; -- ← 第 2 阶段：提交
-- 或者
ROLLBACK PREPARED 'xact_001'; -- ← 第 2 阶段：回滚注意：默认配置 max_prepared_transactions = 0 是禁用的，需先在 postgresql.conf 调大并重启。不建议在没有协调器（如 XA / Sagas）的情况下手工玩。
7.4 并发问题图谱：4 种现象
🎭 生活类比：A 客人在酒店退房，B 客人正在打扫房间。如果两人「不隔离」会出现各种尴尬：
- B 看到 A 还没退房（脏读）
- B 第一次看是空房，再看一次有人（不可重复读）
- B 数房间时，A 偷偷开了一间新房（幻读）
- B 帮忙调度时，A 同时也在调度，俩人各自看到的世界都对，但合起来酒店超卖了（序列化异常）
下面用「转账 + 库存」举例，每种现象给一个精确的双事务时间线。设两个事务 T1、T2，时间从上往下：
7.4.1 脏读 Dirty Read
定义：一个事务读到了另一个尚未提交事务的修改。
T1 T2
───────────────────────────── ─────────────────────────────
BEGIN;
UPDATE ch7_products SET stock=0
WHERE id=1;
BEGIN;
SELECT stock FROM ch7_products
WHERE id=1; -- 读到 0 ❌（脏数据）
ROLLBACK;
-- T2 此时基于 0 做了决策，错了！真实危害：T1 回滚了，T2 看到的是「不存在的现实」。
7.4.2 不可重复读 Non-repeatable Read
定义：同一个事务内，两次查同一行，结果不同（被别人改了）。
T1 T2
───────────────────────────── ─────────────────────────────
BEGIN;
SELECT price FROM ch7_products
WHERE id=1; -- 7999
BEGIN;
UPDATE ch7_products SET price=8999
WHERE id=1;
COMMIT;
SELECT price FROM ch7_products
WHERE id=1; -- 8999 ❌（同一事务读不一致）
COMMIT;真实危害：报表事务里两次 SUM(...) 不一致；锁判断逻辑被破坏。
7.4.3 幻读 Phantom Read
定义：同一个事务内两次执行相同范围查询，第二次多出 / 少了几行（别人 INSERT/DELETE 了）。
T1 T2
───────────────────────────── ─────────────────────────────
BEGIN;
SELECT count(*) FROM ch7_products
WHERE price < 10000; -- 3 行
BEGIN;
INSERT INTO ch7_products
VALUES (5,'AppleTV',999,30);
COMMIT;
SELECT count(*) FROM ch7_products
WHERE price < 10000; -- 4 行 ❌（多出一行幽灵）
COMMIT;📌 「不可重复读」与「幻读」的区别：
- 不可重复读：同一行两次读结果不同（被 UPDATE / DELETE）
- 幻读：范围查询两次行数不同（被 INSERT）
7.4.4 序列化异常 Serialization Anomaly（PG 特别强调）
定义：每个事务自己看都没问题（满足业务规则），但串行不能产生这种结果，合在一起破坏了不变量。又叫「写偏序 Write Skew」。
经典案例：医院值班表。规则「任意时刻必须至少有 1 名医生值班」。
初始数据：Dr.Wang on_duty=true、Dr.Li on_duty=true。
T1（Wang 想请假） T2（Li 想请假）
───────────────────────────── ─────────────────────────────
BEGIN ISOLATION LEVEL
REPEATABLE READ;
SELECT count(*) FROM ch7_doctors
WHERE on_duty; -- 2 ✓
BEGIN ISOLATION LEVEL
REPEATABLE READ;
SELECT count(*) FROM ch7_doctors
WHERE on_duty; -- 2 ✓
UPDATE ch7_doctors
SET on_duty=false WHERE id=1;
UPDATE ch7_doctors
SET on_duty=false WHERE id=2;
COMMIT;
COMMIT;
-- 结果：0 名医生值班 ❌（业务规则被破坏）每个事务独立看都通过了「至少 1 名」的检查，但合起来结果违反业务规则。
这种现象在传统的「3 大现象」（脏读 / 不可重复读 / 幻读）里不会出现，但 PG 把它单独列出来。要消除它，必须用
SERIALIZABLE（PG 的 SSI 算法专治这种病）。
7.5 PostgreSQL 隔离级别详解
SQL 标准定义了 4 个隔离级别，每个允许 / 禁止上述 4 种现象。但 PG 实际只实现了 3 个：
┌─────────────────────┬──────────┬─────────────┬──────────┬──────────┐
│ 隔离级别 │ 脏读 │ 不可重复读 │ 幻读 │ 序列化异常 │
├─────────────────────┼──────────┼─────────────┼──────────┼──────────┤
│ READ UNCOMMITTED │ 标:可能 │ 可能 │ 可能 │ 可能 │
│ ★ PG 实际：等价 RC │ PG:不可能│ 可能 │ 可能 │ 可能 │
├─────────────────────┼──────────┼─────────────┼──────────┼──────────┤
│ READ COMMITTED ★默认│ 不可能 │ 可能 │ 可能 │ 可能 │
├─────────────────────┼──────────┼─────────────┼──────────┼──────────┤
│ REPEATABLE READ │ 不可能 │ 不可能 │ 不可能★ │ 可能 │
│ PG 实际是 SI │ │ │ (PG 严格)│ │
├─────────────────────┼──────────┼─────────────┼──────────┼──────────┤
│ SERIALIZABLE (SSI) │ 不可能 │ 不可能 │ 不可能 │ 不可能 │
└─────────────────────┴──────────┴─────────────┴──────────┴──────────┘7.5.1 READ UNCOMMITTED：PG 不真正实现
- SQL 标准：允许读到别人没提交的数据。
- PG 实现：直接当作
READ COMMITTED用，因为 PG 的 MVCC 机制根本看不到未提交事务的元组。PG 不存在脏读。
sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;
-- transaction_isolation
-- -----------------------
-- read committed ← 看到了吗？被 PG 偷偷升级了7.5.2 READ COMMITTED：PG 默认级别
每条 SELECT 语句 在执行的瞬间获取一个新的 snapshot（快照），只能看到「在这一刻已经提交的数据」。
text
事务开始
│
│── SELECT 1 ──> snapshot1 (能看到所有已 COMMIT 的事务)
│── UPDATE ──> 等待行锁；如果别人提交了，重新读最新版本
│── SELECT 2 ──> snapshot2 (可能比 snapshot1 多 / 少东西)
│
COMMIT特征：
- 不会读到脏数据（PG 永远不会）；
- 同一事务内两次 SELECT 可以看到不同结果（不可重复读、幻读都可能发生）；
UPDATE / DELETE时遇到别人正在改的行 → 等对方COMMIT后用最新版本重做（PG 的「重新评估锁定行」机制，与 MySQL 的「快照读 + 当前读」完全不同）。
📌 与 MySQL 的区别：MySQL 默认
REPEATABLE READ；PG 默认READ COMMITTED。PG 选 RC 是为了减少行锁等待和避免长事务阻塞 VACUUM（详见第 8 章）。
7.5.3 REPEATABLE READ：实际是 Snapshot Isolation
事务的第一条 SQL 执行时获取一个 snapshot，整个事务始终用这个 snapshot 读。
text
事务开始
│── SELECT 1 ──> snapshot @ 此时此刻
│── UPDATE ──> 用同一个 snapshot 读，但写时锁行
│── SELECT 2 ──> 还用这个 snapshot，结果与 SELECT 1 一致
│
COMMIT特征：
- 不可重复读 / 幻读 完全消失；
- 但仍可能发生序列化异常（写偏序）；
- 写冲突时 PG 报错：
ERROR: could not serialize access due to concurrent update，应用必须捕获并重试整个事务。
PG 的 RR 比 SQL 标准更严：标准只要求消除「同一行的不可重复读」，幻读还是可能；但 PG 的 SI（Snapshot Isolation）天生就把所有 INSERT/DELETE 也快照化，所以幻读也不可能。
7.5.4 SERIALIZABLE：基于 SSI 的真·串行化
SSI（Serializable Snapshot Isolation，PG 9.1+） 是 PG 的拳头特性。它的精髓是：
乐观地像 RR 一样跑，每个事务读快照、写自己的版本；但 PG 在背后默默追踪每个事务读了哪些数据、写了哪些数据。提交时检查：是否存在「读 → 写」的循环依赖？如果有，则有可能产生序列化异常，主动回滚其中一个事务。
直观理解：
T1: 读 A → 写 B
T2: 读 B → 写 A
构造依赖图：
T1 ──读后写──> T2 T1 读了 A，T2 写了 A，T1 必须看起来在 T2 前
T2 ──读后写──> T1 T2 读了 B，T1 写了 B，T2 必须看起来在 T1 前
⇒ 出现循环！无法找到等价串行顺序 ⇒ 抛 serialization_failure，回滚一个优点：
- 无锁！比传统的「严格两阶段封锁（S2PL）」快很多；
- 程序员只需把所有事务都设成
SERIALIZABLE，业务逻辑像「单线程」一样写； - 与 MySQL 的「Repeatable Read + Gap Lock」相比，没有间隙锁带来的死锁、阻塞。
代价：
- 出错率上升：高并发下可能频繁
serialization_failure； - 应用必须实现重试逻辑（见 7.6 节代码）；
- PG 在内存中维护「谓词锁（predicate lock）」追踪读集合，对内存有额外开销。
7.5.5 双窗口实战：四种现象眼见为实
实验 1：在 RC 下复现「不可重复读」
text
-- 窗口 A
learn_pg=# BEGIN;
learn_pg=*# SHOW transaction_isolation;
transaction_isolation
-----------------------
read committed
learn_pg=*# SELECT price FROM ch7_products WHERE id = 1;
price
-------
7999.00
-- 窗口 B
learn_pg=# BEGIN;
learn_pg=*# UPDATE ch7_products SET price = 8999
WHERE id = 1;
learn_pg=*# COMMIT;
learn_pg=*# SELECT price FROM ch7_products WHERE id = 1;
price
-------
8999.00 ← 同一个事务两次读不一致 ❌
learn_pg=*# COMMIT;实验 2：升级到 RR，不可重复读消失
text
-- 窗口 A
learn_pg=# BEGIN ISOLATION LEVEL REPEATABLE READ;
learn_pg=*# SELECT price FROM ch7_products WHERE id = 1;
price
-------
8999.00
-- 窗口 B
learn_pg=# BEGIN;
learn_pg=*# UPDATE ch7_products SET price = 9999
WHERE id = 1;
learn_pg=*# COMMIT;
learn_pg=*# SELECT price FROM ch7_products WHERE id = 1;
price
-------
8999.00 ← 还是看到老快照 ✓
learn_pg=*# COMMIT;实验 3：RR 下的写偏序仍存在，需要 SERIALIZABLE 才能解决
text
-- 窗口 A：医生 1 想请假
learn_pg=# BEGIN ISOLATION LEVEL SERIALIZABLE;
learn_pg=*# SELECT count(*) FROM ch7_doctors WHERE on_duty;
count
-------
2
learn_pg=*# UPDATE ch7_doctors SET on_duty = false WHERE id = 1;
UPDATE 1
-- 窗口 B：医生 2 也想请假
learn_pg=# BEGIN ISOLATION LEVEL SERIALIZABLE;
learn_pg=*# SELECT count(*) FROM ch7_doctors
WHERE on_duty;
count
-------
2
learn_pg=*# UPDATE ch7_doctors
SET on_duty = false WHERE id = 2;
UPDATE 1
learn_pg=*# COMMIT;
COMMIT
learn_pg=*# COMMIT;
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.✅ PG 的 SSI 自动检测到了这个写偏序并回滚了 B，保护了业务规则。
7.6 事务 ID 与可见性（为第 8 章 MVCC 铺垫）
每开启一个修改数据的事务，PG 都会分配一个单调递增的 32 位整数 xid（transaction ID）。
text
learn_pg=# BEGIN;
learn_pg=*# SELECT txid_current();
txid_current
--------------
12345
learn_pg=*# UPDATE ch7_accounts SET balance = balance + 1 WHERE id = 1;
learn_pg=*# COMMIT;
learn_pg=# BEGIN;
learn_pg=*# SELECT txid_current();
txid_current
--------------
12346 ← 递增💡 小技巧：只读事务调用
txid_current_if_assigned()不会消耗 xid（PG 优化，避免快只读时浪费 xid 加速回卷）。
每行数据隐含两个字段：
xmin：写入这行的事务 IDxmax：删除 / 更新这行的事务 ID（0 表示这行还活着）
可见性的核心规则（简化版）：
对于一行 tuple，当前事务 my_xid 和 snapshot S：
if tuple.xmin 已 ABORT: 不可见（写入它的事务回滚了）
if tuple.xmin > S.xmax: 不可见（写入者比快照新）
if tuple.xmin in S.xip_list: 不可见（写入者还没提交）
if tuple.xmax 不存在 or 已 ABORT: 可见
if tuple.xmax > S.xmax: 可见（删除者比快照新）
if tuple.xmax in S.xip_list: 可见（删除者还没提交）
否则 不可见（这行已被一个看得见的事务删除）第 8 章会用专门一整章把这套规则讲透。这里你只要知道：事务的 ID 决定它能看见什么。
7.7 与 MySQL 全方位对比
| 维度 | MySQL InnoDB | PostgreSQL |
|---|---|---|
| 默认隔离级别 | REPEATABLE READ | READ COMMITTED |
| READ UNCOMMITTED | 真正实现，能脏读 | 等价于 RC，永远不脏读 |
| RR 实现 | MVCC + Next-Key Lock（行锁 + 间隙锁） | Snapshot Isolation（无锁） |
| RR 是否有幻读 | 当前读（SELECT FOR UPDATE）有幻读 | 完全无幻读 |
| SERIALIZABLE 实现 | 自动给所有 SELECT 加共享锁（性能很差） | SSI（无锁，乐观检测，性能可观） |
| 事务出错后行为 | 出错语句被忽略，事务可继续 | 整个事务被废，必须 ROLLBACK |
| DDL 是否可回滚 | 大多数 DDL 不可 回滚（隐式提交） | 几乎全部 DDL 可回滚（神技） |
| 多版本数据存放位置 | Undo Log（独立段） | 堆表中（详见第 8 章） |
| 写写冲突 | 行锁阻塞 | RC：等待 + 重读；RR / Serializable：直接报错 |
| 保存点 | 支持 | 支持，且 psql 有 ON_ERROR_ROLLBACK 增强 |
| 两阶段提交 | XA 支持 | PREPARE TRANSACTION 原生支持 |
7.8 小结
- ACID 是事务的四大誓言：原子（要么全做要么不做）、一致（满足约束）、隔离（并发互不干扰）、持久（COMMIT 后地震都不丢）。
- PG 默认 READ COMMITTED，与 MySQL 默认 REPEATABLE READ 不同。
- PG 不真正实现 READ UNCOMMITTED（永远不脏读），其他三个级别按 SQL 标准更严格。
- PG 的 RR = Snapshot Isolation，连幻读都消除了，但仍可能产生写偏序（序列化异常）。
- SSI 是 PG 9.1+ 的杀手锏，乐观地检测读 / 写循环依赖，自动回滚冲突事务，应用必须实现重试。
- PG 事务出错后不能继续，必须 ROLLBACK 或用 SAVEPOINT 包住可能出错的语句。
- DDL 可回滚是 PG 相对于 MySQL 的巨大优势：上线脚本一旦出错可以无痛回滚。
- 事务 ID（xid）是单调递增的 32 位整数，它决定每个事务能看见什么版本的数据——这就是下一章 MVCC 的钥匙。
🎮 配套演示
用浏览器打开
./07_transaction/demo.html，跟着可视化动画再走一遍本章核心概念。配套代码在
./07_transaction/code/，每个脚本都可以独立python xxx.py运行，先跑init.sql准备数据。
7.9 面试高频题
Q1：PostgreSQL 的默认隔离级别是什么？为什么不和 MySQL 一样选 RR？
考察点：对 PG 设计哲学的理解、MVCC 与隔离级别的关系。
答案要点：
PG 默认 READ COMMITTED，MySQL InnoDB 默认 REPEATABLE READ，两者设计哲学差异显著：
历史与生态：MySQL 默认 RR 是因为早期为了兼容 MyISAM 表锁的语义、配合基于语句的二进制日志（statement-based binlog）正确复制——RR + Next-Key Lock 才能保证主从行为一致。PG 没有这个历史包袱。
MVCC 实现差异：PG 把多版本数据放在堆表中，长事务持有的旧 snapshot 会阻止 VACUUM 回收死元组，导致表膨胀。RC 模式下每条 SELECT 都拿新 snapshot，「老 snapshot 卡住 VACUUM」的概率小得多；RR 则会让一个长事务持续持有同一个 snapshot 直到结束，对 VACUUM 极不友好。
性能与吞吐：RC 下写写冲突遇到锁会等到对方提交后用最新版本重读，事务能继续跑；RR 模式下写冲突直接报
could not serialize access，应用必须重试。RC 的吞吐通常更高。正确性：RC 已经禁止脏读，对绝大多数 OLTP 业务足够。需要更严格语义的场景（如对账、库存扣减）应显式使用
SERIALIZABLE。
加分项：解释为什么 PG 的 SI 比 SQL 标准的 RR 更严（连幻读都消除了）；提到 RC 模式下 PG 的「行锁定后重读」（EvalPlanQual）机制。
Q2：什么是「写偏序（Write Skew）」？为什么 RR 解决不了？SSI 怎么解决？
考察点：对序列化异常本质的理解、SSI 算法原理。
答案要点：
写偏序：两个并发事务各自读取了一组数据，各自基于读到的数据做出修改，单看每个事务都符合业务规则，但合起来破坏了不变量。
经典案例（医生值班）：规则「至少 1 个医生在班」，初始 Wang.on_duty=true, Li.on_duty=true。两事务并发：
- T1：
SELECT count(*) WHERE on_duty=true → 2，决策「我可以请假」，UPDATE Wang.on_duty=false - T2：
SELECT count(*) WHERE on_duty=true → 2，决策「我也可以请假」，UPDATE Li.on_duty=false - 都 COMMIT 后，0 个医生在班，业务规则被破坏。
为什么 RR 解决不了：RR / SI 只保证「每个事务在自己的快照里看到的数据一致」，但不保证两个事务的写互不影响。两人各自读的快照都是一致的，写的也是不同的行，没有冲突点，所以 SI 检测不到。
SSI 怎么解决：PG 的 Serializable Snapshot Isolation 在 SI 之上，额外追踪每个事务的读集合（read set）和写集合（write set），构造事务间的「依赖图」：
- 如果 T1 读了某个谓词覆盖的数据，而 T2 修改了这个谓词覆盖的数据，则 T1→T2 存在一条「rw 反依赖边」；
- PG 检测到「连续两条 rw 反依赖边」（即所谓的 dangerous structure：T1 → T2 → T3）就把 T2 标记为「pivot」并回滚，避免环出现。
加分项：
- 提到 SSI 的关键论文 Serializable Snapshot Isolation in PostgreSQL (VLDB 2012)；
- 解释「谓词锁（predicate lock）」的实现：PG 用 SIRead lock 跟踪事务读取过的页 / 元组 / 索引项；
- 提示应用必须实现重试逻辑（捕获
SQLSTATE 40001 serialization_failure）； - DEFERRABLE READ ONLY 永远不会被 SSI 回滚的特性。
Q3：PostgreSQL 事务出错后为什么不能继续执行？怎么处理？
考察点：PG 事务实现的特殊性、SAVEPOINT 用法。
答案要点：
PG 一旦事务中任何 SQL 报错，整个事务被立即标记为 aborted state，后续所有 SQL 都会返回：
ERROR: current transaction is aborted, commands ignored until end of transaction block只有 ROLLBACK、COMMIT（实际等于 ROLLBACK）、ROLLBACK TO SAVEPOINT 三种命令能被接受。
设计原因：
- 简化错误处理：避免业务方误以为部分 SQL 成功 / 失败；
- 保证一致性：错误可能让事务内部的数据假设失效，继续执行可能产生隐藏 bug；
- MVCC 实现简单：失败的子操作不需要单独维护「还能继续做哪些事」的状态。
与 MySQL 对比：MySQL 默认行为是「只回滚出错的语句，事务继续」，看似友好但容易隐藏 bug（业务逻辑以为 INSERT 成功了，其实失败了）。
实战处理三方案：
整体捕获并回滚重试：
pythontry: conn.execute("BEGIN") conn.execute(stmt1) conn.execute(stmt2) conn.execute("COMMIT") except Exception: conn.execute("ROLLBACK") # 重试或告警用 SAVEPOINT 局部回滚：
sqlBEGIN; INSERT INTO t1 VALUES (...); SAVEPOINT sp; INSERT INTO t2 VALUES (...); -- 可能失败 -- 失败后：ROLLBACK TO SAVEPOINT sp; 然后继续做别的 COMMIT;psql 交互：
\set ON_ERROR_ROLLBACK interactive让 psql 自动在每条语句前打 SAVEPOINT，调试更舒服。
加分项：解释 SAVEPOINT 在 PG 内部对应「子事务」、子事务有独立 xid，子事务过多会增加 pg_subtrans 压力；提到 subtransaction overflow 64 上限的性能影响。
Q4：PG 的 REPEATABLE READ 与 SQL 标准的区别？为什么 PG 没有幻读？
考察点：MVCC 实现、SI 与传统 RR 的差异。
答案要点：
SQL 标准对 RR 的定义只要求「禁止不可重复读」（同一行的两次读结果一致），幻读仍然可能发生（同一范围两次查询行数不同）。
PG 的 RR 实际是 Snapshot Isolation（SI）：
- 事务开始时（更准确说是事务第一条 SQL 执行时）拿一个全局快照；
- 整个事务始终用这一个快照做所有读操作；
- 每行数据在写入时附带 xmin / xmax，可见性完全由快照判断；
- INSERT 出来的新元组其 xmin 大于快照的 xmax，自然不可见 → 没有幻读；
- DELETE 删的元组其 xmax 大于快照的 xmax，还能看到 → 也没有「消失」。
因此 PG 的 RR 比 SQL 标准更严，幻读、不可重复读都消失。
唯一仍存在的现象：写偏序（序列化异常），因为 SI 不追踪事务间的「rw 依赖」。
与 MySQL InnoDB 对比：
- InnoDB 的 RR 也用 MVCC 解决了快照读（
SELECT）的幻读； - 但 InnoDB 的当前读（
SELECT ... FOR UPDATE）依赖 Next-Key Lock 才能避免幻读； - 如果一个事务先快照读、再当前读，两次结果可能不一致，行为复杂。
加分项：
- 写写冲突时 PG 的行为：RC 是「等 + 重读」，RR 是「直接报 serialization_failure」；
- SI 在 OLTP 高并发下吞吐通常优于 InnoDB 的 RR（无间隙锁）；
- SI 的弱点：写偏序，需 SSI 解决。
Q5：什么场景下应该使用 SERIALIZABLE？怎么处理 serialization_failure？
考察点：对 SSI 的实战理解、应用层重试模式。
答案要点：
适用场景：
- 强一致性要求：库存扣减、座位预订、唯一码生成（要求逻辑上的「先到先得」精确）；
- 业务规则跨多行：如「每个用户最多 3 个订单」「同一时刻至少 1 个值班医生」（多行约束，CHECK 无法表达）；
- 跨表不变量：「订单总额必须等于明细之和」、「账户总余额恒定」；
- 想让程序员像写单线程一样写业务，不用思考各种锁与异常情况。
不适合：
- 高并发短事务热点（如商品详情页的浏览计数器）：会被频繁回滚；
- 长读事务：建议改用
SERIALIZABLE READ ONLY DEFERRABLE，永不被回滚。
应用层处理（必须！否则业务会偶发失败）：
python
import psycopg
from psycopg.errors import SerializationFailure
import time, random
def run_with_retry(sql_func, max_retries=5):
for attempt in range(max_retries):
try:
with conn.transaction(isolation_level=psycopg.IsolationLevel.SERIALIZABLE):
return sql_func()
except SerializationFailure:
backoff = (2 ** attempt) * 0.05 + random.uniform(0, 0.05)
time.sleep(backoff) # 指数退避
continue
raise RuntimeError("Too many serialization failures")关键点：
- 必须捕获 SQLSTATE 40001（
SerializationFailure）； - 指数退避 + 抖动避免雷鸣；
- 重试要把整个事务重做，不能只重做出错的那一句（快照已变）；
- 业务幂等性要小心（外部 API 调用、消息发送应在事务外或确认后）。
加分项：
pg_stat_database.xact_commit / xact_rollback监控冲突率；- 高冲突场景可降级为「悲观锁（
SELECT FOR UPDATE）」或拆解事务； - 提到
default_transaction_isolation = serializable在某些金融行业常被设为库默认。
Q6：什么是两阶段提交（2PC）？PG 怎么实现？什么时候用？
考察点：分布式事务基础、PREPARE TRANSACTION 用法。
答案要点：
两阶段提交（2PC） 解决「跨多个数据库 / 服务的分布式事务」如何原子地一起提交或一起回滚。
两个阶段：
- Prepare 阶段：协调者向每个参与者发
PREPARE TRANSACTION 'xid'；参与者把事务持久化到磁盘但不提交，回应vote-yes / vote-no； - Commit 阶段：协调者根据所有投票决定
COMMIT PREPARED 'xid'或ROLLBACK PREPARED 'xid'，发给所有参与者执行。
PG 实现：
sql
-- 必须先在 postgresql.conf 设置 max_prepared_transactions > 0 并重启
BEGIN;
UPDATE ch7_accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'tx_2024_001'; -- 第 1 阶段
-- 此时连接可以关闭，事务持久化在 pg_twophase 目录
-- 协调者收集所有参与者投票后：
COMMIT PREPARED 'tx_2024_001'; -- 第 2 阶段
-- 或：
ROLLBACK PREPARED 'tx_2024_001';可通过 pg_prepared_xacts 系统视图查看悬挂的 2PC 事务。
适用场景：
- 跨多个 PG 实例的强一致写（如分库分表的事务）；
- PG + 其他数据源（MySQL、消息队列）的 XA 事务；
- 需要 ACID 的微服务跨服务事务（搭配 Atomikos / Narayana 等 TM）。
注意事项与陷阱：
- 不要手工玩：必须有协调器（TM）保证 Prepare 后一定有 Commit / Rollback；
- 悬挂事务的危害：一个 PREPARED 但永不提交的事务持有所有锁、阻止 VACUUM，会让数据库逐渐崩溃。必须监控
pg_prepared_xacts.prepared时间； - 协调者单点故障：协调者挂了就要靠日志恢复，2PC 的「窗口期阻塞」是固有缺陷；
- 现代替代方案：业务上更常用 Saga 模式（每步可补偿）、TCC（Try-Confirm-Cancel）、消息最终一致。除非真的必要，2PC 在工程上很少用。
加分项：解释 Prepare 后参与者持有的资源（行锁、xid、内存）；推荐 Patroni 集群中 max_prepared_transactions 不要设太大（消耗 SubXID slot）；提到 PG 不会自动清理孤儿 2PC 事务，运维需写脚本巡检。
🐘 本章一句话总结：PostgreSQL 的事务系统极其严谨——默认 RC 平衡性能与正确性，RR 用 SI 干掉幻读，SERIALIZABLE 用 SSI 干掉写偏序，DDL 可回滚是工程师之福。但记住，事务出错就 ROLLBACK，DDL 也要包在事务里，让 PG 替你兜底。
🔗 延伸阅读
- 第 8 章 MVCC 与 VACUUM：RR / SERIALIZABLE 的快照机制就靠 MVCC 实现，本章的 xmin/xmax 在那里被讲透。
- 第 11 章 锁机制：行锁、表锁、谓词锁与本章隔离级别协同决定并发行为；
SELECT FOR UPDATE与 SSI 的取舍。 - 第 17 章 性能调优：长事务、写偏序回滚率高时如何排查与优化。
🎬 可视化演示
演示加载缓慢或样式异常？点此在新标签页打开 ↗
💻 示例代码
python
"""
01_acid_demo.py —— ACID 演示：转账场景
依赖：
pip install "psycopg[binary]>=3.1"
用法：
1. 先执行 init.sql 初始化（表名带 ch7_ 前缀）
psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql
2. python 01_acid_demo.py
要点：
- 演示原子性（A）：人为制造异常，整个事务回滚，账户余额不变
- 演示一致性（C）：CHECK 约束阻止余额变成负数
- 演示持久性（D）：COMMIT 后立刻查询，数据已落盘
"""
from __future__ import annotations
import psycopg
from decimal import Decimal
DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
def show_balances(conn: psycopg.Connection, tag: str) -> None:
print(f"\n--- {tag} ---")
with conn.cursor() as cur:
cur.execute("SELECT id, name, balance FROM ch7_accounts ORDER BY id")
for row in cur.fetchall():
print(f" id={row[0]} name={row[1]:<6} balance={row[2]}")
def transfer(conn: psycopg.Connection, src: int, dst: int,
amount: Decimal, fail: bool = False) -> bool:
"""以 atomic 块演示原子性。fail=True 时人为抛异常验证回滚。"""
try:
with conn.transaction(): # psycopg v3：BEGIN ... COMMIT/ROLLBACK
with conn.cursor() as cur:
cur.execute(
"UPDATE ch7_accounts SET balance = balance - %s, "
"updated_at = now() WHERE id = %s",
(amount, src),
)
if fail:
raise RuntimeError("人为抛出异常，验证原子性")
cur.execute(
"UPDATE ch7_accounts SET balance = balance + %s, "
"updated_at = now() WHERE id = %s",
(amount, dst),
)
cur.execute(
"INSERT INTO ch7_transfer_log (from_id, to_id, amount) "
"VALUES (%s, %s, %s)",
(src, dst, amount),
)
return True
except Exception as e:
print(f" 转账失败：{e}")
return False
def demo_consistency(conn: psycopg.Connection) -> None:
"""利用 CHECK (balance >= 0) 约束演示一致性。"""
print("\n=== Consistency 演示：尝试让 Alice 余额变负 ===")
ok = transfer(conn, src=1, dst=2, amount=Decimal("99999.00"))
print(f" 转账结果：{'成功' if ok else '被约束阻止 ✓'}")
def demo_atomicity(conn: psycopg.Connection) -> None:
"""中途抛异常验证「全有 / 全无」。"""
print("\n=== Atomicity 演示：转账中途抛异常 ===")
show_balances(conn, "事务前")
transfer(conn, src=1, dst=2, amount=Decimal("100.00"), fail=True)
show_balances(conn, "异常回滚后（应与事务前完全一致）")
def demo_durability(conn: psycopg.Connection) -> None:
"""COMMIT 后再查，数据已经持久化。"""
print("\n=== Durability 演示：成功转账并立刻查询 ===")
transfer(conn, src=1, dst=2, amount=Decimal("100.00"))
show_balances(conn, "成功 COMMIT 后")
def main() -> None:
with psycopg.connect(DSN) as conn:
# 重置数据，便于反复演示
with conn.cursor() as cur:
cur.execute("UPDATE ch7_accounts SET balance = 1000.00")
cur.execute("TRUNCATE ch7_transfer_log RESTART IDENTITY")
conn.commit()
show_balances(conn, "初始余额")
demo_atomicity(conn)
demo_consistency(conn)
demo_durability(conn)
print("\n=== 流水日志 ===")
with conn.cursor() as cur:
cur.execute(
"SELECT id, from_id, to_id, amount, created_at "
"FROM ch7_transfer_log ORDER BY id"
)
for row in cur.fetchall():
print(f" {row}")
if __name__ == "__main__":
main()python
"""
02_isolation_levels.py —— 不同隔离级别下的并发现象
依赖：
pip install "psycopg[binary]>=3.1"
用法：
1. 初始化：psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql
2. python 02_isolation_levels.py [rc | rr | sz]
- rc：READ COMMITTED 下复现「不可重复读」
- rr：REPEATABLE READ 下证明「不可重复读消失」
- sz：SERIALIZABLE 下复现「写偏序被回滚」
通过两个 psycopg 连接模拟两个并发会话 A、B。
"""
from __future__ import annotations
import sys
import time
import psycopg
from psycopg import IsolationLevel
from psycopg.errors import SerializationFailure
DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
ISO_MAP = {
"rc": IsolationLevel.READ_COMMITTED,
"rr": IsolationLevel.REPEATABLE_READ,
"sz": IsolationLevel.SERIALIZABLE,
}
def reset_data() -> None:
with psycopg.connect(DSN, autocommit=True) as conn:
with conn.cursor() as cur:
cur.execute("UPDATE ch7_products SET price=7999, stock=10 WHERE id=1")
cur.execute("UPDATE ch7_products SET price=4999, stock=20 WHERE id=2")
cur.execute("UPDATE ch7_doctors SET on_duty=TRUE")
def open_conn(level: IsolationLevel) -> psycopg.Connection:
conn = psycopg.connect(DSN, autocommit=False)
conn.isolation_level = level
return conn
def demo_non_repeatable_read(level: IsolationLevel) -> None:
"""同一事务两次 SELECT，看是否一致。"""
label = level.name
print(f"\n=== [{label}] 不可重复读测试 ===")
reset_data()
a = open_conn(level)
b = open_conn(level)
with a.cursor() as ca, b.cursor() as cb:
ca.execute("SELECT price FROM ch7_products WHERE id=1")
p1 = ca.fetchone()[0]
print(f" [A] 第 1 次 SELECT price = {p1}")
cb.execute("UPDATE ch7_products SET price = 8888 WHERE id=1")
b.commit()
print(f" [B] UPDATE price -> 8888 并 COMMIT")
ca.execute("SELECT price FROM ch7_products WHERE id=1")
p2 = ca.fetchone()[0]
print(f" [A] 第 2 次 SELECT price = {p2}")
if p1 == p2:
print(f" ✓ 两次读一致 -> {label} 防住了不可重复读")
else:
print(f" ✗ 两次读不一致 -> {label} 没能防住不可重复读")
a.commit()
a.close()
b.close()
def demo_write_skew(level: IsolationLevel) -> None:
"""医生值班表的写偏序场景。"""
label = level.name
print(f"\n=== [{label}] 写偏序（医生值班）测试 ===")
reset_data()
a = open_conn(level)
b = open_conn(level)
try:
with a.cursor() as ca, b.cursor() as cb:
ca.execute("SELECT count(*) FROM ch7_doctors WHERE on_duty")
n_a = ca.fetchone()[0]
cb.execute("SELECT count(*) FROM ch7_doctors WHERE on_duty")
n_b = cb.fetchone()[0]
print(f" [A] 在班医生 = {n_a}，决定让自己请假")
print(f" [B] 在班医生 = {n_b}，决定让自己请假")
ca.execute("UPDATE ch7_doctors SET on_duty=FALSE WHERE id=1")
cb.execute("UPDATE ch7_doctors SET on_duty=FALSE WHERE id=2")
try:
a.commit()
print(f" [A] COMMIT 成功")
except SerializationFailure as e:
print(f" [A] COMMIT 失败：{e}")
try:
b.commit()
print(f" [B] COMMIT 成功")
except SerializationFailure as e:
print(f" [B] COMMIT 失败（被 SSI 保护）：{e.diag.message_primary}")
finally:
a.close()
b.close()
with psycopg.connect(DSN) as conn:
cur = conn.execute("SELECT count(*) FROM ch7_doctors WHERE on_duty")
n = cur.fetchone()[0]
print(f" 最终在班医生 = {n}（业务规则：必须 ≥ 1）")
def main() -> None:
arg = sys.argv[1] if len(sys.argv) > 1 else "all"
if arg in ("rc", "all"):
demo_non_repeatable_read(IsolationLevel.READ_COMMITTED)
if arg in ("rr", "all"):
demo_non_repeatable_read(IsolationLevel.REPEATABLE_READ)
demo_write_skew(IsolationLevel.REPEATABLE_READ)
if arg in ("sz", "all"):
demo_write_skew(IsolationLevel.SERIALIZABLE)
if __name__ == "__main__":
main()python
"""
03_savepoint.py —— 保存点（SAVEPOINT）演示
依赖：
pip install "psycopg[binary]>=3.1"
要点：
- 在一个事务里依次插入多个购物车项
- 故意让其中一条违反 CHECK (qty > 0)
- 用 SAVEPOINT 局部回滚那条错的，事务整体仍可继续提交
注意：psycopg v3 的 conn.transaction() 支持嵌套，会自动产生 SAVEPOINT。
"""
from __future__ import annotations
import psycopg
DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
def show_cart(conn: psycopg.Connection, tag: str) -> None:
print(f"\n--- {tag} ---")
with conn.cursor() as cur:
cur.execute(
"SELECT id, user_id, product_id, qty FROM ch7_cart_items "
"ORDER BY id"
)
for row in cur.fetchall():
print(f" id={row[0]} user={row[1]} product={row[2]} qty={row[3]}")
if cur.rowcount == 0:
print(" (空)")
def main() -> None:
with psycopg.connect(DSN) as conn:
with conn.cursor() as cur:
cur.execute("TRUNCATE ch7_cart_items RESTART IDENTITY")
conn.commit()
with conn.transaction(): # 主事务
with conn.cursor() as cur:
cur.execute(
"INSERT INTO ch7_cart_items (user_id, product_id, qty) "
"VALUES (10, 1, 1)"
)
print("[OK] 加入 product=1 qty=1")
# 嵌套事务等价于 SAVEPOINT sp1; ... RELEASE sp1;
try:
with conn.transaction():
with conn.cursor() as cur:
cur.execute(
"INSERT INTO ch7_cart_items "
"(user_id, product_id, qty) VALUES (10, 2, -3)"
)
except psycopg.errors.CheckViolation as e:
# SAVEPOINT 自动 ROLLBACK TO，主事务还能继续
print(f"[FAIL] 嵌套事务被 CHECK 拦下：{e.diag.message_primary}")
print(" 已 ROLLBACK TO SAVEPOINT，主事务不受影响")
with conn.cursor() as cur:
cur.execute(
"INSERT INTO ch7_cart_items (user_id, product_id, qty) "
"VALUES (10, 2, 2)"
)
print("[OK] 重新加入 product=2 qty=2")
cur.execute(
"INSERT INTO ch7_cart_items (user_id, product_id, qty) "
"VALUES (10, 3, 1)"
)
print("[OK] 加入 product=3 qty=1")
show_cart(conn, "最终购物车（应有 product 1/2/3，没有失败那条）")
if __name__ == "__main__":
main()python
"""
04_ssi_serialization_failure.py —— SSI 下捕获 serialization_failure 并重试
依赖：
pip install "psycopg[binary]>=3.1"
场景：
多个并发线程在 SERIALIZABLE 隔离级别下让两位医生轮流请假，
必然出现 SSI 检测到的写偏序冲突（SQLSTATE 40001）。
本脚本演示「指数退避 + 整事务重试」的标准做法。
"""
from __future__ import annotations
import random
import threading
import time
from dataclasses import dataclass
import psycopg
from psycopg import IsolationLevel
from psycopg.errors import SerializationFailure
DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
MAX_RETRIES = 8
@dataclass
class Stats:
success: int = 0
failed: int = 0
retries: int = 0
def reset_doctors() -> None:
with psycopg.connect(DSN, autocommit=True) as conn:
conn.execute("UPDATE ch7_doctors SET on_duty = TRUE")
def take_leave(doctor_id: int, stats: Stats) -> None:
"""让 doctor_id 请假；若违反业务规则则不操作；遇到序列化失败则重试。"""
for attempt in range(MAX_RETRIES):
try:
with psycopg.connect(DSN) as conn:
conn.isolation_level = IsolationLevel.SERIALIZABLE
with conn.transaction():
with conn.cursor() as cur:
cur.execute(
"SELECT count(*) FROM ch7_doctors WHERE on_duty"
)
n = cur.fetchone()[0]
if n <= 1:
return
cur.execute(
"UPDATE ch7_doctors SET on_duty = FALSE "
"WHERE id = %s AND on_duty = TRUE",
(doctor_id,),
)
stats.success += 1
return
except SerializationFailure:
stats.retries += 1
backoff = (2 ** attempt) * 0.005 + random.uniform(0, 0.005)
time.sleep(backoff)
except Exception as e:
print(f" 其他异常：{e!r}")
stats.failed += 1
return
stats.failed += 1
def main() -> None:
rounds = 50
print(f"=== SSI 重试演示：进行 {rounds} 轮并发请假 ===")
overall = Stats()
for r in range(rounds):
reset_doctors()
stats = Stats()
t1 = threading.Thread(target=take_leave, args=(1, stats))
t2 = threading.Thread(target=take_leave, args=(2, stats))
t1.start(); t2.start()
t1.join(); t2.join()
with psycopg.connect(DSN) as conn:
n = conn.execute(
"SELECT count(*) FROM ch7_doctors WHERE on_duty"
).fetchone()[0]
assert n >= 1, "业务规则被破坏！SSI 应该阻止这种情况"
overall.success += stats.success
overall.failed += stats.failed
overall.retries += stats.retries
print(
f"\n汇总：成功 = {overall.success}，"
f"放弃 = {overall.failed}，"
f"重试次数 = {overall.retries}"
)
print("最终所有轮次中，在班医生数始终 ≥ 1，业务规则被 SSI 完美保护 ✓")
if __name__ == "__main__":
main()markdown
# 第 7 章 事务与隔离级别 - 可运行代码
## 准备
```bash
# 1. 安装 psycopg v3
pip install "psycopg[binary]>=3.1"
# 2. 初始化测试数据
psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql脚本说明
| 脚本 | 主题 | 知识点 |
|---|---|---|
01_acid_demo.py | ACID 演示 | 转账场景验证 A / C / D（隔离性见后文） |
02_isolation_levels.py | 隔离级别现象 | 在 RC / RR / SERIALIZABLE 下复现典型并发问题 |
03_savepoint.py | 保存点 | psycopg v3 的嵌套事务 = SAVEPOINT |
04_ssi_serialization_failure.py | SSI 自动重试 | 多线程并发触发 40001，指数退避重试 |
运行
bash
python 01_acid_demo.py
python 02_isolation_levels.py rc # 仅跑 READ COMMITTED 场景
python 02_isolation_levels.py rr # 仅跑 REPEATABLE READ 场景
python 02_isolation_levels.py sz # 仅跑 SERIALIZABLE 场景
python 02_isolation_levels.py # 全部
python 03_savepoint.py
python 04_ssi_serialization_failure.py01_acid_demo.py ↗ · 02_isolation_levels.py ↗ · 03_savepoint.py ↗ · 04_ssi_serialization_failure.py ↗ · README.md ↗