Lock queues
A lock that's taken is only half the story. The other half is the queue that forms behind it, because that queue — not the lock itself — is what turns one slow transaction into a site-wide incident. You'll see the extreme version in DDL outages; here's the mechanism on a single hot row.
First come, first locked
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = balance + 1 WHERE id = 1;
UPDATE 1
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = balance + 10 WHERE id = 1;
⏳ B is waiting for a lock…
C> UPDATE accounts SET balance = balance + 100 WHERE id = 1;
⏳ C is waiting for a lock…A fourth session, M, can watch the pile-up in pg_stat_activity.
M> SELECT application_name AS waiting, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY application_name;
waiting | blocked_by
---------+------------
B | {pid(A)}
C | {pid(B)}
(2 rows)A commits. The lock goes to B — the head of the queue — not to C.
A> COMMIT;
COMMIT
⏵ B resumes:
UPDATE 1
M> SELECT application_name AS waiting, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY application_name
-- C is still in line, now behind B
waiting | blocked_by
---------+------------
C | {pid(B)}
(1 row)
B> COMMIT;
COMMIT
⏵ C resumes:
UPDATE 1
C> SELECT balance FROM accounts WHERE id = 1; -- 100 + 1 + 10 + 100 — every update landed, in queue order
balance
---------
211
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
Reading the transcript
Two details in M's output are worth a second look.
The first: C waits on B, not on A. When B joined the queue it claimed a tuple lock — its ticket for first place in line — and only then settled in to wait for A's transaction to end. Everyone who arrives after B queues on that tuple lock, so the wait relationships form a chain, not a star. To find the transaction that's actually holding things up you may have to walk several hops (the monitoring lesson has the query).
The second: pg_blocking_pids() does the hard walk for you. It answers "who is this backend waiting for?" directly, including sessions that merely wait ahead in the queue rather than holding a conflicting lock outright — the manual calls those soft blocks. No manual pg_locks archaeology required.
The rule underneath all of this is arrival order. When a row lock is released it goes to the head of the queue, and everyone else keeps waiting behind the new holder. That's why queue time compounds: three 100 ms transactions serialized on one row take 300 ms, and a genuinely hot row — a counter, an account balance, a "singleton" config row — is a throughput ceiling by design. When you need to see a pile-up as it happens, pg_stat_activity.wait_event_type = 'Lock' paired with pg_blocking_pids() is the fastest look, which is exactly where the monitoring lesson picks up.