5 运筹帷幄：查询、事务与锁的核心心智模型

前四章已经把连接、工具、逻辑模型与物理数据合同固定下来。接下来遇到的三个问题看似分散：为什么同一条 SQL 会选择不同路径，为什么一个会话看不见另一个会话刚写的值，为什么一个“正在运行”的请求其实在等锁。它们必须放在同一张图中理解：SQL 被转换为执行计划，执行节点在某个 MVCC 快照上访问 tuple version，写操作再通过事务状态、锁和 WAL 协调并发与恢复。

本章建立这张原理地图，但刻意不把后续专题挤进一章。这里只要求读者能把现象归入正确层次、找到第一组权威证据，并知道下一步去哪里；第 7 章深入计划和估算，第 8 章建立慢 SQL 诊断流程，第 9 章验证索引设计，第 10 章再系统实验隔离异常与并发控制。

本章目标

完成本章后，读者应当能够：

• 按 raw parsing、semantic analysis、rewrite、planning、execution 解释 SQL 的处理路径；
• 区分 SQL 的关系语义与 Seq Scan、Hash Join、Sort 等物理执行节点；
• 把 planner cost 理解为基于统计与成本参数的比较量，而不是运行时间预言；
• 区分逻辑行与 heap tuple version，知道 xmin、xmax、ctid 只适合诊断；
• 读懂 pg_snapshot 的 xmin:xmax:xip_list，但不手工仿造完整可见性算法；
• 说明普通读为何通常不等待行级写锁，以及这种并发性的存储与维护代价；
• 正确处理 autocommit、显式事务、failed transaction 与 savepoint；
• 区分数据库原子性、WAL 持久化条件、复制确认与外部副作用；
• 区分 table lock、row lock、regular lock manager、LWLock 与 wait event；
• 用 pg_stat_activity、pg_blocking_pids() 和 pg_locks 还原一条阻塞边；
• 准确描述 PostgreSQL 四个隔离级别名称对应的三个实现级别；
• 把 lost update 绑定到具体隔离级别和 SQL 写法，而不是背一句口号；
• 在 Pigsty 的 Activity、Session、Xacts、Persist 与 PGCAT Locks 面板中提出可验证的问题；
• 完成一次 rollback-only 多会话实验，并证明业务状态没有漂移。

开始之前

本章沿用 ch02 的私有 PGSERVICEFILE 和 pg36-admin service，要求 ch04-v1 已验收：

model_version=ch04-v1
order_count=2
relation_checksum=f8a7bfae59c6d16cd323abecfefe1014

实验基线为 PostgreSQL 18.6、Pigsty v4.5.0、Ubuntu 24.04；本章 SQL 和 shell 路径保持 PostgreSQL 14–18 可用。页面讲到 PostgreSQL 18 当前行为时，以 18 版官方文档为准；Pigsty 面板名以 v4.5 文档为准。不同大版本、内核分支或定制 dashboard 必须重新核对，不能仅凭截图类推。

下载资产：

• 实验合同与风险边界
• 会话、快照、tuple 与 WAL 只读观察
• failed transaction 与 savepoint 反例
• 回滚写入与 WAL 位置实验
• blocker SQL
• waiter SQL
• 多会话阻塞编排器
• 阻塞时间线 Mermaid 源文件
• 状态验收
• 综合任务入口

这些实验不创建 ch05 持久对象，也不提交业务写入，所以没有 reset 动作；这不等于“零代价”。回滚的 UPDATE 仍会取得锁、创建 tuple version、产生 WAL 和统计活动，blocking 动作还会取消一个精确识别的实验 backend。只能在已确认可演练的 L1/测试库运行。

一张贯通全章的图

flowchart LR
  A["SQL 文本<br/>参数与会话上下文"] --> B["解析与语义分析<br/>query tree"]
  B --> C["重写<br/>views / rules"]
  C --> D["规划<br/>paths + estimates + cost"]
  D --> E["执行计划树<br/>executor"]
  F["MVCC 快照<br/>XID + tuple versions"] --> E
  G["锁与等待<br/>冲突对象 + wait graph"] --> E
  E --> H["数据页与 WAL<br/>可见结果 / 持久化"]
  I["pg_stat_activity<br/>pg_blocking_pids / pg_locks"] -.观测.-> G
  J["Pigsty dashboards<br/>趋势与上下文"] -.观测.-> E
  J -.观测.-> H

这张图也给出诊断顺序。结果错误先问语义、快照与事务边界；查询慢先区分“在执行”还是“在等待”；计划异常先检查估算，不要看到 Seq Scan 就先建索引；提交延迟则需要区分本地 WAL、同步复制、锁与客户端网络。

本章目录

5.1 SQL 从文本到结果

• 5.1.1 解析、重写、规划与执行
• 5.1.2 关系代数直觉与执行节点
• 5.1.3 优化器为什么做估算而不是预言

先把“声明想要什么”与“服务器怎样得到它”分开，再理解统计估算为何是计划选择的输入而非未来运行时间。

5.2 MVCC 与可见性

• 5.2.1 元组版本、事务 ID 与快照
• 5.2.2 活跃、提交、中止与可见性判断
• 5.2.3 读不阻塞写的条件与代价

逻辑行通过多个物理版本演进；快照与事务状态决定当前语句看到哪个版本，VACUUM 则负责在安全之后回收代价。

5.3 事务边界与失败语义

• 5.3.1 自动提交、显式事务与中止状态
• 5.3.2 原子性、持久性与 WAL
• 5.3.3 保存点、重试边界与外部副作用

一个错误不仅是某条语句失败，还会改变整个事务的可用状态；数据库回滚也不会撤销已经发送的邮件、HTTP 请求或消息。

5.4 锁与等待

• 5.4.1 表锁、行锁与轻量级锁的职责
• 5.4.2 等待图、阻塞链与死锁检测
• 5.4.3 锁模式名称不等于业务影响

锁名、锁对象、冲突模式、持有时长和业务扇出共同决定影响；本章用一条真实的 transaction-ID wait 说明怎样从 waiter 回到 blocker。

5.5 隔离现象与后续路线

• 5.5.1 脏读、不可重复读、幻读与序列化异常
• 5.5.2 lost update 必须绑定具体隔离级别与写法
• 5.5.3 ch07–ch10 如何分别展开计划与并发

PostgreSQL 的 Read Uncommitted 等同 Read Committed，Repeatable Read 又比标准最低要求更强；隔离级别名称必须与具体 SQL 形状一起讨论。

5.6 实战：观察一笔订单事务

• 5.6.1 从 SQL 观察会话、快照、锁和 WAL 位置
• 5.6.2 从 Pigsty 观察连接、事务与等待指标
• 5.6.3 注入阻塞并解释“现象—证据—原理”

综合实验先建立前验，再观察快照和失败事务，最后用两个唯一命名会话制造阻塞、采集锁链、受控释放并执行后验。

章节产物

在 ch05 资产目录运行：

export PGSERVICEFILE="$PWD/pg_service.conf"
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch05/$(date -u +%Y%m%dT%H%M%SZ)"

./task.sh all

执行路径是：

manifest
  → verify-before
  → observe
  → expected errors + savepoint
  → WAL-producing rollback
  → blocker / reader / waiter / observer
  → verify-after

一次 PostgreSQL 18.6 实测中，关键证据为：

assigned_xid_before_write=<none>
transaction-errors: 22012 → 25P02
savepoint error:     23514 → ROLLBACK TO → valid write → ROLLBACK
wal_insert_advanced=t
reader_saw_previous_committed_version=true
waiter_wait_event_type=Lock
waiter_wait_event=transactionid
cancel_exact_blocker=t
state_restored=true
remaining_workers=0

XID、PID、LSN、ctid 和 WAL 字节数每次都会变化；它们是本次证据，不是 golden value。稳定验收是错误类别、阻塞关系和最终不变量：

status=ok
model_version=ch04-v1
lab_state=rollback-only
active_lab_workers=0
order_1002_fingerprint=2bfa6eac30b9a1cfa2d51e98c4e98332
relation_checksum=f8a7bfae59c6d16cd323abecfefe1014

章节验收

1. 能说明 syntax parse 与 catalog-backed semantic analysis 不是同一步；
2. 能从 SQL 语义、plan node 与运行时状态三个层次解释一次查询；
3. 不把 cost 当毫秒，不把一次 EXPLAIN ANALYZE 当未来预测；
4. 能说明 snapshot 的边界含义，不把 xip_list 当全部已提交事务清单；
5. 不把 xmin、xmax、XID、LSN 或 ctid 当长期业务标识；
6. 能复现普通读看到旧已提交版本、同一行写者等待的差异；
7. 遇到 25P02 会回滚或回到 savepoint，而不是继续发送业务 SQL；
8. 能解释 rollback 后数据未变但 WAL insert LSN 仍可能前进；
9. 能说清 synchronous_commit=off 改变的是最近提交的持久性保证，而不是原子性；
10. 知道 ROW EXCLUSIVE 是表锁名称，且普通 SELECT 只与 ACCESS EXCLUSIVE 表锁冲突；
11. 能用 pg_blocking_pids() 建立 blocker 边，再用 activity 与 locks 补上下文；
12. 能区分长等待与死锁环，知道死锁受害事务需整体重试；
13. 能准确填出 PostgreSQL 的隔离现象矩阵；
14. 能分别判断原子 UPDATE、应用 read-modify-write、version predicate 和 FOR UPDATE；
15. all 前后 checksum 一致，且没有残留 lab backend。

下一章 ch06《立木取信：开发规约与交付基线》 会把 ch01–ch05 已经验证的连接、命名、类型、错误、事务、超时和取证规则收敛为团队可执行的开发基线。

参考资料

• PostgreSQL 18：The Path of a Query
• PostgreSQL 18：Executor
• PostgreSQL 18：MVCC Introduction
• PostgreSQL 18：Transaction Isolation
• PostgreSQL 18：Explicit Locking
• PostgreSQL 18：Write-Ahead Logging
• PostgreSQL 18：System Information Functions
• PostgreSQL 18：Cumulative Statistics
• Pigsty v4.5：PostgreSQL dashboards

上一章：量体裁衣：数据类型、约束与可靠数据表达 · 返回上卷导读 · 下一章：立木取信：开发规约与交付基线 · 查看全书目录 · 查看索引中心

5.1 SQL 从文本到结果

SQL 是声明式语言：调用者描述需要的关系结果和允许的变更，PostgreSQL 决定怎样执行。这个抽象让应用不必把“先扫哪张表、用哪个索引”写死，却也带来一个常见误区——把 SQL 文本、逻辑语义、执行计划和某次运行表现混成同一件事。本节先把四层拆开。

5.1.1 解析、重写、规划与执行

一条 SQL 从客户端到结果并不是“解析后立刻跑”。对普通查询，可以用下面的主干理解：

SQL text
  → raw parsing
  → parse analysis / transformation
  → rewrite
  → planning / optimization
  → execution

raw parsing 只知道语法形状

lexer 把关键字、标识符、常量和运算符切成 token，grammar 再建立 raw parse tree。这个阶段能判断括号、关键字位置和语法结构是否成立，却不查询系统目录，因此还不知道 shop.sales_order 是否存在、amount_minor 是什么类型，也无法判定 sum 最后解析为哪个具体函数。

接下来的 parse analysis / transformation 才在事务上下文中解析：

• schema、relation、column、function 与 operator；
• 未限定名称所受的 search_path 影响；
• literal、parameter 与 expression 的数据类型；
• aggregate、window function、target list 与权限所需的语义信息。

所以“解析”在口语中常被用作总称，但诊断时要更精确。少一个右括号通常是 42601 syntax_error；表名不存在是 semantic analysis 期间的 42P01 undefined_table；整数除零则可能直到 executor 求值时才产生 22012。错误出现在哪一层，决定应该检查文本、catalog/类型，还是运行数据。

rewrite 不是字符串替换

rewriter 接受和输出的都是 query tree。它最常见的用途是展开 view：查询 shop_api.order_summary 时，服务器不是把 view 当预先保存的一批行，而是把其定义纳入重写后的查询树。rules 也在这一层处理；row-level trigger 则不是 rewriter，它在执行期间按触发时点工作。

这一区分有两个工程后果：

1. view 后面仍要规划、执行和做 MVCC 可见性判断，普通 view 本身不是结果缓存；
2. EXPLAIN SELECT ... FROM view 展示的是重写之后形成的计划，不等于展示 raw parse tree 或每一步 rewrite 记录。

不要为了观察生产查询而打开 debug_print_parse、debug_print_rewritten 或 debug_print_plan 之类的全局调试输出；它们会改变日志量并可能暴露 SQL。教学上知道阶段即可，生产证据优先来自安全的 EXPLAIN、catalog、统计视图与受控日志。

planner 选择路径，executor 消费计划

planner/optimizer 接收 rewritten query tree，为扫描、连接、排序与聚合生成候选 path，用统计估算行数，再用成本模型比较候选。选中的 cheapest path 被展开成 plan tree。

executor 递归执行这棵树。PostgreSQL 的主体模型是 demand-pull：父节点需要下一行时向子节点索取，直到返回 tuple 或结束。对写语句，ModifyTable 等节点取得目标 tuple，再执行 insert/update/delete/merge、约束、trigger 与 WAL 相关工作。计划是可执行说明，不是运行结果；真正读取哪些 block、遇到哪些可见版本和等待，只有执行时才知道。

协议与计划生命周期也会改变上下文

应用通过 simple query protocol 发送整段 SQL，或通过 extended query protocol 执行 Parse/Bind/Execute。prepared statement 可以把参数绑定与 statement 定义分开，还可能在 custom plan 和 generic plan 之间选择。连接池又可能让同一逻辑请求落到不同 backend。

本章只固定一个原则：保存 SQL 文本还不够，至少还要关联 database、role、search_path、参数类型/值范围、配置、server version 与计划时点。第 7 章会专门验证 prepared statement 的计划选择，不在这里提前给“预编译一定更快”之类错误结论。

用当前订单查询做一个边界观察：

EXPLAIN (VERBOSE, COSTS OFF)
SELECT order_no, order_status, item_subtotal_minor
FROM shop_api.order_summary
WHERE order_id = 1001;

它可以证明 planner 最终交给 executor 的树，也能看到 view 展开后的 base relation；它不能证明估算准确、实际耗时稳定、当前没有锁等待。要回答后面的问题，需要 EXPLAIN (ANALYZE, BUFFERS) 或运行时视图，而带 ANALYZE 会真实执行语句，绝不能对有副作用的 SQL 随意使用。

5.1.2 关系代数直觉与执行节点

理解 plan tree 最有效的方式不是背节点列表，而是先问 SQL 需要哪些关系操作，再问 PostgreSQL 用什么物理算法实现。

逻辑操作与物理节点不是一一对应。例如，一个 B-tree index path 可以同时提供筛选和顺序；HashAggregate 可能同时承担分组与去重；planner 也可能把 predicate 下推到更低节点。反过来，SQL 文本中的一个 join 可能因 view 展开而变成多层 join tree。

用树而不是“执行步骤清单”阅读计划

假设计划形状为：

Sort
  → HashAggregate
      → Hash Join
          → Seq Scan on sales_order_item
          → Hash
              → Seq Scan on sales_order

缩进表示父子关系，不表示“第一行先完整执行，第二行再完整执行”。父节点通常向子节点拉取 tuple；Seq Scan 可以边读边交付，Hash 必须先构建内表，Sort 通常要取得足够输入后才能输出有序行。很多节点可 pipeline，一些节点会阻塞或 materialize；“executor 是 pull model”不等于全计划只保留一行内存。

读计划时来回走两遍：

1. 自下而上：base relation 如何进入 join/aggregate，数据量怎样放大或缩小；
2. 自上而下：最终排序、LIMIT 和输出要求向子树施加了什么 property。

第 7 章会加入 estimated rows、actual rows、loops、buffers、memory、I/O timing 等证据。此处只要求先能指出“哪个节点实现哪个逻辑责任”。

SQL 文本顺序不保证物理顺序

inner join 在满足语义等价时可以重排；predicate 可以下推；subquery 可能被 pull up；CTE 是否 materialize 取决于语义、引用方式和显式关键字。不要把：

FROM a
JOIN b ...
JOIN c ...

理解成服务器必然先 a→b→c。也不要用随意设置 enable_seqscan=off 或 join_collapse_limit=1 作为长期“修计划”方案；这些开关最多用于诊断假设，会改变整个 search space。

另一个必须从关系语义继承的规则是：没有 ORDER BY 就没有结果顺序合同。某次 Seq Scan 看似按 heap 位置返回、某次 Index Scan 看似按 key 返回，都不是 API 可以依赖的排序。VACUUM、并行执行、plan change 或一次普通 UPDATE 都可能改变观测顺序。

节点名也不是性能判决

• 小表 Seq Scan 往往比 index traversal 更便宜；
• Nested Loop 在外表很小、内表有高选择性索引时很好；
• Hash Join 不是天然“吃内存的坏节点”，是否 spill 才需要证据；
• Sort 可能完全在内存，也可能写 temporary files；
• Limit 1 若没有可利用的顺序或选择性，下面仍可能扫描很多行。

节点只描述算法和责任。性能结论必须同时看输入规模、估算误差、loops、filter 丢弃量、buffer/I/O、等待与并发环境。

5.1.3 优化器为什么做估算而不是预言

planner 必须在执行前作选择，因此只能利用当时可得的信息估算候选 path。核心链条是：

table cardinality + column statistics + predicates
  → selectivity estimate
  → rows / width estimate at every node
  → CPU + page + parallel + startup/total cost
  → choose expected cheapest path

cost=0.42..8.44 是按配置成本单位计算的比较量，不是 0.42–8.44 毫秒。estimated rows 也不是承诺返回的行数，而是影响 join order、join algorithm、scan path、parallelism 和 memory assumptions 的关键输入。

统计是有意近似的

pg_class.reltuples、relpages 不随每行写入实时更新；pg_stats 的 most common values、histogram、null fraction 与 distinct estimate 来自 ANALYZE 样本。即使刚分析完，它们仍是近似。planner 还常对多个条件采用独立性假设；城市和邮编、订单状态和支付时间这类相关列可能让乘法选择率严重失真，需要有证据地引入 extended statistics。

常见估算偏差来源包括：

• bulk load 后尚未 ANALYZE，或分布最近发生突变；
• 极端 skew 被有限 MCV/histogram 粒度抹平；
• 多列相关性没有 dependencies / MCV extended statistics；
• expression、function 或 cast 让现有统计不对应实际谓词；
• prepared statement 的未知参数与 generic plan 无法代表特定值；
• 跨表相关、数据新鲜度和未来并发本来就不在单列统计中。

成本模型也不知道未来

cost 参数表达 planner 对 sequential page、random page、CPU tuple/operator、parallel setup 等工作的相对假设。它不知道查询真正运行时：

• 数据页会在 shared buffers、OS cache 还是存储设备；
• 同一磁盘是否正被 checkpoint、backup 或其他查询占用；
• 会不会等待 row lock、LWLock、buffer pin、WAL flush 或客户端；
• 当前主机是否 CPU throttling；
• 结果集会不会因刚提交的数据而改变。

所以 plan 可以“按已知信息做了正确选择”但运行仍慢；也可以因估算错误选错 path。前者应调查资源与等待，后者才进入统计、SQL 形状或索引修正。

用误差定位，不用节点偏好代替诊断

第 7 章会用：

estimate ratio = max(actual rows / estimated rows,
                     estimated rows / actual rows)

逐层寻找第一次显著偏离，并把它与统计和 predicate 对上。第 8 章则先判断 wall time 消耗在 CPU、I/O、lock、WAL、client 还是连接队列。现在只记住三条：

1. estimated cost 只在同一 planning context 下比较候选，不跨服务器当 benchmark；
2. EXPLAIN ANALYZE 是一次真实样本，不是未来流量的预言；
3. 修复顺序是语义正确 → 数据/统计正确 → 估算合理 → 成本假设校准，最后才考虑 hint-like 强制手段。

把 optimizer 当成使用不完整信息的工程决策器，比把它人格化为“聪明/愚蠢”更有用。计划异常通常意味着输入证据或成本假设与现实不匹配，而不是数据库在随机选择。

返回本章目录 · 下一节：MVCC 与可见性 · 查看全书目录 · 查看索引中心

5.2 MVCC 与可见性

应用说“订单 1002 这一行”，heap 中却可能先后存在多个 tuple version。PostgreSQL 不让读取者与写入者围绕同一份可变字节互相排斥，而是让语句用 snapshot 判断哪些版本对自己可见。这就是 MVCC 的核心；它换来的并发性并非免费，旧版本最终必须由 VACUUM 安全回收。

5.2.1 元组版本、事务 ID 与快照

对 heap table，UPDATE 的基本效果是建立新版本并让旧版本退出未来可见范围，不是原地覆盖所有字段。逻辑主键仍指向“同一订单”，但物理 tuple header 携带版本信息：

官方文档把 xmin 定义为“插入这个 row version 的事务”，并明确说每次更新会形成新的 row version。xmax 的语义更复杂：删除事务未提交、已经回滚，或行锁形成 MultiXact 时都可能非零。因此：

SELECT order_id, xmin, xmax, ctid
FROM shop.sales_order
WHERE order_id = 1002;

是很好的实验探针，却不是可靠的“这行是否存活”判断器。让 PostgreSQL 的 visibility machinery 返回普通 SELECT 结果，才是应用应使用的接口。

XID 是版本排序工具，不是永久编号

普通内部 XID 是 32 bit 循环空间，依靠 modulo 比较和 freezing 维持可见性。它会 wrap around，也可能因只读事务尚未写入而暂未分配。pg_current_xact_id_if_assigned() 正是为了在不强行分配 XID 的情况下观察当前事务。

本章一次只读观察得到：

assigned_xid_before_write=<none>
backend_snapshot=<none>|959|<none>|<none>

第一项是当前 backend 尚无 write XID；第二项依次是 backend_xid、backend_xmin、wait type、wait event。它不是“没有事务”：BEGIN ... READ ONLY 已经建立事务与 snapshot，只是尚不需要普通写 XID。

PostgreSQL 13 起提供的 xid8/pg_snapshot 函数给诊断和逻辑解码工具更适合的 64-bit 表达；它仍不是跨 cluster、跨恢复周期的订单 ID。业务标识继续使用 ch04 的 PK/业务键。

snapshot 是边界加活跃集合

pg_current_snapshot() 的文本形状为：

xmin:xmax:xip_list

例如 959:959: 表示：

• snapshot xmin=959：当时最老的活跃 top-level XID 边界；
• snapshot xmax=959：大于或等于这个上界的 XID 在快照时还不能视为已完成可见；
• xip_list 为空：在两个边界之间没有额外列出的活跃 top-level XID。

这不是“所有已提交事务清单”。对某个 tuple，系统还要结合插入/删除 XID 的提交状态、当前事务自身、command ID、MultiXact 与 hint/frozen 状态判断。snapshot 也只列 top-level XID，不直接列每个 subtransaction。

可下载的 observe.sql把同一 snapshot 保存后分别用：

pg_snapshot_xmin(snapshot)
pg_snapshot_xmax(snapshot)
pg_snapshot_xip(snapshot)

解析，避免在应用里用字符串切割重造规则。snapshot 导出/导入还有严格的事务与隔离级别条件，本章不把它扩展成分布式一致性方案。

5.2.2 活跃、提交、中止与可见性判断

tuple header 记录“谁创建/结束版本”，transaction status 记录这些事务最终是 in progress、committed 还是 aborted，snapshot 则记录观察时刻的并发边界。可以先用一张非实现代码的判断图建立直觉：

flowchart TD
  A["候选 tuple version"] --> B{"插入者是自己？"}
  B -- "是" --> C["结合 command ID 判断<br/>本事务内是否已经发生"]
  B -- "否" --> D{"插入 XID 已提交<br/>且在 snapshot 可见？"}
  D -- "否" --> X["不可见"]
  D -- "是" --> E{"该版本是否被<br/>已提交且可见的事务结束？"}
  E -- "是" --> X
  E -- "否" --> V["可见"]

真实实现还要处理 aborted XID、subtransaction、MultiXact、frozen tuple 和多种 infomask；这张图只用于解释责任分工，不能复制成应用端 visibility algorithm。

三种事务状态会留下不同结果

• in progress：普通并发读不能看到它尚未提交的 tuple version；
• committed：是否可见还取决于 snapshot 是否足够新；
• aborted：它写出的版本不成为正常可见数据，但空间和 WAL 工作不会凭空消失。

同一事务总能在适当 command boundary 后看到自己的写入，否则无法执行“插入后查询、再更新”。这由 command ID 与特殊的 self-visibility 规则配合完成，不意味着别的会话也可见。

在默认 Read Committed 中，每个 command 获取新 snapshot。因此事务 A 的两个普通 SELECT 之间若事务 B 提交，第二个查询可以看到新版本。在 Repeatable Read/Serializable 中，普通读取使用 transaction snapshot，不会因 B 后来提交而更新自己的视图。隔离级别改变的是 snapshot 生命周期和冲突处理，不是把 heap 换成另一套存储。

不要从 header 单字段猜提交状态

下面这些推论都不成立：

• xmin 小，所以一定 committed；
• xmax=0，所以永远没有锁；
• xmax<>0，所以该行已经删除；
• ctid 没变，所以没有并发更新；
• 当前 XID 比 tuple XID 大，所以可见。

例如行锁也可能写 xmax/MultiXact；aborted 删除会留下非零信息；VACUUM/freezing 与 hint bits 又会改变内部表示。需要调查某个 XID 时，可以在受控诊断中使用 pg_xact_status(xid8) 等官方函数并注明版本和保留窗口，但应用正确性不能依赖旧 XID 状态永远可查。

另一个边界是 sequence：nextval 的推进不随调用事务 rollback。这是为了并发与唯一分配效率，因此 identity/sequence 允许 gap。不要拿“订单行回滚了但 ID 跳号”反驳事务原子性；序列值本就有专门的非事务语义。

5.2.3 读不阻塞写的条件与代价

PostgreSQL 文档常用“reading never blocks writing and writing never blocks reading”概括 MVCC。工程上应把它展开为：在 primary 上，普通 heap SELECT 不与同一行的 row-level write lock 冲突；读取者可见旧的 committed version，写者创建新版本。

这不意味着任意读永远不会等：

• SELECT ... FOR UPDATE/SHARE 主动成为 row locker；
• 普通 SELECT 的 ACCESS SHARE table lock 会被 ACCESS EXCLUSIVE DDL/maintenance 阻塞；
• query 可能等待 I/O、LWLock、buffer pin、WAL/IPC、parallel worker 或客户端；
• standby 查询可能与 recovery cleanup 冲突；
• CPU、buffer cache 和存储带宽仍会形成资源争用。

因此“读不阻塞写”是 lock compatibility 的精确性质，不是无延迟承诺。

本章实验中的两个并发结果

blocker 对订单 1002 执行未提交 UPDATE 后停在 pg_sleep。此时：

blocker:
  backend_xid=962
  wait_event_type=Timeout
  wait_event=PgSleep

ordinary reader:
  statement_timeout=1s
  result=旧的已提交 request_fingerprint

second writer:
  state=active
  wait_event_type=Lock
  wait_event=transactionid
  pg_blocking_pids={blocker_pid}

普通 reader 没去读取 blocker 的脏版本，而是从版本链找到旧 committed tuple；second writer 不能同时决定同一逻辑行的下一版本，所以等待 blocker XID 完成。这正是“read concurrency 高、write conflict 仍需排序”的组合。

代价落在空间、WAL 与清理

更新旧版本不会立即消失，因为仍可能有旧 snapshot 需要它。结果包括：

• heap 中产生 obsolete/dead tuple，需要 VACUUM 标记空间可重用；
• indexes 可能增加新 entry，取决于 HOT 条件和 indexed columns；
• 写入与回滚都可能产生 WAL、dirty buffers 和统计计数；
• 长事务/长 snapshot 提高全局 xmin horizon，延迟 dead tuple 清理；
• autovacuum 既要回收空间，也要维护 visibility map 和防止 XID wraparound；
• index-only scan 是否免 heap fetch 还依赖 visibility map。

所以生产上“没有锁等待但表越来越大”并不矛盾。MVCC 把读写冲突转成版本管理工作； 第 28 章会专门治理 VACUUM、freeze 和 bloat。

用三个问题判断所谓 MVCC 问题

1. 当前会话在等什么？ 看 state、wait_event_type、wait_event，不要先猜锁。
2. 谁阻碍了清理 horizon？ 看 transaction age、backend_xmin、replication slot/standby feedback 等证据。
3. 版本制造速度和回收速度是否失衡？ 看 tuple change、dead tuple、autovacuum、WAL 与 relation size 趋势。

把所有性能问题笼统叫“MVCC 膨胀”不会产生动作。现象必须落到 version churn、oldest snapshot、vacuum progress、lock/wait 或 I/O 中的一项。

上一节：SQL 从文本到结果 · 返回本章目录 · 下一节：事务边界与失败语义 · 查看全书目录 · 查看索引中心

5.3 事务边界与失败语义

事务不是给一组 SQL 加上 BEGIN 和 COMMIT 的排版形式，而是数据库正确性的最小失败与可见单位。应用必须知道事务何时开始、一个 error 会把它变成什么状态、哪类失败可以重试，以及数据库边界之外的动作为什么不会自动回滚。

5.3.1 自动提交、显式事务与中止状态

PostgreSQL 中每条语句都在事务里。若客户端没有显式 BEGIN，服务器会为单条语句建立隐式 transaction 并在成功后提交；psql 把这种行为暴露为 AUTOCOMMIT=on。显式 transaction block 则把多条语句放进一个原子边界：

BEGIN;
UPDATE ...;
INSERT ...;
COMMIT;

“autocommit”常由客户端/driver 再包装一层。某些 driver 默认自动提交，某些 ORM 打开 request-scoped transaction，连接池还可能在归还连接时 rollback。排查边界时不能只看业务代码有没有 BEGIN，还要检查 driver 配置、middleware 和 server 端的 xact_start。

一条错误会让显式事务进入 failed state

本章脚本故意执行：

BEGIN;
SELECT 1 / 0;        -- 22012 division_by_zero
SELECT 'continue';   -- 25P02 in_failed_sql_transaction
ROLLBACK;

第一个 error 不是只撤销一个函数调用然后“照常继续”。当前 transaction block 被标记 aborted，后续普通 SQL 收到 25P02，直到：

• ROLLBACK 结束整个事务；或
• 之前建立过 savepoint，使用 ROLLBACK TO SAVEPOINT 回到可用 subtransaction 边界。

25P02 通常是二次错误。真正根因是同一连接更早的第一个 SQLSTATE；日志与 tracing 应保留 first error，而不是把最后大量 current transaction is aborted 当根因。

应用端正确骨架是：

checkout:
  acquire connection
  BEGIN
  try:
      perform all database work
      COMMIT
  catch:
      ROLLBACK
      classify original SQLSTATE
  finally:
      return a clean connection

若 connection 在 failed transaction 状态被放回 pool，下一位请求会接到 25P02；若连接停在 idle in transaction，它还可能长期持锁、持 snapshot、阻碍 VACUUM。连接池归还前的 rollback 是卫生线，不代替业务代码正确结束事务。

timeout 也属于失败语义

至少区分：

不要在 global 层随意把 statement_timeout 设成一个小值并宣称“解决慢 SQL”。它是预算控制，不会告诉你时间消耗在哪，也不会自动让被取消事务恢复可用。

5.3.2 原子性、持久性与 WAL

原子性回答“事务的数据库效果是全部可见或全部不可见”；持久性回答“服务器确认 commit 后，在承诺的故障模型下能否恢复”。MVCC、transaction status 和 WAL 各自承担不同责任，不能缩写成“PostgreSQL 会写日志所以 ACID”。

WAL 是 redo 前提，不是业务事件流

Write-Ahead Logging 的核心顺序是：描述数据页变更的 WAL 必须先达到要求的持久位置，相关 dirty data page 才能安全落盘。这样 crash recovery 可以从 checkpoint 之后重放 WAL，把未及时写回的数据页恢复到一致状态。

它带来几个边界：

• WAL 记录面向物理/内部恢复，不是稳定的订单事件 API；
• rollback 不是从 WAL 中“删除刚才几条记录”；
• heap/index 写入可以先产生 WAL，最终 transaction 却 aborted；
• LSN 是实例某条 timeline 上的位置，不是 transaction ID 或业务 offset；
• pg_wal_lsn_diff 观察的是两个全局位置之差，窗口中可能混入其他 backend 的 WAL。

wal-rollback.sql在一个事务中改写订单指纹，取得 write XID 后 rollback。一次实测为：

write_xid=961
wal_lsn_before=0/2E96560
wal_lsn_after=0/2E96630
wal_bytes_observed=208
wal_insert_advanced=t
state_restored=t

208 不是该 UPDATE 的可移植精确尺寸；full-page image、checkpoint、HOT、版本和并发都会改变差值。稳定结论只有：本窗口 WAL insert location 前进，而最终业务值恢复。这反证“LSN 前进 = 事务提交”。

commit acknowledgement 有配置前提

默认 synchronous_commit=on 时，普通本地提交等待 commit WAL flush 到 durable storage 后再向客户端成功；若配置了 synchronous standbys，具体等待还受 synchronous_commit 模式和 synchronous_standby_names 影响。

synchronous_commit=off 允许服务器在 WAL durable flush 之前返回成功。crash 窗口内最近事务可能丢失，但数据库通过已 flush WAL 恢复到一致状态；它改变 durability guarantee，不把已成功返回的事务“半提交”成损坏行。fsync=off 风险更大，可能导致 crash 后不可恢复的不一致/损坏，不能与 async commit 混为一谈。

因此一次关键业务提交至少要记录：

SHOW synchronous_commit;
SHOW fsync;
SHOW full_page_writes;

并把“本地 durable”“同步副本 durable/已应用”“归档可用于 PITR”分成三个承诺。第 19、20 章再把这些承诺落实到 Pigsty HA 和 backup topology。

commit 不等于调用方一定知道结果

客户端在发送 COMMIT 后断线，可能发生两种都合理的现实：

1. server 尚未 commit，事务因连接消失而 rollback；
2. server 已 commit，但成功响应没到客户端。

调用方只知道 outcome ambiguous，不能盲目重放非幂等订单。ch03/ch04 已为 order/payment 建立 request/idempotency key，就是为这种边界提供查询与重试依据。事务原子性保护数据库内部状态，不保证网络把最终答案可靠送达一次且仅一次。

5.3.3 保存点、重试边界与外部副作用

savepoint 把 transaction 切成 subtransaction 边界：

BEGIN;
SAVEPOINT risky_statement;

-- 可能触发一个允许恢复的数据库错误
UPDATE ...;

ROLLBACK TO SAVEPOINT risky_statement;
-- transaction 再次可用
...
COMMIT;

本章实验先用非法 currency_code='USD' 触发 23514，再 ROLLBACK TO，随后成功执行另一条 UPDATE，最后整体 rollback。它证明的是“局部数据库失败可在预设边界恢复”，不是鼓励捕获所有错误后继续提交。

只恢复预期、局部、已理解的失败

适合 savepoint 的例子是：一个明确可选的子操作、已知 constraint exception、恢复后主事务不变量仍成立。以下情况通常应 rollback 整体 transaction：

• 连接丢失、admin shutdown、crash recovery；
• deadlock victim (40P01)；
• serialization failure (40001)；
• statement/query cancel 后应用不清楚执行进度；
• 违反关键业务约束，后续操作依赖失败结果；
• 任意未知 SQLSTATE。

大量逐行 savepoint 还会制造 subtransaction 管理开销；bulk ingest 更适合 staging、set-based validation、ON CONFLICT 的明确策略或分批 transaction。

锁也遵循 savepoint 边界：在 savepoint 之后取得的 table/row lock，回滚到该 savepoint 时释放。savepoint 之前取得的锁仍持有到 outer transaction 结束。看到一次 ROLLBACK TO 不能假定“这个会话已经不持锁”。

重试必须覆盖整个正确性单元

40001 serialization_failure 和 40P01 deadlock_detected 的安全策略通常是：

rollback whole transaction
discard values read inside it
apply bounded backoff + jitter
start from BEGIN with fresh snapshot

只重放最后一条 UPDATE 会复用旧决策和旧读取，不再是同一个正确性证明。重试还必须有总 deadline、最大次数、metrics，并使用 idempotency key 处理 ambiguous commit。constraint violation、syntax error、权限错误通常是确定性失败，盲目 retry 只会制造负载。

数据库 rollback 不会撤销外部世界

下面的顺序有危险：

BEGIN
charge payment provider       ← 外部副作用已发生
INSERT payment row
COMMIT fails

数据库无法“回滚 HTTP”。反过来先 COMMIT 再发消息，也可能 commit 成功而进程在发送前崩溃。常见闭环是：

• 外部接口使用稳定 idempotency key；
• 数据库事务同时写业务事实与 outbox row；
• 独立 publisher 至少一次发送 outbox；
• consumer 用 inbox/dedup key 幂等处理；
• 对 ambiguous result 先查询权威状态，不直接重复副作用。

本书在 ch12 把这套合同接入后端服务，在 ch13 讨论哪些逻辑适合留在数据库。当前最重要的分界是：savepoint 和 transaction 只控制同一 PostgreSQL transaction 内的效果；文件、邮件、支付、Kafka 和另一个数据库都需要额外协议。

上一节：MVCC 与可见性 · 返回本章目录 · 下一节：锁与等待 · 查看全书目录 · 查看索引中心

5.4 锁与等待

“数据库被锁了”通常把至少四件事混在一起：对象上的 regular lock、heap tuple 中的 row lock、共享内存内部的 lightweight lock，以及当前 backend 的 wait event。可靠诊断先确定等待类型，再建立谁等待谁的边，最后才评估是否需要取消或终止。

5.4.1 表锁、行锁与轻量级锁的职责

PostgreSQL 用不同同步机制保护不同层次：

“heavyweight lock”常被用来指 regular lock manager 中会入 lock table、支持等待队列和 deadlock detection 的对象；它不意味着一定很慢或锁住大范围。LWLock 的“lightweight”也不意味着可以忽略：高并发下某个共享结构的 LWLock contention 完全可能成为主要延迟，只是解决方式不是 SELECT FOR UPDATE。

table lock 和 row lock 同时存在

一次：

UPDATE shop.sales_order
SET request_fingerprint = ...
WHERE order_id = 1002;

至少要保护：

• relation 上的 ROW EXCLUSIVE table-level lock，防止冲突 DDL；
• 目标 row version 的 row-level update lock；
• 当前 transaction ID 的状态与等待者；
• buffer/WAL 等内部结构的短期同步。

ROW EXCLUSIVE 名字中有 ROW，却是 table-level mode。row lock 的四种 SQL 语义则是：

FOR KEY SHARE
FOR SHARE
FOR NO KEY UPDATE
FOR UPDATE

强度与冲突矩阵不同。普通 UPDATE 若不改变可用于 foreign key 的 key columns，通常取得较弱的 FOR NO KEY UPDATE 语义；修改 key 或 DELETE 会更强。应用不应根据一个通用单词“exclusive”推断所有冲突。

为什么 pg_locks 里看不到 blocker 的“行锁”

PostgreSQL 不把所有已锁行维护成一张无限增长的 shared-memory 清单；row lock 信息写在 tuple header。发生同一行 update conflict 时，waiter 常先取得一个 tuple lock 以排队，然后等待 blocker 的 transaction ID 完成。

本章现场恰好展示：

blocker:
  transactionid | ExclusiveLock | granted=true | xid=962

waiter:
  tuple         | ExclusiveLock | granted=true  | sales_order page=0 tuple=4
  transactionid | ShareLock     | granted=false | xid=962

真正未获准的是 waiter 对 XID 962 的 ShareLock，所以 activity 的：

wait_event_type=Lock
wait_event=transactionid

与 locks 证据一致。若只搜索 locktype='tuple' AND granted=false，会错误得出“没有行锁等待”。这也是为什么权威 blocker 边优先使用 pg_blocking_pids(waiter_pid)。

5.4.2 等待图、阻塞链与死锁检测

把每个正在等锁的 backend 画成节点，waiter → blocker 画成有向边：

W2 ──waits for──> W1 ──waits for──> B0

这是 blocking chain；只要 B0 最终 commit/rollback，链可以继续推进。若形成环：

T1 → T2 → T1

才是 deadlock。等待很久不自动等于 deadlock，deadlock 也不要求等待很久才在逻辑上成立。

从 waiter 出发，而不是拼一条万能 self-join

第一组只读证据：

SELECT
    pid,
    application_name,
    state,
    wait_event_type,
    wait_event,
    xact_start,
    query_start,
    pg_blocking_pids(pid) AS blocking_pids,
    query
FROM pg_stat_activity
WHERE datname = current_database()
  AND state <> 'idle';

pg_blocking_pids()知道 lock conflict matrix、wait queue 和 parallel worker 映射，比手写 pg_locks self-join可靠。它既可能返回持有冲突锁的 hard blocker，也可能返回排在队列前面的 soft blocker；parallel query 可能出现重复 client-visible PID，prepared transaction blocker 用 PID 0 表示。高频调用还会短暂独占 lock manager shared state，所以它是诊断函数，不应被应用每毫秒轮询。

找到 edge 后再补：

SELECT *
FROM pg_locks
WHERE pid = ANY (ARRAY[waiter_pid, blocker_pid]);

用于解释对象、mode、granted、fastpath、waitstart。pg_locks 是瞬时切片；fast-path、regular 和 predicate lock 的采集并非一个全局冻结时刻，不要把两个相隔数秒的查询拼成绝对一致的历史。

active 不等于正在消耗 CPU

pg_stat_activity.state 与 wait_event 独立：

• state='active' AND wait_event IS NULL：正在执行，但仍需结合 CPU/I/O 证据；
• state='active' AND wait_event IS NOT NULL：query 在执行生命周期中，却卡在某个 wait point；
• idle in transaction：当前没跑 query，但 transaction 仍开着，可能持锁和 snapshot；
• idle：等待客户端下一条命令，通常不持 transaction locks。

本章 waiter 是 active + Lock + transactionid，blocker 却是 active + Timeout + PgSleep。后者不是在等待 waiter，而是在按实验设计睡眠并持有未提交事务。只按 state='active' 排序会把二者都叫“活跃 SQL”，丢失因果关系。

deadlock detector 解决环，不替应用设计顺序

PostgreSQL 检测到锁等待环后会 abort 其中一个 transaction，以 40P01 deadlock_detected 让其他成员继续；不能依赖固定谁当 victim。应用要 rollback 并从事务开头重试。

最有效的预防是所有代码按一致顺序取得多个对象的锁。例如转账总按较小 account ID 后较大 ID；批量更新先排序主键。还应：

• transaction 尽量短，不在持锁时等待用户或远程 API；
• 第一次取得对象时就选择实际需要的 mode，避免难以推理的升级；
• 为 lock wait 设置业务预算并保留原始 SQLSTATE；
• 在受控环境启用合适的 log_lock_waits/deadlock_timeout 取证；
• 监控连接池排队与 database lock 两种不同的“等待”。

本章不主动制造 deadlock，因为一次单边阻塞已经足以建立证据链；第 10 章会用确定性双事务场景验证 40P01、40001 和重试边界。

5.4.3 锁模式名称不等于业务影响

table-level mode 的关键不是英文听感，而是 conflict matrix。常用子集如下：

官方矩阵中，普通 SELECT 的 ACCESS SHARE 只与 ACCESS EXCLUSIVE 冲突。这不表示 DDL 只有 ACCESS EXCLUSIVE 才有业务影响：一个等待取得强锁的 DDL 可能排在队列中，让它后面的请求形成 convoy；CREATE INDEX 还可能争用 I/O/CPU；长 transaction 会让短暂 lock 变成长事故。

同一个 mode，影响可以相差几个数量级

评估锁风险至少要回答：

1. 对象：哪张 relation、哪一行、哪个 XID 或 advisory key？
2. mode 与 conflict：谁与谁冲突，不是名字有多吓人？
3. 范围：命中一行、百万行、所有 partition，还是 catalog object？
4. 持有期：statement 结束还是 transaction 结束？事务已经多老？
5. 扇出：有多少 waiter、上游连接池和同步请求？
6. 可恢复性：cancel statement 足够，还是 backend 必须终止？commit outcome 是否 ambiguous？

对一行的 ROW EXCLUSIVE table lock 可以持续 2 ms，也可以因应用调用支付接口持续 30 s；mode 相同，业务影响完全不同。反之，一个瞬时 ACCESS EXCLUSIVE 若能立即取得并在毫秒内完成，可能比排队十分钟的普通写影响小。DDL 发布必须用真实锁时长、table size、long transaction 和 timeout 演练，而不是静态给 mode 贴“安全/危险”标签。

取消与终止是最后一步

生产处置顺序应是：

确认采样时刻
→ 定位 waiter 与 blocker edge
→ 核对 application/user/database/xact age/query
→ 评估 blocker 是否正在做不可中断业务
→ 优先让 owner 正常结束
→ 必要时 pg_cancel_backend(query)
→ 明确授权后 pg_terminate_backend(session)
→ 验证锁链、业务状态与重试结果

pg_cancel_backend 只请求取消当前 query，不自动关闭 session。本章 blocker 之所以随后释放事务，是因为 psql 设置 ON_ERROR_STOP，收到 57014 query_canceled 后退出连接，server 因断连 rollback。生产 application 可能捕获错误后停在 failed/idle transaction；不能照抄实验把 cancel 当作 transaction cleanup。

pg_terminate_backend 会断开精确 session，影响更大；连接池还可能立刻重连并重放负载。任何处置都要保存 PID、backend start、application name、XID、query/transaction start 与 blocking edge，避免 PID 重用或误伤无关工作。

上一节：事务边界与失败语义 · 返回本章目录 · 下一节：隔离现象与后续路线 · 查看全书目录 · 查看索引中心

5.5 隔离现象与后续路线

隔离级别不是从“弱一致”到“强一致”的四档万能开关。SQL 标准用禁止哪些并发现象来规定最低保证，PostgreSQL 再用 MVCC、snapshot isolation 与 SSI 给出自己的具体实现。讨论任何异常时，都必须同时写出数据库、隔离级别、SQL 形状和最终提交结果。

5.5.1 脏读、不可重复读、幻读与序列化异常

先把四种现象定义准确：

• dirty read：读到并发 transaction 尚未提交的值；
• nonrepeatable read：同一 transaction 再读同一逻辑行，看到另一个已提交 transaction 的修改；
• phantom read：同一 transaction 重跑同一 predicate query，满足条件的 row set 因并发提交而变化；
• serialization anomaly：一组成功提交事务的总体结果无法等价于任何串行顺序。

PostgreSQL 18 的实际矩阵是：

第一处 PostgreSQL 特性是：虽然接受四个标准名称，内部只有三个不同级别，Read Uncommitted 按 Read Committed 执行。第二处是 PostgreSQL Repeatable Read 比标准最低要求更强，不允许 phantom，但仍可能发生 serialization anomaly。

snapshot 生命周期解释大部分差异

Read Committed 是默认级别。每个 command 使用 statement-start snapshot，因此：

BEGIN;
SELECT ...;  -- snapshot S1
-- concurrent transaction commits
SELECT ...;  -- snapshot S2，可能看到新值/新行
COMMIT;

单条普通 SELECT 内部仍看到一致 snapshot，也不会读 dirty tuple。UPDATE/DELETE/locking SELECT 遇到并发更新时会等待，并在 Read Committed 规则下对最新版本重新判断条件；这让一条 command 的行为比“先固定全表 snapshot，再机械写入”更细致。

Repeatable Read 在 transaction 的第一个非 transaction-control statement 时取得 transaction snapshot，此后普通查询保持同一视图。如果它准备更新的目标已被 snapshot 之后的并发事务真正修改并提交，会收到 40001，必须整体重试。只读 Repeatable Read 不会因这种 row update conflict 失败，但仍可能观察到不满足任何串行顺序的跨行组合。

Serializable 在 Repeatable Read 的 snapshot 行为上增加 SSI dependency tracking。predicate lock（SIReadLock）用于发现危险的 read/write dependency，不像普通 row lock 那样阻塞 writer；若无法证明一组并发事务可串行化，至少一个以 40001 失败。因此“Serializable”承诺的是成功提交集合可串行化，不是所有 transaction 都无等待、无 abort。

isolation 不能替代错误处理

更强隔离通常把 silent anomaly 转成可见 abort，而不是让 application 省掉重试。Serializable 环境必须：

• 对 40001 统一执行 whole-transaction retry；
• 只在 commit 成功后信任 transaction 内读到的结果；
• 限制 active connection 与 transaction 时长；
• 将只读事务声明 READ ONLY；
• 对适合的长只读任务考虑 SERIALIZABLE READ ONLY DEFERRABLE，理解它可能在开始时等待安全 snapshot。

sequence 仍有特殊非事务行为；外部 API 仍不受 isolation 管理。把 isolation 调高不能修复缺失 idempotency key、跨库原子性或错误的业务 predicate。

5.5.2 lost update 必须绑定具体隔离级别与写法

“Read Committed 会丢更新”只说了一半。下面两个流程都想把库存从 10 减 1，结果不同。

原子相对更新

两个 session 都执行：

UPDATE inventory
SET stock = stock - 1
WHERE sku = 'SKU-GIFT'
  AND stock > 0
RETURNING stock;

在 Read Committed 下，第一个 writer 锁住目标行；第二个等待，随后在已更新版本上重新检查 stock > 0 并计算 stock - 1。若初始为 10，正常结果依次为 9、8，不会因两者都先拿到常量 10 而覆盖。

这仍需检查 affected row count：库存为 0 时返回零行，应用必须解释为 sold out，而不是假定成功。row-level CHECK (stock >= 0) 可以成为最后防线。

应用层 read-modify-write

两个 session 都先：

SELECT stock FROM inventory WHERE sku = 'SKU-GIFT'; -- 都读到 10

应用各自在内存算出 9，再执行：

UPDATE inventory
SET stock = 9
WHERE sku = 'SKU-GIFT';

第二个 writer 仍会等待第一个，但等待后把最新 9 又覆盖成常量 9；两次业务扣减只留下一个效果。这才是典型 lost update。锁确实排序了物理写入，却不知道常量 9 是由旧 snapshot 推导的。

可选控制方式：

optimistic 例子：

UPDATE inventory
SET stock = :new_stock,
    version = version + 1
WHERE sku = :sku
  AND version = :seen_version
RETURNING stock, version;

返回零行不是 database outage，而是“决策前提已经过期”。应用可返回 conflict 或在新值上重新执行业务逻辑，不能只把同一个常量 UPDATE 无限重试。

lost update 与 write skew 不是同一异常

lost update 竞争同一逻辑值；write skew 往往更新不同行。两个医生各自看到“至少还有另一人值班”，随后分别把自己的行改为 off-call；没有同一行 write/write conflict，两者在 Repeatable Read 可能都提交，却破坏“至少一人值班”的跨行不变量。

处理优先级是：

1. 能用 PK/UK/FK/CHECK/EXCLUDE 等 declarative constraint 表达，就让数据库无条件拒绝；
2. 能收敛为同一 counter/guard row 的 atomic update，就避免分散 predicate；
3. 否则用 Serializable + whole-transaction retry，或明确、顺序一致的 predicate/row locking；
4. 用并发测试证明成功提交集合满足不变量。

只说“加 FOR UPDATE”也不完整：必须锁到所有能改变 predicate 的对象；若满足条件的 row 尚不存在，普通 row lock 没有一行可锁。第 10 章会用 write skew、phantom/predicate 和 retry harness 把这些边界逐项跑出来。

5.5.3 ch07–ch10 如何分别展开计划与并发

本章的作用是建立分诊，不是在第一次遇见概念时把所有旋钮讲完。后续四章各回答一种不同问题：

第 7 章：计划为什么这样选

执行计划与统计信息会深入：

• EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) 的安全使用；
• estimated/actual rows、loops 与第一次估算偏差；
• MCV、histogram、correlation 与 extended statistics；
• custom/generic prepared plans、parallel plan 与 JIT；
• planner cost calibration 和实验对照。

入口问题是“backend 没有明显 wait，但 plan 的工作量/估算哪里异常？”

第 8 章：慢时间到底花在哪里

慢 SQL 诊断方法论会先做 workload attribution，再区分 CPU、I/O、lock、WAL、temp spill、client backpressure 和连接排队；结合 pg_stat_statements、auto_explain、logs、OS/Pigsty metrics 建立时间线。

入口问题是“用户说慢，先用什么证据把 wall time 拆开？”

第 9 章：索引是否真正改善目标 workload

索引设计与效果验证会从 equality/range/order/join pattern 设计 B-tree、GIN、GiST、BRIN、partial/expression/covering index，并同时验证写放大、空间、visibility map 与并发创建风险。

入口问题是“已经证明访问路径缺口，哪种 index contract 能改善且值得成本？”

第 10 章：并发提交是否仍满足不变量

并发控制与隔离异常会用多个真实 session 复现 nonrepeatable read、lost update、write skew、deadlock、serialization failure，比较 atomic SQL、optimistic version、row lock、advisory lock 与 Serializable retry。

入口问题是“单事务看起来正确，多事务交错后哪些成功提交结果不再正确？”

当前应能完成的四向分诊

flowchart TD
  A["请求慢或结果异常"] --> B{"结果/业务不变量错误？"}
  B -- "是" --> C["事务边界、snapshot、SQL 写法<br/>进入 ch10"]
  B -- "否" --> D{"pg_stat_activity 有 wait event？"}
  D -- "Lock" --> E["建立 blocking edge<br/>进入 ch10 / 运维诊断"]
  D -- "IO/LWLock/WAL/Client" --> F["按等待类型取证<br/>进入 ch08"]
  D -- "无明显等待" --> G["计划工作量与估算<br/>进入 ch07"]
  G --> H{"已证明访问路径缺口？"}
  H -- "是" --> I["设计并验证索引<br/>进入 ch09"]
  H -- "否" --> F

一条 SQL 同时可能有多个问题，但动作顺序仍要可证伪。例如 waiter 的 EXPLAIN 再漂亮，也不会解除 blocker；给一个错误的 read-modify-write 加索引，也不会消除 lost update。先判层，再深入，是本章希望形成的习惯。

上一节：锁与等待 · 返回本章目录 · 下一节：实战：观察一笔订单事务 · 查看全书目录 · 查看索引中心

5.6 实战：观察一笔订单事务

这个实验不追求制造最大并发，而是把一条最小 blocking edge 观察完整：blocker 写入未提交版本，普通 reader 读旧版本，waiter 写同一行并等待；observer 同时采集 activity、blocking PID 与 locks，最后取消精确实验 query，让两个事务都回滚并验证状态。

风险分级：

• verify / observe：R0·观察，只读 catalog、sample row 和 WAL positions；
• transaction：R2·受控演练，触发三个预期 error，并 rollback 一次真实 UPDATE；
• blocking：R2·受控演练，两个 session 对订单 1002 UPDATE，调用 pg_cancel_backend 取消精确 blocker；
• all / review：R2·受控演练，执行前验、全部实验和后验。

即使不提交，写入仍产生 tuple/WAL/lock。只在已确认可演练的 Pigsty L1 或本地测试库运行；生产只能复用只读取证方法，不能复用“主动注入阻塞”。

5.6.1 从 SQL 观察会话、快照、锁和 WAL 位置

先使用绝对路径指向自己的私有 service file，不把 password 写进命令历史：

export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin

cd static/labs/ch05
export PG36_EVIDENCE_DIR="$PWD/evidence/ch05/$(date -u +%Y%m%dT%H%M%SZ)"

psql -X -w "service=$PGSERVICE" \
  -c '\conninfo' \
  -c "SELECT current_database(), pg_is_in_recovery();"
./task.sh verify

context guard 要求 database=pg36_shop、primary/writable、可 SET ROLE pg36_owner，且 shop_private.schema_version 是 ch04-v1。verify.sql复用完整 ch04 验收，再额外要求：

active_lab_workers=0
order_1002_fingerprint=2bfa6eac30b9a1cfa2d51e98c4e98332
relation_checksum=f8a7bfae59c6d16cd323abecfefe1014

观察一个没有 write XID 的 transaction

运行：

./task.sh observe
sed -n '1,120p' "$PG36_EVIDENCE_DIR/observe.txt"

observe.sql显式开启：

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;

然后只取一次 pg_current_snapshot()，解析其边界，并从 pg_stat_activity 反查自己的 backend_xid/backend_xmin。典型片段：

transaction_isolation=read committed
transaction_read_only=on
assigned_xid_before_write=<none>
snapshot=959:959:
snapshot_xmin=959
snapshot_xmax=959
snapshot_in_progress_count=0
backend_snapshot=<none>|959|<none>|<none>

XID 数值随实例推进，不能 hard-code。要观察的是：transaction/snapshot 已存在，read-only backend 却可以没有 assigned write XID；backend_xmin 暴露它对清理 horizon 的影响。

同一脚本输出：

tuple_diagnostic=1002|xmin|xmax|ctid|request_fingerprint
wal_positions=insert_lsn|write_lsn|flush_lsn

xmin/xmax/ctid只用于版本取证。反复运行 rollback 实验后，一个仍可见 tuple 甚至可能有非零 xmax，这正说明不能从 xmax<>0 直接推断“已删除”。三个 WAL position 分别表示 insert、write、flush 进度；短暂相等也不证明未来始终没有 pending WAL。

观察 failed transaction 与 savepoint

./task.sh transaction
sed -n '1,120p' "$PG36_EVIDENCE_DIR/transaction-errors.stderr"
sed -n '1,160p' "$PG36_EVIDENCE_DIR/wal-rollback.txt"

task 要求 error stream 中每个 SQLSTATE 恰好一次：

22012  division_by_zero
25P02  in_failed_sql_transaction
23514  check_violation

前两项证明 error 后普通 SQL 不能继续；第三项发生在 savepoint 后，ROLLBACK TO 恢复 transaction，再完成一条合法 UPDATE，最终 outer rollback。脚本不靠本地化错误消息判断，而用稳定 SQLSTATE。

WAL probe 要同时满足：

wal_insert_advanced=t
state_restored=t

LSN 是实例全局位置，差值中可能包含其他 backend；本实验在隔离 L1 中只用它证明“回滚路径仍有 WAL 活动”，不把字节数当单条 SQL benchmark。

5.6.2 从 Pigsty 观察连接、事务与等待指标

SQL catalog 是当前瞬时状态，Pigsty/Grafana 提供时间序列、层级导航和跨组件上下文。两者不是替代关系：

官方 v4.5 dashboard 索引把 PGSQL Activity 定义为 cluster 级 session/load/QPS/TPS/locks，把 Persist 定义为 WAL/XID/checkpoint/archive/I/O，把 PGCAT Locks 定义为 catalog-derived activity 与 lock wait。部署若定制 dashboard、collector 或版本，面板与 metric 可能变化，所以正文依赖的是问题映射，不是像素位置。

让连接可归因

所有 worker 都带唯一 application_name：

pg36-ch05-blocker-<UTC timestamp>-<shell pid>
pg36-ch05-waiter-<UTC timestamp>-<shell pid>

真实应用也应给 service/driver 设置稳定 application name，并在 tracing 中关联：

cluster / instance
database / user / application
request trace ID
backend PID + backend_start
transaction/query start
dashboard time range + timezone

只记录 PID 不够，PID 会重用；只记录 SQL 也不够，同一 statement 可由大量租户并发执行。涉及权限时还要知道：普通角色在 pg_stat_activity 中只能完整看到自己的 session，跨用户 query text/细节需要 pg_read_all_stats 等受控监控权限或 superuser。不要为了 dashboard 方便给业务账号 superuser。

处理采样与瞬时现场的差异

catalog query 能在 waiter 正等待时看到精确 edge；Prometheus/exporter 按采集周期采样，短于一个 scrape interval 的实验可能根本不出现在图上。默认 blocking harness 取完 SQL 证据便立即释放，不靠固定 sleep 同步。

若只在 L1 教学库中需要让 dashboard 有机会采到，可显式延长观察窗：

export PG36_DASHBOARD_HOLD_SECONDS=20  # 只允许 0..25
./task.sh blocking

此变量只在已经确认 edge 后 sleep，不能参与 worker 同步；它会人为延长订单行等待，不允许用于生产。打开 Pigsty Web UI 后，在同一 UTC 时间窗依次看 PGSQL Activity、PGSQL Session/Xacts、PGCAT Locks，再回到 evidence 的 PID/app name 对照。若 panel 没采到，SQL evidence 仍是实验验收依据，不应继续延长生产锁来“等图变漂亮”。

dashboard 擅长回答“何时开始、范围多大、是否反复、同时还有什么资源变化”；catalog 擅长回答“现在这条边究竟是谁阻塞谁”。事故诊断通常先由告警/趋势定位时间窗，再用原生视图和日志确认现场。

5.6.3 注入阻塞并解释“现象—证据—原理”

单独执行 blocking：

unset PG36_DASHBOARD_HOLD_SECONDS
./task.sh blocking

sed -n '1,160p' "$PG36_EVIDENCE_DIR/blocking/summary.txt"
sed -n '1,160p' "$PG36_EVIDENCE_DIR/blocking/activity.csv"
sed -n '1,220p' "$PG36_EVIDENCE_DIR/blocking/locks.csv"

blocking-lab.sh不靠“sleep 两秒大概启动好了”同步。它轮询 blocker 直到：

state=active
wait_event_type=Timeout
wait_event=PgSleep

这证明未提交 UPDATE 已经完成并正持有 transaction。随后 ordinary reader 带 1 秒 statement timeout 读取旧 fingerprint；再启动 waiter，直到 pg_blocking_pids(waiter) 精确等于 blocker PID 且 wait type 是 Lock，才采集 CSV。

sequenceDiagram
    participant B as Blocker
    participant R as Ordinary reader
    participant W as Waiter
    participant O as Observer

    B->>B: BEGIN; UPDATE order 1002
    Note over B: uncommitted new tuple version
    R->>B: plain SELECT
    B-->>R: no row-lock wait; old committed version
    W->>B: UPDATE same logical row
    Note over W: waits for Blocker's XID
    O->>O: activity + blocking_pids + locks
    O->>B: pg_cancel_backend(exact PID)
    Note over B: psql exits on 57014; transaction rolls back
    B-->>W: lock released
    W->>W: UPDATE succeeds; explicit ROLLBACK
    O->>O: verify baseline and no workers

一次实测 summary：

status=ok
reader_saw_previous_committed_version=true
waiter_blocked_by=<blocker_pid>
waiter_wait_event_type=Lock
waiter_wait_event=transactionid
cancel_exact_blocker=t
blocker_expected_nonzero_exit=3
waiter_exit=0
state_restored=true
remaining_workers=0

从三组证据回到原理

这种“现象—证据—原理—边界”四列，比只保存一张 dashboard 截图更可审计。它允许后来者复核当时看见什么、为什么得出结论、结论没有覆盖哪些情况。

失败清理和停止线

正常路径只 pg_cancel_backend 精确 blocker query；blocker psql 因 ON_ERROR_STOP 收到 SQLSTATE 57014 后退出，server rollback connection transaction。waiter 获锁后显式 rollback。

若 harness 中途失败，EXIT trap 只按本次唯一 application names 终止它启动的 blocker/waiter，并等待本地 psql process；不会扫描或清理其他会话。随后仍应运行：

./task.sh verify

若 active_lab_workers<>0、fingerprint/checksum 漂移，或 blocker identity 不再精确，停止自动处置并人工核对。这个实验没有 reset，因为成功路径不应留下需要 reset 的对象或数据；“写一个 reset 抹掉异常”反而会掩盖事务边界错误。

最后做全章验收：

export PG36_EVIDENCE_DIR="$PWD/evidence/ch05/final-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh all

只有 verify-after 恢复稳定摘要，且 blocker cancellation、waiter exit、SQLSTATE 和 WAL/rollback 断言全部通过，才能把本章标记为完成。

上一节：隔离现象与后续路线 · 返回本章目录 · 下一章：立木取信：开发规约与交付基线 · 查看全书目录 · 查看索引中心