Jour 32 Day 32 · jeudi 17 septembre 2026 Thursday 17 September 2026 Data Avancé

Transactions & niveaux d'isolation Transactions & isolation levels

ACID, dirty reads, MVCC, SELECT FOR UPDATE, deadlocks : le sujet qui sépare ceux qui « font du SQL » de ceux qui comprennent ce qui se passe quand deux requêtes arrivent en même temps. ACID, dirty reads, MVCC, SELECT FOR UPDATE, deadlocks: the topic that separates those who "write SQL" from those who understand what happens when two queries arrive at the same time.

L’essentiel

Une transaction est une séquence d’opérations que la base traite comme un tout indivisible : soit tout est appliqué (COMMIT), soit rien (ROLLBACK). Entre BEGIN et COMMIT, la base garantit les quatre propriétés ACID :

Atomicité — tout ou rien. Un crash au milieu d’un virement ne laisse jamais le débit sans le crédit.
Cohérence — chaque transaction fait passer la base d’un état valide à un état valide (contraintes, clés étrangères, checks respectés).
Isolation — les transactions concurrentes ne voient pas leurs états intermédiaires respectifs… dans une certaine mesure : c’est tout le sujet des niveaux d’isolation.
Durabilité — une fois le COMMIT acquitté, la donnée survit à un crash (write-ahead log écrit sur disque avant l’acquittement).

L’atomicité et la durabilité sont binaires ; l’isolation est un curseur. L’isolation parfaite (tout se passe comme si les transactions s’exécutaient une par une) coûte cher en concurrence : le standard SQL définit donc quatre niveaux, du plus laxiste au plus strict, qui autorisent ou interdisent des anomalies précises.

Comment ça marche

Les quatre anomalies classiques, en mini-scénarios (T1 et T2 sont deux transactions concurrentes) :

Dirty read : T1 lit une valeur que T2 a modifiée sans avoir commité. T2 rollback → T1 a travaillé sur une donnée qui n’a jamais existé.
Non-repeatable read : T1 lit une ligne, T2 la modifie et commit, T1 relit → valeur différente au sein d’une même transaction.
Phantom read : T1 exécute SELECT COUNT(*) WHERE …, T2 insère une ligne qui matche et commit, T1 réexécute → des lignes « fantômes » sont apparues.
Lost update : T1 et T2 lisent la même valeur, calculent chacune en mémoire, écrivent chacune leur résultat → la seconde écriture écrase la première. Le grand classique du double débit :

   T1 (retrait 80€)          T2 (retrait 50€)
   BEGIN                     BEGIN
   SELECT solde  → 100
                             SELECT solde  → 100
   UPDATE solde = 100-80
   COMMIT        (solde=20)
                             UPDATE solde = 100-50
                             COMMIT        (solde=50)

   Résultat : 130€ retirés, solde final 50€.
   Le débit de T1 est perdu — lost update.

Les quatre niveaux d’isolation du standard, et ce qu’ils empêchent :

Niveau	Dirty read	Non-repeatable	Phantom
Read uncommitted	Possible	Possible	Possible
Read committed (défaut Postgres)	Empêché	Possible	Possible
Repeatable read	Empêché	Empêché	Possible*
Serializable	Empêché	Empêché	Empêché

* En Postgres, REPEATABLE READ empêche aussi les phantoms (snapshot complet) — un bon point à mentionner. Le lost update n’est pas dans le tableau du standard : en READ COMMITTED il reste possible et doit être traité explicitement (verrou ou update atomique) ; en REPEATABLE READ Postgres, la deuxième écriture échoue avec une erreur de sérialisation à retenter.

Comment Postgres tient l’isolation sans tout verrouiller : MVCC (Multi-Version Concurrency Control). Chaque UPDATE crée une nouvelle version de la ligne plutôt que d’écraser l’ancienne ; chaque transaction voit un snapshot cohérent — les versions commitées avant son début. Résultat fondamental : les lecteurs ne bloquent jamais les écrivains, et inversement. Les vieilles versions sont nettoyées plus tard par VACUUM. Seules deux écritures sur la même ligne se bloquent entre elles.

💡 La phrase qui fait mouche — « Postgres ne pose pas de verrou en lecture : chaque transaction lit un snapshot MVCC, c’est pour ça qu’un gros SELECT analytique ne bloque pas la prod. » Une phrase, et vous venez de dépasser 80 % des candidats.

Concepts clés à maîtriser

SELECT … FOR UPDATE : lit la ligne et la verrouille jusqu’à la fin de la transaction. Toute autre transaction voulant la verrouiller ou la modifier attend. C’est le verrou pessimiste — l’arme anti-lost-update quand la logique doit passer par l’applicatif.
Verrou optimiste (l’alternative sans verrou) : une colonne version, et UPDATE … WHERE id = ? AND version = ? ; zéro ligne affectée = quelqu’un est passé avant, on recharge et on réessaie. Idéal quand les conflits sont rares.
Update atomique : le plus simple quand il suffit — UPDATE comptes SET solde = solde - 80 calcule dans la base, sous verrou de ligne implicite. Pas de fenêtre read-modify-write, pas de lost update.
Deadlock : T1 verrouille A puis veut B ; T2 verrouille B puis veut A — attente circulaire. La base le détecte et tue une des deux (erreur 40P01 en Postgres). Prévention : toujours verrouiller les ressources dans le même ordre (par id croissant, par exemple) et garder les transactions courtes. Guérison : réessayer la transaction tuée.
Transactions courtes : une transaction ouverte pendant un appel API externe garde ses verrous et son snapshot pendant tout ce temps — connexions saturées, VACUUM bloqué, deadlocks. Règle : jamais d’I/O externe dans une transaction.

Le lost update et ses corrections, en SQL :

-- ❌ BUGGY : fenêtre entre la lecture et l'écriture
BEGIN;
SELECT solde FROM comptes WHERE id = 1;      -- lit 100
-- ... l'applicatif calcule 100 - 80 ...
-- (une autre transaction peut lire 100 ici aussi !)
UPDATE comptes SET solde = 20 WHERE id = 1;  -- écrase aveuglément
COMMIT;

-- ✅ Correction 1 : update atomique (à privilégier si possible)
UPDATE comptes SET solde = solde - 80
WHERE id = 1 AND solde >= 80;                -- le calcul ET la garde
-- se font dans la base, sous verrou de ligne ; 0 ligne = solde insuffisant

-- ✅ Correction 2 : verrou pessimiste (logique applicative complexe)
BEGIN;
SELECT solde FROM comptes WHERE id = 1 FOR UPDATE;  -- verrouille la ligne
-- toute transaction concurrente sur cette ligne ATTEND ici
UPDATE comptes SET solde = 20 WHERE id = 1;
COMMIT;                                       -- libère le verrou

🎤 En entretien — le virement bancaire est LE scénario à dérouler : « je débite A et crédite B dans une seule transaction (atomicité : jamais l’un sans l’autre). Contre les retraits concurrents, update atomique avec garde solde >= montant, ou SELECT FOR UPDATE. Et pour éviter le deadlock entre un virement A→B et un virement B→A simultanés, je verrouille toujours les comptes dans le même ordre — par id croissant. » Atomicité, concurrence, deadlock : trois points en trente secondes.

En entretien

« Explique ACID avec un exemple concret. » — Le virement : atomicité (débit + crédit, tout ou rien), cohérence (contrainte solde >= 0 jamais violée), isolation (une transaction concurrente ne voit pas l’état intermédiaire débité-mais-pas-crédité), durabilité (commit acquitté = écrit dans le WAL, survit au crash).

« Quelle différence entre non-repeatable read et phantom read ? » — Non-repeatable : une ligne existante relue a changé (UPDATE commité entre les deux lectures). Phantom : l’ensemble des lignes matchant un critère a changé (INSERT/DELETE commité) — des lignes apparaissent ou disparaissent. La nuance compte car REPEATABLE READ du standard bloque le premier mais pas le second.

« Pourquoi ne met-on pas tout en SERIALIZABLE ? » — Coût : la base doit détecter les dépendances entre transactions et en avorter certaines (erreurs de sérialisation à retenter) ; débit en baisse, code de retry obligatoire. READ COMMITTED + verrous ciblés là où ça compte est le compromis pragmatique par défaut.

« Comment Postgres permet-il de lire sans bloquer les écritures ? » — MVCC : chaque UPDATE crée une nouvelle version de ligne, chaque transaction lit un snapshot cohérent des versions commitées à son début. Lecteurs et écrivains ne se bloquent jamais mutuellement ; seules deux écritures sur la même ligne se sérialisent. VACUUM recycle les versions mortes.

« Deux virements croisés A→B et B→A deadlockent. Que se passe-t-il et comment l’éviter ? » — Chacun tient un verrou et attend l’autre : attente circulaire. Postgres la détecte et tue une transaction (à retenter côté applicatif). Prévention : ordonner les acquisitions de verrous (toujours l’id le plus petit d’abord) — plus de cycle possible — et transactions courtes.

Pièges & idées reçues

⚠️ L’autocommit piège — sans BEGIN explicite, chaque statement est sa propre transaction. Deux UPDATE consécutifs dans votre code ne sont pas atomiques : un crash entre les deux laisse la base incohérente. Les ORM ouvrent souvent des transactions implicites — sachez ce que fait le vôtre (prisma.$transaction, @Transactional…).

« Une transaction, ça verrouille la table » — non : MVCC verrouille au pire des lignes, et la lecture ne verrouille rien du tout. Croire ça mène à sur-verrouiller « par prudence » et à créer les deadlocks qu’on voulait éviter.
« READ COMMITTED me protège du lost update » — non : il empêche seulement les dirty reads. Le read-modify-write applicatif reste vulnérable ; il faut un update atomique, FOR UPDATE ou un verrou optimiste.
SERIALIZABLE sans retry : ce niveau avorte des transactions par design. Sans boucle de retry sur les erreurs de sérialisation, vous avez juste ajouté des 500 aléatoires.
Transactions longues : appel HTTP, envoi d’email ou attente utilisateur dans une transaction = verrous tenus des secondes, VACUUM bloqué, contention en cascade. I/O externe toujours hors transaction.
Compter sur le défaut sans le connaître : Postgres et MySQL/InnoDB ne partagent ni le même défaut (READ COMMITTED vs REPEATABLE READ) ni la même implémentation des niveaux. « Ça dépend du moteur » est une réponse d’expert, pas une esquive.

Pour aller plus loin

PostgreSQL — Transaction Isolation — le chapitre à lire en entier, avec les subtilités Postgres vs standard
PostgreSQL — Explicit Locking : FOR UPDATE, FOR SHARE, deadlocks
Designing Data-Intensive Applications (Kleppmann), chapitre 7 « Transactions » — la meilleure explication écrite des anomalies et de la sérialisabilité
Manipuler : ouvrir deux psql côte à côte, BEGIN dans chaque, et rejouer le lost update puis le deadlock — dix minutes qui valent toutes les fiches

The essentials

A transaction is a sequence of operations the database treats as an indivisible whole: either everything is applied (COMMIT), or nothing is (ROLLBACK). Between BEGIN and COMMIT, the database guarantees the four ACID properties:

Atomicity — all or nothing. A crash in the middle of a transfer never leaves the debit without the credit.
Consistency — each transaction moves the database from one valid state to another (constraints, foreign keys, checks respected).
Isolation — concurrent transactions don’t see each other’s intermediate states… to some extent: that’s the whole topic of isolation levels.
Durability — once the COMMIT is acknowledged, the data survives a crash (write-ahead log flushed to disk before acknowledging).

Atomicity and durability are binary; isolation is a dial. Perfect isolation (everything behaves as if transactions ran one at a time) is expensive in concurrency: the SQL standard therefore defines four levels, from laxest to strictest, which allow or forbid specific anomalies.

How it works

The four classic anomalies, as mini-scenarios (T1 and T2 are two concurrent transactions):

Dirty read: T1 reads a value T2 modified without having committed. T2 rolls back → T1 worked on data that never existed.
Non-repeatable read: T1 reads a row, T2 modifies it and commits, T1 re-reads → different value within a single transaction.
Phantom read: T1 runs SELECT COUNT(*) WHERE …, T2 inserts a matching row and commits, T1 re-runs → “phantom” rows have appeared.
Lost update: T1 and T2 read the same value, each computes in memory, each writes its result → the second write overwrites the first. The great classic of the double debit:

   T1 (withdraw €80)         T2 (withdraw €50)
   BEGIN                     BEGIN
   SELECT balance → 100
                             SELECT balance → 100
   UPDATE balance = 100-80
   COMMIT       (balance=20)
                             UPDATE balance = 100-50
                             COMMIT       (balance=50)

   Result: €130 withdrawn, final balance €50.
   T1's debit is gone — lost update.

The standard’s four isolation levels, and what they prevent:

Level	Dirty read	Non-repeatable	Phantom
Read uncommitted	Possible	Possible	Possible
Read committed (Postgres default)	Prevented	Possible	Possible
Repeatable read	Prevented	Prevented	Possible*
Serializable	Prevented	Prevented	Prevented

* In Postgres, REPEATABLE READ also prevents phantoms (full snapshot) — a good point to mention. The lost update isn’t in the standard’s table: under READ COMMITTED it remains possible and must be handled explicitly (lock or atomic update); under Postgres REPEATABLE READ, the second write fails with a serialization error to be retried.

How Postgres maintains isolation without locking everything: MVCC (Multi-Version Concurrency Control). Each UPDATE creates a new version of the row instead of overwriting the old one; each transaction sees a consistent snapshot — the versions committed before it started. The fundamental result: readers never block writers, and vice versa. Old versions are cleaned up later by VACUUM. Only two writes to the same row block each other.

💡 The line that lands — “Postgres takes no locks on reads: each transaction reads an MVCC snapshot, which is why a big analytical SELECT doesn’t block production.” One sentence, and you’ve just passed 80% of candidates.

Key concepts to master

SELECT … FOR UPDATE: reads the row and locks it until the end of the transaction. Any other transaction wanting to lock or modify it waits. That’s the pessimistic lock — the anti-lost-update weapon when the logic must go through application code.
Optimistic locking (the lock-free alternative): a version column, and UPDATE … WHERE id = ? AND version = ?; zero rows affected = someone got there first, reload and retry. Ideal when conflicts are rare.
Atomic update: the simplest when it suffices — UPDATE accounts SET balance = balance - 80 computes inside the database, under an implicit row lock. No read-modify-write window, no lost update.
Deadlock: T1 locks A then wants B; T2 locks B then wants A — circular wait. The database detects it and kills one of the two (error 40P01 in Postgres). Prevention: always acquire locks in the same order (ascending id, for instance) and keep transactions short. Cure: retry the killed transaction.
Short transactions: a transaction held open during an external API call keeps its locks and snapshot the whole time — saturated connections, blocked VACUUM, deadlocks. Rule: never do external I/O inside a transaction.

The lost update and its fixes, in SQL:

-- ❌ BUGGY: window between the read and the write
BEGIN;
SELECT balance FROM accounts WHERE id = 1;      -- reads 100
-- ... the application computes 100 - 80 ...
-- (another transaction can read 100 here too!)
UPDATE accounts SET balance = 20 WHERE id = 1;  -- blindly overwrites
COMMIT;

-- ✅ Fix 1: atomic update (prefer it when possible)
UPDATE accounts SET balance = balance - 80
WHERE id = 1 AND balance >= 80;                 -- the math AND the guard
-- happen inside the database, under a row lock; 0 rows = insufficient funds

-- ✅ Fix 2: pessimistic lock (complex application logic)
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- locks the row
-- any concurrent transaction on this row WAITS here
UPDATE accounts SET balance = 20 WHERE id = 1;
COMMIT;                                          -- releases the lock

🎤 In an interview — the bank transfer is THE scenario to walk through: “I debit A and credit B in a single transaction (atomicity: never one without the other). Against concurrent withdrawals, an atomic update with a balance >= amount guard, or SELECT FOR UPDATE. And to avoid the deadlock between a simultaneous A→B transfer and B→A transfer, I always lock accounts in the same order — ascending id.” Atomicity, concurrency, deadlock: three points in thirty seconds.

In an interview

“Explain ACID with a concrete example.” — The transfer: atomicity (debit + credit, all or nothing), consistency (the balance >= 0 constraint never violated), isolation (a concurrent transaction doesn’t see the debited-but-not-credited intermediate state), durability (acknowledged commit = written to the WAL, survives a crash).

“What’s the difference between non-repeatable read and phantom read?” — Non-repeatable: an existing row re-read has changed (committed UPDATE between the two reads). Phantom: the set of rows matching a predicate has changed (committed INSERT/DELETE) — rows appear or disappear. The nuance matters because the standard’s REPEATABLE READ blocks the former but not the latter.

“Why not run everything in SERIALIZABLE?” — Cost: the database must track dependencies between transactions and abort some of them (serialization errors to retry); lower throughput, mandatory retry code. READ COMMITTED + targeted locks where it matters is the pragmatic default trade-off.

“How does Postgres let you read without blocking writes?” — MVCC: each UPDATE creates a new row version, each transaction reads a consistent snapshot of the versions committed at its start. Readers and writers never block each other; only two writes to the same row serialize. VACUUM recycles dead versions.

“Two crossed transfers A→B and B→A deadlock. What happens and how do you avoid it?” — Each holds one lock and waits for the other: circular wait. Postgres detects it and kills one transaction (to be retried by the application). Prevention: order lock acquisitions (always the smallest id first) — no cycle possible anymore — and short transactions.

Pitfalls & misconceptions

⚠️ The autocommit trap — without an explicit BEGIN, each statement is its own transaction. Two consecutive UPDATEs in your code are not atomic: a crash between them leaves the database inconsistent. ORMs often open implicit transactions — know what yours does (prisma.$transaction, @Transactional…).

“A transaction locks the table” — no: MVCC locks rows at worst, and reading locks nothing at all. Believing this leads to over-locking “just in case” and creating the very deadlocks you wanted to avoid.
“READ COMMITTED protects me from lost updates” — no: it only prevents dirty reads. Application-level read-modify-write remains vulnerable; you need an atomic update, FOR UPDATE or optimistic locking.
SERIALIZABLE without retries: this level aborts transactions by design. Without a retry loop on serialization errors, you’ve just added random 500s.
Long transactions: an HTTP call, email send or user wait inside a transaction = locks held for seconds, blocked VACUUM, cascading contention. External I/O always outside the transaction.
Relying on the default without knowing it: Postgres and MySQL/InnoDB share neither the same default (READ COMMITTED vs REPEATABLE READ) nor the same implementation of the levels. “It depends on the engine” is an expert answer, not a dodge.

Going further

PostgreSQL — Transaction Isolation — the chapter to read in full, with the Postgres-vs-standard subtleties
PostgreSQL — Explicit Locking: FOR UPDATE, FOR SHARE, deadlocks
Designing Data-Intensive Applications (Kleppmann), chapter 7 “Transactions” — the best written explanation of anomalies and serializability
Hands-on: open two psql side by side, BEGIN in each, and replay the lost update then the deadlock — ten minutes worth more than any cheat sheet

S'entraîner sur ce sujet → Practice this topic →