Lock queues
Waiting requests can block later requests as well as requests held outright. This schedule follows three updates of one row and observes B completing before C. It does not establish universal FIFO fairness or a throughput bound.
Observed order: B completes before C
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. B completes while C is observed waiting again.
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.6 · Run it yourself · Scenario source
Reading the transcript
M first observes B and C waiting. After A commits, B's update completes; the runner waits until C is again observed waiting, and M sees C as the remaining waiter. After B commits, C finishes and the asserted balance is 211. The initial observation asserts waiter names, not the contents of blocked_by; it does not independently assert the initial B-to-A and C-to-B edges.
pg_blocking_pids() reports immediate hard blockers (conflicting locks held) and soft blockers (conflicting requests ahead in the queue). It does not recursively return every ancestor. Walk the returned relationships to find the root of a longer chain. The monitoring lesson explains a direct-edge query; the DDL lesson asserts a two-edge chain.