Transakcje w Bazie Danych: ACID i Poziomy Izolacji

Dlaczego przelew bankowy wymaga więcej niż dwóch UPDATE

Przenieś 50 zł z jednego konta na drugie, a naiwna wersja to dwie instrukcje: odejmij od konta A, dodaj do konta B. Wykonaj je osobno, a jeśli proces padnie albo połączenie się zerwie między tymi dwoma, pieniądze znikają z A i nigdy nie docierają do B. Baza danych zrobiła dokładnie to, o co ją poproszono, dwa razy. Biznes i tak stracił 50 zł.

Transakcja łączy obie instrukcje w jedną jednostkę: baza danych wykonuje albo obie, albo żadną. Ta gwarancja, razem z trzema kolejnymi, to właśnie znaczenie skrótu “ACID”. Dzięki niej możesz powierzyć bazie danych pieniądze albo stany magazynowe: miejsca, gdzie “zrobione do połowy” jest gorsze niż “niezrobione”.

Co gwarantują cztery właściwości ACID

• Atomowość (Atomicity). Wszystkie instrukcje transakcji zatwierdzają się razem albo żadna się nie zatwierdza. Jeśli proces padnie po obciążeniu konta, ale przed zasileniem drugiego, baza danych wycofuje też to obciążenie. Nie ma żadnego stanu częściowego do ręcznego czyszczenia.
• Spójność (Consistency). Transakcja może przenieść bazę danych tylko z jednego poprawnego stanu do innego poprawnego stanu. Jeśli ograniczenie CHECK mówi, że saldo nie może być ujemne, żadna transakcja nie może zatwierdzić wiersza, który to narusza, niezależnie od tego, co próbował zrobić kod aplikacji.
• Izolacja (Isolation). Transakcje działające w tym samym czasie nie widzą nawzajem swojej niedokończonej pracy. To, jak rygorystycznie jest to wymuszane, to pokrętło, nie sztywna reguła: tym pokrętłem jest poziom izolacji, opisany dalej.
• Trwałość (Durability). Gdy baza danych powie, że transakcja się zatwierdziła, te dane przetrwają awarię sekundę później. Do tego służy write-ahead log: zmiana jest na dysku (albo zreplikowana), zanim commit wraca.

Atomowość i trwałość to zadanie bazy danych, i popularne silniki robią to dobrze. Izolacja to ta właściwość, o której decydujesz sam. Bardziej rygorystyczna izolacja kosztuje równoległość, a ten koszt widać na wykresach opóźnień na produkcji.

Jak wykonać transakcję za pomocą BEGIN, COMMIT i ROLLBACK

W PostgreSQL (i większości baz SQL, z niewielkimi różnicami w składni) transakcja to instrukcje między BEGIN i COMMIT:

1
2
3
4
5
6

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

COMMIT;

Jeśli coś pójdzie nie tak przed COMMIT, na przykład naruszenie ograniczenia albo sprawdzenie reguły biznesowej w kodzie aplikacji, ROLLBACK wycofuje wszystko, co stało się od BEGIN:

1
2
3
4
5

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- sprawdzenie reguły biznesowej w kodzie aplikacji wykrywa ujemne saldo
ROLLBACK;

Po rollbacku saldo konta 1 jest dokładnie takie, jakie było przed wykonaniem BEGIN. Przy długiej transakcji, w której chcesz wycofać tylko część pracy, savepoint daje ci częściowy rollback:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

ROLLBACK TO before_credit;  -- wycofuje tylko zasilenie
UPDATE accounts SET balance = balance + 50 WHERE id = 3; -- zasila inne konto

COMMIT;

Poza jawnym BEGIN większość sterowników wykonuje każdą instrukcję w swojej własnej, automatycznie zatwierdzanej transakcji. Każda instrukcja sama w sobie jest atomowa, ale nic nie łączy dwóch instrukcji razem. Pomiń BEGIN w przelewie bankowym i wracasz do straconych 50 zł.

Czemu zapobiegają poziomy izolacji transakcji

Dwie równoczesne transakcje mogą zakłócać się nawzajem w trzech konkretnych sposobach, a poziomy izolacji definiuje się na podstawie tego, które z nich dopuszczają:

Standard SQL definiuje cztery poziomy wokół tych trzech anomalii:

Rzadko będziesz chciał użyć READ UNCOMMITTED. PostgreSQL nawet go nie implementuje i po cichu wykonuje Read Committed; MySQL respektuje go i pokaże ci dirty read. W praktyce liczą się dwa poziomy: Read Committed i Repeatable Read, a dwie główne bazy open source mają różne domyślne ustawienia:

• PostgreSQL domyślnie używa Read Committed. Każda instrukcja w transakcji widzi świeży snapshot, zrobiony w momencie startu tej instrukcji, więc dwa SELECTy w tej samej transakcji mogą zwrócić różne dane, jeśli inna transakcja zatwierdziła się w międzyczasie.
• MySQL (InnoDB) domyślnie używa Repeatable Read. Snapshot jest robiony raz, przy pierwszym odczycie w transakcji, i każdy kolejny zwykły SELECT w tej transakcji widzi ten sam snapshot, nawet jeśli inna transakcja od tamtej pory zatwierdziła update.

W PostgreSQL ustaw poziom zaraz po BEGIN, przed wykonaniem jakiegokolwiek zapytania:

1
2
3
4
5
6
7

-- PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- ... później w tej samej transakcji ...
SELECT balance FROM accounts WHERE id = 1; -- gwarantowana ta sama wartość jak powyżej
COMMIT;

W MySQL forma SESSION zmienia poziom dla każdej kolejnej transakcji na tym połączeniu:

1
2

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Jak Repeatable Read blokuje phantom read w PostgreSQL

Otwórz dwie sesje psql na tej samej tabeli i wykonaj to. Zobaczyć to raz zapada w pamięć lepiej niż zapamiętanie tabeli.

Sesja A:

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

Sesja B, podczas gdy transakcja A jest wciąż otwarta:

1
2
3

BEGIN;
INSERT INTO accounts (id, balance) VALUES (9, 2000);
COMMIT;

Sesja A, ta sama otwarta transakcja, uruchamia identyczne zapytanie ponownie:

1
2
3
4

SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

Wiersz, który B wstawiło i zatwierdziło, nie pojawia się. Repeatable Read w PostgreSQL robi swój snapshot przy pierwszym zapytaniu transakcji A i trzyma go do COMMIT, więc nowe wiersze z innych transakcji nigdy się nie pojawiają. To jest bardziej rygorystyczne, niż wymaga standard: tabela powyżej dopuszcza phantomy na tym poziomie, ale implementacja PostgreSQL ich nie produkuje. Wykonaj tę samą sekwencję z A na Read Committed, a drugi SELECT zwróci nowy wiersz, bo Read Committed robi świeży snapshot przy każdej instrukcji.

Serializable idzie dalej. Wykrywa, kiedy dwie równoczesne transakcje wyprodukowały wynik, który nie mógłby się zdarzyć w żadnej sekwencyjnej kolejności, i przerywa jedną z nich błędem serializacji (PostgreSQL zwraca SQLSTATE 40001) zamiast pozwolić, by anomalia się zatwierdziła. To poziom, który zamyka klasyczny race condition, gdzie dwie transakcje czytają jakąś wartość, decydują na jej podstawie i obie zapisują. Kod używający SERIALIZABLE musi być gotowy na ponowną próbę:

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... instrukcje ...
COMMIT;
-- przy błędzie 40001 ("could not serialize access due to read/write dependencies"):
-- wykonaj rollback i powtórz całą transakcję

Co kosztują bardziej rygorystyczne poziomy izolacji

Cena wyższej izolacji to równoległość:

• Zapisy są blokowane do commitu, na każdym poziomie. UPDATE bierze blokadę wiersza w momencie wykonania i trzyma ją do COMMIT albo ROLLBACK, niezależnie od poziomu izolacji. To, co dodają Repeatable Read i Serializable, to trzymanie tego samego snapshotu (albo, dla Serializable, blokad predykatowych na wierszach, które przeglądały twoje zapytania) przez całą transakcję. Zrób wolne wywołanie zewnętrznego API między BEGIN i COMMIT, i każdy wiersz, który zapisałeś, zostaje zablokowany, a każdy wiersz, który odczytałeś pod Serializable, zostaje śledzony — tak długo, jak trwa to wywołanie.
• Ponowne próby nie są opcjonalne przy Serializable. Z założenia odrzuca transakcje, które wydają się poprawne, przy równoczesnym obciążeniu. Jeśli twoja aplikacja nie przechwytuje błędów serializacji i nie powtarza transakcji, te transakcje zawodzą sporadycznie, pod obciążeniem, czyli w najgorszym możliwym momencie. Repeatable Read może rzucić ten sam błąd 40001 przy zwykłym równoczesnym update, więc kod ponownych prób nie jest potrzebny tylko dla Serializable: każda transakcja powyżej Read Committed go potrzebuje.
• Długie transakcje blokują zmiany schematu i rozdymają storage. MVCC w PostgreSQL trzyma starsze wersje wierszy tak długo, jak jakaś otwarta transakcja może ich jeszcze potrzebować. Transakcja pozostawiona otwarta godzinami (częsty błąd: otworzenie jej w żądaniu web i nigdy niewykonanie commit czy rollback) blokuje VACUUM przed odzyskaniem tego miejsca, a ALTER TABLE na tabeli, której dotknęła, czeka, aż się zakończy.
• Nie każdy zapis potrzebuje jawnej transakcji. Pojedyncza instrukcja UPDATE jest już atomowa. Opakowanie jednej instrukcji w BEGIN/COMMIT dodaje dodatkowy round trip bez żadnej dodatkowej gwarancji. Używaj jawnej transakcji, gdy poprawność zależy od tego, że dwie lub więcej instrukcji powiedzą się lub zawiodą razem: przelew bankowy, rejestracja, która zapisuje zarówno wiersz w users, jak i w accounts, zamówienie, które zmniejsza stan magazynowy i wstawia rekord zamówienia.

ORM-y (Entity Framework, SQLAlchemy, ActiveRecord) zwykle otwierają transakcję na jednostkę pracy i zatwierdzają ją za ciebie. Nie walcz z tym. Błąd, na który trzeba uważać, to coś odwrotnego: kod, który wywołuje .save() albo .commit() osobno dla dwóch powiązanych zapisów, wyrzucając atomowość, którą dałby ORM.

Jaki poziom izolacji wybrać

Zacznij od Read Committed i podnoś poziom tylko dla konkretnych transakcji, gdzie umiesz nazwać wyścig, który zamykasz. To ta sama dyscyplina co dodawanie indeksu tylko po znalezieniu wolnego zapytania. Jeśli nie umiesz zapisać tego wyścigu w jednym zdaniu, jeszcze nie potrzebujesz bardziej rygorystycznego poziomu.

Powiązane artykuły

• Jak Działają Indeksy w Bazie Danych i Kiedy Je Dodawać
• Typy JOIN w SQL Wyjaśnione na Przykładach
• Co To Jest Race Condition i Jak Ją Naprawić