Concurrence : verrous, niveaux d'isolation, lectures fantômes

Comprenez le modèle de verrouillage de SQLite, ses transactions et les anomalies d'isolation que la norme SQL décrit.

12 min · 3 questions

SQLite verrouille au niveau du fichier de base entier, pas de la ligne. Dans le mode par défaut (journal de rollback), les lecteurs partagent un verrou SHARED, mais un seul écrivain à la fois peut obtenir le verrou EXCLUSIVE. Conséquence : les écritures sont sérialisées. Créons une table pour manipuler des transactions.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  balance INTEGER NOT NULL
);

INSERT INTO accounts (id, balance)
VALUES (1, 100), (2, 50);

-- Lecture : transaction differee (defaut)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

Le verrou évolue par paliers : UNLOCKED, puis SHARED (lecture), RESERVED (intention d'écrire), PENDING et enfin EXCLUSIVE (écriture). Si une transaction détient déjà le verrou d'écriture, une autre reçoit l'erreur SQLITE_BUSY. Plutôt que d'échouer aussitôt, réglez un délai d'attente avec busy_timeout, et activez le mode WAL pour laisser lecteurs et écrivain progresser en parallèle.

-- Un seul writer, lecteurs concurrents
PRAGMA journal_mode = WAL;

-- Attendre jusqu'a 5 s si la base est occupee
PRAGMA busy_timeout = 5000;

La norme SQL décrit trois anomalies : la lecture sale (lire une donnée non validée), la lecture non répétable (relire une ligne et voir une autre valeur), et la lecture fantôme (rejouer une requête de plage et voir surgir de nouvelles lignes). Comme SQLite sérialise les écrivains, il se comporte de fait en SERIALIZABLE : ces anomalies n'apparaissent pas (hors mode cache partagé avec read_uncommitted). Ailleurs, on choisit le niveau explicitement.

-- Virement atomique : verrou d'ecriture immediat
BEGIN IMMEDIATE;
UPDATE accounts
SET balance = balance - 10
WHERE id = 1;
UPDATE accounts
SET balance = balance + 10
WHERE id = 2;
COMMIT;

Quiz de validation

Vérifiez que vous avez bien retenu les points clés de cette leçon.

1. Quel niveau d'isolation SQLite offre-t-il en pratique ?
   • READ UNCOMMITTED, les lectures sales sont possibles par défaut
   • READ COMMITTED, comme PostgreSQL
   • SERIALIZABLE, car un seul écrivain agit à la fois
   • Aucun, il n'y a pas de transactions
2. Quelle est la différence entre BEGIN et BEGIN IMMEDIATE ?
   • BEGIN IMMEDIATE prend le verrou d'écriture aussitôt ; BEGIN attend le premier accès en écriture
   • BEGIN IMMEDIATE désactive le journal de rollback
   • BEGIN IMMEDIATE crée une nouvelle base de données
   • Il n'y a aucune différence de comportement
3. Qu'est-ce qu'une lecture fantôme (phantom read) ?
   • Lire une valeur qu'une autre transaction n'a pas encore validée
   • Relire la même ligne et obtenir une valeur différente
   • Rejouer une requête de plage et voir apparaître de nouvelles lignes insérées entre-temps
   • Lire une ligne qui vient d'être supprimée