Transakcje w bazie danych - ACID, COMMIT, ROLLBACK i izolacja

Gdy płatność zostaje pobrana, ale zamówienie nie zapisuje się w systemie, problem zwykle nie leży w pojedynczym zapytaniu SQL, lecz w źle obsłużonej transakcji. W tym artykule pokazuję, jak działają transakcje bazy danych, na czym polega model ACID, kiedy używać COMMIT, ROLLBACK i SAVEPOINT oraz jak ograniczać problemy z równoległym dostępem do danych.

Czym jest transakcja w bazie danych

Transakcja to zestaw operacji, który baza traktuje jako jedną logiczną całość. Może obejmować odczyt, kilka modyfikacji i sprawdzenie warunków. Jeżeli wszystkie kroki zakończą się poprawnie, zmiany zostają zatwierdzone. Gdy wystąpi błąd, system może wycofać wykonane operacje.

Najłatwiej zobaczyć to na przykładzie przelewu. System musi zmniejszyć saldo konta A i zwiększyć saldo konta B. Samo wykonanie pierwszego UPDATE nie wystarczy. Gdyby aplikacja zakończyła działanie przed drugim zapytaniem, pieniądze zniknęłyby z jednego konta, ale nie pojawiły się na drugim. Transakcja zabezpiecza oba działania przed takim częściowym wykonaniem.

Typowy przebieg wygląda następująco:

1. Rozpoczęcie transakcji.
2. Odczyt danych i sprawdzenie warunków.
3. Wykonanie jednego lub wielu poleceń SQL.
4. Zatwierdzenie przez COMMIT albo wycofanie przez ROLLBACK.

W wielu systemach domyślnie działa autocommit. Oznacza to, że pojedyncze polecenie jest automatycznie zatwierdzane po wykonaniu. To wygodne przy prostych odczytach i niezależnych zmianach, ale nie wystarcza wtedy, gdy kilka operacji musi zakończyć się razem.

Model ACID decyduje o bezpieczeństwie danych

Skrót ACID opisuje cztery właściwości, których oczekujemy od solidnego mechanizmu transakcyjnego. Nie jest to tylko teoria z podręcznika. Każda z tych cech odpowiada za inny rodzaj ryzyka, z którym aplikacja może spotkać się podczas awarii albo dużego obciążenia.

Trzeba jednak rozumieć granice ACID. Baza może zagwarantować spójność własnych tabel, ale nie cofnie wysłanego e-maila, żądania do zewnętrznego API ani płatności wykonanej poza nią. Dlatego w systemach rozproszonych stosuje się dodatkowe mechanizmy, na przykład idempotencję, kolejki i wzorzec outbox.

Moja praktyczna zasada jest prosta. Do transakcji wkładam tylko operacje, które rzeczywiście muszą być atomowe, a komunikację z zewnętrznymi usługami obsługuję osobno. Dzięki temu połączenie z bazą nie pozostaje zablokowane przez wolny serwis płatniczy.

COMMIT, ROLLBACK i SAVEPOINT w SQL

Podstawowe polecenia transakcyjne są podobne w większości relacyjnych systemów baz danych, choć szczegóły składni mogą się różnić. Najczęściej spotkasz BEGIN lub START TRANSACTION, COMMIT oraz ROLLBACK.

BEGIN;

UPDATE konta
SET saldo = saldo - 100
WHERE id = 1 AND saldo >= 100;

UPDATE konta
SET saldo = saldo + 100
WHERE id = 2;

COMMIT;

Ten przykład ma ważne ograniczenie. Sam kod nie sprawdza, czy pierwsze UPDATE rzeczywiście zmieniło rekord. Jeżeli saldo wynosiło mniej niż 100 zł, druga operacja nadal może zostać wykonana. W praktycznej aplikacji trzeba sprawdzić liczbę zmodyfikowanych wierszy i w razie niepowodzenia wywołać rollback całej transakcji.

BEGIN;

UPDATE konta
SET saldo = saldo - 100
WHERE id = 1 AND saldo >= 100;

-- aplikacja sprawdza, czy zmieniono dokładnie 1 rekord

UPDATE konta
SET saldo = saldo + 100
WHERE id = 2;

COMMIT;

ROLLBACK przywraca stan z początku bieżącej transakcji. Jest szczególnie istotny przy wyjątkach, błędach walidacji i konfliktach blokad. W kodzie aplikacji powinien znajdować się w obsłudze błędu, a połączenie z bazą nie powinno być ponownie używane, dopóki transakcja nie zostanie zakończona.

częściowe wycofanie przez SAVEPOINT

SAVEPOINT tworzy punkt kontrolny wewnątrz transakcji. Pozwala wycofać tylko późniejsze operacje, zachowując wcześniejsze zmiany. Przydaje się na przykład podczas importu wielu rekordów, gdy pojedynczy wadliwy element nie powinien anulować całego procesu.

BEGIN;

INSERT INTO zamowienia (id, klient_id)
VALUES (101, 7);

SAVEPOINT przed_pozycjami;

INSERT INTO pozycje_zamowien (zamowienie_id, produkt_id, liczba)
VALUES (101, 55, 2);

ROLLBACK TO SAVEPOINT przed_pozycjami;

COMMIT;

Po poleceniu ROLLBACK TO SAVEPOINT zamówienie pozostaje w transakcji, ale operacje wykonane po punkcie kontrolnym zostają cofnięte. Nie należy jednak traktować savepointów jako zamiennika rozsądnego podziału procesu. Bardzo długa transakcja nadal zużywa zasoby i może utrudniać pracę innym użytkownikom.

Poziomy izolacji a równoległe operacje

Izolacja określa, jak jedna transakcja widzi działania innych transakcji wykonywanych w tym samym czasie. Im silniejsza izolacja, tym mniejsze ryzyko niespójnych odczytów, ale zwykle rośnie koszt blokad, oczekiwania i ponawiania operacji.

Nie istnieje jeden najlepszy poziom dla całej aplikacji. PostgreSQL domyślnie korzysta z READ COMMITTED, natomiast InnoDB w MySQL domyślnie używa REPEATABLE READ. To jedna z przyczyn, dla których identyczny kod może zachowywać się inaczej po przeniesieniu między silnikami.

Warto znać także typowe anomalie. Dirty read oznacza odczyt zmiany, która może zostać wycofana. Non-repeatable read występuje wtedy, gdy ten sam rekord zwraca w jednej transakcji różne wartości. Phantom read pojawia się, gdy ponowne wykonanie warunku zwraca dodatkowe albo znikające wiersze.

Poziom SERIALIZABLE nie jest magicznym wyłącznikiem problemów. System może odrzucić transakcję z powodu konfliktu i oczekiwać, że aplikacja ponowi cały proces od początku. Retry powinien mieć limit, opóźnienie i ochronę przed wielokrotnym wykonaniem skutków ubocznych.

Jak projektować transakcje, żeby nie szkodziły wydajności

Dobra transakcja jest możliwie krótka, ale obejmuje wszystkie operacje, które muszą być atomowe. Nie warto otwierać jej przed wykonaniem kosztownego obliczenia, pobraniem pliku ani wywołaniem zewnętrznego serwisu. Każda dodatkowa sekunda może oznaczać dłużej utrzymywane blokady i większe kolejki.

Najczęstsze błędy

• Brak rollbacku po wyjątku. Po błędzie połączenie może pozostawać w niedokończonej transakcji.
• Założenie, że każde zapytanie jest częścią transakcji. Przy autocommicie każde polecenie może zostać zatwierdzone osobno.
• Brak kontroli liczby zmienionych rekordów. Zapytanie może wykonać się poprawnie technicznie, ale nie zmienić żadnego wiersza.
• Trzymanie transakcji podczas żądania HTTP. Wolny klient lub usługa zewnętrzna może blokować zasoby bazy.
• Niewłaściwy silnik tabeli. W MySQL mieszanie tabel transakcyjnych i nietransakcyjnych może uniemożliwić pełne cofnięcie zmian.
• Brak obsługi zakleszczeń. Dwie transakcje mogą czekać na zasoby zablokowane przez siebie nawzajem.

Zakleszczenie nie zawsze oznacza błąd projektu. Przy dużej współbieżności może wystąpić nawet w dobrze napisanym systemie. Aplikacja powinna przechwycić odpowiedni błąd, odczekać krótko i ponowić bezpieczną transakcję, najlepiej z losowym opóźnieniem.

Pomaga także stała kolejność modyfikowania rekordów. Jeżeli każda transakcja najpierw blokuje konto o niższym identyfikatorze, a dopiero później konto o wyższym, ryzyko wzajemnego oczekiwania wyraźnie spada.

Przeczytaj również: Normalizacja danych w SQL - Jak uporządkować bazę?

Transakcja w Pythonie

W kodzie Pythona trzeba sprawdzić, jak konkretna biblioteka obsługuje zatwierdzanie. W poniższym przykładzie używam wbudowanego modułu sqlite3. Blok with zatwierdzi zmiany po pomyślnym zakończeniu, a wyjątek spowoduje ich wycofanie.

import sqlite3

with sqlite3.connect("sklep.db") as conn:
    conn.execute(
        "UPDATE konta SET saldo = saldo - ? "
        "WHERE id = ? AND saldo >= ?",
        (100, 1, 100)
    )

    if conn.total_changes != 1:
        raise ValueError("Brak wystarczających środków")

    conn.execute(
        "UPDATE konta SET saldo = saldo + ? WHERE id = ?",
        (100, 2)
    )

W większych aplikacjach podobny schemat realizuje się przez menedżer kontekstu sesji lub połączenia. Najważniejsze jest, aby commit następował dopiero po przejściu wszystkich walidacji, a wyjątek nie był cicho ignorowany.

Transakcje w realnych scenariuszach

W systemie sklepowym jedna transakcja może utworzyć zamówienie, odjąć stan magazynowy i zapisać pozycje zamówienia. To dobry kandydat do atomowego wykonania, ponieważ brak któregokolwiek elementu utrudnia późniejsze rozliczenie.

BEGIN;

INSERT INTO zamowienia (klient_id, status)
VALUES (7, 'nowe');

UPDATE produkty
SET stan = stan - 1
WHERE id = 55 AND stan > 0;

-- aplikacja sprawdza, czy stan został zmniejszony

INSERT INTO pozycje_zamowien (zamowienie_id, produkt_id, liczba)
VALUES (101, 55, 1);

COMMIT;

Nie umieszczałbym jednak w tej samej transakcji wysyłki e-maila ani oczekiwania na operatora płatności. Lepszy układ to zapisanie zdarzenia do tabeli outbox, zatwierdzenie zmian, a potem wysłanie komunikatu przez osobny proces. Awaria wysyłki nie niszczy wtedy poprawnie zapisanego zamówienia.

Inaczej wygląda masowy import. Transakcja obejmująca kilka milionów rekordów może długo zajmować blokady, rozrastać log transakcyjny i utrudniać odzyskiwanie po awarii. W takim przypadku często lepiej użyć partii, na przykład po 1000 lub 10 000 rekordów, po wcześniejszym przetestowaniu wpływu na konkretny system.

W aplikacjach finansowych, magazynowych i rezerwacyjnych szczególne znaczenie ma warunek sprawdzany razem z modyfikacją. Zamiast najpierw odczytywać saldo, a później wykonywać niezależny zapis, można zastosować UPDATE ... WHERE saldo >= kwota. To ogranicza okno, w którym dwie równoległe operacje mogłyby wykorzystać ten sam stan danych.

Jak sprawdzić, czy mechanizm działa poprawnie

Test transakcji nie powinien kończyć się na sprawdzeniu, czy zapytanie zwróciło wynik. Trzeba zasymulować również błąd po pierwszej modyfikacji, zerwanie połączenia, dwa równoległe żądania i próbę zapisania danych łamiących ograniczenie.

• Sprawdź, czy błąd w połowie procesu wycofuje wszystkie wcześniejsze zmiany.
• Uruchom równolegle dwie operacje dotyczące tego samego rekordu.
• Przetestuj zachowanie po przekroczeniu limitu lub braku produktu.
• Zmierz czas trwania transakcji i liczbę oczekujących blokad.
• Zweryfikuj, czy retry nie tworzy podwójnego zamówienia albo podwójnej płatności.

Loguję przede wszystkim identyfikator transakcji, czas rozpoczęcia, czas zakończenia i przyczynę wycofania. Nie zapisuję bez potrzeby pełnych danych użytkownika ani wrażliwych parametrów. Takie metryki szybko pokazują, czy problemem jest baza, aplikacja czy zewnętrzna usługa.

Jeżeli transakcje często kończą się błędem, samo zwiększenie limitu czasu oczekiwania zwykle nie rozwiązuje problemu. Najpierw sprawdzam kolejność blokowania, indeksy, długość operacji i poziom izolacji. Wydajność transakcji poprawia się przez skracanie pracy wewnątrz niej, a nie przez bezrefleksyjne wyłączanie zabezpieczeń.

Najprostszy model decyzyjny przed wdrożeniem

Przed połączeniem kilku zapytań w jedną transakcję zadaję sobie trzy pytania. Czy wszystkie operacje muszą udać się razem? Czy dane są modyfikowane równolegle przez wielu użytkowników? Czy aplikacja potrafi bezpiecznie powtórzyć proces po konflikcie?

Jeżeli odpowiedź na pierwsze pytanie brzmi tak, potrzebujesz jawnej transakcji i kontroli błędów. Gdy ważna jest współbieżność, dobierz poziom izolacji do konkretnego przypadku, zamiast automatycznie wybierać najbardziej restrykcyjny wariant. Jeżeli operacja może zostać ponowiona, zadbaj o idempotentny identyfikator, który uniemożliwi podwójny zapis.

Najważniejsza lekcja jest praktyczna. Transakcja nie służy do opakowania całej funkcji aplikacji, tylko do ochrony konkretnej zmiany, która musi przejść z jednego poprawnego stanu do drugiego. Tak zaprojektowane mechanizmy są łatwiejsze do testowania, szybsze i znacznie mniej podatne na trudne do odtworzenia błędy.