Tryb WAL i współbieżność w SQLite: odczyty, zapisy, checkpointy

Tryb domyślny i jego ograniczenia

Domyślnie SQLite używa rollback journal. Przy zapisie SQLite kopiuje oryginalne strony do pliku -journal, modyfikuje główną bazę i usuwa dziennik przy zatwierdzeniu. Jeśli proces padnie w trakcie zapisu, dziennik jest odtwarzany wstecz, żeby cofnąć częściową zmianę.

To proste i bezpieczne, ale ma jedną bolesną cechę: piszący i czytelnicy walczą o ten sam plik. Dopóki piszący trzyma blokadę bazy, żaden czytelnik nie może rozpocząć nowej transakcji. Dopóki czytelnicy są aktywni, piszący czeka. W obciążonej aplikacji, na przykład na serwerze WWW z kilkoma równoczesnymi żądaniami, błędy SQLITE_BUSY pojawią się szybciej, niż można by się spodziewać.

Tryb WAL to zmienia.

Co właściwie robi WAL

Write-ahead logging odwraca ten model. Zamiast modyfikować główny plik bazy na miejscu, piszący dopisuje zatwierdzone strony do osobnego pliku z przyrostkiem -wal. Czytelnicy nadal czytają plik główny, ale zaglądają też do WAL, żeby zobaczyć nowsze wersje potrzebnych stron.

Efekt: piszący i dowolna liczba czytelników mogą działać w tym samym momencie. Każdy czytelnik widzi spójną migawkę z chwili rozpoczęcia swojej transakcji, a piszący dopisuje do WAL, nie ruszając tego, na co patrzą czytelnicy.

PRAGMA journal_mode=WAL;

Ta jedna pragma przełącza bazę. Tryb jest trwały: zapisuje się w nagłówku pliku, więc każde przyszłe połączenie automatycznie używa WAL. Nie musisz uruchamiać tego przy każdym połączeniu, wystarczy raz przy przygotowaniu bazy (albo w narzędziu do migracji).

Pragma zwraca nowy tryb. Jeśli zwraca wal, wszystko gotowe. Jeśli zwraca coś innego, system plików prawdopodobnie nie obsługuje pamięci współdzielonej (więcej o tym niżej).

Włączanie i sprawdzanie

Bieżący tryb możesz sprawdzić w dowolnej chwili:

PRAGMA journal_mode=WAL;
PRAGMA journal_mode;

CREATE TABLE messages (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO messages (body) VALUES ('hello'), ('world');
SELECT * FROM messages;

Pierwsze wywołanie włącza WAL i zwraca nowy tryb. Drugie (bez =) tylko go odczytuje. Od tej pory, gdy baza jest w użyciu, w katalogu z messages.db będą trzy pliki: messages.db, messages.db-wal i messages.db-shm. Dwa ostatnie pojawiają się i znikają w zależności od tego, czy są otwarte połączenia.

Pliki -wal i -shm

Z WAL przychodzą dwa dodatkowe pliki i warto wiedzieć, do czego służą:

• -wal przechowuje zatwierdzone transakcje, które nie zostały jeszcze scalone z główną bazą. Rośnie wraz z zapisami i kurczy się (albo zeruje) przy checkpoincie.
• -shm to plik pamięci współdzielonej. Jest indeksem do WAL, dzięki któremu wszystkie połączenia zgadzają się co do tego, gdzie leżą które strony, bez skanowania WAL przy każdym zapytaniu.

Praktyczna konsekwencja: nigdy nie kopiuj bazy w trybie WAL, kopiując tylko plik .db. Najnowsze dane są w -wal, a bez niego twoja kopia jest nieaktualna albo uszkodzona. Albo skopiuj wszystkie trzy pliki, gdy żadne połączenie nie zapisuje, albo, co dużo lepsze, użyj API kopii zapasowych SQLite (omawiamy je w następnym rozdziale).

Współbieżność: jeden piszący, wielu czytelników

WAL nie daje współbieżnych zapisów. SQLite nadal wykonuje je po kolei: w każdej chwili blokadę zapisu trzyma dokładnie jedna transakcja. Zmieniło się to, że zapisy nie blokują odczytów, a odczyty nie blokują zapisów.

Typowa aplikacja webowa w trybie WAL zachowuje się więc tak:

• Endpointy, które głównie czytają, działają równolegle bez rywalizacji.
• Endpointy zapisujące na chwilę ustawiają się w kolejce, ale nie blokują odczytów.
• Długo działający czytelnicy (zapytania analityczne, eksporty) nie każą piszącym czekać.

Jeśli dwa połączenia próbują pisać jednocześnie, drugie dostaje SQLITE_BUSY. Rozwiązaniem jest zwykle rozsądny limit oczekiwania: każ SQLite chwilę poczekać, zanim się podda:

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

PRAGMA busy_timeout=5000;

BEGIN IMMEDIATE;
INSERT INTO messages (body) VALUES ('queued write');
COMMIT;

SELECT * FROM messages;

busy_timeout=5000 oznacza „jeśli blokada jest zajęta, czekaj na nią do 5 sekund, zanim zgłosisz błąd”. W połączeniu z WAL to rozwiązuje rywalizację, z którą faktycznie mierzy się większość aplikacji. Forma BEGIN IMMEDIATE zakłada blokadę zapisu na początku transakcji zamiast przy pierwszym zapisie, co pozwala uniknąć całej klasy zakleszczeń przy podnoszeniu blokady, gdy kilka połączeń zamierza pisać.

Checkpointy: scalanie WAL z powrotem

Plik WAL nie może rosnąć w nieskończoność. Checkpoint to proces, który bierze zatwierdzone strony z WAL, zapisuje je w głównej bazie, a potem zeruje WAL.

SQLite wykonuje checkpoint automatycznie, gdy WAL przekroczy ok. 1000 stron (domyślne wal_autocheckpoint). W większości aplikacji możesz to zostawić. Jeśli chcesz to dostroić albo wywołać checkpoint ręcznie:

PRAGMA wal_autocheckpoint;
PRAGMA wal_autocheckpoint=2000;

PRAGMA wal_checkpoint(TRUNCATE);

Pragma wal_checkpoint przyjmuje tryb:

• PASSIVE: wykonaj checkpoint w możliwie największym zakresie, nie przeszkadzając czytelnikom ani piszącym. Domyślny.
• FULL: poczekaj, aż aktywni piszący skończą, a potem przenieś wszystko, co zatwierdzone.
• RESTART: jak FULL, a do tego zablokuj nowym czytelnikom korzystanie ze starego WAL.
• TRUNCATE: jak RESTART, a do tego zmniejsz plik WAL z powrotem do zera bajtów.

Większość serwerów nigdy nie musi wywoływać tego ręcznie. Jeśli wydajesz aplikację desktopową, która ma utrzymywać porządek w rozmiarach plików przy zamykaniu, checkpoint TRUNCATE przed zamknięciem ostatniego połączenia to rozsądny nawyk.

Kilka pragm, które dobrze współgrają z WAL

Sam WAL jest dobry. WAL z kilkoma innymi ustawieniami to zwykle to, czego używają aplikacje produkcyjne:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;

Krótki przegląd:

• synchronous=NORMAL to zalecana para dla WAL. Chroni przed awariami aplikacji i systemu operacyjnego; tylko utrata zasilania w nieodpowiednim momencie może skasować najnowsze transakcje, a nawet wtedy baza pozostaje spójna. Domyślne FULL jest bezpieczniejsze, ale wyraźnie wolniejsze.
• busy_timeout omówiliśmy wyżej.
• foreign_keys=ON nie ma związku z WAL, ale warto to ustawiać przy każdym połączeniu: SQLite domyślnie nie wymusza kluczy obcych ze względu na zgodność wsteczną.

Te ustawienia działają na poziomie połączenia (poza journal_mode, które zostaje na stałe). Uruchamiaj je w kodzie aplikacji zaraz po otwarciu połączenia.

Kiedy WAL nie jest właściwym wyborem

WAL to domyślna rekomendacja, ale w kilku sytuacjach się nie sprawdza:

• Sieciowe systemy plików. WAL opiera się na pamięci współdzielonej (mmap) między procesami korzystającymi z bazy. NFS, SMB i podobne nie obsługują tego niezawodnie. Jeśli baza leży na udziale sieciowym, zostań przy rollback journal, a jeszcze lepiej: nie trzymaj SQLite na udziale sieciowym.
• Nośniki tylko do odczytu. WAL musi zapisywać pliki -wal i -shm. Baza na płycie CD-ROM lub podobnym nośniku musi używać trybu dziennika, który nie zapisuje (albo być otwarta tylko do odczytu z mode=ro).
• Zadania wsadowe z jednym piszącym i bez współbieżnych czytelników. WAL nie zaszkodzi, ale też nic nie zyskasz. Domyślny rollback journal wystarczy.

W 95% aplikacji (backendy webowe, aplikacje desktopowe i mobilne, urządzenia wbudowane z lokalną pamięcią) WAL to właściwy wybór.

Realistyczna konfiguracja

Oto kształt, jaki przyjmuje większość produkcyjnych konfiguracji SQLite, zebrany w pragmy do uruchomienia:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;
PRAGMA temp_store=MEMORY;

CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    payload TEXT NOT NULL
);

INSERT INTO events (payload) VALUES ('login'), ('signup'), ('purchase');
SELECT id, created_at, payload FROM events;

temp_store=MEMORY trzyma tymczasowe tabele i indeksy w RAM zamiast na dysku: mała korzyść, która nic nie kosztuje, jeśli masz zapas pamięci.

Skonfiguruj to raz przy nawiązywaniu połączenia w warstwie bazy danych swojej aplikacji, a załatwisz większość tego, czego aplikacja oparta na SQLite potrzebuje, żeby dobrze działać pod współbieżnym obciążeniem.

Dalej: kopia zapasowa i przywracanie

Skoro baza ma teraz towarzyszy -wal i -shm, kopiowanie pliku nie jest już bezpieczną strategią tworzenia kopii zapasowych. Następny rozdział pokazuje, jak poprawnie zrobić kopię działającej bazy SQLite: polecenie .backup, API kopii zapasowych online i co zrobić, gdy potrzebujesz spójnej migawki bez wyłączania aplikacji.