Blocking transaction holds a row lock

Blocking transaction holds a row lock
Type: incident simulation · Topic: Locks & Transactions · Level: L2 · Duration: 10–15 min
Launch: ride postgres start stage-02/01-blocking-transaction

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: one transaction ran an UPDATE on a row and stayed open without
committing, holding the row lock. Every other write to that row queued behind
it — the app "hung" while CPU stayed low, because the work was waiting on a
lock, not running.

How it was found: pg_stat_activity showed a backend `active` with
`wait_event_type = 'Lock'` (the victim) and another sitting in an open
transaction (the blocker); pg_locks (or pg_blocking_pids) tied them together.

The fix: SELECT pg_terminate_backend(<blocker_pid>);
Terminating the blocking backend rolls back its transaction and releases the
lock, so the queued write proceeds immediately.

Lesson: "hung but low CPU" is a locking signature. Find the blocker via
pg_stat_activity / pg_locks / pg_blocking_pids and terminate *that* backend —
don't kill random sessions, and don't reach for an index or ANALYZE. Longer
term, fix the app that left the transaction open and add sane timeouts
(`idle_in_transaction_session_timeout`, `lock_timeout`).

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. CPU is fine but writes hang — that's a lock, not a slow query. Look at pg_stat_activity: who is `active` and `wait_event_type = 'Lock'`, and who holds an open transaction?
2. Join the waiting query to its blocker. `SELECT * FROM pg_locks WHERE NOT granted` shows what's queued; pg_blocking_pids(pid) gives the blocker directly.
3. Free the queue by terminating the blocking backend: SELECT pg_terminate_backend(<blocking_pid>). Don't add indexes or run ANALYZE — this is a lock, not a plan.