idle-in-transaction и каскад блокировок

Это самый частый способ положить базу одной строкой. Разберём по шагам, как безобидное сочетание превращается в каскад.

Сцена из трёх сессий

-- A: открыл транзакцию, прочитал, забыл закрыть

BEGIN;

SELECT count(*) FROM flights;   -- ACCESS SHARE; state = idle in transaction

-- B: выкатывается миграция

ALTER TABLE flights ADD COLUMN gate text;  -- хочет ACCESS EXCLUSIVE → ждёт A

-- C: обычный пользовательский запрос

SELECT * FROM flights WHERE flight_no = 'PG1';  -- ACCESS SHARE → ждёт B

A и C совместимы - два читателя. Но между ними стоит B с ACCESS EXCLUSIVE, а очередь блокировок честная: новые запросы не перепрыгивают ждущего. Поэтому C виснет за B, а B - за A. Один забытый BEGIN плюс одна миграция останавливают всю таблицу.

В чём именно ловушка

Опасен не ALTER TABLE сам по себе, а его сочетание с долгой открытой транзакцией впереди. Тяжёлая блокировка держится до конца транзакции (см. relation-locks), а не до конца запроса. Сессия в состоянии idle in transaction ничего не делает, но всё ещё держит ACCESS SHARE.

Диагностика

SELECT w.pid AS waiting, b.pid AS blocking, b.state, b.query

FROM pg_stat_activity w

JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid) ON true

JOIN pg_stat_activity b ON b.pid = bp.pid

WHERE cardinality(pg_blocking_pids(w.pid)) > 0;

pg_blocking_pids(pid) сразу называет корень. Если виновник в idle in transaction, снять его pg_cancel_backend чаще всего не выйдет - там нет активного запроса. Нужен pg_terminate_backend: он закроет сессию, транзакция откатится, очередь рассосётся.

Профилактика

• idle_in_transaction_session_timeout прибивает забытые транзакции;
• lock_timeout не даёт DDL стоять в очереди бесконечно;
• короткие транзакции и COMMIT сразу после работы.

В отличие от deadlocks, каскад - это не цикл: его не разрывает детектор, он будет стоять, пока не вмешаешься.