Przeniesienie 300 rupii z Ashy na Bena wymaga dwóch aktualizacji: odjęcia z jednego konta i dodania do drugiego. Jeśli system między nimi zawiedzie, pieniądze znikną. Transakcje SQL uniemożliwiają to, traktując oba kroki jako pojedynczą jednostkę.

W tym przewodniku omawiamy zatwierdzenia, wycofywania zmian i niepowodzenia w małej tabeli kont. Uruchomiliśmy te przykłady w SQLite i pokazane salda pochodzą z tego uruchomienia. Podstawowe polecenia są podobne w różnych bazach danych, ale zachowanie blokowania i izolacji znacznie się różni, dlatego wskazujemy, gdzie można sprawdzić dokumentację własnej bazy danych.

Co to jest transakcja?

Transakcja to sekwencja jednej lub większej liczby instrukcji SQL, które baza danych traktuje jako pojedynczą operację logiczną. Oto nasza tabela:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner TEXT,
  balance INT CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ben', 200);

Ograniczenie CHECK to zasada mówiąca, że saldo nigdy nie może spaść poniżej zera, z której skorzystamy później. Należy zauważyć, że składnia rozpoczęcia transakcji jest różna: SQLite i PostgreSQL akceptują BEGIN, MySQL ma również START TRANSACTION, a SQL Server używa BEGIN TRANSACTION.

Poza jawną transakcją większość baz danych korzysta z opcji autocommit, w której każda instrukcja jest zapisywana zaraz po jej zakończeniu. Oracle jest godnym uwagi wyjątkiem, ponieważ tamtejsza sesja pozostaje częścią transakcji do czasu zatwierdzenia.

COMMIT: uczyń to trwałym

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;

-- Asha | 700
-- Ben  | 500

Dopóki COMMIT nie zostanie uruchomiony, zmiany mają charakter tymczasowy. Po pomyślnym powrocie baza danych gwarantuje, że zostaną one zapisane. Suma na obu kontach pozostała na poziomie 1200 i na tym właśnie polega cały cel tego ćwiczenia.

ROLLBACK: anuluj wszystko

ROLLBACK cofa wszystko od rozpoczęcia transakcji:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT owner, balance FROM accounts;
-- Asha | 600   (visible inside this transaction)
-- Ben  | 500

ROLLBACK;
SELECT owner, balance FROM accounts;
-- Asha | 700   (back to where it was)
-- Ben  | 500

Wycofywanie następuje również automatycznie, jeśli połączenie zostanie zerwane lub serwer ulegnie awarii przed zatwierdzeniem, więc w połowie ukończona transakcja nie przetrwa.

Gdy wypowiedź się nie powiedzie

Oto bardziej realistyczny przypadek. Asha próbuje wysłać 5000, ale na koncie nie ma. Najpierw uruchamiany jest kredyt dla Bena, a następnie debet łamie regułę CHECK:

BEGIN;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- error: CHECK constraint failed: balance >= 0

ROLLBACK;

-- Asha | 700
-- Ben  | 500

Bez transakcji Ben zatrzymałby 5000, a księgi byłyby błędne. Należy pamiętać o jednym ważnym szczególe: brak oświadczenia nie zawsze sam w sobie anuluje transakcję. Na przykład w SQLite i MySQL wcześniejsze instrukcje mogą pozostać w toku, więc Twój kod musi wywołać wycofanie. Zamiast tego PostgreSQL oznacza całą transakcję jako przerwaną i odmawia dalszych instrukcji, dopóki nie wycofasz.

W kodzie aplikacji zawsze łącz transakcję z obsługą błędów. Oto pomysł w Pythonie, a nasz przewodnik po obsłudze wyjątków w Pythonie wyjaśnia składnię:

try:
    conn.execute("BEGIN")
    conn.execute(debit_sql, values)
    conn.execute(credit_sql, values)
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

Punkty zapisu

Punkt zapisu oznacza pozycję wewnątrz transakcji, dzięki czemu można cofnąć tylko część pracy:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;
ROLLBACK TO bonus;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Asha | 650
-- Ben  | 550

Premia 999 została cofnięta, natomiast reszta transakcji została kontynuowana i zatwierdzona. Jest to przydatne w przypadku opcjonalnych etapów długiego procesu.

Wyjaśniono właściwości kwasu

Spójność jest powszechnie źle rozumiana. Baza danych wymusza tylko zadeklarowane przez Ciebie reguły, takie jak ograniczenia i klucze obce. Nie powstrzyma to Twojego kodu przed przesłaniem niewłaściwej kwoty. Trwałość zwykle osiąga się za pomocą dziennika zapisu z wyprzedzeniem, w którym zmiany są bezpiecznie rejestrowane na dysku przed potwierdzeniem zatwierdzenia.

Co się dzieje w przypadku jednoczesnych transakcji?

Otworzyliśmy dwa połączenia z jednym plikiem SQLite. Pierwszy rozpoczął transakcję i zaktualizował wiersz z 1 do 2, jeszcze bez zatwierdzania. Oto co zobaczyło drugie połączenie:

Connection B reads:   1                     -- no uncommitted data
Connection B writes:  database is locked    -- it has to wait
(A commits)
Connection B reads:   2                     -- now it sees the change

SQLite umożliwia jednoczesne zapisywanie tylko jednego zapisu, co jest prostsze niż bazy danych serwera, w których wiele transakcji może jednocześnie zapisywać różne wiersze. Zasada jest jednak taka sama: przy normalnych ustawieniach inne sesje nie powinny widzieć niezatwierdzonych zmian, a zapisy powodujące konflikt muszą poczekać. W naszym teście drugie połączenie ustawiliśmy tak, aby natychmiast się poddawało, a nie czekało, więc błąd pojawił się od razu.

Przeczytaj anomalie

Kiedy transakcje nakładają się na siebie, mogą wystąpić pewne problemy. Standard SQL definiuje je w kategoriach tego, co może zaobserwować transakcja:

Kolejnym problemem jest utracona aktualizacja. Dwie sesje odczytują saldo 100, każda dodaje 10 i każda zapisuje 110. Jedna aktualizacja faktycznie zniknęła, podczas gdy odpowiedź powinna wynosić 120.

Poziomy izolacji SQL

Poziomy izolacji pozwalają zamienić ścisłość na współbieżność. Oto, na co pozwala standard SQL na każdym poziomie:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Dokładna składnia i zachowanie zależą od bazy danych. Wartości domyślne również się różnią: PostgreSQL i SQL Server korzystają z zatwierdzonego odczytu, podczas gdy InnoDB MySQL używa powtarzalnego odczytu. Niektóre bazy danych dają silniejsze gwarancje niż wymaga tego standard lub traktują jeden poziom jako drugi. Na przykład w PostgreSQL odczyt niezatwierdzony zachowuje się jak odczyt zatwierdzony. Zawsze czytaj dokumentację bazy danych, zanim zaczniesz polegać na konkretnym zachowaniu.

Zakleszczenia

Zakleszczenie ma miejsce, gdy dwie transakcje zawierają blokadę, której potrzebuje druga. Transakcja A blokuje wiersz 1 i chce wiersza 2, podczas gdy transakcja B blokuje wiersz 2 i chce wiersza 1. Żadna z nich nie może być kontynuowana.

Bazy danych takie jak PostgreSQL, MySQL i SQL Server wykrywają to i anulują jedną transakcję z błędem. Jest to normalne zachowanie i Twoja aplikacja powinna być na to przygotowana. Aby ograniczyć zakleszczenia, zawsze aktualizuj wiersze w spójnej kolejności, np. najpierw najniższy identyfikator konta, dbaj o krótkie transakcje i spróbuj ponownie, gdy któreś z nich zostanie wybrane jako ofiara.

Najlepsze praktyki

• Trzymaj krótkie transakcje. Długie blokują blokady i blokują innych użytkowników.
• Nigdy nie czekaj na użytkownika lub powolny interfejs API w ramach transakcji. Wykonaj tę pracę przed rozpoczęciem lub później.
• Zawsze obsługuj niepowodzenie. Każda ścieżka powinna kończyć się zatwierdzeniem lub wycofaniem.
• Dodaj ograniczenia. Dają one bazie danych rzeczywiste reguły do ​​egzekwowania. Dobry projekt stołu też pomaga.
• Używaj dobrych indeksów. Szybsze wyszukiwanie oznacza, że ​​blokady są utrzymywane krócej.
• Uważaj przy ponawianiu prób. Ponów próbę w przypadku zakleszczenia, ale upewnij się, że operacja nie może zostać zastosowana dwukrotnie.
• Celowo przetestuj współbieżność. Wiele z tych błędów pojawia się tylko pod obciążeniem, więc same testy integracyjne mogą ich nie wykryć.

Podsumowanie

Transakcje zamieniają kilka ryzykownych kroków w jedną bezpieczną operację, a ACID opisuje gwarancje, które czynią tę operację wiarygodną. Ilekroć działanie biznesowe dotyka więcej niż jednego wiersza lub tabeli, zadaj sobie pytanie, co by się stało, gdyby zatrzymało się w połowie. Jeśli odpowiedź Cię niepokoi, zapakuj ją w transakcję. Aby poćwiczyć, otwórz dwie sesje w lokalnej bazie danych i spróbuj samodzielnie wygenerować anomalie.

Najczęstsze pytania

Ucz się dalej