Modalità WAL in SQLite: concorrenza, lettori, scrittori e checkpoint

La modalità predefinita e i suoi limiti

Per impostazione predefinita SQLite usa un rollback journal. Quando scrivi, SQLite copia le pagine originali in un file -journal, modifica il database principale ed elimina il journal al commit. Se il processo si blocca durante una scrittura, il journal viene riapplicato al contrario per annullare la modifica parziale.

È semplice e sicuro, ma ha una caratteristica fastidiosa: scrittori e lettori si contendono lo stesso file. Mentre uno scrittore ha il lock sul database, nessun lettore può iniziare una nuova transazione. Mentre ci sono lettori attivi, lo scrittore aspetta. In un'app trafficata, per esempio un server web con qualche richiesta in parallelo, vedrai errori SQLITE_BUSY prima di quanto vorresti.

La modalità WAL cambia le cose.

Cosa fa davvero WAL

Il write-ahead logging ribalta il modello. Invece di modificare sul posto il file principale del database, lo scrittore aggiunge le pagine confermate a un file separato con il suffisso -wal. I lettori continuano a leggere il file principale, ma danno anche un'occhiata al WAL per vedere eventuali versioni più recenti delle pagine che servono loro.

Il risultato: uno scrittore e un numero qualsiasi di lettori possono essere attivi nello stesso momento. Ogni lettore vede uno snapshot coerente di quando è iniziata la sua transazione, e lo scrittore è impegnato ad aggiungere dati al WAL senza toccare quello che i lettori stanno guardando.

PRAGMA journal_mode=WAL;

Quel solo pragma cambia modalità al database. La modalità è persistente: viene salvata nell'intestazione del file, quindi ogni connessione futura usa WAL automaticamente. Non serve eseguirlo su ogni connessione, basta una volta quando prepari il database (o nel tuo sistema di migrazioni).

Il pragma restituisce la nuova modalità. Se restituisce wal, sei a posto. Se restituisce qualcos'altro, probabilmente il file system non supporta la memoria condivisa (ne parliamo più sotto).

Attivare e verificare

Puoi controllare la modalità attuale in qualsiasi momento:

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;

La prima chiamata attiva WAL e restituisce la nuova modalità. La seconda (senza =) si limita a interrogarla. Da qui in poi, quando c'è attività, la cartella di messages.db conterrà tre file: messages.db, messages.db-wal e messages.db-shm. Gli ultimi due compaiono e scompaiono a seconda che ci siano connessioni aperte.

I file -wal e -shm

Con WAL arrivano due file in più, ed è bene sapere cosa fanno:

• -wal contiene le transazioni confermate che non sono ancora state riportate nel database principale. Cresce man mano che avvengono scritture e si riduce (o si azzera) al momento del checkpoint.
• -shm è un file di memoria condivisa. È un indice del WAL che permette a tutte le connessioni di concordare su dove si trovano le pagine senza scorrere il WAL a ogni query.

La conseguenza pratica: non copiare mai un database in modalità WAL copiando solo il file .db. I dati più recenti stanno nel -wal, e senza di esso la tua copia è vecchia o corrotta. Copia tutti e tre i file mentre nessuna connessione sta scrivendo oppure, molto meglio, usa la backup API di SQLite (la vediamo nel prossimo capitolo).

Concorrenza: uno scrittore, molti lettori

WAL non ti dà scritture concorrenti. SQLite continua a serializzarle: in ogni momento, esattamente una transazione ha il lock di scrittura. Quello che cambia è che le scritture non bloccano le letture e le letture non bloccano le scritture.

Quindi una tipica app web che gira in WAL si comporta così:

• Gli endpoint con tante letture vengono eseguiti in parallelo senza contesa.
• Gli endpoint di scrittura si mettono brevemente in coda uno dietro l'altro, ma non bloccano le letture.
• I lettori che durano a lungo (query di analisi, esportazioni) non fanno aspettare gli scrittori.

Se due connessioni provano a scrivere nello stesso momento, la seconda riceve SQLITE_BUSY. Di solito la soluzione è un busy timeout ragionevole, cioè dire a SQLite di aspettare un po' prima di arrendersi:

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 significa "se un lock è occupato, aspetta fino a 5 secondi prima di sollevare un errore". Insieme a WAL, gestisce la contesa che la maggior parte delle app incontra davvero. La forma BEGIN IMMEDIATE prende il lock di scrittura all'inizio della transazione invece che alla prima scrittura, ed evita così un'intera categoria di deadlock di upgrade quando più connessioni intendono scrivere.

Checkpoint: riportare il WAL nel database

Il file WAL non può crescere all'infinito. Il checkpoint è il processo che prende le pagine confermate nel WAL, le scrive nel database principale e poi azzera il WAL.

SQLite fa il checkpoint in automatico quando il WAL supera circa 1000 pagine (il wal_autocheckpoint predefinito). Nella maggior parte delle app puoi lasciarlo com'è. Se vuoi regolarlo o avviarne uno a mano:

PRAGMA wal_autocheckpoint;
PRAGMA wal_autocheckpoint=2000;

PRAGMA wal_checkpoint(TRUNCATE);

Il pragma wal_checkpoint accetta una modalità:

• PASSIVE: fa il checkpoint di quanto più possibile senza disturbare lettori e scrittori. È il predefinito.
• FULL: aspetta che gli scrittori attivi finiscano, poi fa il checkpoint di tutto ciò che è confermato.
• RESTART: come FULL, e in più impedisce ai nuovi lettori di usare il vecchio WAL.
• TRUNCATE: come RESTART, e in più riduce il file WAL a zero byte.

La maggior parte dei server non ha mai bisogno di chiamarlo a mano. Se distribuisci un'app desktop che vuole tenere in ordine la dimensione dei file alla chiusura, un checkpoint TRUNCATE prima di chiudere l'ultima connessione è una buona abitudine.

Alcuni pragma che si abbinano bene a WAL

WAL da solo è già buono. WAL più un paio di altre impostazioni è quello che usano di solito le app in produzione:

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

Un giro veloce:

• synchronous=NORMAL è l'abbinamento consigliato con WAL. È sicuro contro i crash dell'applicazione e del sistema operativo; solo un'interruzione di corrente nell'istante sbagliato può far perdere le transazioni più recenti, e anche in quel caso il database resta coerente. Il FULL predefinito è più sicuro ma sensibilmente più lento.
• busy_timeout l'abbiamo visto sopra.
• foreign_keys=ON non c'entra con WAL, ma conviene impostarlo su ogni connessione: SQLite lascia disattivato il controllo delle chiavi esterne per compatibilità con il passato.

Queste impostazioni valgono per connessione (tranne journal_mode, che resta). Eseguile subito dopo aver aperto la connessione nel codice della tua app.

Quando WAL non è la scelta giusta

WAL è la raccomandazione predefinita, ma alcune situazioni vanno in direzione contraria:

• File system di rete. WAL si basa sulla memoria condivisa (mmap) tra i processi che accedono al database. NFS, SMB e simili non la supportano in modo affidabile. Se il tuo database sta su una condivisione di rete, resta sul rollback journal oppure, meglio ancora, non mettere SQLite su una condivisione di rete.
• Supporti in sola lettura. WAL deve scrivere i file -wal e -shm. Un database su un CD-ROM o simili deve usare una modalità journal che non scrive (oppure essere aperto in sola lettura con mode=ro).
• Job batch con un solo scrittore e nessun lettore concorrente. WAL non fa danni, ma non ci guadagni niente. Il rollback journal predefinito va bene.

Per il 95% delle applicazioni (backend web, app desktop, app mobile, dispositivi embedded con memoria locale) WAL è la scelta giusta.

Una configurazione realistica

Ecco la forma che prende la maggior parte delle configurazioni SQLite in produzione, condensata in pragma eseguibili:

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 tiene le tabelle e gli indici temporanei in RAM invece che su disco: un piccolo guadagno gratuito se hai memoria in abbondanza.

Collega tutto questo una volta, al momento della connessione, nella configurazione del database della tua applicazione, e avrai gestito la gran parte di ciò che serve a un'app basata su SQLite per comportarsi bene sotto carico concorrente.

Prossimo passo: backup e ripristino

Ora che il tuo database ha i compagni -wal e -shm, copiare il file non è più una strategia di backup sicura. Il prossimo capitolo spiega il modo giusto per fare il backup di un database SQLite attivo: il comando .backup, la backup API online e cosa fare quando ti serve uno snapshot coerente senza mettere offline l'app.