Долгая / открытая транзакция

Долгая или открытая транзакция накапливает блокировки, undo и раздувание (bloat). Найдём проблемную транзакцию, определим её тип (idle in transaction, длинный пакет, влияние) и безопасно устраним.

Какая открытая транзакция самая старая?

Выведите открытые транзакции по времени начала. Источник проблемы обычно — самая старая. Проверьте её состояние: она активно выполняется или находится в 'idle in transaction', ожидая commit?

SELECT s.sid, s.serial#, s.username, s.status, t.start_time, t.used_ublk*8/1024 AS undo_mb
FROM v$transaction t JOIN v$session s ON s.taddr=t.addr
ORDER BY t.start_time;

• idle in transaction (commit забыт, простаивает)
• Активный, но очень долгий пакет/DML
• Накапливает блокировки/bloat/undo, пока транзакция продолжается

Почему возникает idle in transaction?

Сессия открыла транзакцию, но простаивает, не выполняя запросов; то есть commit/rollback забыт. Это без необходимости удерживает блокировки и undo. Достаточно исправить приложение или нужно срочно завершить её?

SELECT pid, now()-state_change AS idle_age, left(query,80) AS last_query
FROM pg_stat_activity WHERE state='idle in transaction' ORDER BY state_change;

• Приложение/пул соединений не выполняет commit (постоянное исправление)
• Мне нужно завершить её прямо сейчас

Что делает активная долгая транзакция?

Сессия действительно выполняется (active). Это один огромный DML/отчёт или блокирующая сессия, которая заставляет ждать другие сессии и должна быть срочно прервана?

SELECT s.sid, s.serial#, sq.sql_text, s.last_call_et AS running_secs
FROM v$session s JOIN v$sql sq ON sq.sql_id=s.sql_id
WHERE s.status='ACTIVE' AND s.type='USER' ORDER BY s.last_call_et DESC;

• Один огромный DML/отчёт; его нужно разбить
• Она заставляет ждать другие; срочно завершить

Что накапливает долгая транзакция?

Определите побочный эффект долгой транзакции: это в основном очередь блокировок, заставляющая ждать другие сессии, или раздувание из-за удержания старых версий строк и блокировки очистки (VACUUM/undo)?

SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid))>0;
SELECT max(age(backend_xmin)) AS max_xmin_age FROM pg_stat_activity WHERE backend_xmin IS NOT NULL;

• Очередь блокировок заставляет ждать другие сессии
• Накопление старых версий блокирует очистку (undo/bloat)

Первопричина: приложение не выполняет commit (idle in transaction)

Пул соединений или приложение открывает транзакцию, а затем держит соединение в простое без commit/rollback. Это без необходимости удерживает блокировки и undo.

Решение: безопасно завершите проблемную сессию

Если блокирующая/долгая сессия заставляет ждать другие, её нужно контролируемо завершить. Завершение запускает откат, которому могут понадобиться пространство/время; будьте осторожны.

Первопричина: один огромный DML/отчёт

DML, обрабатывающий миллионы строк в одной транзакции, или очень долгий отчёт накапливают блокировки и undo/bloat на всё время выполнения.

Первопричина: очередь блокировок

Из-за удерживаемых блокировок долгая транзакция создала за собой очередь ожидания; множество сессий ждут один и тот же ресурс.

Первопричина: накопление старых версий (undo/bloat)

Долгая транзакция мешает очистке старых версий строк, появившихся до неё. В Postgres это проявляется как раздувание таблиц/индексов, в Oracle — как давление на undo.