SQLite WAL : concurrence, lecteurs, écrivains

Le mode par défaut et ses limites

Par défaut, SQLite utilise un rollback journal. À chaque écriture, SQLite copie les pages originales dans un fichier -journal, modifie la base principale, puis supprime le journal au commit. Si le processus plante en pleine écriture, le journal est rejoué à l'envers pour annuler la modification partielle.

C'est simple et sûr, mais il y a un défaut bien gênant : lecteurs et écrivains se disputent le même fichier. Tant qu'un écrivain détient le verrou de la base, aucun lecteur ne peut démarrer une nouvelle transaction. Et tant que des lecteurs sont actifs, l'écrivain patiente. Sur une appli sollicitée — un serveur web avec quelques requêtes concurrentes, par exemple — vous allez voir débarquer des erreurs SQLITE_BUSY plus vite que prévu.

Le mode WAL change la donne.

Ce que fait réellement le mode WAL de SQLite

Le write-ahead logging inverse complètement le principe. Au lieu de modifier le fichier principal de la base sur place, l'écrivain ajoute les pages validées à la fin d'un fichier séparé avec le suffixe -wal. Les lecteurs continuent de lire le fichier principal, mais ils jettent aussi un œil au WAL pour récupérer les versions plus récentes des pages dont ils ont besoin.

Résultat : un écrivain et autant de lecteurs que vous voulez peuvent travailler en même temps. Chaque lecteur voit un snapshot cohérent correspondant au moment où sa transaction a démarré, pendant que l'écrivain alimente tranquillement le WAL sans toucher à ce que lisent les autres.

PRAGMA journal_mode=WAL;

Ce simple pragma fait basculer la base de données. Le mode est persistant : il est inscrit dans l'en-tête du fichier, donc chaque nouvelle connexion utilise automatiquement WAL. Pas besoin de l'exécuter à chaque connexion, une seule fois suffit, au moment de provisionner la base (ou dans votre script de migration).

Le pragma renvoie le nouveau mode en retour. S'il retourne wal, c'est bon, tout est en place. S'il retourne autre chose, c'est probablement que le système de fichiers ne prend pas en charge la mémoire partagée (on y revient plus bas).

Activer le mode WAL SQLite et vérifier qu'il est bien en place

Vous pouvez vérifier le mode actuel à tout moment :

PRAGMA journal_mode=WAL;
PRAGMA journal_mode;

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

Le premier appel active le mode WAL et renvoie le nouveau mode. Le second (sans =) se contente de l'interroger. À partir de là, le dossier de votre messages.db contiendra trois fichiers dès qu'il y a de l'activité : messages.db, messages.db-wal et messages.db-shm. Les deux derniers apparaissent et disparaissent au gré des connexions ouvertes.

Les fichiers -wal et -shm

Le mode WAL s'accompagne de deux fichiers supplémentaires, et il vaut mieux savoir à quoi ils servent :

• -wal contient les transactions validées qui n'ont pas encore été réintégrées dans la base principale. Il grossit au fil des écritures, puis se réduit (ou se remet à zéro) au moment du checkpoint.
• -shm est un fichier de mémoire partagée. Il sert d'index dans le WAL pour que toutes les connexions sachent où se trouve chaque page, sans avoir à parcourir le WAL à chaque requête.

Conséquence concrète : ne copiez jamais une base en mode WAL en ne récupérant que le fichier .db. Les données les plus récentes vivent dans le -wal, et sans lui votre copie est périmée, voire corrompue. Soit vous copiez les trois fichiers à un moment où aucune connexion n'écrit, soit — et c'est largement préférable — vous utilisez l'API de sauvegarde de SQLite (que l'on verra au prochain chapitre).

Concurrence : un seul rédacteur, plusieurs lecteurs

Le mode WAL ne permet pas les écritures concurrentes. SQLite continue de les sérialiser : à un instant donné, une seule transaction détient le verrou d'écriture. Ce qui change, c'est que les écritures ne bloquent plus les lectures, et inversement.

Du coup, une application web typique sous WAL se comporte ainsi :

• Les endpoints qui lisent beaucoup tournent en parallèle sans se marcher dessus.
• Les endpoints qui écrivent font brièvement la queue, mais ne bloquent pas les lectures.
• Les lectures longues (requêtes analytiques, exports) ne forcent pas les écrivains à patienter.

Si deux connexions tentent d'écrire en même temps, la seconde reçoit un SQLITE_BUSY. La parade habituelle est de fixer un busy timeout raisonnable — autrement dit, dire à SQLite d'attendre un peu avant d'abandonner :

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

PRAGMA busy_timeout=5000;

BEGIN IMMEDIATE;
INSERT INTO messages (body) VALUES ('écriture en file');
COMMIT;

SELECT * FROM messages;

busy_timeout=5000 veut dire « si un verrou est posé, attends jusqu'à 5 secondes avant de remonter une erreur ». Couplé au mode WAL, ça suffit à gérer la contention que rencontrent la plupart des applis dans la vraie vie. La forme BEGIN IMMEDIATE, elle, prend le verrou d'écriture dès le début de la transaction au lieu d'attendre la première écriture — ce qui évite toute une catégorie d'interblocages liés à la promotion de verrou quand plusieurs connexions veulent écrire en même temps.

Checkpoint WAL : replier le journal dans la base

Le fichier WAL ne peut pas grossir indéfiniment. Le checkpoint est l'opération qui prend les pages validées dans le WAL, les recopie dans la base principale, puis réinitialise le WAL.

SQLite déclenche un checkpoint automatiquement dès que le WAL dépasse ~1000 pages (la valeur par défaut de wal_autocheckpoint). Dans la majorité des cas, autant ne pas y toucher. Mais si vous voulez ajuster ce seuil ou forcer un checkpoint manuellement :

PRAGMA wal_autocheckpoint;
PRAGMA wal_autocheckpoint=2000;

PRAGMA wal_checkpoint(TRUNCATE);

Le pragma wal_checkpoint accepte un mode :

• PASSIVE — fait avancer le checkpoint au maximum sans gêner les lecteurs ni les écrivains. C'est la valeur par défaut.
• FULL — attend que les écritures en cours se terminent, puis applique le checkpoint sur tout ce qui a été validé.
• RESTART — comme FULL, mais empêche aussi les nouveaux lecteurs d'utiliser l'ancien WAL.
• TRUNCATE — comme RESTART, et en plus ramène le fichier WAL à zéro octet.

La plupart des serveurs n'ont jamais besoin de l'appeler à la main. En revanche, si vous distribuez une application de bureau et que vous voulez garder des fichiers propres à la fermeture, lancer un checkpoint TRUNCATE avant de fermer la dernière connexion est une bonne habitude à prendre.

Quelques pragmas qui se marient bien avec le mode WAL

Le mode WAL tout seul, c'est déjà très bien. Mais en pratique, les applis en production l'associent presque toujours à deux ou trois autres réglages :

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

Petit tour d'horizon :

• synchronous=NORMAL est le réglage recommandé en combinaison avec WAL. Il résiste aux crashs applicatifs comme aux crashs de l'OS ; seule une coupure de courant au pire moment peut faire perdre les dernières transactions, et même dans ce cas la base reste cohérente. La valeur par défaut FULL est plus sûre, mais sensiblement plus lente.
• busy_timeout, on en a parlé plus haut.
• foreign_keys=ON n'a rien à voir avec WAL, mais ça vaut le coup de l'activer sur chaque connexion — SQLite laisse la vérification des clés étrangères désactivée par défaut, pour des raisons de compatibilité ascendante.

Ces réglages s'appliquent par connexion (sauf journal_mode, qui lui est persistant). Lancez-les juste après l'ouverture de la connexion, dans le code de votre appli.

Quand WAL n'est pas le bon choix

Le mode WAL est la recommandation par défaut, mais quelques cas de figure poussent à faire autrement :

• Systèmes de fichiers réseau. Le mode WAL repose sur la mémoire partagée (mmap) entre les processus qui accèdent à la base. NFS, SMB et compagnie ne gèrent pas ça de façon fiable. Si votre base vit sur un partage réseau, restez sur le journal de rollback — ou mieux, évitez carrément de mettre du SQLite sur un partage réseau.
• Supports en lecture seule. WAL a besoin d'écrire les fichiers -wal et -shm. Une base sur un CD-ROM ou support similaire doit utiliser un mode de journalisation qui n'écrit pas (ou être ouverte en lecture seule avec mode=ro).
• Traitements batch mono-écrivain sans lecteurs concurrents. Le mode WAL ne pose pas de problème, mais vous n'y gagnez rien non plus. Le journal de rollback par défaut fait très bien le job.

Pour 95 % des applications — backends web, applis desktop, applis mobiles, appareils embarqués avec stockage local — WAL est le bon choix.

Une configuration réaliste

Voici à quoi ressemblent la plupart des configurations SQLite en production, condensées en pragmas prêts à exécuter :

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 ('connexion'), ('inscription'), ('achat');
SELECT id, created_at, payload FROM events;

temp_store=MEMORY garde les tables et index temporaires en RAM plutôt que sur le disque — un petit gain quasi gratuit si vous avez un peu de mémoire à revendre.

Branchez tout ça une seule fois à l'ouverture de la connexion, dans la phase d'initialisation de la base côté application, et vous aurez réglé l'essentiel de ce qu'il faut pour qu'une appli adossée à SQLite tienne la charge en accès concurrents.

La suite : sauvegarde et restauration

Maintenant que votre base s'accompagne de fichiers -wal et -shm, copier bêtement le fichier .db ne constitue plus une stratégie de sauvegarde fiable. Le prochain chapitre détaille la bonne façon de sauvegarder une base SQLite en cours d'utilisation : la commande .backup, l'API de sauvegarde en ligne, et comment obtenir un instantané cohérent sans avoir à couper l'application.