[!info] related notes

• 课程 MOC：BodySense 数据库工程深化课程 MOC
• 前置：第 13 课
• 知识：数据库锁

BodySense 数据库第 14 课：Row Lock、Deadlock、Advisory Lock 与 pg_locks

[!abstract] 本课目标 不只是知道“有锁”，而是能画 wait-for graph，解释谁持有什么锁、谁在等谁、为什么形成 deadlock。

从零建立概念：Lock 是对“冲突资源”的协调协议

MVCC 让普通 Reader/Writer 少互相阻塞。

但仍有一些动作必须协调：

两个 transaction 同时修改同一 row
两个流程都想成为某资源唯一 owner
DDL 想修改 table structure
删除 parent 同时有人插 child

Lock 的本质是：

声明我正在以某种方式使用一个资源，让不兼容的其他操作等待或失败。

Blocking / Waiting 是正常机制，不一定是事故

例如：

T1 locks row X
T2 wants conflicting lock on X

T2：

wait

只要 T1 很快 commit：

T2 resumes

这就是正常 lock wait。

事故发生在：

wait too long
too many waiters
unexpected lock scope
deadlock cycle

PostgreSQL 有多层 Lock

常见至少：

Table-level locks
Row-level locks
Page/internal locks
Predicate locks for Serializable
Advisory locks

应用最常直接使用的是 row lock 和 advisory lock；DDL 会让你碰到 table lock。

SELECT ... FOR UPDATE 到底做什么

普通：

SELECT * FROM body_states WHERE user_id=$1;

只是按 snapshot 读取。

FOR UPDATE：

SELECT *
FROM body_states
WHERE user_id=$1
FOR UPDATE;

还会对目标 row 获取 row-level lock，阻止其他 transaction 对同一 row 做不兼容的 lock/update，直到当前 transaction 结束。

典型模式：

BEGIN
↓
SELECT current state FOR UPDATE
↓
validate invariant
↓
write next state
↓
COMMIT

为什么必须在同一个 transaction：

SELECT FOR UPDATE
→ transaction ends
→ lock released
→ later another transaction UPDATE

中间就失去保护。

NOWAIT / SKIP LOCKED

有些场景不想一直等。

NOWAIT

row already locked
→ immediately error

适合 UI/操作想快速告诉用户“资源正被其他流程处理”。

SKIP LOCKED

locked rows
→ skip
→ return other available rows

常用于多个 Worker 从 Job table 竞争领取任务：

SELECT ...
FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;

每个 Worker 可以领取不同 row，而不是全部堵在第一条 pending job。

但它返回的是“不完整视图”，不适合普通业务一致性查询。

Deadlock：不是“一个人等很久”，而是等待图成环

Lock Wait：

T1 holds A
T2 waits A

只要 T1 结束，就能继续。

Deadlock：

T1 holds A
→ waits B held by T2

T2 holds B
→ waits A held by T1

形成 cycle：

T1 → T2 → T1

没有一个 transaction 能自己前进。

PostgreSQL 会检测 deadlock，并 abort 其中一个 transaction。

所以应用必须把 deadlock error 理解成：

一个 transaction 被选为 victim，需要回滚/重试，而不是数据库彻底坏了。

为什么统一 Lock Order 能减少 Deadlock

如果所有流程都：

先 lock smaller user_id
再 lock larger user_id

就很难出现：

T1 A→B
T2 B→A

反向环。

这和经典银行转账按 account ID 排序加锁是同一个原理。

Table Lock 与 Migration

PostgreSQL DDL 会使用不同强度的 table lock。

最强的 ACCESS EXCLUSIVE 会与几乎所有 table access 冲突。

所以线上：

ALTER TABLE
CREATE INDEX
VACUUM FULL

不能只看 SQL 是否正确，还要看：

lock mode
lock duration
table size
concurrent traffic

这与第 5 课 Migration 连起来。

Advisory Lock：数据库替应用保管一把“业务命名锁”

有些冲突没有一条天然 row 可以 FOR UPDATE。

例如：

同一 user 的某类 expensive rebuild
全局 migration coordinator
某业务 aggregate 尚不存在但要先互斥创建

PostgreSQL Advisory Lock 允许应用用整数 key 代表一个业务资源。

重要：

PostgreSQL 不理解这个整数为什么代表 user 42。

Lock identity 映射由应用负责。

Session-level vs Transaction-level Advisory Lock

不同 advisory lock API 有不同生命周期：

session-level
→ connection/session release

transaction-level
→ transaction end auto release

如果连接来自 pool，session-level lock 使用错误尤其危险，因为 connection 会回 pool，被另一个 request 复用。

所以 Web backend 通常更偏向 transaction-scoped advisory lock，除非非常清楚 connection ownership。

如何真正排查 Blocking

pg_stat_activity 可以看到：

pid
state
query
transaction start
wait_event_type
wait_event

pg_locks 可以看到 lock request/held 状态。

常见排查思路：

slow request
→ find waiting backend
→ find lock it waits for
→ find blocking PID
→ inspect blocker query / transaction age

不要直接：

kill random connection

先知道谁是 root blocker。

Lock Timeout / Statement Timeout / Deadlock Detection 不一样

deadlock detection
→ 检测等待环

lock_timeout
→ 等某个 lock 超过预算就失败

statement_timeout
→ 整条 statement 执行超过预算失败

它们保护不同问题。

Production 里通常需要有界等待，而不是让 blocked transaction 无限排队。

1. Row Lock

SELECT ... FOR UPDATE 常用于：

先读取当前状态
→ 阻止其他 transaction 同时修改该 row
→ 在同一 transaction 内安全决定下一状态

BodySense bodyStateLockNextRevision 正是一个很好的真实案例。

2. Table Lock

DDL、VACUUM、CREATE INDEX 等操作会拿不同 table-level lock mode。生产 migration 必须知道自己的 DDL 是否会与在线读写冲突。

3. Deadlock

经典：

T1 locks A → waits B
T2 locks B → waits A

数据库会检测环并 abort 一个 transaction。

最重要的预防方法之一：对多个资源使用稳定一致的 lock order。

4. Advisory Lock

Advisory lock 允许应用把“某个业务资源 ID”映射成数据库协调锁，它不直接绑定某一行。适合不存在自然 row 可锁或需要业务级互斥的场景，但需要严格 owner/lifetime 设计。

5. 观察工具

核心：

pg_stat_activity
pg_locks
wait_event / wait_event_type
blocking PID
query / transaction age

Prediction

[!question]

1. 两个事务都只 UPDATE 不同 row，是否永远不会 deadlock？
2. 为什么固定锁顺序可以减少 deadlock？
3. SELECT FOR UPDATE 是否应该在 transaction 外单独执行？
4. Advisory Lock 是否会自动通过 FK/row relation 理解业务对象？
5. 遇到 lock wait，第一反应为什么不应该只是重启数据库？

[!success]- Answer / 解析 1. 不一定。 如果后续又交叉访问另一行、索引/约束触发其他锁，仍可能形成环。

2. 所有 transaction 都按同一顺序获取资源时，形成 A→B 与 B→A 反向等待环的机会大幅降低。

3. 不应。 Transaction 结束时锁就释放；先锁后另开 transaction 处理失去了保护意义。

4. 不会。 它只是应用定义的整数/键空间协调机制，语义由应用自己维护。

5. 需要先找 blocker、transaction age、SQL 和锁类型；重启只会清现场并可能掩盖根因。

Lab

用两个 psql session 制造一个可控 deadlock；在 abort 前/lock wait 阶段查询 pg_locks + pg_stat_activity。画出 wait-for graph。

BodySense Trace

阅读 bodyStateLockNextRevision：说明为什么先确保 state row 存在，再 FOR UPDATE，以及这个锁保护的 invariant 是什么。

L4 Gate

给定一次锁等待事故快照，能定位 blocker，并提出“缩短事务、统一锁序、索引/constraint、重构冲突粒度”等有证据的修复。