How a lock pile-up looks

A lock pile-up rarely starts with something exotic. A transaction updates a row and then waits on the application; a migration asks for ACCESS EXCLUSIVE on a busy table; a report keeps a long SELECT open. Every session that needs a conflicting lock waits, and every session behind those waits too. From outside it looks like the database stopped answering.

PostgreSQL can tell you who blocks whom. pg_blocking_pids(pid) returns the sessions that block a given one, including those that are only ahead of it in the lock queue:

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       now() - query_start   AS waiting_for,
       left(query, 60)       AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

That gives you the edges. Turning them into a tree, finding the root and deciding what to stop is the part you don’t want to do in a terminal on your phone.

What HeapDeck shows

The Locks screen builds the tree in one query over pg_locks, pg_blocking_pids() and pg_stat_activity, refreshed every 5 seconds:

a summary like 4 sessions waiting · 1 chain · longest wait 04:12;
the root blocker card: pid, user@app, state, holding for 06:48 and the statement it is running;
every waiting session under it, with how long it has waited and what for, such as RowExclusiveLock on public.orders.

The Blocked tile on Health reads the same tree, so a red tile and the Locks screen always agree.

Unblocking, with a preview

Cancel root (unblocks 4) says how many waiting sessions you free before you tap it. The confirmation lists their pids and tells you separately when some of them have another blocker as well.

Before anything is sent, HeapDeck reads the chain again. If the root is gone, you see that the chain cleared on its own and nothing is sent. If the number of sessions you would free has changed, the sheet stays open with The chain changed while you were deciding and the new numbers.

Which action to pick depends on the root:

Cancel calls pg_cancel_backend(): the running statement stops, the session and its transaction stay open.
Terminate calls pg_terminate_backend(): the session ends and its transaction rolls back. For a root that is idle in transaction, this is the action that releases its locks, because there is no running statement to cancel. Terminating a root asks you to type its pid first.

Both actions are part of HeapDeck Pro. On the free version you can open the sheet and see exactly what would happen.

Deadlocks

When sessions wait on each other in a circle, Locks shows These sessions are waiting on each other instead of a tree. That is a deadlock, and PostgreSQL resolves it on its own by cancelling one of the sessions, usually within a second (deadlock_timeout).

Without superuser

The tree is built for any role. Without pg_monitor, PostgreSQL hides the SQL of other users’ sessions; HeapDeck shows the tree anyway, labels the hidden statements and offers the GRANT pg_monitor to fix it. To cancel sessions of other roles you need the usual rights, such as membership in pg_signal_backend.

For the story of how a single ALTER TABLE can stop every query on a table, see why one ALTER TABLE can block a whole table.