Idle in transaction holds a lock

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

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: a session opened a transaction, ran an UPDATE, and then went idle
without committing — `idle in transaction`. It wasn't running any query, so it
was easy to miss, but it kept the row lock and an old snapshot the whole time,
blocking other writers and holding back vacuum/cleanup.

How it was found: pg_stat_activity showed a backend with state =
'idle in transaction' and an old xact_start (a large now() - xact_start age).

The fix: SELECT pg_terminate_backend(<pid>);
Terminating it rolls back the abandoned transaction and releases the lock and
snapshot.

Lesson: an "idle in transaction" session is a silent blocker — no active query,
but real locks and an old xmin. Find it by state and transaction age, terminate
the right backend, and prevent it app-side: commit/rollback promptly and set
`idle_in_transaction_session_timeout`. Don't reach for indexes or ANALYZE.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. There's no active query holding things up — look for a session in pg_stat_activity with state = 'idle in transaction'.
2. Check how long it's been open: xact_start and now() - xact_start. An old idle-in-transaction session holds locks and an old snapshot (blocking VACUUM/cleanup).
3. Terminate that idle-in-transaction backend with pg_terminate_backend(pid). The real fix is app-side: commit/rollback promptly and set idle_in_transaction_session_timeout.