Transactions SQLite : BEGIN, COMMIT et ROLLBACK

Une transaction, c'est du tout ou rien

Une transaction SQLite regroupe plusieurs instructions qui s'exécutent comme un seul bloc : soit tout passe, soit rien ne passe. Si quoi que ce soit tourne mal en cours de route, on annule la transaction et la base se retrouve exactement dans l'état de départ.

L'exemple le plus parlant, c'est le virement bancaire :

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;

Les deux UPDATE forment un tout. Si la base plantait entre les deux, Ada perdrait 2000 centimes et Boris ne recevrait rien. En les enveloppant dans un BEGIN ... COMMIT, on rend la paire atomique : soit les deux passent, soit aucun.

Le mode autocommit de SQLite : ce que vous utilisez déjà sans le savoir

Chaque instruction SQL que vous avez exécutée jusqu'ici était en réalité une transaction. Par défaut, SQLite fonctionne en mode autocommit : un BEGIN et un COMMIT implicites entourent automatiquement chaque instruction.

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

INSERT INTO notes (body) VALUES ('première');
INSERT INTO notes (body) VALUES ('deuxième');
INSERT INTO notes (body) VALUES ('troisième');

SELECT * FROM notes;

Trois INSERT, trois transactions distinctes, et trois allers-retours sur disque pour faire un fsync à chaque modification. Ça passe pour des écritures isolées, mais c'est trop lent pour des chargements en masse — et surtout, impossible d'annuler un groupe d'instructions d'un seul coup. BEGIN désactive le mode autocommit jusqu'au prochain COMMIT ou ROLLBACK.

ROLLBACK : faire comme si rien ne s'était passé

ROLLBACK annule tout ce qui a été fait depuis le BEGIN correspondant. La base revient à son état d'avant la transaction.

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

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'pomme';
DELETE FROM inventory WHERE item = 'poire';
ROLLBACK;

SELECT * FROM inventory;

UPDATE et DELETE disparaissent tous les deux : la table revient exactement à l'état où elle était avant le BEGIN. C'est le filet de sécurité qui permet au code applicatif d'abandonner proprement quand une erreur survient au milieu d'une opération composée de plusieurs instructions.

Au passage, une violation de contrainte à l'intérieur d'une transaction ne déclenche pas automatiquement un rollback global. SQLite annule uniquement l'instruction fautive et laisse la transaction ouverte, en attendant votre décision. Si vous voulez du tout-ou-rien, c'est à l'application d'émettre un ROLLBACK dès qu'elle détecte une erreur.

Accélérer les insertions en masse

Comme chaque instruction en mode autocommit déclenche son propre fsync, regrouper un lot dans une seule transaction est souvent 100 fois plus rapide :

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;

Une seule synchro disque au moment du COMMIT au lieu d'une par ligne. Si un jour vous importez des milliers de lignes et que vous vous demandez pourquoi ça rame à mort, c'est presque toujours ça la réponse.

DEFERRED, IMMEDIATE, EXCLUSIVE

BEGIN accepte un mode qui contrôle à quel moment SQLite pose les verrous :

• BEGIN DEFERRED (le mode par défaut) — aucun verrou tant que vous ne lisez ni n'écrivez. Le verrou en écriture est posé de façon paresseuse, à la première instruction d'écriture.
• BEGIN IMMEDIATE — pose le verrou en écriture immédiatement. Les autres connexions peuvent encore lire, mais aucune autre connexion ne peut commencer à écrire.
• BEGIN EXCLUSIVE — comme IMMEDIATE, sauf qu'aucune autre connexion ne peut lire non plus. En mode WAL, le comportement est identique à IMMEDIATE ; la différence ne joue que dans l'ancien mode rollback journal.

BEGIN DEFERRED;     -- identique à un simple BEGIN
BEGIN IMMEDIATE;    -- réserver le verrou d'écriture maintenant
BEGIN EXCLUSIVE;    -- tout réserver (mode rollback-journal)

Le choix a son importance pour la concurrence. Avec un simple BEGIN, deux connexions peuvent toutes les deux démarrer une transaction, lire tranquillement chacune de leur côté, puis entrer en collision au moment d'écrire — la deuxième à demander le verrou d'écriture récolte un SQLITE_BUSY, et le pire, c'est qu'elle a déjà fait des lectures qu'il va falloir jeter à la poubelle.

BEGIN IMMEDIATE règle le problème : si vous savez d'avance que vous allez écrire, réclame le verrou d'écriture dès le départ. La deuxième connexion est bloquée (ou échoue tout de suite) avant même d'avoir fait du travail à jeter.

Règle pratique : si votre transaction va écrire, utilise BEGIN IMMEDIATE.

Lire dans une transaction, c'est lire un instantané

Tant qu'une transaction est ouverte, vos lectures voient un instantané cohérent de la base telle qu'elle était au démarrage de la transaction (en mode WAL) ou lors de votre première lecture (en mode rollback-journal). Les modifications validées par d'autres connexions n'apparaîtront pas comme par magie dans vos requêtes.

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, votre propre écriture est visible
COMMIT;

SELECT n FROM counter;

Vous voyez vos propres écritures non validées ; les autres connexions, non. Dès que vous faites COMMIT, la nouvelle valeur devient visible pour tout le monde. C'est précisément ce qu'on entend par serializable quand on parle de SQLite — il n'y a pas de bouton READ COMMITTED à activer, parce que le niveau par défaut est déjà le plus strict.

Une transaction sqlite dans le code applicatif

Dans un vrai programme, le schéma classique consiste à entourer le corps de la transaction d'un try/except (ou try/catch), avec un ROLLBACK sur la branche d'erreur :

-- Pseudo-code pour n'importe quelle bibliothèque cliente
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;

La plupart des bibliothèques clientes (le module sqlite3 de Python, better-sqlite3, etc.) encapsulent tout ça pour vous via un bloc with ou un helper transaction(). Ça vaut le coup de jeter un œil à la doc de votre bibliothèque : les valeurs par défaut réservent parfois des surprises. Le module sqlite3 de Python, en particulier, a longtemps eu un comportement d'autocommit assez bizarre ; les versions récentes ont enfin ajouté un vrai paramètre autocommit pour régler ça.

Les pièges classiques

• Le DDL fonctionne dans une transaction. CREATE TABLE, ALTER TABLE, et même DROP TABLE peuvent être annulés via un rollback. SQLite est atypique sur ce point — beaucoup de bases de données committent automatiquement le DDL.
• VACUUM ne peut pas tourner dans une transaction. Pareil pour quelques autres commandes de maintenance. À lancer en mode autocommit.
• Un COMMIT qui échoue reste un vrai échec. Si COMMIT renvoie SQLITE_BUSY (rare, mais possible), la transaction n'est pas validée. Votre code doit gérer ce cas — typiquement en réessayant.
• Les transactions longues bloquent les autres écrivains. Une transaction qui reste ouverte plusieurs minutes va bloquer les autres écritures pendant tout ce temps. Ouvrez-les tard, committez-les vite.

La suite : les savepoints

BEGIN et COMMIT, c'est du tout ou rien. Mais parfois on veut annuler seulement une partie d'une transaction — par exemple, abandonner une étape risquée tout en gardant le reste. C'est exactement le rôle des savepoints, et c'est ce qu'on voit juste après.