ACID — les 4 garanties d'une transaction

Atomicité, Cohérence, Isolation, Durabilité — quatre promesses que la base de données fait quand tu écris BEGIN.

Une transaction, c'est un groupe d'opérations SQL traité comme une seule unité indivisible. Soit tout réussit, soit rien ne change. La base de données fait quatre promesses pour garantir ça — résumées par l'acronyme ACID.

Atomicité

Imagine un virement bancaire : débiter le compte A et créditer le compte B. Si le crédit échoue après le débit, l'atomicité garantit que le débit est annulé automatiquement.

-- Virement atomique
BEGIN;
  UPDATE comptes SET solde = solde - 100 WHERE id = 1;
  UPDATE comptes SET solde = solde + 100 WHERE id = 2;
COMMIT; -- ou ROLLBACK si erreur

Cohérence

La cohérence est garantie par la combinaison de l'atomicité ET des contraintes déclarées dans le schéma. Si une opération violerait une contrainte, la transaction est annulée.

Isolation

C'est la propriété la plus complexe à mettre en œuvre. On verra dans les sections suivantes que les bases de données offrent plusieurs degrés d'isolation, chacun avec ses compromis.

Durabilité

Le WAL est un journal séquentiel : toute modification est d'abord écrite dans ce journal, puis confirmée au disque via fsync(), avant que le COMMIT soit acquitté. En cas de crash, le journal permet de rejouer les transactions commitées.

Anomalies de concurrence

Dirty read, non-repeatable read, phantom read, write skew — ce qui peut mal tourner quand deux transactions s'exécutent en même temps.

Quand plusieurs transactions s'exécutent en même temps, des interactions inattendues peuvent survenir. Ces anomalies de concurrence sont ce que les niveaux d'isolation cherchent à prévenir.

Dirty Read

BEGIN;
-- Lit solde = 1100
-- (donnée non commitée !)
SELECT solde FROM comptes
  WHERE id = 1;
-- Prend une décision
-- basée sur 1100...

BEGIN;
UPDATE comptes
  SET solde = 1100
  WHERE id = 1;
-- pas encore commitée


ROLLBACK; -- annulée !

Non-Repeatable Read

A a pourtant lu la même requête dans la même transaction. Avec un Non-Repeatable Read, le résultat change entre deux lectures.

Phantom Read

C'est comme le Non-Repeatable Read, mais pour un ensemble de lignes plutôt qu'une seule. Les "fantômes" sont ces nouvelles lignes qui apparaissent (ou disparaissent) entre deux lectures.

Lost Update

-- T1 lit stock=10, décrémente
UPDATE produits SET stock = 10 - 1 WHERE id = 5;

-- T2 a aussi lu stock=10, décrémente
UPDATE produits SET stock = 10 - 1 WHERE id = 5;
-- Résultat : stock=9 au lieu de 8 → mise à jour perdue

Write Skew

Ce que rend le Write Skew insidieux : chaque transaction individuelle respecte les contraintes telles qu'elle les voit. C'est leur combinaison qui les viole. Seul SERIALIZABLE le prévient.

Les 4 niveaux d'isolation

READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE — chaque niveau ajoute une protection, retire une anomalie.

La norme SQL:1992 définit quatre niveaux d'isolation. Chaque niveau ajoute une protection contre une anomalie, au prix d'un coût de synchronisation un peu plus élevé. Tu choisis le compromis selon le besoin de ton application.

-- Définir le niveau avant ou juste après BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- ... tes opérations ...
COMMIT;

READ UNCOMMITTED

Pratiquement jamais utilisé en production. PostgreSQL l'accepte syntaxiquement mais le traite comme READ COMMITTED (un Dirty Read n'a aucun sens avec MVCC).

READ COMMITTED

C'est le niveau par défaut de PostgreSQL. Pour la majorité des applications web (lecture-modification-écriture sans dépendances complexes), il est suffisant.

REPEATABLE READ

Défaut MySQL InnoDB. PostgreSQL l'implémente via MVCC — chaque transaction travaille avec un snapshot cohérent du début à la fin.

SERIALIZABLE

* PostgreSQL REPEATABLE READ élimine aussi les Phantom Reads via MVCC.

MVCC — PostgreSQL et les snapshots

Au lieu de locker, PostgreSQL crée des versions — chaque transaction vit dans son propre snapshot du passé.

PostgreSQL n'utilise pas de locks de lecture pour implémenter l'isolation. À la place, il crée des versions multiples de chaque ligne — c'est le MVCC (Multi-Version Concurrency Control).

Résultat : les lecteurs ne bloquent jamais les écrivains, et les écrivains ne bloquent jamais les lecteurs.

xmin et xmax

Chaque ligne en PostgreSQL porte deux colonnes cachées :

-- Voir les versions cachées
SELECT xmin, xmax, id, solde FROM comptes WHERE id = 1;

-- Résultat possible :
--  xmin | xmax | id | solde
--  1043 |    0 |  1 |  900

Le snapshot de transaction

VACUUM — nettoyage des versions mortes

-- VACUUM manuel (normalement automatique)
VACUUM comptes;

-- Voir les tables avec le plus de tuples morts
SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

READ COMMITTED vs REPEATABLE READ en MVCC

InnoDB — row locks, gap locks, next-key locks

MySQL prévient les phantoms autrement que PostgreSQL — en lockant l'espace entre les valeurs d'index.

MySQL InnoDB utilise une approche différente de PostgreSQL. En plus du MVCC (qui existe aussi dans InnoDB), il emploie des locks au niveau des lignes et des index pour prévenir les anomalies de concurrence.

Row Lock

-- Row lock implicite lors d'un UPDATE
UPDATE commandes SET statut = 'expédiée' WHERE id = 42;
-- La ligne 42 est lockée jusqu'au COMMIT

Gap Lock

Exemple : si tu lis SELECT ... WHERE montant BETWEEN 100 AND 200, InnoDB locke le gap entre 100 et 200. Aucune autre transaction ne peut insérer une ligne avec montant = 150 pendant que ta transaction est active.

Next-Key Lock

-- Cette requête pose des next-key locks
SELECT * FROM commandes
  WHERE montant > 100
  FOR UPDATE;
-- Locke les lignes ET les gaps dans l'index montant

SELECT FOR UPDATE — le verrou explicite

Quand tu as besoin de lire ET écrire sans conflit, FOR UPDATE acquiert le lock dès la lecture — pas au COMMIT.

SELECT ... FOR UPDATE est le verrou explicite de SQL. Il dit à la base de données : "Je vais lire ces lignes ET les modifier — acquiers le lock dès maintenant, pas au moment du COMMIT."

-- T1 : lit stock=10
SELECT stock FROM produits
  WHERE id = 5;

-- T2 aussi lit stock=10
-- T2 écrit stock=9

-- T1 écrit stock=9
-- (devrait être 8 !)

-- T1 : lit ET locke
SELECT stock FROM produits
  WHERE id = 5
  FOR UPDATE;

-- T2 bloquée ici
-- T1 écrit stock=9

-- T1 COMMIT
-- T2 lit stock=9, écrit 8 ✓

FOR UPDATE vs FOR SHARE

-- Lock exclusif : personne d'autre ne peut lire/écrire ces lignes
SELECT * FROM commandes WHERE statut = 'en_attente' FOR UPDATE;

-- Lock partagé : d'autres peuvent lire, mais pas modifier
SELECT * FROM produits WHERE id = 5 FOR SHARE;
-- MySQL : équivalent LOCK IN SHARE MODE

SKIP LOCKED et NOWAIT

Par défaut, FOR UPDATE attend que les lignes lockées soient libérées. Deux options permettent de changer ce comportement :

-- NOWAIT : échoue immédiatement si locked
SELECT * FROM jobs WHERE statut = 'pending'
  FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "jobs"

-- SKIP LOCKED : ignore les lignes lockées (idéal pour les queues)
SELECT * FROM jobs WHERE statut = 'pending'
  LIMIT 1
  FOR UPDATE SKIP LOCKED;
-- Retourne la première ligne NON lockée — parfait pour workers parallèles

Deadlocks — détecter et prévenir les cycles

Transaction A attend B, B attend A — un cycle que la base brise en sacrifiant une transaction.

Un deadlock survient quand deux transactions (ou plus) s'attendent mutuellement, chacune tenant un verrou que l'autre veut. Le cycle est indéfini — aucune ne peut progresser sans l'aide du moteur.

Détection et résolution

Les bases de données modernes détectent automatiquement les deadlocks :

-- PostgreSQL : l'erreur reçue par la victime
-- ERROR: deadlock detected
-- DETAIL: Process 12345 waits for ShareLock on transaction 789;
-- blocked by process 67890.
-- Hint: See server log for query details.

-- MySQL : voir les deadlocks récents
SHOW ENGINE INNODB STATUS;

Prévention

Optimistic locking — écrire sans verrouiller

Pas de lock — juste une colonne version. Au COMMIT, si quelqu'un a modifié entre-temps, la mise à jour échoue proprement.

Le locking pessimiste (FOR UPDATE) part du principe que les conflits sont probables — il locke préventivement. Le locking optimiste fait le pari inverse : les conflits sont rares, donc on ne locke pas. On vérifie au moment d'écrire que rien n'a changé.

-- Lecture (pas de lock)
SELECT id, solde, version FROM comptes WHERE id = 1;
-- Retourne : id=1, solde=1000, version=7

-- Modification avec vérification de version
UPDATE comptes
  SET solde = 900, version = 8
  WHERE id = 1 AND version = 7;

-- Si 0 ligne affectée → conflit → rejouer la transaction

Optimiste vs Pessimiste — quand choisir ?

Implémentation en Doctrine ORM

Doctrine supporte nativement les deux modes via le LockMode :

// Pessimiste : FOR UPDATE
$em->find(Compte::class, $id, LockMode::PESSIMISTIC_WRITE);

// Optimiste : vérification de version
try {
    $em->lock($compte, LockMode::OPTIMISTIC, $versionLue);
    $em->flush();
} catch (OptimisticLockException $e) {
    // Conflit — rejouer la transaction
}

Savepoints — le point de sauvegarde partiel

Annuler une partie d'une transaction sans perdre tout le travail — SAVEPOINT, ROLLBACK TO, RELEASE.

Un savepoint est un point de restauration partielle à l'intérieur d'une transaction. Il permet d'annuler une partie du travail sans tout rollbacker — comme un "Ctrl+Z" sélectif.

Les trois commandes

BEGIN;

INSERT INTO logs (msg) VALUES ('étape 1'); -- OK

SAVEPOINT avant_etape2;

INSERT INTO commandes (id) VALUES (999); -- Peut échouer

-- Si l'INSERT a échoué, on revient au savepoint
ROLLBACK TO SAVEPOINT avant_etape2;
-- L'INSERT de logs est toujours là, l'INSERT commandes est annulé

RELEASE SAVEPOINT avant_etape2; -- Libère le savepoint (optionnel)

COMMIT; -- Commite ce qui reste (logs)

Cas d'usage — boucle avec gestion d'erreurs partielles

Les savepoints sont particulièrement utiles pour importer un lot d'enregistrements où certains peuvent échouer individuellement, sans sacrifier les succès.

BEGIN;

DO $$
DECLARE
    rec RECORD;
    i INT := 0;
BEGIN
    FOR rec IN SELECT * FROM import_staging LOOP
        i := i + 1;
        SAVEPOINT sp;
        BEGIN
            INSERT INTO commandes VALUES (rec.id, rec.montant);
        EXCEPTION WHEN OTHERS THEN
            ROLLBACK TO SAVEPOINT sp; -- annule juste cet INSERT
            INSERT INTO erreurs_import (ligne, msg)
              VALUES (i, SQLERRM);
        END;
        RELEASE SAVEPOINT sp;
    END LOOP;
END;
$$;

COMMIT; -- Commite les succès, les erreurs sont loggées

Savepoints et Doctrine

$conn->beginTransaction(); // BEGIN réel

$conn->beginTransaction(); // SAVEPOINT doctrine_savepoint_1
try {
    // opérations partielles
    $conn->commit(); // RELEASE SAVEPOINT doctrine_savepoint_1
} catch (Exception $e) {
    $conn->rollBack(); // ROLLBACK TO SAVEPOINT doctrine_savepoint_1
}

$conn->commit(); // COMMIT réel

Synthèse — choisir le bon niveau d'isolation

Quel niveau pour quelle application ? Optimistic ou pessimistic ? Le guide de décision.

Maintenant que tu connais tous les outils — niveaux d'isolation, MVCC, locks, optimistic locking — il s'agit de choisir le bon pour chaque situation. Voici le guide de décision.

Quel niveau d'isolation ?

Optimistic ou Pessimistic ?

Commandes SQL de référence

-- Définir le niveau d'isolation
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

-- Ou au niveau session (PostgreSQL)
SET default_transaction_isolation = 'repeatable read';

-- Lock explicite
SELECT * FROM t WHERE id = 1 FOR UPDATE;
SELECT * FROM t WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM t LIMIT 1 FOR UPDATE SKIP LOCKED;

-- Savepoints
SAVEPOINT mon_save;
ROLLBACK TO SAVEPOINT mon_save;
RELEASE SAVEPOINT mon_save;

-- Diagnostique PostgreSQL
SELECT * FROM pg_locks;
SELECT * FROM pg_stat_activity;

-- Diagnostique InnoDB
SHOW ENGINE INNODB STATUS;

Checklist de bonnes pratiques

• Transactions courtes : moins de temps entre BEGIN et COMMIT = moins de locks tenus = moins de contentions.
• Index sur les colonnes de filtre : un UPDATE sans index peut locker une table entière au lieu d'une ligne.
• Ordre de lock cohérent : si plusieurs entités sont lockées, toujours dans le même ordre dans toutes les transactions.
• Retry automatique : deadlock = erreur transitoire. Implémenter un retry avec backoff. Idem pour les serialization failures.
• Surveiller pg_stat_activity : les transactions idle-in-transaction qui traînent tiennent des locks et bloquent VACUUM.
• SERIALIZABLE n'est pas gratuit : en charge élevée, les false positives SSI peuvent augmenter le taux de retry. Mesure avant de généraliser.