10 顾此失彼：并发控制与隔离异常

单条 SQL 正确、单个事务顺序执行正确，不代表并发执行仍正确。并发控制的起点不是先选隔离级别，而是写出业务不变量和允许的失败语义：

business invariant
  → concurrent read/write set and possible interleavings
  → snapshot/isolation guarantee
  → atomic SQL, row lock, optimistic CAS, SSI or advisory coordination
  → retryable SQLSTATE + whole-transaction replay
  → idempotency and external-effect protocol
  → lock/error/latency evidence
  → two-connection invariant test

PostgreSQL 的 Read Committed、Repeatable Read 与 Serializable 不是“性能低、中、高”的旋钮。它们允许或拒绝的交错不同；拒绝通常表现为需要应用处理的 40001，不是数据库自动把原事务重新执行。

本章目标

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

• 区分 Read Committed 的语句快照与 Repeatable Read 的事务快照；
• 知道 PostgreSQL 的 Read Uncommitted 实际等同 Read Committed；
• 解释 PostgreSQL Repeatable Read 是 snapshot isolation，仍允许 write skew；
• 说明 Serializable Snapshot Isolation 如何用 SIReadLock 识别危险依赖；
• 把 40001 理解为“整个事务必须重放”，而非重试最后一条 SQL；
• 用确定性交错重现 read–compute–write lost update；
• 用 SET col = col - $1 WHERE ... 做原子条件更新；
• 用 version compare-and-swap 识别并处理乐观冲突；
• 区分 FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE 与 FOR KEY SHARE；
• 正确选择 NOWAIT、SKIP LOCKED，并说明后者为何只适合 queue-like workload；
• 建立统一 lock order，识别 40P01 并回放整个事务；
• 区分 session-level 与 transaction-level advisory lock 生命周期；
• 设计 advisory key namespace、owner、timeout 与释放协议；
• 使用 pg_stat_activity、pg_locks 和 pg_blocking_pids() 保存精确阻塞图；
• 用 Pigsty 的 Activity/Xacts/PGCAT Locks/日志时间窗量化并发问题；
• 设计 payment idempotency key、payload fingerprint 与响应复用；
• 用 transactional outbox 跨越数据库 commit 与外部消息边界；
• 把隔离、重试、幂等与外部副作用要求追加到 DEFAULT-TXNN-007。

实验边界

实验基线为 PostgreSQL 18.6、Pigsty v4.5.0、Ubuntu 24.04 L1；主体机制保持 PostgreSQL 14–18 可用。只创建六张带固定 marker 的表：

ch10_inventory        two SKUs / available=100 / version=0
ch10_doctor           two on-call doctors
ch10_deadlock_probe   two lock-order rows
ch10_job              six queued jobs
ch10_payment_request  idempotency authority
ch10_outbox            committed external-effect intent

协调器使用 advisory 两整数 key space：

(3610, 1001..1016)

它只控制教学 interleaving，不参与业务正确性。每个 case 后必须 worker=0、barrier lock=0。setup 在 marker 匹配后重建，属于 R1；reset 属于 R2，需要 action/target 双 token。

下载资产：

• 实验合同
• 上下文 guard
• 确定性 fixture
• 并发协调与断言器
• lost-update worker
• 原子更新 worker
• 乐观更新 worker
• 乐观重试
• Repeatable Read worker
• write-skew/SSI worker
• 行锁 holder
• 行锁 waiter
• NOWAIT probe
• SKIP LOCKED worker
• deadlock worker
• payment idempotency worker
• 不同 payload 反例
• 最终状态验收
• 双 token reset
• 语义审查器
• v0.5 规则提案
• 任务入口

本章目录

10.1 隔离级别与可观察现象

• 10.1.1 Read Committed 的语句快照
• 10.1.2 Repeatable Read 的事务快照与写冲突
• 10.1.3 Serializable、谓词冲突与序列化失败

10.2 Lost update 不是一句口号

• 10.2.1 读—算—写在 Read Committed 下如何丢更新
• 10.2.2 原子更新与带版本条件的更新
• 10.2.3 更高隔离级别何时拒绝而不是静默覆盖

10.3 悲观锁与锁队列

• 10.3.1 FOR UPDATE、NO KEY UPDATE 与引用关系
• 10.3.2 NOWAIT、SKIP LOCKED 与任务领取
• 10.3.3 锁顺序、阻塞链与死锁

10.4 乐观控制、重试与幂等

• 10.4.1 版本列、唯一键与条件写入
• 10.4.2 重试只包围可重放的事务
• 10.4.3 支付、消息与外部副作用的边界

10.5 咨询锁与跨行协调

• 10.5.1 会话级与事务级咨询锁
• 10.5.2 键空间、碰撞与所有权
• 10.5.3 不用咨询锁掩盖缺失的数据约束

10.6 观察与诊断并发

• 10.6.1 pg_stat_activity、pg_locks 与等待事件
• 10.6.2 从 Pigsty 定位锁等待与长事务
• 10.6.3 保存阻塞图，而不是先杀会话

10.7 实战：库存扣减与支付幂等

• 10.7.1 在指定隔离级别重现丢更新和死锁
• 10.7.2 比较原子更新、行锁与可重试事务
• 10.7.3 用并发测试验证业务不变量并追加规约

实测摘要

一次 PostgreSQL 18.6 全量验收得到：

lost:
  both read 100 / requested total 30
  serial expected 70 / actual 80 or 90

safe writes:
  atomic → 2 successes / final 70 / version 2
  optimistic → first 1 success + 1 conflict
               whole retry 1 success / final 70 / version 2

isolation:
  Repeatable Read same-row update → 1 commit + one 40001
  Repeatable Read write skew      → 2 commits / on-call 0
  Serializable write skew         → SIReadLock observed
                                    1 commit + one 40001 / on-call 1

locks:
  NOWAIT=55P03
  deadlock=one 40P01 / survivor leaves rows [1,1]
  SKIP LOCKED=two workers × 3 / duplicate 0
  row lock=one blocker edge / waiter sees 90 / final 70

idempotency:
  concurrent requests 2 / inserted 1 / reused 1
  payment 1 / outbox 1 / distinct response 1
  same key different payload=P0001 / state unchanged

final:
  worker=0 / advisory barrier=0
  relation checksum=f8a7bfae59c6d16cd323abecfefe1014

胜出事务、PID、XID、backend_start、lost-update 最终是 80 还是 90 都不是 golden。稳定断言是允许/拒绝的交错、SQLSTATE、多连接关系、业务不变量和最终清理。

章节验收

1. 每个并发写先声明 invariant、read set、write set 与 failure contract；
2. Read Committed 的两条普通 SELECT 可见不同已提交状态；
3. UPDATE SET x=x+... 与 application read–compute–write 的语义差别明确；
4. optimistic zero-row update 被当冲突，而非成功；
5. Repeatable Read concurrent row update 以 40001 拒绝；
6. Repeatable Read write skew 反例被实际重现；
7. Serializable 的 SIReadLock 和 40001 都有 raw evidence；
8. 整事务 retry 有 attempt/time budget、backoff/jitter 与新 snapshot；
9. row lock mode 与 foreign-key key update 边界正确；
10. NOWAIT 55P03、SKIP LOCKED queue-only 边界明确；
11. 所有多行写有统一 lock order，40P01 仍被整事务处理；
12. advisory key namespace/lifetime/owner/timeout 可审计；
13. 阻塞动作使用 PID + backend_start + database + application identity；
14. Pigsty 时间窗能落回 exact blocker graph、SQLSTATE 与 transaction age；
15. idempotency key 绑定 payload fingerprint 与 response；
16. 相同 key 不同 payload 必须拒绝；
17. 数据库事务内只写 outbox，不调用远程支付/消息；
18. task.sh all、双 token reset 与错误 token 反例均通过；
19. 最终 worker/advisory=0，业务 checksum 不变；
20. v0.5 仍是有依赖的 candidate，不冒充已发布 baseline。

下一章 ch11《守正出奇：模式变更与安全发布》 将把本章的锁、事务与兼容语义应用到真实 DDL 发布。

参考资料

• PostgreSQL 18：Transaction Isolation
• PostgreSQL 18：Explicit Locking
• PostgreSQL 18：Serialization Failure Handling
• PostgreSQL 18：Advisory Lock Functions
• Pigsty：PostgreSQL Dashboards

上一章：巧夺天工：索引设计与效果验证 · 返回上卷导读 · 下一章：守正出奇：模式变更与安全发布 · 查看全书目录 · 查看索引中心

10.1 隔离级别与可观察现象

隔离级别描述“并发事务成功提交后允许出现什么结果”，不是给单条查询加一层缓存。PostgreSQL 的实现关系是：

PostgreSQL Repeatable Read 比 SQL 标准最低要求更强：它不允许 phantom read；但“看见稳定快照”仍不等于“所有成功事务可排成某个串行顺序”。

10.1.1 Read Committed 的语句快照

每条普通查询重新取 snapshot

默认 Read Committed 下，一条普通 SELECT 看见：

• 该语句开始前已提交的数据；
• 当前事务自己先前的写入；
• 看不见其他事务未提交的写入；
• 看不见该语句执行过程中才提交的新版本。

同一事务中的下一条 SELECT 会取得新 snapshot，因此可能看到并发提交：

T1                                      T2
BEGIN;                                  BEGIN;
SELECT available;  -- 100
                                        UPDATE ... SET available=90;
                                        COMMIT;
SELECT available;  -- 90
COMMIT;

这不是“不可重复读 bug”，而是 Read Committed 的合同。需要一个稳定跨语句视图时，要重新设计事务、锁或隔离级别，而不是假设 BEGIN 自动冻结所有读。

查看当前事务设置：

SHOW transaction_isolation;
SELECT current_setting('transaction_isolation');

显式设置应在 transaction 第一条 query 前：

BEGIN ISOLATION LEVEL READ COMMITTED;
-- work
COMMIT;

不能先执行业务查询再把当前事务切到更高隔离级别。

写语句会等待并重新检查目标行

UPDATE、DELETE、SELECT ... FOR UPDATE/SHARE 搜索候选时使用语句 snapshot，但候选行可能已被并发事务修改。PostgreSQL 会等待先行 writer：

先行事务 rollback
  → 后行事务可处理原版本

先行事务 commit update
  → 后行事务在新版本上重新检查 WHERE
  → 仍满足才执行

先行事务 commit delete
  → 后行事务跳过该行

这解释了为什么原子条件更新安全：

UPDATE inventory
SET available = available - $2
WHERE sku_id = $1
  AND available >= $2
RETURNING available;

若另一事务先扣减并提交，后行 UPDATE 会在最新 row version 上重新检查 available >= $2，不会拿语句开始时的旧值硬算。

但它也意味着一条复杂 Read Committed 写语句可能观察到“目标行的新版本”，却看不见同一并发事务在其他行的变化。对预先确定的单行原子写通常正合适；对跨行不变量必须更谨慎。

snapshot 不覆盖所有数据库对象

sequence 的变化立即对其他事务可见，且 abort 不会回滚：

SELECT nextval('order_id_seq');
ROLLBACK;

被取走的值不会“归还”。因此序列 gap 不是事务隔离失败，ID 连续性也不应作为业务不变量。外部 API、文件、消息系统同样不受 PostgreSQL snapshot/rollback 管理。

10.1.2 Repeatable Read 的事务快照与写冲突

snapshot 从首个真正语句开始

Repeatable Read transaction 看见首个非事务控制语句开始前已提交的数据，之后普通查询保持相同视图：

T1 (RR)                                  T2
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT available;  -- snapshot=100
                                         UPDATE ... SET available=90;
                                         COMMIT;
SELECT available;  -- 仍为 100
COMMIT;

事务开始的 wall-clock 时刻不一定是 snapshot 时刻；只执行 BEGIN 后长时间空闲，再执行首个 query，snapshot 才建立。诊断时同时看 xact_start、query_start、state 与 backend_xmin，不要把它们混成一个时间。

修改 snapshot 后已被改过的行会拒绝

若 RR 事务想更新/锁定一个在 snapshot 建立后被其他事务实际更新或删除并提交的 row，PostgreSQL 不会把旧计算静默覆盖到新版本，而是：

SQLSTATE 40001
could not serialize access due to concurrent update

本章两个 worker 都先读 available=100，再分别准备写 90 和 80。协调屏障放行后：

one transaction commits
the other exits 40001
final = 90 or 80 / version=1

哪个事务赢不是合同；“一提交、一拒绝、无静默覆盖”才是。

应用必须 ROLLBACK 并从 BEGIN 前重放整个逻辑。只重试失败的 UPDATE 会继续使用旧 snapshot、旧决策或旧 application state。

稳定快照仍允许 write skew

两名医生都在值班，规则是“至少一人 on call”。两个 RR 事务分别：

T1 reads count(on_call)=2       T2 reads count(on_call)=2
T1 turns doctor 1 off           T2 turns doctor 2 off
T1 commits                      T2 commits

它们写不同 row，没有 same-row write conflict；各自在自己的 snapshot 中都满足规则，最终却是 0。PostgreSQL RR 阻止 phantom，但仍允许这种 snapshot-isolation serialization anomaly。

因此：

“我的事务里连续两次读一样”
≠
“所有提交结果都等价于事务逐个执行”

跨行不变量可以用锁住共同 guard row、锁定完整决策集合、显式 table lock、可验证的数据约束或 Serializable；选择取决于冲突率和模型。

10.1.3 Serializable、谓词冲突与序列化失败

SSI 不把所有读变成阻塞锁

PostgreSQL Serializable 在 Repeatable Read snapshot 上增加 Serializable Snapshot Isolation（SSI）依赖检测。它跟踪：

transaction A read something
transaction B wrote something that would have changed A's result

并分析这些 read/write dependency 是否组成无法串行化的危险结构。必要时拒绝一个事务：

SQLSTATE 40001
could not serialize access due to read/write dependencies among transactions

它不是传统的“所有 predicate read 都阻塞 writer”。SSI predicate lock 在 pg_locks 中显示为：

mode = SIReadLock

这种锁用于依赖检测，不造成常规 blocking，也不参与 deadlock。锁粒度取决于实际 plan：可能是 tuple、page 或 relation；资源紧张时还会提升到更粗粒度。因此索引/计划会影响 predicate-lock footprint 和 abort rate，但不能为了减少 40001 就盲目强制 index scan。

本章 Serializable 医生 case 在两个事务都读到 on_call=2 后捕获：

pg36-ch10-write-skew-ser-a / relation / SIReadLock / ch10_doctor
pg36-ch10-write-skew-ser-b / relation / SIReadLock / ch10_doctor

随后一事务提交，另一事务 40001，最终仍有一人值班。SSI 保证的是成功提交的集合可串行化；被 abort 事务里读到的任何结果都不能对外生效。

40001 是正常控制流，但不是无限重试许可

正确 handler：

BEGIN new transaction
  → obtain new snapshot
  → re-read every decision input
  → recompute
  → redo only idempotent/database-contained effects
COMMIT

还必须有：

• max attempts；
• total elapsed deadline；
• exponential backoff + jitter；
• cancellation/request deadline；
• retry/abort metrics；
• final error contract；
• idempotency key；
• 对外部副作用的隔离。

高 40001 比率不是“把 attempts 调大”。它可能表示事务太长、连接过多、热点冲突、predicate lock 过粗或数据模型缺少更自然的协调点。

除 40001 外，文档建议在某些应用中也把 40P01 deadlock failure 作为 whole-transaction retry 候选；23505/23P01 有时也可能与 serializable interleaving 有关，但它们通常首先是业务冲突，只有应用能基于完整协议判断是否重试。禁止“所有数据库错误都重试”。

read-only deferrable 的特殊用途

长时间一致性报表可以显式：

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE
READ ONLY
DEFERRABLE;

它可能在开始读取前等待一个已证明安全的 snapshot；一旦取得，便可避免 serialization failure。它适合能接受启动等待的只读批处理，不适合低延迟请求，也不能包含写入。

选级别的顺序

不要从“统一把数据库设成 Serializable”开始。逐个 transaction family 记录：

隔离级别只有与这张合同绑定，才是工程决定。

延伸阅读

• PostgreSQL 18：Transaction Isolation
• PostgreSQL 18：Serialization Failure Handling
• PostgreSQL 18：SET TRANSACTION
• PostgreSQL 18：pg_locks

返回本章目录 · 下一节：Lost update 不是一句口号 · 查看全书目录 · 查看索引中心

10.2 Lost update 不是一句口号

“并发 UPDATE 会丢更新”不准确。下面两条 SQL 的并发语义不同：

-- server-side read-modify-write：后行 writer 在最新 row version 上计算
UPDATE counter
SET value = value + 1
WHERE id = $1;

-- application 把旧绝对值写回来：可能覆盖另一个已提交结果
SELECT value FROM counter WHERE id = $1;  -- application computes 101
UPDATE counter SET value = 101 WHERE id = $1;

Lost update 不是看到两个 writer 就贴上的标签；要画出 read、compute、write 及它们之间允许的 interleaving。

10.2.1 读—算—写在 Read Committed 下如何丢更新

最小反例

库存初值 100，两个请求分别扣 10 和 20：

T1                                      T2
BEGIN RC;                               BEGIN RC;
SELECT available;  -- 100               SELECT available;  -- 100
application computes 90                 application computes 80
UPDATE SET available=90;
COMMIT;
                                        UPDATE SET available=80;
                                        COMMIT;

最终 80，T1 的扣减消失；若写入顺序相反，最终 90，T2 的扣减消失。正确串行结果应是：

100 - 10 - 20 = 70

PostgreSQL 确实让第二个 UPDATE 等待第一个 row lock，但第二条 SQL 的意思是“写绝对值 80”，不是“从提交后的当前值再减 20”。数据库忠实执行了错误合同。

常见来源：

• ORM load entity → 修改字段 → save all columns；
• HTTP GET 旧 representation → PUT 覆盖；
• cache 中取旧 aggregate 再写回；
• 前端 hidden form 带旧 version，却没放进 WHERE；
• worker 先读状态，长时间调用外部 API，再写“成功”；
• 同一对象多个字段被不同功能全行覆盖。

不能用“最后写入者胜出”掩盖需要累计/合并的业务语义。

确定性实验，而不是靠 sleep

本章 lost-update-worker.sql 让两个真实 backend：

1. 都在 Read Committed transaction 中读取 100；
2. 各自计算 90/80；
3. 都在 (3610,1001) advisory barrier 上等待；
4. controller 确认两个 wait_event=advisory；
5. 放行后按任意顺序写绝对值并提交。

一次结果：

{
  "both_observed": 100,
  "requested_total": 30,
  "serial_expected": 70,
  "actual": 80,
  "lost_update_observed": true
}

另一次可能为 90。golden 是 actual ∈ {80,90} 且不为 70，不是胜者名字。barrier 只固定“两边都先读旧值”这一关键关系。

行内 CHECK 是最后防线，不会恢复丢失语义

CHECK (available >= 0)

能阻止负库存版本提交，却不能发现“两个合法绝对值中一个覆盖另一个”。如果 90 和 80 都合法，constraint 无从知道业务本想累计扣 30。约束、并发协议和幂等分别解决不同层次：

CHECK          → 单个新 row 是否在值域内
atomic/CAS     → 并发写是否基于正确版本
idempotency    → 同一业务请求是否只产生一次效果

三者经常同时需要。

10.2.2 原子更新与带版本条件的更新

首选把简单不变量压进一条 SQL

库存扣减可写成：

UPDATE inventory
SET available = available - $2,
    version = version + 1,
    updated_at = clock_timestamp()
WHERE sku_id = $1
  AND available >= $2
RETURNING available, version;

Read Committed 下两个 writer 针对同一 row：

1. 一个取得 row lock 并更新；
2. 另一个等待；
3. 先行事务提交后，后行事务在新 row version 上重新判断 available >= $2；
4. 满足则从新值继续扣；不满足则影响 0 行。

本章两个请求都满足，结果：

successful writes=2
final available=70
version=2

若库存不足，row_count=0 是业务结果，不是数据库故障。API 要区分：

row returned     → 扣减成功
zero rows        → SKU 不存在或库存不足，需要再查询/细分合同
SQLSTATE error   → transaction 失败
connection lost  → commit outcome 可能未知

若“SKU 不存在”和“库存不足”必须不同响应，可在同事务中做后续只读，或用 function 返回结构化 outcome；不要先无锁查询再假设状态没变。

version compare-and-swap

当 application 必须基于多个字段/复杂规则计算新状态时，用 version 把旧 snapshot 变成显式前置条件：

SELECT available, version
FROM inventory
WHERE sku_id = $1;

-- application computes new_available

UPDATE inventory
SET available = $new_available,
    version = version + 1
WHERE sku_id = $1
  AND version = $observed_version
  AND available >= $quantity
RETURNING available, version;

两个请求都读 version=0 后，只有一个能影响 1 行；另一个得到 0 行：

first round:
  success=1
  optimistic conflict=1
  final=80 or 90 / version=1

loser starts a new transaction:
  re-read value/version
  recompute
  conditional update=1
  final=70 / version=2

version 列本身不提供保护。Lost-update case 也递增了 version，但没有在 WHERE 比较旧版本，因此两个绝对写都成功。CAS 的关键是：

SET version = version + 1
AND WHERE version = observed_version
AND caller treats zero rows as conflict

不要用 system column xmin 代替长期 API version：它受 vacuum/freeze、wraparound、导入和物理生命周期影响，不是业务稳定 token。显式 bigint version 更容易测试和传入 ETag/If-Match。

选择 atomic、CAS 还是 row lock

“乐观一定快”与“悲观一定安全”都不成立。热点冲突下 CAS 反复失败可能比短 row lock 更贵；row lock 包住远程 API 则会把外部延迟放大为数据库队列。

10.2.3 更高隔离级别何时拒绝而不是静默覆盖

Repeatable Read 拒绝 same-row stale write

把 lost-update 两个事务改为 Repeatable Read：

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT available FROM inventory WHERE sku_id = 1001;
-- both snapshots see 100
UPDATE inventory SET available = $computed WHERE sku_id = 1001;
COMMIT;

第一个提交后，第二个不能在旧 snapshot 中修改该 row：

one commit
one SQLSTATE 40001
final 80 or 90 / version=1

这把静默错误转换成显式失败，但业务操作仍未完成。没有正确 retry，用户只会看到 500；若只重试最后一条 UPDATE，旧计算仍不可信。

Serializable 解决 predicate anomaly，不替代重试

RR 对不同 row 的 write skew 不报错；Serializable 才跟踪“双方都读取 on-call predicate，随后各写一行”的危险结构。它会让一个事务 40001，使成功集合保持至少一人 on call。

但 Serializable 不承诺：

• 没有 blocking；
• 没有 deadlock；
• 每个 transaction 都成功；
• 自动重试；
• 外部 API 自动幂等；
• 错误的单事务业务逻辑变正确。

Serializable 只保证：成功提交的事务效果可等价于某个串行顺序。单独运行就会扣错、重复发消息或遗漏条件的 transaction，在 Serializable 中仍会错。

error taxonomy 先于 retry

应用至少分开：

SQLSTATE 是机器合同，message text 只用于人类诊断。driver 要保留原始 SQLSTATE 和 transaction state，不能把所有异常扁平成同一个 DatabaseError 后无限 retry。

用并发测试验收，而不是单线程单测

对每个策略至少运行：

two independent connections
same deterministic initial state
barrier before contested write
explicit isolation level
captured SQLSTATE/row count
serial oracle
final invariant query
worker/session/lock cleanup
repeat with either winner

只有这样才能证明它处理的是 interleaving，而不是单线程 happy path。

延伸阅读

• PostgreSQL 18：Read Committed
• PostgreSQL 18：Repeatable Read
• PostgreSQL 18：UPDATE

上一节：隔离级别与可观察现象 · 返回本章目录 · 下一节：悲观锁与锁队列 · 查看全书目录 · 查看索引中心

10.3 悲观锁与锁队列

悲观锁把冲突变成等待或立即失败：

lock current row/version
  → make database-only decision
  → write
  → commit/rollback releases lock

它适合冲突概率高、临界区短、等待可预算的事务。若锁内包含用户输入、HTTP、支付或消息调用，临界区就不再由数据库控制。

10.3.1 FOR UPDATE、NO KEY UPDATE 与引用关系

四种 row lock mode

SELECT ...
FROM account
WHERE account_id = $1
FOR UPDATE;

PostgreSQL 有四个强度：

同一 transaction 不与自己冲突；它后续可以升级 lock。row lock 通常持有到 transaction 结束。若 lock 是在 savepoint 后取得，ROLLBACK TO SAVEPOINT 会释放该 savepoint 后的 lock。

FOR UPDATE 不只是“更强所以更保险”。它会与 foreign-key 检查取得的 FOR KEY SHARE 冲突，可能无谓阻塞只改非 key 的事务。

NO KEY UPDATE 中的 key 指什么

普通 UPDATE 自动获取：

• 若改变了可用于 foreign key 的唯一 key 列，获取 FOR UPDATE；
• 否则获取 FOR NO KEY UPDATE。

这让子表插入检查父 key 时的 KEY SHARE 可以与父行的非 key 更新共存，却不能与删除/改 key 共存。

“可用于 foreign key 的唯一索引”有具体条件；partial unique 和 expression unique 不属于普通 FK target。不要按列名猜自动 lock mode，遇到争议可在目标版本用 pg_locks/阻塞实验验证。

锁行不等于锁业务谓词

SELECT *
FROM doctor
WHERE on_call
FOR UPDATE;

只锁本次查询返回的实际 rows。并发事务仍可能插入另一条满足 predicate 的 row；空结果更是“没有 row 可锁”。需要保护“目前不存在”或范围 predicate 时，选择：

• unique/exclusion constraint；
• 锁一条稳定 guard row；
• 更强 table lock；
• Serializable SSI；
• 重新建模为单一 authority row。

不能写 SELECT ... FOR UPDATE 后就声称任意跨行不变量已保护。

行锁与普通读

row lock 不阻塞普通 MVCC SELECT；普通 reader 仍读取合适的 committed version。它阻塞的是会修改/删除/取得冲突 row lock 的事务。只有 ACCESS EXCLUSIVE table lock 会阻塞不带 locking clause 的普通 SELECT。

因此“读者没有等”不能证明 holder 没持锁；第 5、8 章都已验证普通 reader 与 waiter 的差别。

10.3.2 NOWAIT、SKIP LOCKED 与任务领取

等待、立即失败还是跳过是 API 决策

默认 locking clause 等待：

SELECT ...
FOR UPDATE;

立即失败：

SELECT ...
FOR UPDATE NOWAIT;
-- SQLSTATE 55P03 lock_not_available

跳过当前无法立即锁定的行：

SELECT ...
FOR UPDATE SKIP LOCKED;

NOWAIT/SKIP LOCKED 只作用于 row-level lock；查询仍会正常取得 ROW SHARE table lock，若 table lock 冲突仍可能等待。需要 table lock 也不等待时，要显式 LOCK ... NOWAIT 并理解更大影响面。

选择由上层合同决定：

SKIP LOCKED 明确返回不一致视图，不适合余额、报表、权限或普通分页。

正确的 queue claim 是锁定并更新同一批

一个常见 pattern：

BEGIN;

WITH picked AS (
    SELECT job_id
    FROM job
    WHERE state = 'queued'
    ORDER BY priority DESC, job_id
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
UPDATE job AS j
SET state = 'running',
    claimed_by = $worker_id,
    claimed_at = clock_timestamp()
FROM picked
WHERE j.job_id = picked.job_id
RETURNING j.*;

COMMIT;

关键点：

• pick 与 state transition 在同一短 transaction；
• 有确定 ordering，但不承诺全局严格公平；
• (state, priority DESC, job_id) 等候选索引需按第 9 章验证；
• batch 有上限；
• worker identity 与 lease/heartbeat 可查询；
• crash 后有 reaper 将过期 running 恢复或重投；
• job handler 自身仍需幂等；
• 结果依赖 RETURNING，不另行猜测领取集合。

本章六个 job：

worker A 先锁 1,2,3 并停在 barrier
worker B 用 SKIP LOCKED 跳过它们，锁 4,5,6
both commit

稳定结果：

two workers × 3
distinct jobs=6
duplicate claims=0

它证明 claim 不重复，不证明任务外部副作用 exactly once。worker 在 commit 后、调用外部系统前后崩溃，仍需要 idempotency/outbox/reconciliation。

ORDER BY 与 locking 的 Read Committed 边界

Read Committed 中，查询可先按 snapshot 排序，再等待某行 lock；等待期间排序列被并发更新后，最终返回顺序可能相对新值失序。若严格按当前值排序并锁定是正确性要求，可以把 locking query 放入子查询，但这可能锁更多行；或提高隔离级别并处理 40001。不要把一个语法改写当无代价修复。

10.3.3 锁顺序、阻塞链与死锁

等待环才是 deadlock

普通 blocking 是一条有根的依赖链：

waiter B → holder A

deadlock 是环：

T1 locks row 1
T2 locks row 2
T1 waits row 2
T2 waits row 1

没有任何事务能自行前进。PostgreSQL 等到 deadlock_timeout 后运行检测，选择一个 victim：

SQLSTATE 40P01 deadlock_detected

victim 的整个 transaction abort；另一事务取得 lock 继续。应用不能假设“自己的第一条 UPDATE 已保留”。

本章两个 worker 先分别锁 row 1/2，再由两个 barrier 同时放行去锁对方。一次稳定结果：

one exit 40P01
one commit
row 1 value=1
row 2 value=1
workers=0

哪个 worker 被选中、检测耗时和 PID 都会变化。

统一 lock order 是首要预防

转账/批量库存等多对象 transaction，应把 key 排序后按相同顺序取得 lock：

SELECT account_id
FROM account
WHERE account_id = ANY($1)
ORDER BY account_id
FOR UPDATE;

所有代码路径、trigger、foreign key cascade 与后台 job 都要遵循同一 order。只修一个 service、另一个 service 反向锁仍会成环。

还要缩短锁持有：

• 进入 transaction 前完成可安全的输入校验；
• transaction 内不调用远程 API；
• 使用合适索引减少被访问/锁定的 rows；
• 限制 batch；
• 设置 request、statement、lock、idle-in-transaction timeout；
• commit/rollback 后再做可重放的外部工作。

lock_timeout 是 statement 等 lock 的预算，不是 transaction deadline；把它全局设得极短会让正常 DDL/写入随机失败。按 transaction family/session 设置，并让应用识别 SQLSTATE。

deadlock 能重试，根因仍要修

如果 transaction 可完整重放，40P01 可以和 40001 一样进入有界 whole-transaction retry。backoff/jitter 能降低再次同时碰撞，但不能替代：

• 统一 lock order；
• 减少 transaction scope；
• 移除外部等待；
• 热点拆分；
• 正确索引；
• 可见的 deadlock log/metric。

若 deadlock 突增，先保存 error detail 中的 process/transaction/SQL 关系和 log_lock_waits 上下文，再改代码。只把 retry 次数从 3 调到 20，会放大数据库负载和用户延迟。

table lock 也可能参与环

所有 DML/DDL 都会自动取得 table-level locks。例如：

UPDATE              → ROW EXCLUSIVE
CREATE INDEX         → SHARE
CREATE INDEX CONCURRENTLY → SHARE UPDATE EXCLUSIVE
ALTER/DROP/TRUNCATE  → 常见 ACCESS EXCLUSIVE

row、transaction ID、relation、advisory 等不同 lockable object 可以共同成环。诊断不能只筛 locktype='tuple'。

延伸阅读

• PostgreSQL 18：Row-Level Locks
• PostgreSQL 18：Deadlocks
• PostgreSQL 18：SELECT Locking Clause
• PostgreSQL 18：Lock Management Settings

上一节：Lost update 不是一句口号 · 返回本章目录 · 下一节：乐观控制、重试与幂等 · 查看全书目录 · 查看索引中心

10.4 乐观控制、重试与幂等

乐观控制不是“不加锁”。UPDATE 和 unique check 最终仍使用 PostgreSQL 并发控制；“乐观”指 application 不预先持有长期 row lock，而在写入时验证前置版本，失败后放弃或重算。

要把三个概念分开：

optimistic concurrency → 旧版本还能不能写？
retry                  → 一个失败事务能不能从头安全重放？
idempotency            → 同一业务请求重放会不会产生第二次效果？

CAS 成功不代表请求不会重复，幂等键存在也不代表任意 transaction error 都该 retry。

10.4.1 版本列、唯一键与条件写入

version 是业务前置条件

DDL：

CREATE TABLE document (
    document_id bigint PRIMARY KEY,
    body jsonb NOT NULL,
    version bigint NOT NULL DEFAULT 0,
    CHECK (version >= 0)
);

读取：

SELECT document_id, body, version
FROM document
WHERE document_id = $1;

条件写：

UPDATE document
SET body = $2,
    version = version + 1
WHERE document_id = $1
  AND version = $3
RETURNING version;

影响一行表示“以我观察到的版本为前提，写入成功”；零行可能是不存在或版本冲突。API 可把 version 暴露为 ETag，并要求 If-Match，但要防止：

• decoder 丢失/默认 version；
• ORM UPDATE 不含 version predicate；
• bulk update 绕过 version；
• trigger 修改却不递增 version；
• conflict 被当 200 success；
• 失败后重复使用旧 application object；
• version 与 tenant/authorization scope 未一起放入 WHERE。

更完整：

UPDATE document
SET body = $body,
    version = version + 1
WHERE tenant_id = $tenant
  AND document_id = $id
  AND version = $expected
RETURNING document_id, version;

authorization predicate 和 concurrency predicate 同时成立，才能写。

unique key 是并发仲裁器

“先查不存在，再插入”有 race：

T1 SELECT none        T2 SELECT none
T1 INSERT             T2 INSERT

真正保证只能有一个 owner 的是 unique constraint/index：

ALTER TABLE payment_request
ADD CONSTRAINT payment_request_idempotency_key
PRIMARY KEY (tenant_id, idempotency_key);

然后用：

INSERT ...
ON CONFLICT (tenant_id, idempotency_key) DO NOTHING
RETURNING ...;

或：

INSERT ...
ON CONFLICT (...) DO UPDATE
SET ...
RETURNING ...;

PostgreSQL 的 ON CONFLICT DO UPDATE 在 Read Committed 下保证每个输入 row 得到 insert 或 update 之一；它不等于业务幂等。若 conflict 分支重复触发审计 trigger、覆盖已完成 response，反而制造第二次效果。

DO NOTHING 也有 snapshot 细节：它可能因另一未在当前 command snapshot 可见的事务结果而不插入。若要读取 winner row，常用两条 statement：

INSERT ... ON CONFLICT DO NOTHING
  → if inserted, create owner result
  → else, next Read Committed statement reads committed owner row

或设计经过验证的 DO UPDATE ... RETURNING，并承担额外 update/trigger/HOT/WAL 语义。不要从网上复制一个“一条 CTE 万能 get-or-create”就默认并发可见性正确。

idempotency key 必须绑定请求语义

表至少保存：

scope/tenant
idempotency key
request fingerprint
operation type
owner aggregate/payment id
terminal/in-progress state
canonical response or response reference
created/expires timestamps

同 key：

• fingerprint 相同 → 等待/读取同一权威结果；
• fingerprint 不同 → 明确 conflict，不能把旧 response 交给不同请求。

fingerprint 应基于 canonical request fields，而不是未规范化 JSON 文本、会变化的 header 或 secret。key scope、长度、熵、认证主体、保留期与重用政策必须写进 API 合同。

10.4.2 重试只包围可重放的事务

40001 后从 transaction function 外重来

伪代码：

deadline = request_deadline
for attempt in 1..max_attempts:
    begin a new transaction
    try:
        read all decision inputs
        recompute from the new snapshot
        write database state and outbox
        commit
        return committed result
    catch SQLSTATE in retryable_set:
        rollback
        if attempt/deadline exhausted: return retry_exhausted
        sleep(exponential_backoff_with_jitter)
    catch anything else:
        rollback
        rethrow

retry loop 必须位于 transaction 外。40001 后当前 transaction 已失败；在同一 transaction 里再发 SQL 只会得到 25P02。savepoint 也不能把一个 serializability failure 局部修复成“事务其余部分仍然基于正确 snapshot”。

whole-transaction replay 的原因：

• 决策 read 已过期；
• query 结果集合可能改变；
• generated ID/sequence 可能已消耗；
• lock order/owner 可能改变；
• application memory 中保存了旧值；
• 第一个 attempt 的响应不能对外承诺。

把事务体封装为无共享可变状态的 function，输入只来自 request/idempotency context，通常更容易证明可重放。

retryable set 要窄且有语义

默认候选：

40001 serialization_failure
40P01 deadlock_detected

40P01 重试同时要修 lock order。其他信号要逐案：

• 55P03：可能按产品语义快速返回 busy，也可能短暂 backoff；
• 23505：通常是业务 owner 已存在，应读取/返回 conflict；
• 57014 query_canceled：可能 request 已取消，不能擅自继续；
• connection failure：commit 结果未知，必须按 idempotency key 查询；
• syntax/permission/check violation：重试不会变好。

禁止：

catch DatabaseError → sleep → retry forever

它会重放永久错误、越过 request deadline、制造 retry storm。

budget 同时约束尝试数与总时间

例如：

max attempts = 4
max total elapsed = 800 ms
base backoff = 10 ms
cap = 150 ms
jitter = full/randomized

数字由 SLO、冲突率和事务成本决定。至少记录：

• attempts histogram；
• success-after-retry；
• exhausted；
• SQLSTATE；
• total retry time；
• transaction family/query identity；
• lock/serialization/deadlock rate；
• request cancellation。

当冲突持续，高 attempt 只把相同热点放大。应转向短 row lock、sharding authority、queueing、批处理或模型重构。

pool/driver 必须保留同一 connection 到结束

一个 transaction 的所有 statement 必须在同一 backend/connection 上。transaction-pooling proxy、异步 driver 和 ORM 需要正确 pin；失败时：

ROLLBACK or discard broken connection
clear local transaction state
do not return idle-in-transaction/failed connection to pool
start retry on a clean transaction

连接断开后不能依据 client 是否收到 COMMIT 响应判断数据库结果。commit 可能已经成功而 ACK 丢失，或根本没提交；业务 idempotency record 才是查询 authority。

10.4.3 支付、消息与外部副作用的边界

数据库不能回滚已经发出的远程请求

危险顺序 A：

BEGIN
  write payment row
  call payment provider  ← provider success
  database ROLLBACK      ← remote charge remains

危险顺序 B：

call provider success
process crashes
database has no durable record
retry calls provider again

把 HTTP 放进 transaction 还会长时间持锁和 snapshot。PostgreSQL two-phase commit 也不会让任意 HTTP/邮件/SaaS 自动加入一个可靠 distributed transaction。

transactional outbox 固定“提交了发送意图”

在同一短数据库 transaction：

BEGIN;

INSERT INTO payment_request (...);

INSERT INTO outbox (
    event_key,
    aggregate_key,
    event_type,
    payload
) VALUES (...);

COMMIT;

然后独立 relay：

claim committed outbox rows
publish with stable event_key
mark delivered / record attempt
retry on failure

数据库原子保证 payment state 与 event intent 同时有/同时无。它不保证 broker 只收到一次：relay 可能 publish 成功后、mark delivered 前崩溃。因此 consumer 也需要 inbox/dedup key 或幂等业务写。

准确表述是：

at-least-once delivery
+ stable event identity
+ idempotent consumer/reconciliation
→ effectively-once business effect within declared scope

不要承诺跨任意系统的神奇 exactly-once。

本章 payment 并发合同

两个 backend 使用：

idempotency_key = idem-order-1001
fingerprint =
  sha256:amount=3000;currency=CNY;merchant=demo

它们各自提出不同 payment ID，在 barrier 放行后并发：

1. INSERT ... ON CONFLICT (idempotency_key) DO NOTHING；
2. winner 在同一 transaction 写一条 outbox；
3. loser的下一条 Read Committed statement 读取 winner record；
4. 两者返回同一个 canonical response。

实测：

requests=2
inserted=1
reused=1
distinct responses=1
payment rows=1
outbox rows=1

让两个 worker 提出不同 payment ID 很重要：idempotency key 才是 arbiter。若同时让另一个 unique payment ID 也相同，冲突可能在错误的 unique constraint 上报 23505，掩盖协议。

随后用同一 key、不同 fingerprint 请求 9999：

SQLSTATE P0001
payment rows remains 1
outbox rows remains 1

P0001 是本实验自定义错误；生产可定义稳定 domain error/SQLSTATE/API 409 contract。核心是不同 payload 不复用旧操作。

in-progress、失败与保留期

真实 payment 还要处理：

owner request still in progress
owner crashed before terminal response
provider timeout with unknown outcome
declined vs retriable provider failure
idempotency record expiry
client retries after expiry
manual reconciliation
refund/compensation

一套常见状态机：

accepted request
  → intent committed
  → provider pending
  → succeeded | declined | unknown
  → reconciled/compensated

同 key caller 读取同一状态，不另起一次 payment。删除 idempotency record 前要保证 provider 和所有下游重投窗口都已过；保留期是财务/合规/容量决定，不是随手 TTL。

延伸阅读

• PostgreSQL 18：INSERT ... ON CONFLICT
• PostgreSQL 18：Serialization Failure Handling
• PostgreSQL 18：Error Codes

上一节：悲观锁与锁队列 · 返回本章目录 · 下一节：咨询锁与跨行协调 · 查看全书目录 · 查看索引中心

10.5 咨询锁与跨行协调

Advisory lock 让应用给一个整数 key 赋予“资源正在被协调”的含义。PostgreSQL lock manager 只知道 key、shared/exclusive、session/transaction lifetime；它不知道这个 key 是 tenant、invoice、cron job 还是部署。

因此 advisory lock 的正确性来自两部分：

PostgreSQL guarantees mutual exclusion for the same key
+
all participants voluntarily use the same key/lifetime/protocol

任何绕过协议的 SQL 仍可修改底层 rows。

10.5.1 会话级与事务级咨询锁

两种 lifetime

transaction-level：

BEGIN;
SELECT pg_advisory_xact_lock(42);
-- protected database work
COMMIT;  -- 自动释放，不能手工提前释放

session-level：

SELECT pg_advisory_lock(42);
-- protected session work
SELECT pg_advisory_unlock(42);

关键差异：

本章真实验证：

pg_advisory_lock(3610,1015)
  → ROLLBACK
  → lock still granted
  → explicit unlock

pg_advisory_xact_lock(3610,1016)
  → COMMIT
  → lock count=0

session lock 在 transaction rollback 后仍存在不是 bug。若 pool 把同一 backend 交给另一请求，它会继承 lock；重复 acquire 还会 stack。除非保护范围确实跨多个 transaction，并有严格 connection pin/unlock/finally，优先 xact lock。

blocking 与 try 版本

等待：

SELECT pg_advisory_xact_lock($key);
SELECT pg_advisory_xact_lock_shared($key);

不等待：

SELECT pg_try_advisory_xact_lock($key);         -- boolean
SELECT pg_try_advisory_xact_lock_shared($key);  -- boolean

session 级也有 pg_advisory_lock*/pg_try_advisory_lock* 与显式 unlock。shared locks 彼此兼容，但与 exclusive 冲突。

选择与 row lock 类似：

• 必须串行且允许等待 → blocking + timeout；
• leader/cron 若已有 owner 就跳过 → try；
• 多 reader、单 writer → shared/exclusive，但协议更难；
• transaction 内数据库不变量 → xact lock；
• 跨事务外部资源 → session lock 只在有明确租约/断连语义时使用。

Advisory lock 也会参与 deadlock detection。两个 transaction 反向取得 advisory keys，同样可能 40P01。

不要让 SQL expression order 偷锁

危险：

SELECT pg_advisory_lock(id)
FROM resource
WHERE id > 12345
LIMIT 100;

SQL 不保证 volatile function 一定在 LIMIT 后只对最终 100 行求值；可能取得超出预期的 locks。先固定子查询：

SELECT pg_advisory_lock(resource.id)
FROM (
    SELECT id
    FROM resource
    WHERE id > 12345
    ORDER BY id
    LIMIT 100
) AS resource;

即使如此，一次取得 100 个 session locks 也需要完整 unlock/failure 设计。更常见的安全选择是逐批 transaction-level lock 或重新设计 queue。

10.5.2 键空间、碰撞与所有权

两种 key 形式互不重叠

PostgreSQL 提供：

one signed bigint
two signed integer values

两个 key space 不重叠。团队应只选一种规范并写出 namespace：

key1 = domain namespace
key2 = stable resource identifier

(100, tenant_id)   tenant maintenance
(200, report_id)   report generation
(300, shard_id)    shard rebalance

本章保留 (3610,1001..1016)，所以 fixture cleanup 能精确查：

SELECT *
FROM pg_locks
WHERE locktype = 'advisory'
  AND classid = 3610::oid
  AND objid BETWEEN 1001::oid AND 1016::oid;

pg_locks 的 classid/objid/objsubid 是 lock manager 编码，不是自带业务字典。runbook 必须能把数字反解为 domain/resource，并保留 application name。

hash 不是无碰撞 identity

把任意字符串压成 64-bit：

hash(tenant || ':' || external_id)

理论上会碰撞。低概率碰撞对“偶尔多串行一次”可能可接受，对“错误资源被授权/跳过”则不可接受。评审：

• 输入 canonicalization；
• tenant/domain 是否进入 key；
• hash algorithm/seed 是否跨语言稳定；
• collision 的错误方向；
• 是否能直接使用无碰撞的 numeric ID；
• mapping 版本升级如何兼容；
• 同一资源的所有 caller 是否实现一致。

不要使用语言运行时每进程随机化的 hash()；不同 process 可能为同一字符串产生不同 key，互斥完全失效。

lock 没有内建 owner metadata

PostgreSQL 记录 backend PID/session、lock mode、key 与 granted，不记录：

业务 owner
request id
lease expiry
why acquired
runbook

这些要来自：

• unique application_name；
• request/job identity；
• transaction/session start；
• companion owner table（若需要 durable lease）；
• structured log/trace；
• documented key dictionary。

session 断开会释放 lock，因此 advisory lock 不是 durable ownership record。需要“进程死后仍知道谁做到哪一步”的任务系统，应把 state/lease/checkpoint 存表，lock 只协调瞬时竞争。

Advisory locks 使用 shared lock memory，受 max_locks_per_transaction 与连接数规模影响；大量不同 keys 不是免费 distributed cache。不要为每个长期对象永久持锁。

timeout 与 cancellation

blocking advisory function obeys session/statement timeout 与 cancellation。生产 transaction family 应设置：

SET LOCAL lock_timeout = '200ms';
SET LOCAL statement_timeout = '2s';

具体预算由 SLO 决定。timeout 后 transaction 进入 failed state，需要 rollback；不能在同一 transaction 当作“没拿到锁，继续无锁执行”。若产品语义是立即跳过，用 pg_try_advisory_xact_lock() 的 boolean 更清晰。

10.5.3 不用咨询锁掩盖缺失的数据约束

能用 constraint 表达的仍交给 constraint

错误：

acquire advisory hash(email)
SELECT whether email exists
INSERT
release

如果任一 importer、migration、另一个 service 忘记拿 lock，就可产生重复。正确 authority：

UNIQUE (normalized_email)

advisory lock 可以减少预期冲突噪声，却不能替代 unique/exclusion/FK/check。

类似：

Advisory lock 适合数据库没有天然 row 可锁、且不变量难以直接约束的短协调，例如：

• 每 tenant 同时只运行一个 schema backfill；
• 同一报表参数只生成一次昂贵结果；
• cron leader 竞争；
• 按 external resource ID 协调，但 durable state 仍存表。

所有路径必须加入协议

若决定使用：

API writer
batch/import
admin script
retry worker
migration
repair/reconciliation

都必须调用同一个 key mapping 和 lifetime wrapper。通过受控数据库 function 封装可降低漂移，但仍要：

• schema-qualify；
• 固定 search_path；
• 处理权限；
• 返回 lock outcome；
• 约束 timeout；
• 写测试证明竞争与释放；
• 保留 constraint 作为可表达不变量的最后 authority。

不在锁内等待外部系统

“先拿 advisory lock，再调用 payment provider”仍会把外部延迟塞进 PostgreSQL lock queue；session 中断还会释放 lock，而 provider 可能已处理。支付应使用 durable idempotency state/outbox/reconciliation，不把 advisory lock 当跨系统 distributed transaction。

若确实协调外部资源，至少有：

durable owner/lease row
fencing token
expiry/renewal
stale-owner recovery
idempotent remote operation
reconciliation

仅有一个 session lock 无法提供 fencing：旧 owner 网络暂停后恢复，可能与新 owner 同时对外部系统操作。

审查问题

每个 advisory lock 提案必须回答：

1. 为什么 row/constraint/Serializable 不能更直接表达？
2. key space 与 collision policy 是什么？
3. shared 还是 exclusive？
4. session 还是 transaction lifetime，为什么？
5. 谁是 owner，如何观察？
6. 等待/try/timeout 语义是什么？
7. error/rollback/pool release 时怎样保证释放？
8. 所有写路径如何遵守？
9. 进程崩溃后 durable state 在哪里？
10. 是否跨外部系统，fencing/idempotency 如何做？

答不全就不是一个可上线协议。

延伸阅读

• PostgreSQL 18：Advisory Locks
• PostgreSQL 18：Advisory Lock Functions
• PostgreSQL 18：pg_locks

上一节：乐观控制、重试与幂等 · 返回本章目录 · 下一节：观察与诊断并发 · 查看全书目录 · 查看索引中心

10.6 观察与诊断并发

并发故障有两个时间尺度：

historical:
  lock/deadlock/rollback/latency metrics + logs + traces

live:
  exact session → wait → blocker graph + transaction age + SQL

指标告诉你“何时、影响多大”，catalog 告诉你“现在谁等谁”。杀会话只会改变 live graph，不会自动解释根因。

10.6.1 pg_stat_activity、pg_locks 与等待事件

state 与 wait_event 是两个维度

SELECT
    pid,
    backend_start,
    xact_start,
    query_start,
    state_change,
    datname,
    usename,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    backend_xid,
    backend_xmin,
    query_id,
    left(query, 500) AS query_sample
FROM pg_stat_activity
WHERE backend_type = 'client backend';

state='active' 只表示 backend 正在执行 query；它仍可能：

active + Lock/transactionid  → 等另一事务结束
active + Lock/relation       → 等 table lock
active + Lock/advisory       → 等 advisory key
active + Client/ClientWrite  → server 等客户端读取
active + IO/...              → I/O wait

idle in transaction 则没有正在执行 query，却仍持有 transaction、snapshot 和 locks；它常比一条 active query 更危险。

完整 query/session 信息需要适当监控权限，例如受控 pg_read_all_stats；不要给普通应用 superuser。query text、参数、client_addr 可能含敏感数据，证据包应脱敏并设置保留期。

pg_locks 是 lockable object 明细

SELECT
    lock.pid,
    activity.application_name,
    lock.locktype,
    lock.mode,
    lock.granted,
    lock.fastpath,
    lock.waitstart,
    lock.relation::regclass AS relation_name,
    lock.page,
    lock.tuple,
    lock.transactionid,
    lock.virtualxid,
    lock.classid,
    lock.objid,
    lock.objsubid
FROM pg_locks AS lock
LEFT JOIN pg_stat_activity AS activity
  ON activity.pid = lock.pid
WHERE lock.database = (
          SELECT oid
          FROM pg_database
          WHERE datname = current_database()
      )
   OR lock.database IS NULL
ORDER BY lock.granted, lock.waitstart, lock.pid;

字段按 locktype 才有意义。relation、transactionid、virtualxid、tuple、advisory、object 等可能共同出现。

row-level lock 的常见观察陷阱：holder 的 row locks 通常不逐行显示在 pg_locks；当另一个 transaction 等该 row 时，它经常表现为等待 holder 的 transaction ID：

wait_event_type=Lock
wait_event=transactionid

所以只搜 locktype='tuple' 会漏掉真实 row blocker。

直接使用 pg_blocking_pids()

手工用 pg_locks 所有 nullable identity columns 做 self join 容易错，也难处理 soft blockers。PostgreSQL 提供：

SELECT
    waiter.pid,
    waiter.backend_start,
    waiter.application_name,
    waiter.wait_event_type,
    waiter.wait_event,
    pg_blocking_pids(waiter.pid) AS blocker_pids
FROM pg_stat_activity AS waiter
WHERE cardinality(pg_blocking_pids(waiter.pid)) > 0;

展开成边：

SELECT
    waiter.pid AS waiter_pid,
    waiter.backend_start AS waiter_epoch,
    waiter.application_name AS waiter_app,
    waiter.wait_event_type,
    waiter.wait_event,
    blocker.pid AS blocker_pid,
    blocker.backend_start AS blocker_epoch,
    blocker.application_name AS blocker_app,
    blocker.state AS blocker_state,
    blocker.xact_start AS blocker_xact_start,
    blocker.query_start AS blocker_query_start
FROM pg_stat_activity AS waiter
CROSS JOIN LATERAL unnest(
    pg_blocking_pids(waiter.pid)
) AS edge(blocker_pid)
LEFT JOIN pg_stat_activity AS blocker
  ON blocker.pid = edge.blocker_pid;

LEFT JOIN 很重要：prepared transaction 可能成为 blocker 却没有普通 backend activity row。遇到 blocker PID/活动缺失，应同时查 prepared transactions 和 lock catalog，而不是假设采样坏了。

一次采样只是瞬间

短等待可能在两次查询之间消失。实时事件要：

• 设置低成本周期采样或 exporter；
• 保存 UTC timestamp；
• 保留 session identity epoch；
• 关联 log/trace/query id；
• 不因某次 snapshot 为空就否定历史 lock spike；
• 不用高频全字段 query 把监控本身变成压力。

本章屏障让 row-lock edge 停住，便于可靠捕获；生产没有这种配合。

10.6.2 从 Pigsty 定位锁等待与长事务

从影响面缩到 exact graph

在 Pigsty v4.5 的当前仪表盘体系中，可按以下顺序：

PGSQL Activity
  sessions/load/active-idle/locks overview

PGSQL Xacts
  transaction rate, rollback, locks, transaction time

PGCAT Locks
  current activity and lock waits from catalog

PGSQL Query / PGCAT Query
  affected query family and statistics

PGLOG Overview / Session
  deadlock, lock wait, timeout and SQLSTATE context

PGSQL Persist / Replication
  long snapshot, WAL, replica side effects

仪表盘名称/布局会随版本变化，以当前 Dashboard 文档 为准，不把截图坐标写进 runbook。

常用时间序列包括：

pg_lock_count{mode=...}
pg_db_deadlocks
pg_db_ixact_time
transaction commit/rollback rate
session state/time
query calls/runtime
WAL and replica lag

具体 metric/label 以当前 Pigsty Metrics reference 为准。counter 要用 rate/increase 并注意 reset epoch；deadlocks=0 的瞬时值不能代表历史从未发生。

先固定四个维度

调查窗口至少固定：

1. cls/ins：哪个 cluster/instance，primary 还是 replica；
2. datname：哪个 database；
3. UTC time range：与用户错误/发布窗口对齐；
4. query/application identity：谁受影响、谁可能持锁。

然后回答：

等待数量/持续时间是否超过 SLO？
是 Lock 还是 Client/IO/其他 wait？
一条 root blocker 还是多条独立冲突？
blocker 是 active、idle in transaction、DDL、autovacuum、
prepared transaction 还是业务 writer？
transaction age 从何时开始？
是否伴随 deployment、batch、schema change、retry storm？

看到 lock count 高不一定是问题：已 granted 的非冲突 locks 很正常。重点是 ungranted wait、阻塞时长、队列扩散和用户 SLI。

40001 不一定在 lock 面板出现

SSI SIReadLock 不造成常规 blocking；serialization failure 可能没有一条长 lock wait 曲线。需要 application/driver 暴露 SQLSTATE 40001 和 retry attempts，并关联：

• transaction family；
• abort/success rate；
• active connections；
• transaction duration；
• query plan/predicate lock 粒度；
• hot key/tenant；
• deploy/version。

同理，deadlock victim 很快被 abort，live graph 已消失；pg_db_deadlocks 与 PostgreSQL log 才保留历史。

transaction age 是放大器

长 transaction：

• 持锁更久；
• 保留 old snapshot；
• 增加 SSI overlap；
• 阻碍 vacuum cleanup；
• 放大 WAL/replication/DDL 等待；
• 让 retry 代价更大。

因此并发性能优化常常不是改 lock mode，而是把 remote call、用户思考、巨大 batch 移出 transaction，并治理 pool 中 idle in transaction。

10.6.3 保存阻塞图，而不是先杀会话

动作前证据包

最小 live artifact：

captured_at UTC
cluster/instance/database
waiter PID + backend_start + user/app/client
waiter state/wait/query/xact/query start
every blocker edge
blocker PID + backend_start + state/query/xact age
relevant pg_locks rows
query_id / normalized query / parameters where safe
deployment/job/request identity
impact/SLO

本章由并发协调器生成的 row-lock-graph.csv，一次关系是：

waiter:
  pg36-ch10-row-lock-waiter
  active / Lock / transactionid

blocker:
  pg36-ch10-row-lock-holder
  active / Lock / advisory

edge count=1

holder 故意等教学 barrier；生产则要问 holder 为何尚未 commit。

找 root blocker，而非随便处理叶子

取消 waiter 只减少一个症状，root blocker 仍可能阻塞几十个请求。应把 graph 沿边向上追到：

no blocker
or cycle/deadlock
or prepared transaction

再按影响与业务 owner 决策。root 也可能是正在执行必须完成的财务事务、migration 或恢复操作；“阻塞最多”不自动等于“应该杀”。

cancel 与 terminate 不同

SELECT pg_cancel_backend($pid);

请求取消当前 query。若 session 在显式 transaction 中，query error 会使 transaction failed，但 client 若不 rollback，仍可能继续占用连接/某些事务资源。

SELECT pg_terminate_backend($pid);

终止整个 backend，未提交 transaction rollback，client 断开。它的影响更大，可能触发应用 retry storm 或留下外部副作用未知状态。

执行前必须重新验证 PID epoch，避免 PID reuse：

SELECT pid, backend_start, datname, usename, application_name
FROM pg_stat_activity
WHERE pid = $pid
  AND backend_start = $captured_epoch
  AND datname = $expected_db
  AND application_name = $expected_app;

还要确认：

• 是否为 autovacuum/background/replication/system backend；
• transaction rollback 的业务影响；
• application 是否会自动 retry；
• external effect 是否 commit-unknown；
• 是否有 owner/incident approval；
• 动作后怎样验收 graph 与数据不变量。

自动化绝不能按 xact_start 最老或 application name 模糊匹配批量 kill。

处理后仍要解释根因

完成止血后保存 after：

edge disappeared
waiter outcomes
rollback/commit
application error/retry
business invariant/reconciliation
remaining workers/locks

再修：

• transaction scope；
• lock order；
• missing index 导致访问过多 rows；
• queue claim；
• external call in transaction；
• timeout/retry storm；
• DDL 发布方式；
• leaked pool connection；
• missing idempotency。

“杀掉 blocker，图空了”只是动作成功，不是问题解决。

延伸阅读

• PostgreSQL 18：Monitoring Database Activity
• PostgreSQL 18：Viewing Locks
• PostgreSQL 18：pg_blocking_pids
• Pigsty：Dashboard
• Pigsty：Metrics

上一节：咨询锁与跨行协调 · 返回本章目录 · 下一节：实战：库存扣减与支付幂等 · 查看全书目录 · 查看索引中心

10.7 实战：库存扣减与支付幂等

本节把并发正确性做成一个机器可验证的矩阵：

same fixture
  × two independent PostgreSQL sessions
  × controlled interleaving
  × explicit isolation/lock strategy
  × SQLSTATE + row count
  × final serial oracle/invariant
  × exact session/advisory cleanup

它不依赖两个终端由人“尽量同时按回车”，也不把 PID、胜者或毫秒写成 golden。

10.7.1 在指定隔离级别重现丢更新和死锁

确认 disposable L1

使用权限受控的 service file：

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

psql -X -w \
  --dbname='service=pg36-admin application_name=pg36-ch10-preflight' \
  --command="
    SELECT current_database(),
           current_user,
           current_setting('server_version'),
           pg_is_in_recovery();
  "

只在已确认可写、可重建的 L1/本地目标继续。脚本会再验证 ch04-v1/ch05 rollback-only 合同。没有 service、database 错误、recovery target、effective role/search_path 不符或 marker collision 都 fail closed。

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

为什么结果可重复

每个 worker 的关键顺序：

BEGIN at declared isolation
  → execute the decision read or acquire first row lock
  → wait on a dedicated advisory barrier

controller 查询 pg_stat_activity，确认所有目标 worker：

state=active
wait_event_type=Lock
wait_event=advisory

才释放 barrier。这样固定“都先读 100”“双方各自先锁一行”等关键 happens-before，不固定谁最终赢。

barrier key 只用两整数空间 (3610,1001..1016)。它是 test harness，不是被测业务机制；case 结束后 namespace 必须为 0。

Lost update

两个 lost-update worker 在 Read Committed：

A reads 100, computes 90
B reads 100, computes 80
barrier release
both absolute UPDATE and commit

一次 raw output：

worker=a/observed=100/qty=10/replacement=90
worker=b/observed=100/qty=20/replacement=80

最终：

requested total=30
serial expected=70
actual=80       # 也可为 90
version=2       # 证明“有 version 列”但不用 predicate 仍无保护

Repeatable Read same-row conflict

同一 interleaving 改为 Repeatable Read worker：

one commit
one psql exit=3
one stderr SQLSTATE=40001
final=80 or 90 / version=1

审查器只要求一条 40001，不绑定 A/B。

Write skew 与 SSI

两个 doctor 都初始 on call。两个 doctor worker 分别关闭自己：

Repeatable Read:
  both read on_call=2
  commits=2
  final on_call=0
  invariant violated

Serializable:
  both read on_call=2
  SIReadLock rows observed >=2
  commits=1
  SQLSTATE 40001=1
  final on_call=1

raw serializable-siread.csv 保存每个 application 的 lock mode、relation/page/tuple 粒度，不把 relation-level 当所有计划的固定粒度。

Deadlock

两个 deadlock worker：

A updates row 1 and waits gate 1011
B updates row 2 and waits gate 1012
controller captures both lock sets
release both
A asks row 2; B asks row 1

稳定断言：

SQLSTATE 40P01=1
commit=1
row values=[1,1]
worker=0

victim 的第一条 UPDATE 被整事务 rollback，幸存者对两行各加 1。

10.7.2 比较原子更新、行锁与可重试事务

四种库存策略的实测对照

atomic-update-worker.sql 的核心：

UPDATE shop_private.ch10_inventory
SET available = available - :qty,
    version = version + 1
WHERE sku_id = 1001
  AND available >= :qty
RETURNING available, version;

optimistic-worker.sql 则比较 observed version。首轮 loser 影响 0 行；协调器识别 loser quantity，启动一个全新 transaction 执行重试，最终 70。

行锁还要保存 blocker edge

holder：

FOR UPDATE sees 100
waits advisory barrier while holding row

waiter：

FOR UPDATE blocks

observer 捕获：

waiter application=pg36-ch10-row-lock-waiter
wait=Lock/transactionid
blocker application=pg36-ch10-row-lock-holder
blocker edge=1

放行后 holder 扣 10 并提交，waiter 锁住新版本 90、再扣 20，最终 70。这个 raw graph 证明 blocking 原因，而不是只看最终正确值。

NOWAIT、SKIP LOCKED 与 advisory lifetime

完整 locking case 还断言：

NOWAIT:
  contested row → SQLSTATE 55P03

SKIP LOCKED:
  worker A holds jobs 1..3
  worker B skips them and claims 4..6
  duplicate=0

advisory:
  session lock survives ROLLBACK until explicit unlock
  xact lock disappears at COMMIT

它们不是互换的优化项：

• NOWAIT 把排队变成明确失败；
• SKIP LOCKED 只适合可替代 queue rows；
• advisory lock 协调 application-defined resource；
• row lock 保护真实 tuple/version。

Payment idempotency 与 outbox

两个 payment worker 使用同一：

idempotency key=idem-order-1001
request fingerprint=sha256:amount=3000;currency=CNY;merchant=demo

但各自提出不同 payment ID。winner：

insert payment
insert matching outbox
commit

loser的 conflict statement 完成后，用下一条 Read Committed statement 读取 winner response。结果：

concurrent requests=2
inserted=1 / reused=1
distinct responses=1
payment=1 / outbox=1

不同 payload 反例 用同 key 请求 amount 9999，必须 P0001，且两张表计数仍为 1。实验只写 outbox，不调用任何外部系统。

10.7.3 用并发测试验证业务不变量并追加规约

全量 evidence

一次 all：

manifest.txt
preflight.txt
setup.txt

lost-*.stdout/stderr
lost-waiting.csv
atomic-*.stdout/stderr
optimistic-*.stdout/stderr
rr-update-*.stdout/stderr
write-skew-*.stdout/stderr
serializable-siread.csv

nowait-*.stdout/stderr
job-*.stdout/stderr
deadlock-*.stdout/stderr
deadlock-before-release.csv
row-lock-*.stdout/stderr
row-lock-graph.csv
advisory-*-gate.stdout/stderr

payment-*.stdout/stderr
concurrency-result.json
verify.txt
review.json
review.txt

manifest.txt 保存 UTC、action、service、client/server/Python version 和所有 source SHA-256。raw artifact 保留动态身份，review 只比较稳定关系。

一次审查摘要：

lost=observed:100+100/expected:70/actual:80
safe=atomic:70/optimistic:1-conflict+1-retry->70
isolation=rr-update:40001/rr-skew:0/serializable:40001->1
locks=55P03/40P01/blocker-edge:1/skip-locked:6-distinct
advisory=session-survives-rollback/xact-released
idempotency=requests:2/payment:1/outbox:1/mismatch:P0001
proposal=0.1.0->0.5.0/DEFAULT-TXNN-007/depends-on-v0.4
final=workers:0/advisory:0/
      checksum:f8a7bfae59c6d16cd323abecfefe1014

DEFAULT-TXNN-007 v0.5 candidate

baseline-v0.5-proposal.json 在原规则“事务只覆盖保持不变量所需的最短边界”上提议追加：

并发写入合同必须声明：
  business invariant
  isolation level or lock strategy
  retryable SQLSTATE
  retry budget
  idempotency key
  external side-effect boundary

提案绑定：

immutable baseline v0.1 canonical checksum
ch09 v0.4 proposal canonical checksum
ch10 source/evidence paths

review.py 每次重算 checksum；依赖漂移、artifact 缺失或 rule id 改变都会 fail。它仍是 candidate，晋升条件包括：

• PostgreSQL 14–18 compatibility；
• 至少一个真实 driver/pool 的断连、timeout、重复投递测试；
• Pigsty L1 下的 abort/lock/tail 指标；
• 先晋升 v0.2–v0.4 依赖链。

实验通过不冒充治理基线已发布。

Reset 与负向安全

all 保留最终 fixture 供复核。删除属于 R2：

cd static/labs/ch10
export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch10/reset-$(date -u +%Y%m%dT%H%M%SZ)"

export PG36_RESET_TOKEN=RESET_CH10_CONCURRENCY_LAB
export PG36_RESET_TARGET=pg36_shop/shop_private/ch10
./task.sh reset

reset 必须同时：

• action token 正确；
• database/schema/chapter target 正确；
• 六张同名对象的 marker 正确；
• 没有活跃 pg36-ch10-* worker。

成功：

status=ok
reset_target=pg36_shop/shop_private/ch10
remaining_ch10_relations=0

然后 ch05 verify 再次证明业务 checksum 不变。错误 token、错误 target、无 service、marker collision 或 active worker 都必须非零退出且不删除对象。

静态与最终复现

bash -n static/labs/ch10/task.sh

PYTHONPYCACHEPREFIX=/tmp/pg36-pycache \
  python3 -m py_compile \
    static/labs/ch10/run_concurrency.py \
    static/labs/ch10/review.py

python3 -m json.tool \
  static/labs/ch10/baseline-v0.5-proposal.json >/dev/null

export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch10/final-$(date -u +%Y%m%dT%H%M%SZ)"
static/labs/ch10/task.sh all
static/labs/ch10/task.sh verify

通过后，团队获得的是可迁移的并发验收模板：每个正确性结论都由至少两个连接、明确 interleaving、机器错误合同和最终不变量共同证明。

上一节：观察与诊断并发 · 返回本章目录 · 下一章：守正出奇：模式变更与安全发布 · 查看全书目录 · 查看索引中心