Lock contention and blocking trees

Locks queue, and the queue is fair

Postgres takes a table-level lock for every statement, in one of eight modes. A SELECT takes ACCESS SHARE, an INSERT or UPDATE takes ROW EXCLUSIVE, vacuum and CREATE INDEX CONCURRENTLY take SHARE UPDATE EXCLUSIVE, and most forms of ALTER TABLE take ACCESS EXCLUSIVE, which conflicts with every other mode including the one plain reads use.

Which modes conflict is well documented and rarely the surprise. The surprise is the queue. Lock requests are granted in order, and a request that cannot be granted blocks the ones behind it even if those would have been compatible with what is currently held. So an ALTER TABLE that waits behind one long-running SELECT does not merely wait. It parks itself at the head of the queue, and every subsequent read of that table stacks up behind it. Within seconds a change you expected to take a millisecond has stopped all traffic to the table, and the thing at the root of it is a reporting query nobody thought about.

That asymmetry is why lock incidents escalate so quickly and why the session you need to find is almost never the one generating the most alerts.

Finding who is waiting on whom

pg_blocking_pids() does the hard part. It takes a process ID and returns the array of process IDs that are stopping it from acquiring a lock, which is more accurate than reconstructing the same answer by joining pg_locks to itself, because it understands lock groups and parallel workers.

SELECT waiter.pid                                  AS waiting_pid,
       waiter.wait_event_type,
       waiter.wait_event,
       now() - waiter.query_start                  AS waiting_for,
       left(waiter.query, 60)                      AS waiting_query,
       blocker.pid                                 AS blocking_pid,
       blocker.state                               AS blocker_state,
       now() - blocker.xact_start                  AS blocker_xact_age,
       left(blocker.query, 60)                     AS blocker_query
FROM pg_stat_activity waiter
JOIN LATERAL unnest(pg_blocking_pids(waiter.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocker ON blocker.pid = b.pid
ORDER BY waiting_for DESC;

Read blocker_state carefully. If it says active, the blocker is doing work and the question is why that work is slow. If it says idle in transaction, the blocker is doing nothing at all: it acquired a lock, the application moved on, and the transaction was never closed. Those need opposite responses and the flat list above is the fastest way to tell them apart.

Walking the tree to the root

The flat query shows edges. When the chain is more than one deep, you want the root, and a recursive query gets there:

WITH RECURSIVE tree AS (
    SELECT a.pid,
           a.pid            AS root_pid,
           0                AS depth
    FROM pg_stat_activity a
    WHERE cardinality(pg_blocking_pids(a.pid)) = 0
      AND EXISTS (SELECT 1 FROM pg_stat_activity w
                  WHERE a.pid = ANY (pg_blocking_pids(w.pid)))
  UNION ALL
    SELECT w.pid, t.root_pid, t.depth + 1
    FROM tree t
    JOIN pg_stat_activity w ON t.pid = ANY (pg_blocking_pids(w.pid))
    WHERE t.depth < 10
)
SELECT t.root_pid,
       t.depth,
       t.pid,
       a.state,
       a.wait_event_type,
       a.wait_event,
       now() - a.xact_start AS xact_age,
       a.application_name,
       left(a.query, 60)    AS query
FROM tree t
JOIN pg_stat_activity a ON a.pid = t.pid
ORDER BY t.root_pid, t.depth, t.pid;

The anchor selects sessions that block somebody and are themselves blocked by nobody, which is the definition of a root. The depth guard is there because a genuine lock cycle would otherwise recurse forever; in practice the deadlock detector breaks those, but a query you run during an incident should not be able to hang.

Everything at depth zero is a candidate for cancellation. Everything below it will clear on its own once the root does, and cancelling a session halfway down the tree achieves nothing except losing that session’s work.

What the lock actually is

pg_blocking_pids() tells you there is a conflict without saying over what. pg_locks says:

SELECT l.pid,
       l.locktype,
       l.mode,
       l.granted,
       l.waitstart,
       CASE l.locktype
         WHEN 'relation'      THEN l.relation::regclass::text
         WHEN 'transactionid' THEN l.transactionid::text
         WHEN 'tuple'         THEN l.relation::regclass || ' page ' || l.page
         ELSE coalesce(l.objid::text, '')
       END AS target
FROM pg_locks l
WHERE l.pid IN (1234, 5678)
ORDER BY l.pid, l.granted DESC;

locktype is the real diagnosis. relation with mode ACCESS EXCLUSIVE is a schema change. transactionid means one transaction is waiting for another to finish because both tried to modify the same row, which is ordinary row contention and not a schema problem at all. tuple is the short-lived lock taken while queuing for a row and usually means the row above it is very hot. advisory means your application took the lock deliberately and the bug is there rather than here. waitstart, available since PostgreSQL 14, is when the wait began, which beats inferring it from query_start on a session that has run several statements in the same transaction.

A wait event name that means nothing to you is worth looking up rather than guessing at, and from PostgreSQL 17 the server carries a catalog of every one of them with its description that joins straight onto the query above.

Not causing it in the first place

Most lock incidents are self-inflicted by a migration, and the fixes are well understood.

Take lock_timeout before any DDL, so that a statement which cannot get its lock quickly gives up instead of building a queue behind itself. A few seconds is generous:

SET lock_timeout = '3s';
ALTER TABLE public.orders ADD COLUMN notes text;

If it fails, retry it. A retry loop with a short timeout completes far sooner than one attempt with an unbounded wait, and it never stops traffic while it tries.

Build indexes with CREATE INDEX CONCURRENTLY, which takes SHARE UPDATE EXCLUSIVE instead of SHARE and lets writes continue. It cannot run inside a transaction block, it takes two passes over the table, and it can leave an invalid index behind if it fails, which is dealt with in unused and missing indexes.

Keep transactions short, and specifically keep application logic out of them. The gap between “we opened a transaction” and “we called an external service” is where idle in transaction sessions come from, and behind a pooler the damage spreads further than the one table, as described in connection pooling and PgBouncer.

Finally, turn on log_lock_waits. It defaults to off, including on PostgreSQL 18, and switching it on means any wait longer than deadlock_timeout leaves a log line naming the blocked statement, the lock it wanted and the process holding it. That is a complete post-mortem of a lock incident for the cost of one setting, and without it you are relying on someone having been logged in with the query above open at the moment it happened.

Deadlocks are the special case where the detector resolves things for you by aborting one transaction after deadlock_timeout. A rising deadlocks count in pg_stat_database is not a database problem to tune; it means two code paths acquire the same rows in different orders, and the fix is to make them agree on an order.