could not serialize access due to concurrent update - błąd poziomu izolacji transakcji

PostgreSQL

could not serialize access due to concurrent update - błąd poziomu izolacji transakcji

W skrócie

Ten błąd zgłaszają aplikacje pracujące na poziomie izolacji REPEATABLE READ albo SERIALIZABLE, ustawionym w kodzie, w konfiguracji sterownika albo w bazie. Pojawia się nieregularnie, gdy dwie transakcje zmieniają ten sam wiersz w krótkim odstępie, i bywa mylony z zakleszczeniem. Pokażemy, jak odtworzyć go dwiema sesjami, dlaczego PostgreSQL przerywa transakcję i jak napisać ponawianie, które nie zgubi danych.

Jak to wygląda w praktyce

Odtworzyliśmy błąd na PostgreSQL 16.4 na tabeli accounts. Sesja A otwiera transakcję na poziomie REPEATABLE READ i czyta saldo. Sesja B zmienia ten sam wiersz i zatwierdza zmianę. Gdy sesja A próbuje zmienić wiersz, dostaje błąd:

-- sesja A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- 1000.00

-- sesja B (autocommit)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- sesja A
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
ERROR:  could not serialize access due to concurrent update

Kod SQLSTATE to 40001 (serialization_failure). Transakcja A jest już przerwana: każde kolejne polecenie przed ROLLBACK kończy się błędem current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02). Gdy sesja B jeszcze nie zatwierdziła zmiany, UPDATE w sesji A czeka, a w pg_stat_activity widać wait_event_type = 'Lock' i wait_event = 'transactionid'. Po COMMIT w sesji B sesja A dostaje ten sam błąd, a po ROLLBACK w sesji B jej UPDATE wykonuje się poprawnie.

Dlaczego tak się dzieje

Na poziomie REPEATABLE READ każde zapytanie w transakcji widzi migawkę danych z chwili pierwszego polecenia po BEGIN, a nie z chwili bieżącego polecenia. Taka transakcja nie może zmieniać ani blokować wierszy, które po jej rozpoczęciu zmieniła i zatwierdziła inna transakcja, bo nowej wersji wiersza w swojej migawce nie widzi. Zamiast po cichu nadpisać cudzą zmianę, PostgreSQL przerywa transakcję błędem 40001. Na poziomie SERIALIZABLE ten sam kod ma inny komunikat, w naszym teście could not serialize access due to read/write dependencies among transactions ze wskazówką The transaction might succeed if retried.

Zobacz też: Szkolenie Administracja, replikacja i tuning baz danych PostgreSQL

Na domyślnym poziomie READ COMMITTED błędu nie ma. UPDATE czeka na zakończenie cudzej transakcji, a potem ponownie sprawdza warunek WHERE na nowej wersji wiersza i wykonuje się na niej. W naszym teście sesja A przeczytała saldo 900.00, potem sesja B odjęła 100, a UPDATE sesji A zwrócił 850.00, czyli policzył od wartości zmienionej przez sesję B. Aplikacje, które podejmują decyzję na podstawie wcześniejszego odczytu, wybierają więc REPEATABLE READ, a dokumentacja PostgreSQL mówi wprost, że na tym poziomie muszą być przygotowane na ponawianie transakcji.

Jak to rozwiązać krok po kroku

1. Ustal poziom izolacji. SHOW default_transaction_isolation; pokazuje domyślny, a SHOW transaction_isolation; w sesji aplikacji bieżący. Ustawienia przypisane do ról i baz pokaże zapytanie:

   SELECT setrole::regrole AS role_name,
          (SELECT datname FROM pg_database
           WHERE oid = setdatabase) AS db_name,
          setconfig
   FROM pg_db_role_setting;

   Myślnik w kolumnie role_name oznacza ustawienie dla całej bazy, a puste db_name ustawienie roli we wszystkich bazach. Sprawdź też konfigurację sterownika lub frameworka.
2. Rozpoznawaj błąd po kodzie SQLSTATE 40001, nie po treści komunikatu, bo tekst zależy od sytuacji. Po błędzie wykonaj ROLLBACK, inaczej sesja odrzuci każde kolejne polecenie.
3. Ponawiaj całą transakcję, łącznie z odczytami i logiką, która na ich podstawie wybiera wartości. Ponowienie samego UPDATE użyłoby nieaktualnego salda. Wzorzec w Pythonie (psycopg2), który sprawdziliśmy na serwerze testowym:

   import time
   import psycopg2
   from psycopg2 import errors
   from psycopg2.extensions import ISOLATION_LEVEL_REPEATABLE_READ
   
   READ = "SELECT balance FROM accounts WHERE id = %s"
   SUB = "UPDATE accounts SET balance = balance - %s WHERE id = %s"
   ADD = "UPDATE accounts SET balance = balance + %s WHERE id = %s"
   RETRY = (errors.SerializationFailure, errors.DeadlockDetected)
   
   def transfer(conn, src, dst, amount, max_tries=5):
       for attempt in range(1, max_tries + 1):
           try:
               # COMMIT przy sukcesie, ROLLBACK przy wyjątku
               with conn, conn.cursor() as cur:
                   cur.execute(READ, (src,))
                   (balance,) = cur.fetchone()
                   if balance < amount:
                       raise ValueError("brak środków na koncie")
                   cur.execute(SUB, (amount, src))
                   cur.execute(ADD, (amount, dst))
               return attempt
           except RETRY:
               # krótka, rosnąca przerwa przed kolejną próbą
               time.sleep(0.05 * attempt)
       raise RuntimeError(
           "transakcja nie powiodła się po %d próbach" % max_tries)
   
   conn = psycopg2.connect(host="127.0.0.1", dbname="shop",
                           user="app_user")
   conn.set_session(isolation_level=ISOLATION_LEVEL_REPEATABLE_READ)

4. Ogranicz liczbę prób i rób między nimi krótką, rosnącą przerwę. Dokumentacja zaznacza, że przy dużej rywalizacji o te same wiersze transakcja może potrzebować wielu prób. Ta sama pętla obsłuży błąd 40P01 (deadlock_detected), czyli zakleszczenie dwóch transakcji czekających na siebie nawzajem, który według dokumentacji także może być wart ponowienia.
5. Jeśli logika sprowadza się do odczytu i zmiany jednego wiersza, rozważ READ COMMITTED z jawną blokadą: SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; blokuje wiersz do końca transakcji, więc druga sesja czeka, zamiast dostać błąd. W naszym teście UPDATE z drugiej sesji odczekał 1 s do COMMIT pierwszej i wykonał się na aktualnym saldzie.

Jak sprawdzić, że zadziałało

Powtórz test z dwiema sesjami, tym razem przez funkcję z ponawianiem. W naszym teście przelew, w trakcie którego inna sesja zmieniła saldo, zakończył się powodzeniem w drugiej próbie, a końcowe saldo uwzględniało obie zmiany:

SELECT id, balance FROM accounts ORDER BY id;

W logu serwera każdy taki błąd zostawia wpis z tekstem could not serialize access i treścią polecenia, bo domyślnie log_min_error_statement ma wartość error. Porównaj liczbę tych wpisów z liczbą ponowień w logach aplikacji. Błędy bez udanego ponowienia wskazują miejsca w kodzie, które nadal nie obsługują 40001.

Powiązane problemy

• Jeśli zamiast 40001 widzisz kod 40P01, przejdź do tekstu deadlock detected, czyli jak zdiagnozować i uniknąć zakleszczeń.
• Gdy UPDATE długo czeka na cudzą transakcję, sesję blokującą znajdziesz według tekstu zapytanie wisi zablokowane, czyli jak znaleźć blokującą sesję.
• O tym, czym kończy się brak ROLLBACK po błędzie, piszemy w tekście transakcja się nie kończy, czyli zapomniany COMMIT lub ROLLBACK.

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