Lock queue amplification behind one blocker

Lock queue amplification behind one blocker
Type: incident simulation · Topic: Locks & Transactions · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-02/04-lock-queue-amplification

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: a single transaction held a row lock, and several writers to the
same row queued behind it. The symptom looked like "lots of slow queries", but
they were all waiting on one root blocker — a lock queue, not independent
slowness.

How it was found: pg_stat_activity showed multiple backends `active` with
wait_event_type = 'Lock'; pg_blocking_pids(pid) showed every waiter pointing at
the same blocking PID.

The fix: SELECT pg_terminate_backend(<root_blocker_pid>);
Killing the one root blocker releases the lock and the entire queue drains at
once — there's no need to touch the waiters.

Lesson: when many sessions wait on locks, find the *root* blocker with
pg_blocking_pids and terminate that one. Killing waiters is whack-a-mole; they'll
just re-queue. And don't reach for indexes — this is a lock queue, not a plan.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Several queries hang at once with wait_event_type = 'Lock'. Don't treat them as many separate problems — they're probably one queue behind a single root blocker.
2. Use pg_blocking_pids to see the relationships: SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0. The waiters all point at the same blocker.
3. Terminate the root blocker (not the waiters). pg_terminate_backend(<root_pid>) drains the whole queue at once.