Forgotten advisory lock blocks jobs

Forgotten advisory lock blocks jobs
Type: incident simulation · Topic: Locks & Transactions · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-02/07-advisory-lock-forgotten

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: a worker took a session-level advisory lock (pg_advisory_lock) to
serialize a job, then never released it (it stayed connected and idle). Other
workers calling pg_advisory_lock on the same key blocked forever. There were no
row or table locks to explain it — only an advisory lock.

How it was found: pg_locks showed locktype = 'advisory'; pg_blocking_pids tied
the waiting workers to the one holder.

The fix: SELECT pg_terminate_backend(<holder_pid>);
A session-level advisory lock is only released when its session ends (or it
calls pg_advisory_unlock in the *same* session). Terminating the holder releases
it and the waiters proceed.

Lesson: advisory locks are easy to leak. Use pg_advisory_xact_lock (auto-released
at end of transaction) or guarantee unlock in the same session. When one is
stuck, find the holder via pg_locks/pg_blocking_pids and end that session — don't
kill the waiters.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Jobs are stuck but there's no row/table lock to explain it. Look for advisory locks: SELECT * FROM pg_locks WHERE locktype = 'advisory'.
2. Tie waiters to the holder: SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0. The blocker took a session-level advisory lock and never released it.
3. A session-level advisory lock is released by ending that session. Terminate the holder: pg_terminate_backend(<holder_pid>). (pg_advisory_unlock from another session won't release it.)