Transakcja się nie kończy - zapomniany COMMIT/ROLLBACK

PostgreSQL

Transakcja się nie kończy - zapomniany COMMIT/ROLLBACK

W skrócie

To cichy zabójca wydajności: aplikacja albo człowiek w narzędziu graficznym rozpoczyna transakcję poleceniem BEGIN, wykonuje jakieś zmiany i... nie robi COMMIT ani ROLLBACK. Transakcja zostaje otwarta, sesja przechodzi w stan "idle in transaction" i tak wisi - minutami, a czasem godzinami. Przez ten czas trzyma blokady na zmodyfikowanych wierszach i, co gorsza, blokuje sprzątanie martwych wersji wierszy w całej bazie. Objawy potrafią wyglądać jak poważna awaria, choć źródło jest banalne: jedna zapomniana transakcja.

Jak to wygląda w praktyce

Użytkownicy zgłaszają, że część operacji "wisi". Zaglądasz do aktywności bazy i widzisz sesję w stanie idle in transaction, której xact_start (czas rozpoczęcia transakcji) sprzed wielu minut lub godzin. Inne zapytania, które chcą zmodyfikować te same wiersze, czekają na zwolnienie blokad. Równolegle rośnie bloat i pojawia się dziwny objaw: autovacuum pracuje, ale liczba martwych wierszy w tabelach wcale nie spada. W skrajnym przypadku w logach zaczynają się ostrzeżenia o zbliżającym się przekroczeniu zakresu identyfikatorów transakcji. Często winowajcą jest klient graficzny z wyłączonym autocommit, w którym ktoś wykonał UPDATE, po czym poszedł na obiad, albo aplikacja, która pobrała połączenie z puli, otworzyła transakcję i nie zwolniła go po błędzie.

Dlaczego tak się dzieje

W PostgreSQL każda zmiana musi być domknięta: COMMIT ją utrwala, ROLLBACK wycofuje. Dopóki żadne z tych poleceń nie padnie, transakcja jest otwarta i aktywna z punktu widzenia silnika - nawet jeśli sesja nic nie robi (stąd stan "idle in transaction"). Otwarta transakcja ma dwa kosztowne skutki. Po pierwsze trzyma blokady założone przez swoje zmiany, więc inni czekają. Po drugie, i groźniej, ustala tak zwany horyzont: mechanizm wielowersyjności (MVCC) nie może usunąć wersji wierszy, które mogłyby być jeszcze widoczne dla najstarszej otwartej transakcji w klastrze. Dlatego jedna zapomniana transakcja blokuje sprzątanie martwych krotek nie tylko w swojej tabeli, ale w całej bazie - autovacuum mieli, lecz nie może niczego zwolnić. To także droga do problemu z zakresem identyfikatorów transakcji, jeśli taka sesja wisi bardzo długo. Przyczyną bywa wyłączony autocommit w kliencie, błąd w kodzie aplikacji, który nie domyka transakcji po wyjątku, albo połączenie oddane do puli bez wcześniejszego ROLLBACK.

Jak to rozwiązać krok po kroku

1. Znajdź winowajcę. Wypisz otwarte transakcje bezczynne od dłuższego czasu: SELECT pid, usename, state, xact_start, now() - xact_start AS czas_transakcji, query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_start;. Kolumna query pokaże ostatnie polecenie, a czas_transakcji - jak długo to trwa.
2. Jeśli to Twoja własna sesja (na przykład w kliencie graficznym), po prostu domknij ją poleceniem COMMIT; lub - gdy zmian nie chcesz zachować - ROLLBACK;. To najczystsze rozwiązanie.
3. Gdy sesja należy do kogoś innego i wisi długo, rozłącz ją awaryjnie: SELECT pg_terminate_backend();, podstawiając PID z pierwszego kroku. PostgreSQL wykona wtedy automatyczny rollback tej transakcji.
4. Sprawdź, czy zablokowane zapytania ruszyły - po zamknięciu winowajcy blokady zwalniają się natychmiast, a czekające sesje kończą pracę.
5. Ustaw zabezpieczenie na przyszłość: ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min'; i przeładuj konfigurację przez SELECT pg_reload_conf();. Od tej pory serwer sam zamknie każdą transakcję bezczynną dłużej niż ustawiony limit.
6. Napraw źródło po stronie klienta: włącz autocommit w narzędziu albo popraw kod, żeby zawsze domykał transakcję w bloku obsługi błędów i wykonywał ROLLBACK przed oddaniem połączenia do puli.

Jak sprawdzić, że zadziałało

Powtórz zapytanie z pierwszego kroku - lista sesji w stanie idle in transaction z długim czasem trwania powinna być pusta. Że blokady zniknęły, potwierdzisz przez SELECT count(*) FROM pg_locks WHERE NOT granted; - brak nieprzyznanych blokad oznacza, że nikt już nie czeka. Skuteczność sprzątania martwych wierszy sprawdzisz po chwili w pg_stat_user_tables: po zamknięciu długiej transakcji autovacuum wreszcie zdoła obniżyć n_dead_tup. Że limit czasu bezczynności faktycznie obowiązuje, potwierdzi SHOW idle_in_transaction_session_timeout; z ustawioną wartością - a najlepszym dowodem będzie to, że kolejna celowo pozostawiona otwarta transakcja zostanie samodzielnie zamknięta przez serwer po upływie tego czasu, z odpowiednim wpisem w logu.

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