Who is blocking whom
Chapter 3 explained lock queues and how to read pg_locks; this is the 3 a.m. version. One query, paste it as-is, and it answers the three questions that matter: who is stuck, who is the blocker, and what is the blocker doing?
A updates a row and then… goes to lunch. The transaction stays open.
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1
B> UPDATE accounts SET balance = 300 WHERE id = 1;
⏳ B is waiting for a lock…Someone pages you: 'updates are hanging'. This is the query you paste:
M> SELECT waiter.application_name AS waiter,
waiter.query AS waiting_query,
blocker.application_name AS blocker,
blocker.state AS blocker_state,
blocker.query AS blocker_last_query
FROM pg_stat_activity waiter
JOIN pg_stat_activity blocker ON blocker.pid = ANY (pg_blocking_pids(waiter.pid))
WHERE waiter.wait_event_type = 'Lock';
waiter | waiting_query | blocker | blocker_state | blocker_last_query
--------+------------------------------------------------+---------+---------------------+------------------------------------------------
B | UPDATE accounts SET balance = 300 WHERE id = 1 | A | idle in transaction | UPDATE accounts SET balance = 200 WHERE id = 1
(1 row)The culprit isn't running anything — it's IDLE, holding locks. The fix is blunt:
M> SELECT pg_terminate_backend(pid) AS terminated
FROM pg_stat_activity WHERE application_name = 'A';
terminated
------------
t
(1 row)A's transaction dies and rolls back; B gets the lock and finishes at last.
⏵ B resumes:
UPDATE 1
B> SELECT balance FROM accounts WHERE id = 1; -- A's 200 rolled back with its termination; B's 300 committed
balance
---------
300
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
Reading the answer
The transcript shows the classic production shape: the blocker's state is idle in transaction — it isn't running anything, it's holding things (locks it took that live until COMMIT) while the application forgot about it. blocker_last_query shows the last statement it ran, which is usually all you need to find the guilty code path.
Two escalation levels, straight from the manual:
pg_cancel_backend: "Cancels the current query of the session whose backend process has the specified process ID" — polite, but useless against an idle blocker: there is no current query to cancel.pg_terminate_backend: "Terminates the session whose backend process has the specified process ID" — the whole session dies and its transaction rolls back, which is exactly what the transcript shows freeing the queue.
So the whole investigation is one join worth memorizing: pg_stat_activity waiter × pg_blocking_pids() × pg_stat_activity blocker, which names names in a single round trip. An idle-in-transaction blocker can't be canceled, only terminated, and terminating it rolls back whatever it was holding — but a rollback is always safe, so killing a blocker never corrupts data. The real fix lives in the code that left the transaction open, and the next lesson hunts those sessions down.