Zapytanie wisi zablokowane - jak znaleźć blokującą sesję

PostgreSQL

Zapytanie wisi zablokowane - jak znaleźć blokującą sesję

W skrócie

Wiszące zapytanie to zwykle nie awaria bazy, lecz kolejka po blokadę. Sztuka polega na tym, żeby szybko wskazać sesję, która blokadę trzyma, i podjąć decyzję: poczekać, anulować zapytanie czy rozłączyć sesję. Pokazujemy komplet zapytań diagnostycznych.

Jak to wygląda w praktyce

Aplikacja zgłasza timeout albo zawiesza się na jednym zapytaniu. W pg_stat_activity widzisz backend w stanie active z rosnącym czasem trwania i niepustą kolumną wait_event_type = 'Lock'. Typowy scenariusz: ALTER TABLE lub DROP czeka, bo ktoś ma otwartą transakcję dotykającą tej tabeli. Albo UPDATE jednego wiersza stoi, bo inna transakcja zaktualizowała ten wiersz i jeszcze nie zrobiła COMMIT.

Dlaczego tak się dzieje

PostgreSQL zabezpiecza spójność danych blokadami. Blokady wierszy powstają przy UPDATE, DELETE i SELECT ... FOR UPDATE i trwają do końca transakcji. Blokady na poziomie tabeli bierze m.in. DDL - ALTER TABLE potrzebuje blokady ACCESS EXCLUSIVE, która jest niekompatybilna nawet ze zwykłym odczytem.

Gdy transakcja A trzyma blokadę, a transakcja B chce niekompatybilnej blokady na tym samym zasobie, B czeka. Jeśli A długo nie robi COMMIT (bo aplikacja utknęła, bo to sesja idle in transaction albo bo A sama na coś czeka), B wisi. Problem łączy się w łańcuchy: C czeka na B, które czeka na A. Żeby to rozwiązać, trzeba dojść do sesji na początku łańcucha.

Jak to rozwiązać krok po kroku

1. Zobacz, kto na co czeka, jednym zapytaniem z funkcją pomocniczą: SELECT pid, pg_blocking_pids(pid) AS blokuja_go, wait_event_type, left(query,60) AS zapytanie FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;. Kolumna blokuja_go wskazuje PID-y sesji, które trzymają potrzebną blokadę.
2. Sprawdź szczegóły sesji blokującej. Podstaw PID zwrócony wyżej: SELECT pid, state, xact_start, query_start, wait_event_type, query FROM pg_stat_activity WHERE pid = 12345;. Zwróć uwagę na stan - idle in transaction to jasny sygnał zapomnianej transakcji.
3. Jeśli chcesz zobaczyć konkretne blokady na obiektach, połącz pg_locks z pg_stat_activity: SELECT l.pid, l.mode, l.granted, c.relname FROM pg_locks l JOIN pg_class c ON c.oid = l.relation WHERE NOT l.granted; pokaże blokady oczekujące, których nikt jeszcze nie przyznał.
4. Oceń, czy sesja blokująca robi coś pożytecznego, czy tylko wisi. Jeśli to długie, ale sensowne zapytanie - poczekaj. Jeśli to idle in transaction albo utknięta operacja - przejdź do rozłączenia.
5. Delikatnie anuluj wiszące zapytanie sesji blokującej, nie zrywając jej połączenia: SELECT pg_cancel_backend(12345);. To najbezpieczniejsza opcja, bo aplikacja dostaje błąd zamiast zerwanego połączenia.
6. Gdy anulowanie nie skutkuje (np. sesja jest w idle in transaction i nie ma czego anulować), rozłącz ją: SELECT pg_terminate_backend(12345);. Transakcja zostanie wycofana, blokady zwolnione, a kolejka ruszy.

Jak sprawdzić, że zadziałało

Powtórz zapytanie z pg_blocking_pids - lista sesji oczekujących na blokadę powinna być pusta. Zapytanie, które wisiało, powinno się teraz zakończyć; sprawdź w pg_stat_activity, czy jego backend zmienił stan z active na idle. Upewnij się też, że SELECT count(*) FROM pg_locks WHERE NOT granted; zwraca zero, co oznacza brak nieprzyznanych blokad. Jeśli problem się powtarza, warto ustawić globalny lock_timeout, żeby zapytania nie czekały w nieskończoność.

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