Bien gérer ses transactions en base de données

Une transaction, c’est l’outil qu’on apprend en premier et qu’on maîtrise en dernier.
Ce qui se joue vraiment reste souvent invisible jusqu’au jour où la production ralentit sans raison apparente :

• les verrous qu’on tient,
• la mémoire qu’on empêche de nettoyer,
• la connexion qu’on immobilise.

Cet article n’est pas une référence exhaustive sur l’ACID mais un retour d’expérience sur trois questions concrètes :

• quand une transaction est-elle nécessaire,
• quand ne l’est-elle pas,
• quel est le piège qui revient sur presque tous les projets.

On prendra PostgreSQL comme exemple, mais l’essentiel vaut pour n’importe quelle base transactionnelle.

🧩 Une transaction, c’est quoi au juste ?

Une transaction regroupe plusieurs opérations en une seule unité indivisible :

• soit tout réussit et devient visible d’un bloc au COMMIT,
• soit rien ne s’applique après un ROLLBACK (ou une erreur).

C’est la propriété d’atomicité, le « A » d’ACID.

En SQL, une transaction s’ouvre avec l’instruction BEGIN puis se ferme soit via COMMIT, soit via ROLLBACK.

L’exemple canonique, le virement bancaire :

débiter un compte et créditer l’autre doivent se produire ensemble, sinon l’argent se volatilise ou se duplique.

-- On démarre la transaction
BEGIN;
-- On applique les modifications
UPDATE comptes SET solde = solde - 100 WHERE id = 'A';
UPDATE comptes SET solde = solde + 100 WHERE id = 'B';
-- ✅ On valide les modifications car tout s'est bien passé 
COMMIT;

A l’inverse:

-- On démarre la transaction
BEGIN;
-- On applique les modifications
UPDATE comptes SET solde = solde - 100 WHERE id = 'A';
-- 💥 Erreur de typage
UPDATE comptes SET solde = solde + 'Hello' WHERE id = 'B';
-- ❌ On abandonne les modifications car une erreur est survenue
ROLLBACK;

Sous le capot, PostgreSQL s’appuie sur le MVCC (Multi-Version Concurrency Control) :

chaque transaction travaille sur un instantané cohérent de la base.

Les lecteurs ne bloquent jamais les écrivains, et inversement, seuls deux écrivains qui touchent la même ligne se disputent.
C’est cette mécanique qui, mal comprise, se retourne contre nous avec les transactions longues (on y revient).

🔤 ACID : et les trois autres lettres ?

On retient surtout le « A ». Mais une transaction porte trois autres garanties, et chacune se traduit par une décision très concrète une fois en production.

C comme Cohérence

Une transaction fait passer la base d’un état valide à un autre. Jamais elle ne la laisse entre les deux.

En pratique, c’est PostgreSQL qui refuse de valider quoi que ce soit qui violerait une contrainte : clé primaire dupliquée, clé étrangère orpheline, CHECK non respecté, NOT NULL oublié. La moindre violation fait échouer l’instruction, et l’atomicité se charge d’annuler le reste.

Le vrai levier est là : tout invariant qu’on sait exprimer en contrainte de base est un invariant qu’on n’a plus à défendre à la main. Un montant qui ne peut pas être négatif, c’est un CHECK (montant >= 0), pas trois vérifications éparpillées dans le code applicatif. Ce que la base ne connaît pas, en revanche, les règles qu’on garde côté application, reste à notre charge, et c’est souvent pour ça qu’on ouvre une transaction : tenir un invariant que le schéma seul ne sait pas exprimer.

I comme Isolation

Deux transactions qui tournent en même temps doivent donner le même résultat que si elles s’étaient exécutées l’une après l’autre.

C’est exactement ce que le MVCC orchestre : chaque transaction travaille sur son instantané et ne voit jamais les écritures non validées des autres. PostgreSQL propose plusieurs niveaux, du plus souple au plus strict :

Deux conséquences directes pour la gestion des transactions.
D’abord, aux niveaux stricts, PostgreSQL peut avorter une transaction avec l’erreur 40001 (could not serialize access) : ce n’est pas un bug, c’est le contrat, et l’application doit la rejouer.
Ensuite, plus une transaction dure, plus son instantané s’éloigne de la réalité et plus le risque de conflit grimpe, une raison de plus de les garder courtes (on y revient).

D comme Durabilité

Quand le COMMIT rend la main, l’écriture est gravée. Même si le serveur perd le courant la seconde d’après.

PostgreSQL y parvient grâce au WAL (Write-Ahead Log) : avant de valider un COMMIT, il écrit d’abord l’opération dans un journal séquentiel forcé sur disque. En cas de crash, la base rejoue ce journal au redémarrage et récupère toutes les transactions validées.

Ce même WAL est le fil qui relie ce chapitre au reste de l’article : c’est lui qu’on envoie aux réplicas pour étendre la durabilité au-delà d’une seule machine, et c’est précisément ce flux qu’exploitent les transactions en lecture seule, plus bas.

🎯 Quand a-t-on vraiment besoin d’une transaction ?

La règle tient en une phrase : dès qu’un invariant métier s’étend sur plusieurs écritures, il faut une transaction, sinon on peut s’en passer.

Une transaction en lecture seule explicite a quand même son utilité dès qu'on veut un instantané cohérent sur plusieurs requêtes, ou router la lecture vers un réplica.

Le réflexe inverse est tout aussi coûteux : entourer par précaution tout et n’importe quoi d’un BEGIN … COMMIT.
Grouper deux écritures sans lien logique, c’est tenir des verrous plus longtemps que nécessaire et allonger la durée de la transaction sans rien y gagner. La bonne granularité, c’est l’invariant métier, ni plus, ni moins.

👉 Ne demandez pas « puis-je mettre une transaction ici ? » mais « ces écritures doivent-elles réussir ou échouer ensemble ? ».
👉 Si la réponse est non, pas de transaction.

⏳ Le vrai piège : la transaction longue

Voici l’erreur que presque tout le monde a commise une fois, moi y compris.
On veut débiter une commande et encaisser un paiement via un prestataire externe.
En apparence, c’est un cas d’école pour une transaction :

BEGIN;
SELECT * FROM commandes WHERE id = $1 FOR UPDATE;   -- 🔒 verrou pris sur la ligne
-- ⬇️ code applicatif : await paiement.charger(...)   -- 2 à 5 secondes de réseau
UPDATE commandes SET statut = 'payee' WHERE id = $1;
COMMIT;                                              -- 🔒 verrou enfin relâché

Ça « marche » en développement. En production, sous charge, ça met la base à genoux. Le SELECT … FOR UPDATE a posé un verrou sur la ligne, et ce verrou est tenu pendant tout l’appel réseau. Chaque autre transaction qui veut cette commande attend 2 à 5 secondes. Les connexions s’accumulent, le pool se vide, et l’incident se propage bien au-delà de la table concernée.

Les dégâts ne s’arrêtent pas aux verrous. Une transaction qui reste ouverte longtemps a trois effets pervers, propres à MVCC :

• elle bloque le VACUUM et pas seulement sur sa table,

Tant qu’une vieille transaction est vivante, PostgreSQL ne peut pas recycler les dead tuples qu’elle pourrait encore voir, sur toute la base. Résultat : les tables et index gonflent (bloat), tiennent moins en cache, et les scans ralentissent.

• elle immobilise une connexion,

Une connexion idle in transaction est le problème opérationnel MVCC numéro un : elle ne fait rien, mais elle empêche le ménage et occupe une place dans le pool.

• elle tient ses verrous jusqu’au bout.

Un pic de latence chez le prestataire externe se propage en contention généralisée chez vous.

La correction est presque toujours la même : sortir toute I/O externe de la transaction.
On appelle le prestataire d’abord, puis on ouvre une transaction courte, purement base de données :

-- 1) l'application appelle paiement.charger(...) HORS transaction, récupère payment_id

-- 2) transaction courte, aucune I/O réseau à l'intérieur
BEGIN;
UPDATE commandes
SET statut = 'payee', payment_id = $1
WHERE id = $2 AND statut = 'en_attente'   -- conditionnel : idempotent + détecte les courses
RETURNING *;
COMMIT;

La clause AND statut = 'en_attente' rend l’UPDATE idempotent et détecte les courses concurrentes sans avoir à poser de verrou explicite. Si zéro ligne est renvoyée, c’est qu’une autre exécution est déjà passée et on le gère côté application.

-- tue les sessions restées « idle in transaction » (30 s à 5 min selon la charge)
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';

-- borne toute requête qui s'emballe
SET statement_timeout = '5s';   -- par session, ou SET LOCAL en transaction

SELECT pg_reload_conf();

Pour repérer les coupables déjà en place :

SELECT pid, state, now() - xact_start AS age, wait_event, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;   -- les plus vieilles transactions en tête

🪎 La règle d’or : une transaction se compte en millisecondes.
✅ Aucun appel réseau, aucune attente utilisateur, aucun sleep entre le BEGIN et le COMMIT.
🎯 Le travail lent se fait avant ou après, jamais pendant.

📦 Tout ce qui précède existe en Go et s’exécute : le dépôt compagnon de cet article. Chaque thèse y a un test qui la prouve, pas un test qui la répète — le chemin fautif ci-dessus y est chronométré face à sa correction, à travail égal et résultat en base identique.

📖 La lecture seule brille avec la réplication

On l’a dit : pour une simple lecture, pas besoin d’entourer quoi que ce soit.
Mais déclarer explicitement une transaction en lecture seule apporte deux choses réelles.

BEGIN TRANSACTION READ ONLY;
SELECT ... ;   -- plusieurs requêtes sur un instantané cohérent
SELECT ... ;
COMMIT;

1. Une intention claire, et un filet de sécurité. PostgreSQL refusera toute écriture dans cette transaction (ERROR: cannot execute UPDATE in a read-only transaction). C’est une garantie utile pour du reporting ou des exports : impossible de modifier la base par accident.

2. Le vrai bénéfice : router la lecture vers un réplica. C’est là que ça devient intéressant quand la réplication est en place. Le primaire encaisse les écritures ; un ou plusieurs hot standbys reçoivent le flux WAL et servent les lectures. Une transaction marquée READ ONLY peut partir sur un réplica sans jamais toucher le primaire.

Concrètement, tout le trafic de lecture (reporting, pages de consultation, exports, tableaux de bord) part sur les réplicas, et le primaire se concentre sur les écritures. Sur une application où les lectures dominent, ce qui est le cas le plus fréquent, le primaire respire et la capacité de lecture grimpe pour presque rien une fois la réplication en place.

Deux réglages utiles autour de tout ça :

1. default_transaction_read_only = on sur la connexion vers les réplicas : toute transaction y est en lecture seule par défaut.
C’est un réglage de connexion, pas de transaction : il couvre donc aussi l’autocommit, et pas seulement ce qu’on a pensé à entourer d’un BEGIN. Sur un standby, c’est de toute façon une ceinture par-dessus des bretelles, car un serveur en recovery (replica de lecture) refuse les écritures même si la session remet le réglage à off.

SET transaction_read_only = off;              -- au niveau session
-- ou, ciblé sur une transaction explicite :
BEGIN;
SET TRANSACTION READ WRITE;
INSERT INTO ...;
COMMIT;

BEGIN TRANSACTION READ ONLY;
SET TRANSACTION READ WRITE;
-- primaire : SET          ✅ le drapeau se lève, aucune requête n'a encore été jouée
-- standby  : ERROR 0A000  ❌ cannot set transaction read-write mode during recovery

2. max_standby_streaming_delay (30 s par défaut) et hot_standby_feedback (off par défaut) pour les longues requêtes analytiques sur un réplica. C’est là qu’est le vrai risque d’annulation : le rejeu du WAL entre en conflit avec l’instantané de la requête, et le standby finit par la tuer. Le premier fixe le délai de grâce avant l’annulation, le second demande au primaire de ne pas recycler les lignes dont le standby a encore besoin.

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

📝 En résumé

✅ La longue transaction ne se voit pas en développement. Elle se paie en production, sous charge, sur une table qui n’a parfois rien à voir. Gardez-les courtes, gardez l’I/O dehors, et servez la lecture depuis les réplicas.

🧑‍💻 Retrouver le code associé à cet article avec les thèses ci-dessus en Go, chacune avec le test qui la prouve.

Crédits & Ressources

• PostgreSQL — Transactions — les bases du modèle transactionnel
• PostgreSQL — Transaction Isolation — niveaux d’isolation et MVCC
• PostgreSQL — Explicit Locking — comprendre les verrous et FOR UPDATE
• PostgreSQL — Hot Standby — lectures sur réplica, conflits de rejeu et max_standby_streaming_delay
• PostgreSQL — Handling Query Conflicts — les cinq types de conflit et les paramètres de délai
• PostgreSQL — pg_stat_database_conflicts — compteurs de conflits par cause, côté standby
• Gérer ses migrations de schéma PostgreSQL — l’autre facette du travail avec Postgres en production
• Gérer des jobs asynchrones avec PostgreSQL — SKIP LOCKED et transactions courtes en pratique