Transactions de base de données et niveaux d'isolation expliqués

« Enveloppez simplement ça dans une transaction » est un conseil courant, mais une transaction seule ne garantit pas que deux opérations concurrentes ne se marcheront pas dessus : cela dépend du niveau d'isolation, et celui par défaut dans la plupart des bases est plus faible qu'on ne le croit. Se tromper là-dessus, c'est se retrouver avec un bug qui n'apparaît que sous une vraie charge de production, jamais dans un environnement de développement mono-utilisateur.

Ce qu'une transaction garantit réellement

Une transaction vous donne l'atomicité (toutes ses écritures ont lieu, ou aucune) et la durabilité (une fois validée, elle survit à un plantage). Ces deux-là sont rarement en question. Ce qui varie, et beaucoup, c'est l'isolation : quelle part du travail en cours d'une transaction est visible par une autre transaction qui s'exécute en même temps.

L'isolation s'exprime comme un spectre. Une isolation plus faible signifie plus de concurrence et un meilleur débit, mais plus de façons pour des transactions concurrentes de s'interférer. Une isolation plus forte signifie moins de surprises, mais plus de verrouillage et de contention.

Les trois anomalies classiques

Avant que les niveaux d'isolation aient un sens, il faut connaître les problèmes dont ils portent le nom :

Lecture sale (dirty read) : la transaction A lit une ligne que la transaction B a écrite mais pas encore validée. Si B annule ensuite, A a lu des données qui n'ont jamais réellement existé.

Lecture non répétable (non-repeatable read) : la transaction A lit une ligne deux fois et obtient des valeurs différentes, parce que B a validé une modification de cette ligne entre-temps.

Lecture fantôme (phantom read) : la transaction A exécute deux fois la même requête de plage (par exemple WHERE amount > 100) et obtient un ensemble de lignes différent la seconde fois, parce que B a inséré ou supprimé une ligne correspondante entre-temps.

Les niveaux d'isolation standard

SQL définit quatre niveaux, chacun empêchant davantage de ce qui précède :

• Read Uncommitted : n'empêche rien. Les lectures sales sont possibles. PostgreSQL ne l'implémente pas réellement de façon distincte (il se comporte comme Read Committed) ; InnoDB de MySQL, si.
• Read Committed : empêche les lectures sales. Vous ne verrez jamais que des données validées, mais une valeur peut encore changer entre deux lectures dans la même transaction. C'est la valeur par défaut dans PostgreSQL, Oracle et SQL Server.
• Repeatable Read : empêche les lectures sales et les lectures non répétables. Une fois que votre transaction a lu une ligne, elle verra la même valeur pour cette ligne pour le reste de la transaction, même si une autre transaction valide une modification. C'est la valeur par défaut d'InnoDB de MySQL. Les lectures fantômes restent techniquement possibles selon la norme SQL, bien que l'implémentation de PostgreSQL (qui utilise l'isolation par instantané) les empêche aussi.
• Serializable : empêche les trois. Les transactions se comportent comme si elles s'exécutaient une par une, dans un certain ordre, alors qu'elles ont réellement tourné en concurrence.

Voir Read Committed vous piquer

Deux transactions vérifient toutes deux le solde d'un compte avant de le débiter :

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- reads 100
-- ...application logic decides it's safe to withdraw 80...
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

-- Transaction B, running concurrently
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- also reads 100
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

Si les deux transactions lisent le solde avant que l'une ou l'autre ne valide, toutes deux croient qu'il y a assez d'argent. Sous Read Committed, le second UPDATE se bloquera en réalité jusqu'à la validation de la première transaction (verrouillage au niveau de la ligne pour l'écriture), puis s'appliquera au solde courant de 20 : le SQL brut seul ne produira donc pas de solde négatif. Mais si votre application a lu le solde dans une requête puis validé « 80 <= solde ? » dans le code applicatif avant d'émettre une mise à jour distincte, cette séquence vérifier-puis-agir est exactement l'endroit où se loge la condition de concurrence, et Read Committed ne la protège en rien.

La corriger : verrouillage explicite ou niveau d'isolation plus fort

Option 1 : SELECT ... FOR UPDATE : verrouiller explicitement la ligne lue, de sorte qu'une transaction concurrente qui lit la même ligne se bloque jusqu'à votre validation.

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- application logic
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

Option 2 : relever le niveau d'isolation et réessayer en cas de conflit.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 80 WHERE id = 1;
COMMIT;

Serializable ne verrouille pas de façon proactive : il détecte les conflits à la validation et abandonne l'une des transactions en conflit avec un échec de sérialisation. Votre application doit l'intercepter et réessayer :

async function withdrawSerializable(db, accountId, amount) {
  for (let attempt = 0; attempt < 3; attempt++) {
    try {
      await db.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const { rows } = await db.query('SELECT balance FROM accounts WHERE id = $1', [accountId]);
      if (rows[0].balance < amount) throw new Error('insufficient funds');
      await db.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, accountId]);
      await db.query('COMMIT');
      return;
    } catch (err) {
      await db.query('ROLLBACK');
      if (err.code !== '40001') throw err; // not a serialization failure — don't retry
    }
  }
  throw new Error('could not complete withdrawal after retries');
}

Option 3 : une seule instruction atomique, qui contourne toute la condition de concurrence lecture-puis-écriture :

UPDATE accounts SET balance = balance - 80
WHERE id = 1 AND balance >= 80;
-- check affected row count; 0 means the guard failed

C'est généralement la meilleure option quand elle convient : pas de verrouillage explicite, pas de logique de nouvelle tentative, pas de réglage de niveau d'isolation. Elle n'est pas toujours applicable (parfois on a vraiment besoin de lire, d'appliquer une logique non triviale, puis d'écrire), mais quand un seul UPDATE ... WHERE peut exprimer toute l'opération, c'est plus simple et plus rapide que toutes les alternatives.

Choisir un niveau en pratique

N'allez pas chercher Serializable partout : il ajoute de la complexité de nouvelle tentative et réduit le débit sous contention. Une valeur par défaut raisonnable :

• Read Committed (ou la valeur par défaut de votre base) pour la plupart des opérations.
• Verrouillage explicite de ligne (FOR UPDATE) pour les séquences lecture-puis-écriture sur une ligne précise, comme dans l'exemple du solde.
• Serializable pour les opérations dont la justesse dépend réellement du fait que la transaction voie un instantané cohérent sur plusieurs instructions : grands livres financiers, comptages de stocks, tout cas où une lecture fantôme ou non répétable produirait un résultat métier faux.
• Une instruction atomique unique chaque fois que l'opération peut s'exprimer ainsi, quel que soit le niveau d'isolation, car elle supprime entièrement la fenêtre de concurrence.

Le niveau d'isolation est un réglage de compromis, pas un interrupteur de « sécurité activée », et la bonne valeur dépend de la condition de concurrence précise qui vous préoccupe dans un morceau de code donné, pas d'un réglage global pour toute l'application.