锁与等待

等待中的会话

SELECT pid, usename, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

锁关系

SELECT
  a.pid AS blocked_pid,
  a.query AS blocked_query,
  b.pid AS blocking_pid,
  b.query AS blocking_query
FROM pg_stat_activity a
JOIN pg_locks la ON la.pid = a.pid AND NOT la.granted
JOIN pg_locks lb ON lb.locktype = la.locktype
  AND lb.database IS NOT DISTINCT FROM la.database
  AND lb.relation IS NOT DISTINCT FROM la.relation
  AND lb.page IS NOT DISTINCT FROM la.page
  AND lb.tuple IS NOT DISTINCT FROM la.tuple
  AND lb.virtualxid IS NOT DISTINCT FROM la.virtualxid
  AND lb.transactionid IS NOT DISTINCT FROM la.transactionid
  AND lb.granted
JOIN pg_stat_activity b ON b.pid = lb.pid;

这条用来定位，不是要背下来。很多环境会做成视图。

常见坑

• 会话开了事务然后去喝水，后面的 DDL 全堵
• CREATE INDEX 在旧版本会锁表较久，新版本有 CONCURRENTLY
• 显式 LOCK TABLE 比行锁粗得多

结束会话：

SELECT pg_terminate_backend(12345);

PID 换成你查到的。优先 pg_cancel_backend 取消语句，必要才 terminate。

参考：Locking