idle in transaction blokuje bazę - jak temu zaradzić

PostgreSQL

idle in transaction blokuje bazę - jak temu zaradzić

W skrócie

Stan idle in transaction to jeden z najbardziej podstępnych problemów PostgreSQL. Sesja nic nie robi, więc łatwo ją przeoczyć, a jednocześnie blokuje VACUUM, trzyma stary horyzont XID i może zatrzymywać inne zapytania. Pokazujemy, jak je namierzyć i jak zabezpieczyć się na przyszłość.

Jak to wygląda w praktyce

W pg_stat_activity widzisz backendy ze stanem idle in transaction, których xact_start jest sprzed wielu minut albo godzin. Równocześnie autovacuum nie nadąża, tabele puchną, a wiek najstarszego XID rośnie. Czasem inne zapytania wiszą, bo taka sesja trzyma blokadę na wierszu, którego dotknęła przed zawieszeniem. Liczba połączeń zbliża się do limitu, bo martwe transakcje nie zwalniają slotów.

Dlaczego tak się dzieje

Sesja wchodzi w idle in transaction, gdy wykona BEGIN (lub pracuje z wyłączonym autocommit), zrobi jakieś zapytanie, po czym nie wysyła ani COMMIT, ani ROLLBACK. Z punktu widzenia bazy transakcja trwa i musi być traktowana poważnie: jej blokady są aktywne, a horyzont widoczności, który ustala, blokuje VACUUM przed usunięciem martwych wierszy nowszych niż ta transakcja.

Przyczyna niemal zawsze leży po stronie aplikacji: pula połączeń oddaje połączenie z otwartą transakcją, kod obsługuje wyjątek i nie wywołuje rollbacku, albo długie operacje po stronie aplikacji (wolne API, czekanie na użytkownika) odbywają się w środku otwartej transakcji. Skutki są niewspółmierne do bezczynności sesji - to dlatego ten stan jest tak groźny.

Jak to rozwiązać krok po kroku

1. Namierz winowajców, od najstarszych: SELECT pid, usename, application_name, state, xact_start, now() - xact_start AS czas, left(query,60) AS ostatnie FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_start;.
2. Oceń wpływ każdej sesji: sprawdź, czy trzyma blokady, które blokują innych - SELECT pid, cardinality(pg_blocking_pids(pid)) FROM pg_stat_activity; oraz czy nie trzyma najstarszego horyzontu XID. Kolumna application_name podpowie, która aplikacja zostawia otwarte transakcje.
3. Rozłącz sesje, które wiszą długo i szkodzą: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - xact_start > interval '10 minutes';. Transakcje zostaną wycofane, blokady zwolnione.
4. Ustaw automatyczne zamykanie takich sesji na przyszłość: w postgresql.conf ustaw idle_in_transaction_session_timeout = '5min' i wykonaj SELECT pg_reload_conf();. Baza sama zakończy transakcję, która zbyt długo wisi bezczynnie.
5. Popraw aplikację: upewnij się, że każdy blok transakcyjny kończy się COMMIT lub ROLLBACK także w scenariuszu błędu, i że pula połączeń wykonuje rollback przy oddawaniu połączenia. Nie wykonuj powolnych operacji zewnętrznych wewnątrz otwartej transakcji.
6. Dla puli połączeń (np. PgBouncer albo pula frameworka) włącz mechanizm, który czyści stan połączenia między użyciami, aby transakcja nie przechodziła do kolejnego zadania.

Jak sprawdzić, że zadziałało

Ponownie odpytaj pg_stat_activity WHERE state = 'idle in transaction' - lista powinna być pusta lub zawierać tylko świeże, krótkotrwałe wpisy. Po ustawieniu timeoutu przetestuj go: otwórz sesję, wykonaj BEGIN; SELECT 1; i zostaw ją - po upływie limitu połączenie powinno zostać zamknięte przez bazę z komunikatem o przekroczeniu idle_in_transaction_session_timeout. Sprawdź też, że autovacuum znów sprząta (spadający n_dead_tup) i że liczba aktywnych połączeń wróciła do normy.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5