DBAtlas · Database operations atlas

Inspect lock waits in detail by object and lock mode (pg_locks)

PostgreSQLSessions

Command

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;

What it does

For every ungranted lock request (granted = false), shows the target object (table, transaction id, etc.), the requested mode, the blocking PID and its mode, whether the blocker actually holds the lock, and the approximate wait time.

When to use it

To understand which table and lock mode conflict causes the blocking; e.g. a DDL waiting for AccessExclusiveLock, or a row lock showing up as a transactionid wait.

Caution

Because matching uses pg_blocking_pids(), only truly conflicting requests are listed. blocker_holds = false means the blocker does not hold the lock yet but is queued ahead with a conflicting mode (lock queue). locktype = 'transactionid' is a row-level conflict: the waiter waits until the transaction that modified the row ends; the row itself is not visible in pg_locks. pg_locks can contain thousands of rows on large systems; do not run this frequently. waiting_for is an approximation based on state_change.

Open in DBAtlas →

Related