Finding the blocker · PostgreSQL

At three in the morning the question is never “what lock modes conflict”. It is “which session do I need to deal with”, and getting that wrong costs another twenty minutes.

The one query

pg_blocking_pids(pid) returns the array of process ids blocking a given backend. It handles the cases a hand-written pg_locks self-join gets wrong, and it is the right foundation.

SELECT a.pid,
       a.usename,
       a.application_name,
       a.state,
       now() - a.xact_start          AS xact_duration,
       now() - a.state_change        AS in_state_for,
       a.wait_event_type,
       a.wait_event,
       pg_blocking_pids(a.pid)       AS blocked_by,
       left(a.query, 80)             AS query
  FROM pg_stat_activity a
 WHERE a.backend_type = 'client backend'
   AND (cardinality(pg_blocking_pids(a.pid)) > 0
        OR a.pid = ANY (SELECT unnest(pg_blocking_pids(b.pid))
                          FROM pg_stat_activity b))
 ORDER BY cardinality(pg_blocking_pids(a.pid)), a.xact_start;

That returns everyone involved: the waiters and the sessions they are waiting on. Ordering by the number of blockers puts the root — which waits on nobody — at the top.

Why the first pid you find is usually the wrong one

Read-only / Safea three-session chain, measured— Captured on 18.6. A running SELECT, an ALTER TABLE waiting for it, and a second SELECT arriving after the ALTER.

$ psql -U postgres -c "SELECT a.pid, a.state, l.mode, l.granted, left(a.query,42) AS query, pg_blocking_pids(a.pid) AS blocked_by FROM pg_locks l JOIN pg_stat_activity a ON a.pid=l.pid JOIN pg_class c ON c.oid=l.relation WHERE c.relname='lockdemo' ORDER BY a.pid"

 pid  | state  |        mode         | granted |                   query                    | blocked_by
------+--------+---------------------+---------+--------------------------------------------+------------
1615 | active | AccessShareLock     | t       | SELECT pg_sleep(20);                       | {}
1622 | active | AccessExclusiveLock | f       | ALTER TABLE lockdemo ADD COLUMN note text; | {1615}
1629 | active | AccessShareLock     | f       | SELECT count(*) FROM lockdemo;             | {1622}
(3 rows)

The application reports that SELECT count(*) is hanging. Investigating that session, pid 1629, shows it blocked by 1622 — the ALTER TABLE.

But 1622 holds no lock. Its granted column is false. Terminating it frees 1629, and the migration has failed for no reason while pid 1615 carries on and blocks the next attempt.

The session that must be dealt with is 1615, which appears nowhere in 1629’s blocked_by. Chains have to be walked.

Walking the chain to the root

WITH RECURSIVE chain AS (
    SELECT a.pid,
           pg_blocking_pids(a.pid) AS blockers,
           1                        AS depth,
           ARRAY[a.pid]             AS path
      FROM pg_stat_activity a
     WHERE cardinality(pg_blocking_pids(a.pid)) > 0

    UNION ALL

    SELECT b.pid,
           pg_blocking_pids(b.pid),
           c.depth + 1,
           c.path || b.pid
      FROM chain c
      CROSS JOIN LATERAL unnest(c.blockers) AS blocker(pid)
      JOIN pg_stat_activity b ON b.pid = blocker.pid
     WHERE NOT b.pid = ANY (c.path)
       AND c.depth < 20
)
SELECT DISTINCT ON (chain.pid)
       chain.pid,
       chain.depth,
       cardinality(chain.blockers) AS blocker_count,
       a.state,
       now() - a.xact_start        AS xact_duration,
       a.wait_event_type, a.wait_event,
       left(a.query, 70)           AS query
  FROM chain
  JOIN pg_stat_activity a ON a.pid = chain.pid
 ORDER BY chain.pid, chain.depth DESC;

The rows with blocker_count = 0 are the roots. Those are the sessions worth acting on.

Reading the root

Once you have the root, pg_stat_activity tells you what kind of problem you have.

`state`	Meaning	Typical action
`active`, recent `xact_start`	Doing real work	Wait, or cancel if it is a runaway
`active`, very old `xact_start`	Long-running query	Decide deliberately; lesson IX-06
`idle in transaction`	Client walked away	Terminate. It is not doing anything
`idle in transaction (aborted)`	Errored, client has not rolled back	Terminate. Holds nothing but a slot
`idle`	Not in a transaction	It is not the blocker. Look again

wait_event_type and wait_event say what the root itself is waiting on, and a root that is waiting on IO or Client is a different problem from one that is computing.

Turn on log_lock_waits, permanently

ALTER SYSTEM SET log_lock_waits = 'on';
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

log_lock_waits defaults to off. With it on, any session that waits longer than deadlock_timeout for a lock produces a log entry naming the waiter, the blocker, the lock mode and the statements.

The cost is one log line per lock wait exceeding one second, which on a healthy system is close to nothing. The benefit is that after an incident you can reconstruct what happened — as opposed to knowing only that the application timed out.

deadlock_timeout does double duty here: it is both when the deadlock detector runs and the threshold for this logging. Lowering it makes both more sensitive at the cost of more frequent deadlock checks.

Also worth setting so the log is usable:

ALTER SYSTEM SET log_min_duration_statement = '1s';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d/%a ';

%a is the application name, which is what lets you attribute a blocking session to a service rather than to a pid that no longer exists.

Cases where the root holds no lock

Three situations where the chain leads somewhere unexpected.

The root is idle in transaction. It holds locks from statements that have already finished. pg_stat_activity.query shows its last statement, not what it is doing, and reading that query as the cause is a standard misdiagnosis.

The root is waiting on something that is not a lock. A session blocked on IO, or on Client while the application fails to read its results, still holds everything it has acquired. wait_event_type says which.

The blocker is not a client backend at all. An autovacuum worker holds SHARE UPDATE EXCLUSIVE and blocks DDL; an anti-wraparound worker does not yield, as lesson VIII-05 covers. Filtering pg_stat_activity to backend_type = 'client backend' hides these, which is why the query above applies that filter only to the waiters.

Why pg_blocking_pids is expensive, and what to do about that

pg_blocking_pids() is not a lookup. For each call it acquires the lock manager’s partition locks and walks the wait graph, and calling it once per row of pg_stat_activity means doing that once per session.

The documentation is explicit that it is intended for diagnostics rather than for frequent monitoring, and the reason is exactly this: on a cluster with a thousand connections during a locking incident, a dashboard calling it every five seconds is adding contention to the lock manager at the moment it is most contended.

Two practical consequences.

Do not put it in a five-second monitoring loop. Alert on the cheap signal — wait_event_type = 'Lock' in pg_stat_activity, which is a plain read of shared memory — and run the expensive chain query only when investigating.

SELECT count(*) AS sessions_waiting_on_locks
  FROM pg_stat_activity
 WHERE wait_event_type = 'Lock';

The snapshot is not atomic. The wait graph can change between the rows of a single result set, so a chain query can return a picture that never quite existed. In practice this matters rarely, because incidents that need this query tend to be stable for minutes. It matters when you are chasing something intermittent, and then the log — populated by log_lock_waits — is the more reliable record, because each entry was written from a consistent view at the moment the wait was detected.

What to take from this

pg_blocking_pids() is the right primitive. Walk it recursively to find roots.
Include a cycle guard and a depth limit. Deadlocks make an unguarded recursive query hang.
The first blocker in a chain is often itself waiting. Act on the root, not on the nearest name.
log_lock_waits = on costs nothing on a healthy system and is the only way to reconstruct an incident afterwards.
Alert on wait_event_type = 'Lock'; investigate with pg_blocking_pids.

Cross-course references

Linux for Production Sysadmins — Part LXXIX (Troubleshooting methodology) covers following a dependency chain to its head rather than acting on the loudest symptom, and Part LXXXI (Incident command) covers who authorises terminating the session at the head of it.
Observability for Production Sysadmins — Part CIX (Incident investigation workflows) covers capturing the blocking tree before clearing it, because it does not exist afterwards.