Transakcje w SQLite: BEGIN, COMMIT i ROLLBACK

Transakcja to pakiet typu „wszystko albo nic”

Transakcja grupuje kilka instrukcji tak, że albo wszystkie zostają wykonane, albo żadna. Jeśli coś pójdzie nie tak w połowie, możesz wycofać zmiany i baza wraca dokładnie do stanu początkowego.

Klasyczny przykład to przelew pieniędzy:

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

Te dwa UPDATE należą do siebie. Gdyby baza padła między nimi, Ada byłaby o 2000 centów biedniejsza, a Boris nie dostałby nic. Ujęcie ich w BEGIN ... COMMIT sprawia, że para jest atomowa: wykonują się obie albo żadna.

Autocommit: domyślny tryb, którego już używasz

Każda instrukcja SQL uruchomiona do tej pory była transakcją. SQLite domyślnie działa w trybie autocommit: każda instrukcja dostaje własne, niejawne BEGIN i COMMIT.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

INSERT INTO notes (body) VALUES ('first');
INSERT INTO notes (body) VALUES ('second');
INSERT INTO notes (body) VALUES ('third');

SELECT * FROM notes;

Trzy wstawienia, trzy osobne transakcje, trzy zapisy na dysk z fsync. To wystarcza przy pojedynczych zapisach, ale przy masowym ładowaniu danych jest wolne, a do tego nie możesz cofnąć grupy instrukcji jako całości. BEGIN wyłącza autocommit do najbliższego COMMIT albo ROLLBACK.

ROLLBACK: udajemy, że nic się nie stało

ROLLBACK odrzuca wszystko, co zrobiono od odpowiadającego mu BEGIN. Baza wraca do stanu sprzed transakcji.

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

Znikają zarówno UPDATE, jak i DELETE: tabela wygląda tak jak przed BEGIN. To siatka bezpieczeństwa, dzięki której kod aplikacji może czysto przerwać operację złożoną z wielu instrukcji, gdy w połowie trafi na błąd.

Przy okazji: naruszenie ograniczenia wewnątrz transakcji nie wycofuje automatycznie całej transakcji. Wycofuje tylko instrukcję, która je spowodowała, i zostawia transakcję otwartą, czekając na twoją decyzję. Jeśli chcesz „wszystko albo nic”, aplikacja musi wykonać ROLLBACK, gdy zobaczy błąd.

Przyspieszanie masowych wstawień

Ponieważ każda instrukcja w trybie autocommit wykonuje własny fsync, ujęcie paczki w jedną transakcję jest często nawet 100 razy szybsze:

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

Jedna synchronizacja z dyskiem przy COMMIT zamiast jednej na każdy wiersz. Jeśli kiedyś importujesz tysiące wierszy i zastanawiasz się, czemu to się tak wlecze, odpowiedź prawie zawsze jest właśnie ta.

DEFERRED, IMMEDIATE, EXCLUSIVE

BEGIN przyjmuje tryb, który decyduje, kiedy SQLite zakłada blokady:

• BEGIN DEFERRED (domyślny): żadnej blokady, dopóki nie zaczniesz czytać lub pisać. Blokada zapisu jest zakładana leniwie, przy pierwszej instrukcji zapisującej.
• BEGIN IMMEDIATE: blokada zapisu od razu. Inne połączenia nadal mogą czytać, ale żadne inne połączenie nie może zacząć pisać.
• BEGIN EXCLUSIVE: jak IMMEDIATE, a do tego żadne inne połączenie nie może też czytać. W trybie WAL działa tak samo jak IMMEDIATE; różnica ma znaczenie tylko w starszym trybie rollback journal.

BEGIN DEFERRED;     -- to samo co zwykłe BEGIN
BEGIN IMMEDIATE;    -- zarezerwuj blokadę zapisu teraz
BEGIN EXCLUSIVE;    -- zarezerwuj wszystko (tryb rollback journal)

Ten wybór ma znaczenie dla współbieżności. Przy zwykłym BEGIN dwa połączenia mogą rozpocząć transakcję, spokojnie czytać, a potem ścigać się przy próbie zapisu: to, które drugie poprosi o blokadę zapisu, dostaje SQLITE_BUSY, a co gorsza, wykonało już odczyty, które teraz musi wyrzucić.

BEGIN IMMEDIATE to rozwiązuje: jeśli wiesz, że będziesz pisać, najpierw poproś o blokadę zapisu. Drugie połączenie zostaje zablokowane (albo szybko dostaje błąd) od razu, zanim wykona pracę, którą musiałoby odrzucić.

Praktyczna zasada: jeśli twoja transakcja będzie zapisywać dane, użyj BEGIN IMMEDIATE.

Odczyty w transakcji widzą migawkę

Dopóki transakcja jest otwarta, twoje odczyty widzą spójną migawkę bazy z chwili rozpoczęcia transakcji (w trybie WAL) albo z chwili pierwszego odczytu (w trybie rollback journal). Zmiany zatwierdzane przez inne połączenia nie pojawią się nagle w twoich zapytaniach.

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99, twój własny zapis jest widoczny
COMMIT;

SELECT n FROM counter;

Widzisz własne niezatwierdzone zapisy; inne połączenia ich nie widzą. Po COMMIT nowa wartość staje się widoczna dla wszystkich. To właśnie mają na myśli ludzie, którzy mówią, że SQLite jest serializable: nie ma przełącznika READ COMMITTED, bo domyślny poziom jest już najsilniejszy.

Transakcja w kodzie aplikacji

W prawdziwym programie schemat to zwykle try/except (albo try/catch) wokół treści, z ROLLBACK na ścieżce błędu:

-- Pseudokod dla dowolnej biblioteki klienckiej
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

Większość bibliotek klienckich (sqlite3 w Pythonie, better-sqlite3 itd.) opakowuje to za ciebie w blok with albo pomocniczą funkcję transaction(). Warto sprawdzić dokumentację swojej biblioteki: domyślne ustawienia nie zawsze są takie, jakich się spodziewasz. Zwłaszcza sqlite3 w Pythonie miał historycznie dziwne zachowanie autocommit; nowsze wersje dodały porządny parametr autocommit, który to naprawia.

Na czym ludzie się potykają

• DDL wewnątrz transakcji działa. CREATE TABLE, ALTER TABLE, a nawet DROP TABLE można wycofać. Pod tym względem SQLite jest nietypowy: wiele baz danych automatycznie zatwierdza DDL.
• VACUUM nie może działać wewnątrz transakcji. Podobnie kilka innych poleceń konserwacyjnych. Uruchamiaj je w trybie autocommit.
• Nieudany COMMIT to wciąż prawdziwy błąd. Jeśli COMMIT zwraca SQLITE_BUSY (rzadko, ale możliwe), transakcja nie jest zatwierdzona. Twój kod musi to obsłużyć, zwykle przez ponowienie próby.
• Długie transakcje blokują piszących. Transakcja otwarta przez kilka minut przez kilka minut blokuje inne zapisy. Otwieraj je późno, zatwierdzaj szybko.

Dalej: savepointy

BEGIN i COMMIT działają na zasadzie „wszystko albo nic”. Czasem chcesz wycofać tylko część transakcji, na przykład porzucić jeden ryzykowny krok, a resztę zachować. Do tego służą savepointy i to one są następne.