PostgreSQL — 查询堆积等待锁

用 pg_locks 连接查询诊断 PostgreSQL 中被长持锁者阻塞的语句，找到阻塞源查询与会话，并修复持锁模式。

症状

　　堆积有独特形状：多个会话处于 wait_event Lock、共享同一个阻塞 pid、等待起点是同一时刻——一个长持锁者变成一堆无关查询的排队。用 blocked/blocker 连接查询点名双方：一个 blocker、state 为 idle in transaction、xact_start 是十分钟前——最经典的元凶：持着锁却什么都没做。还有一条会惊到所有人的队列规则：PostgreSQL 锁队列按模式严格 FIFO，一个等待请求会挡住它后面的一切，包括与持有者本可兼容的请求——这就是“为什么我不冲突也要等”的答案。

诊断思路

　　先用 pg_stat_activity 自连接（或 pg_blocking_pids）找等待者与阻塞者对，确认单阻塞源多等待者的形态；再查 pg_locks 的 not granted 行与同关系的 granted 行对比，弄清头部队持有的模式；最后检查持有者会话状态——idle in transaction 加陈旧 xact_start 是铁证。迁移在长事务里跑 ALTER TABLE、事务内做慢速外部调用、会话遗留 idle in transaction，是三大入口。

根因

　　PostgreSQL 锁队列按锁模式严格 FIFO：一个等待请求阻塞它之后的一切，即便后来者的模式只与等待者冲突而不与持有者冲突。一个长跑的持有者因此放大成一堆积压的无关查询。

修复与验证

　　短期有意识地清空队头：pg_cancel_backend 取消当前语句（优先），pg_terminate_backend 终结会话（最后手段，会回滚其工作）。长期改变持锁模式：慢工作（HTTP 调用、sleep、文件 IO）移出事务，毫秒级的更新才在锁内；维护路径设 lock_timeout 与 statement_timeout，ALTER 失败快于排队一切；给超过 60 秒的 idle in transaction 配看门狗告警。验证时把慢工作移出事务后重放负载，断言不再出现 Lock 等待堆积、负载下队列深度有界。

代码示例

SELECT w.pid            AS waiting_pid,
       w.query          AS waiting_query,
       w.wait_event_type,
       b.pid            AS blocker_pid,
       b.state         AS blocker_state,
       b.xact_start    AS blocker_xact_start,
       b.query         AS blocker_query
FROM pg_stat_activity w
LEFT JOIN pg_stat_activity b
  ON w.waiting;  -- or use pg_blocking_pids(w.pid)
WHERE w.wait_event_type = 'Lock';

SELECT pg_cancel_backend(blocker_pid);    -- cancel current statement
SELECT pg_terminate_backend(blocker_pid); -- last resort

# slow work OUTSIDE the transaction:
data = fetch_from_api()          # seconds, no locks held
conn.begin()
apply_update(conn, data)         # milliseconds, locks held briefly
conn.commit()

SET lock_timeout = '5s';
SET statement_timeout = '30s';
ALTER TABLE orders ... ;   -- fails fast instead of queueing everything