Les transactions SQL : tout réussit, ou rien n’est conservé

Ce que vous saurez faire

Choisir la frontière d’une transaction, comprendre pourquoi PostgreSQL refuse vos requêtes après une erreur, et reconnaître les cas où une transaction n’apporte rien. Travaillez sur une base d’exercice, jamais sur une base de production.

1. Une seule instruction est déjà atomique

Hors transaction explicite, PostgreSQL entoure chaque instruction de sa propre transaction. Un UPDATE qui touche mille lignes réussit donc pour les mille lignes, ou pour aucune.

-- Contexte : psql, base d’exercice vide.
CREATE TABLE comptes (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  solde integer NOT NULL
);
INSERT INTO comptes (solde) VALUES (500), (200);
UPDATE comptes SET solde = solde + 10;
SELECT id, solde FROM comptes ORDER BY id;

Résultat attendu : les deux soldes valent 510 et 210. Si l’instruction avait échoué en cours de route, aucune ligne n’aurait été modifiée. BEGIN ne sert donc pas à protéger une instruction isolée : il sert à en grouper plusieurs.

Référence : transactions.

2. Après une erreur, la session refuse tout

C’est le comportement qui déroute le plus. Dès qu’une instruction échoue à l’intérieur d’une transaction, PostgreSQL place celle-ci en état d’échec et rejette les instructions suivantes, avec un message du type « current transaction is aborted, commands ignored until end of transaction block ».

-- Contexte : psql, suite de l’atelier.
BEGIN;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
UPDATE comptes SET solde = 'texte' WHERE id = 2;   -- erreur de type
SELECT solde FROM comptes;                         -- refusé
ROLLBACK;

Résultat attendu : le SELECT ne renvoie pas de données, il renvoie une erreur. Seul ROLLBACK débloque la session ; un COMMIT envoyé dans cet état se comporte lui aussi comme une annulation. Le retrait de 100 n’est pas conservé.

Un point de sauvegarde permet une annulation partielle : SAVEPOINT etape_1 puis, en cas d’échec, ROLLBACK TO SAVEPOINT etape_1 ramène la transaction à ce point sans perdre ce qui précède.

3. La durée d’une transaction est un coût

Une transaction ouverte conserve les verrous pris sur les lignes qu’elle modifie et retarde le nettoyage des anciennes versions de lignes. Une transaction qui attend une réponse réseau immobilise donc des ressources partagées.

BEGIN
  écriture
  appel d’une API externe (8 s)   ← verrous maintenus
  écriture
COMMIT

Placez les appels externes avant ou après, jamais pendant. Dans le code applicatif, l’équivalent habituel est le suivant.

// Contexte : Node.js, avec un ORM déjà configuré et connecté.
await db.transaction(async (tx) => {
  await tx.insert(orders).values(commande);
  await tx.update(products).set({ stock: nouveauStock });
});

Sortie normale de la fonction : COMMIT. Erreur levée : ROLLBACK. Ce dépôt utilise ce mécanisme pour enregistrer une ressource et la trace de son changement d’état en une seule opération.

Référence : BEGIN.

Pièges et limites

• Croire qu’un ROLLBACK annule le monde extérieur. Il ne rappelle pas un courriel envoyé ni un paiement déjà transmis à un prestataire. Déclenchez ces actions après le COMMIT, pas au milieu.
• Oublier de clore une transaction en échec. Une session laissée dans cet état rejette toutes les requêtes suivantes. Le symptôme ressemble à une panne générale alors qu’il suffisait de terminer le bloc.
• Confondre atomicité et isolation. Une transaction garantit le « tout ou rien ». Elle n’empêche pas à elle seule une autre session de modifier les mêmes données entre votre lecture et votre écriture.
• Encapsuler des traitements longs. Plus la transaction dure, plus elle retient des verrous et gêne les autres requêtes. Une attente utilisateur n’a rien à faire dans une transaction.

À vous de jouer

Dans psql, sur la table comptes de l’atelier, ouvrez une transaction, retirez 100 au compte 1, provoquez volontairement une erreur de type sur le compte 2, puis essayez de lire les soldes sans terminer la transaction. Que se passe-t-il, et quel est le solde final du compte 1 ?

Correction commentée

La lecture échoue. PostgreSQL indique que la transaction courante est en échec et que les commandes sont ignorées jusqu’à la fin du bloc. Il faut envoyer une annulation pour retrouver une session utilisable.

ROLLBACK;
SELECT id, solde FROM comptes ORDER BY id;

Le compte 1 a retrouvé son solde d’avant la transaction : le retrait n’a jamais été validé. C’est exactement l’atomicité à l’œuvre.

Deux enseignements. D’abord, une erreur au milieu d’un groupe d’écritures ne laisse pas la base à moitié modifiée : c’est la garantie que vous êtes venu chercher en écrivant BEGIN. Ensuite, une session qui refuse toutes les requêtes après une erreur n’est pas cassée ; c’est une transaction ouverte qu’il faut clore. Ce second point explique une bonne part des incidents attribués à tort au réseau ou au pilote de base de données.