按对象和锁模式详细检查锁等待（pg_locks）

命令

SELECT DISTINCT
       w.pid AS waiting_pid,
       w.locktype,
       coalesce(w.relation::regclass::text, 'xid ' || w.transactionid, w.virtualxid, w.objid::text) AS target,
       w.mode AS wanted_mode,
       b.pid AS blocker_pid,
       b.mode AS blocker_mode,
       b.granted AS blocker_holds,
       now() - wa.state_change AS waiting_for,
       left(wa.query, 60) AS waiting_query,
       left(ba.query, 60) AS blocker_query
FROM pg_locks w
JOIN pg_locks b
  ON b.pid = ANY (pg_blocking_pids(w.pid))
 AND b.locktype = w.locktype
 AND b.database IS NOT DISTINCT FROM w.database
 AND b.relation IS NOT DISTINCT FROM w.relation
 AND b.page IS NOT DISTINCT FROM w.page
 AND b.tuple IS NOT DISTINCT FROM w.tuple
 AND b.virtualxid IS NOT DISTINCT FROM w.virtualxid
 AND b.transactionid IS NOT DISTINCT FROM w.transactionid
 AND b.classid IS NOT DISTINCT FROM w.classid
 AND b.objid IS NOT DISTINCT FROM w.objid
 AND b.objsubid IS NOT DISTINCT FROM w.objsubid
JOIN pg_stat_activity wa ON wa.pid = w.pid
JOIN pg_stat_activity ba ON ba.pid = b.pid
WHERE NOT w.granted
ORDER BY waiting_for DESC, waiting_pid;

作用

对每个未授予的锁请求（granted = false），显示目标对象（表、事务 ID 等）、请求的模式、阻塞者 PID 及其模式、阻塞者是否实际持有该锁，以及大致的等待时间。

适用场景

用于了解是哪个表和哪种锁模式冲突导致了阻塞；例如等待 AccessExclusiveLock 的 DDL，或表现为 transactionid 等待的行锁。

注意

由于匹配使用 pg_blocking_pids()，只会列出真正冲突的请求。blocker_holds = false 表示阻塞者尚未持有该锁，但以冲突模式排在前面（锁队列）。locktype = 'transactionid' 是行级冲突：等待者会一直等到修改该行的事务结束；行本身在 pg_locks 中不可见。在大型系统上 pg_locks 可能包含数千行；不要频繁运行。waiting_for 是基于 state_change 的近似值。