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

LLMS 索引： [llms.txt](/llms.txt)

---

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

```text
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 在指定隔离级别重现丢更新和死锁 {#item-10-7-1}

### 确认 disposable L1

使用权限受控的 service file：

```bash
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。

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

### 为什么结果可重复

每个 worker 的关键顺序：

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

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

```text
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](/labs/ch10/lost-update-worker.sql) 在 Read Committed：

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

一次 raw output：

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

最终：

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

### Repeatable Read same-row conflict

同一 interleaving 改为 [Repeatable Read worker](/labs/ch10/repeatable-update-worker.sql)：

```text
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](/labs/ch10/doctor-worker.sql) 分别关闭自己：

```text
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](/labs/ch10/deadlock-worker.sql)：

```text
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
```

稳定断言：

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

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

## 10.7.2 比较原子更新、行锁与可重试事务 {#item-10-7-2}

### 四种库存策略的实测对照

| 策略 | 两请求首轮 | 最终 | application 责任 |
|---|---|---|---|
| RC 绝对值写回 | 2 commit | 80/90，错误 | 禁止该 pattern |
| RC 原子条件 UPDATE | 2 commit | 70/version2 | 解释 zero rows |
| RC version CAS | 1 success + 1 zero-row conflict | 首轮80/90；重算后70/version2 | whole operation re-read/recompute |
| row `FOR UPDATE` | waiter blocking | holder 后 waiter 见90，最终70 | 短事务、timeout、lock order |
| RR stale row write | 1 commit + 1×40001 | 80/90/version1 | whole transaction retry |

[atomic-update-worker.sql](/labs/ch10/atomic-update-worker.sql) 的核心：

```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](/labs/ch10/optimistic-worker.sql) 则比较 observed version。首轮 loser 影响 0 行；协调器识别 loser quantity，启动一个全新 transaction 执行[重试](/labs/ch10/optimistic-retry.sql)，最终 70。

### 行锁还要保存 blocker edge

holder：

```text
FOR UPDATE sees 100
waits advisory barrier while holding row
```

waiter：

```text
FOR UPDATE blocks
```

observer 捕获：

```text
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 还断言：

```text
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](/labs/ch10/payment-worker.sql) 使用同一：

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

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

```text
insert payment
insert matching outbox
commit
```

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

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

[不同 payload 反例](/labs/ch10/payment-mismatch.sql) 用同 key 请求 amount 9999，必须 P0001，且两张表计数仍为 1。实验只写 outbox，不调用任何外部系统。

## 10.7.3 用并发测试验证业务不变量并追加规约 {#item-10-7-3}

### 全量 evidence

一次 `all`：

```text
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 只比较稳定关系。

一次审查摘要：

```text
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](/labs/ch10/baseline-v0.5-proposal.json) 在原规则“事务只覆盖保持不变量所需的最短边界”上提议追加：

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

提案绑定：

```text
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：

```bash
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。

成功：

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

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

### 静态与最终复现

```bash
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、机器错误合同和最终不变量共同证明。

---

[上一节：观察与诊断并发](../06/) · [返回本章目录](../) · [下一章：守正出奇：模式变更与安全发布](/schema-change-release/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)