"Il y a une transaction, donc c'est safe" est l'une des suppositions les plus coûteuses qu'on croise en revue de code PostgreSQL. Une transaction garantit l'atomicité et la cohérence de ses propres opérations, mais rien n'empêche par défaut deux transactions concurrentes de lire la même donnée, de raisonner dessus séparément, et d'écrire chacune un résultat qui écrase silencieusement celui de l'autre. C'est le lost update, et c'est le bug de concurrence le plus commun en base relationnelle.

Le scénario qui casse en charge

Un cas classique de SaaS e-commerce : décrémenter un stock au moment du paiement.

-- transaction A                          -- transaction B
BEGIN;                                    BEGIN;
SELECT quantity FROM stock                SELECT quantity FROM stock
  WHERE product_id = 42;                    WHERE product_id = 42;
-- renvoie 1                               -- renvoie 1
-- (logique applicative : 1 - 1 = 0)       -- (logique applicative : 1 - 1 = 0)
UPDATE stock SET quantity = 0
  WHERE product_id = 42;
COMMIT;
                                           UPDATE stock SET quantity = 0
                                             WHERE product_id = 42;
                                           COMMIT;

Les deux transactions lisent quantity = 1, chacune pense avoir le dernier exemplaire, chacune commit 0. Résultat : deux commandes ont été acceptées pour un seul article en stock. Aucune erreur SQL n'est levée, aucune contrainte n'est violée, le bug est purement logique et ne se voit qu'en audit du panier métier.

Pourquoi PostgreSQL laisse passer ça

Au niveau d'isolation par défaut, Read Committed, chaque requête voit un instantané des données committées au moment où elle démarre, pas au moment où la transaction a démarré. PostgreSQL utilise le MVCC (Multi-Version Concurrency Control) : chaque ligne modifiée crée une nouvelle version, et une transaction ne voit que les versions committées avant le début de sa requête courante. Ce mécanisme empêche nativement les lectures sales (dirty reads, impossible sous PostgreSQL même en Read Committed, contrairement à certains autres SGBD), mais n'empêche absolument pas deux transactions de lire la même version d'une ligne et d'écrire chacune dessus sans se voir.

Un point souvent mal compris : le SELECT seul ne pose aucun verrou. PostgreSQL ne verrouille une ligne qu'au moment de l'UPDATE/DELETE, ou si on le demande explicitement. Entre le SELECT et l'UPDATE de la transaction A, rien n'empêche B de lire la même ligne.

Diagnostiquer le problème

Le signal le plus fiable n'est pas une erreur, c'est une incohérence métier constatée après coup (stock négatif impossible mais commandes en trop, solde qui ne correspond pas à la somme des mouvements). Pour reproduire en local, deux sessions psql côte à côte avec un BEGIN manuel sur chacune, en intercalant les commandes à la main, suffisent à démontrer le lost update en quelques lignes, exactement comme dans le bloc ci-dessus.

La correction : verrouiller explicitement la ligne lue

SELECT ... FOR UPDATE force PostgreSQL à poser un verrou exclusif sur les lignes sélectionnées. Toute autre transaction qui tente un SELECT FOR UPDATE ou un UPDATE sur la même ligne attend que la première commit ou rollback.

BEGIN;
SELECT quantity FROM stock
  WHERE product_id = 42
  FOR UPDATE;

-- la transaction concurrente qui tente le même SELECT FOR UPDATE
-- se bloque ici jusqu'au COMMIT/ROLLBACK de celle-ci

UPDATE stock SET quantity = quantity - 1
  WHERE product_id = 42 AND quantity > 0;
COMMIT;

Noter le UPDATE ... SET quantity = quantity - 1 plutôt que de recalculer en application : laisser PostgreSQL faire l'arithmétique dans la même requête supprime le besoin de relire après le verrou. Le WHERE quantity > 0 est la garde finale qui transforme un bug silencieux en zéro ligne affectée, détectable côté application.

Pour des files d'attente de type "prendre une tâche disponible" (plusieurs workers qui piochent dans une table de jobs), FOR UPDATE SKIP LOCKED évite que les workers s'attendent entre eux : chaque worker saute les lignes déjà verrouillées par un autre et prend la suivante disponible, ce qui donne un pattern de distribution de tâches sans verrou bloquant partagé.

Le niveau intermédiaire : REPEATABLE READ

Entre Read Committed et Serializable, PostgreSQL propose Repeatable Read, qui garantit qu'une transaction voit un instantané figé au moment de son premier SELECT, et non plus un instantané rafraîchi à chaque requête. Ça élimine la lecture non répétable (relire deux fois la même ligne dans la transaction et obtenir deux valeurs différentes parce qu'une autre transaction a committé entre-temps), mais ne protège toujours pas contre le lost update de l'exemple du stock : les deux transactions concurrentes voient chacune quantity = 1 sur leur propre instantané figé, et l'UPDATE de la seconde à committer échoue silencieusement de la même façon qu'en Read Committed, sauf si PostgreSQL détecte un conflit d'écriture explicite sur la ligne, ce qu'il fait uniquement pour une ligne déjà modifiée par une transaction concurrente déjà committée (dans ce cas précis, PostgreSQL lève bien une erreur de sérialisation en Repeatable Read, contrairement à Read Committed). C'est une amélioration réelle sur la cohérence des lectures au sein d'une même transaction, mais ce n'est pas un substitut à SERIALIZABLE pour l'intégrité transactionnelle croisée entre plusieurs transactions concurrentes.

Coordonner sans verrouiller de ligne : les verrous consultatifs

Quand la contention porte sur une ressource logique qui n'a pas forcément de ligne dédiée en base (par exemple : "un seul import CSV à la fois par compte client", où le verrou doit porter sur un identifiant métier plutôt que sur une ligne précise), PostgreSQL propose les verrous consultatifs (advisory locks). Ils fonctionnent par convention applicative : PostgreSQL ne les attache à aucune ligne ni table, c'est à l'application de respecter la discipline de les acquérir avant toute opération concurrente sur la ressource logique visée.

-- acquiert un verrou consultatif basé sur l'identifiant du compte (hashé en bigint)
SELECT pg_advisory_lock(hashtext('import:account:42'));

-- traitement de l'import CSV ici

SELECT pg_advisory_unlock(hashtext('import:account:42'));

pg_try_advisory_lock renvoie immédiatement false plutôt que d'attendre si le verrou est déjà pris, ce qui permet d'implémenter un "un seul traitement à la fois, les autres sont rejetés tout de suite" sans file d'attente implicite ; pg_advisory_lock bloque jusqu'à disponibilité, pour un comportement de file d'attente séquentielle.

L'alternative : SERIALIZABLE avec retry

Le verrouillage explicite demande de savoir à l'avance quelles lignes protéger, ce qui devient difficile dès que la logique métier touche plusieurs tables selon des chemins différents. Le niveau SERIALIZABLE déplace le problème : PostgreSQL détecte après coup si l'exécution concurrente aurait pu produire un résultat impossible en exécution strictement séquentielle, et dans ce cas annule une des transactions avec une erreur de sérialisation (SQLSTATE 40001).

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT quantity FROM stock WHERE product_id = 42;
-- logique applicative
UPDATE stock SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;
-- si COMMIT échoue avec SQLSTATE 40001 ("could not serialize access"),
-- l'application doit rejouer toute la transaction depuis le début
// application (exemple Node.js avec pg)
async function decrementStockWithRetry(client, productId, maxAttempts = 3) {
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      await client.query(
        "UPDATE stock SET quantity = quantity - 1 WHERE product_id = $1 AND quantity > 0",
        [productId]
      );
      await client.query("COMMIT");
      return;
    } catch (err) {
      await client.query("ROLLBACK");
      if (err.code === "40001" && attempt < maxAttempts) continue;
      throw err;
    }
  }
}

SERIALIZABLE protège aussi contre le write skew, une variante plus subtile du lost update où deux transactions lisent des lignes différentes mais liées par une contrainte métier implicite (exemple classique : deux médecins de garde qui se retirent chacun de la liste au même moment parce que chacun voit l'autre encore présent, laissant zéro médecin de garde alors que la règle impose au moins un).

Le compromis à assumer

FOR UPDATE coûte du débit : les transactions concurrentes attendent littéralement les unes derrière les autres sur la ligne verrouillée. SERIALIZABLE coûte moins d'attente mais demande que toute la couche applicative sache rejouer une transaction entière sur erreur de sérialisation, ce qui n'est pas gratuit dès qu'il y a des effets de bord non idempotents (appel à un service de paiement externe, envoi d'email) dans la transaction. Mélanger du SERIALIZABLE avec un appel externe non rejouable dans la même transaction est une erreur fréquente : si le retry rejoue aussi l'appel externe, on obtient une double facturation plutôt qu'une protection.

Quand ça ne suffit pas

Si le volume de contention est élevé sur une ligne chaude (un compteur de stock sur un best-seller en soldes flash), ni FOR UPDATE ni SERIALIZABLE ne scalent bien : l'un sérialise l'accès, l'autre multiplie les retries sous forte contention. La solution à ce stade n'est plus au niveau transactionnel mais architectural : réserver le stock de façon asynchrone via une file, ou déplacer le compteur chaud vers une structure dédiée (compteur atomique Redis avec réconciliation périodique vers PostgreSQL) pour sortir la ligne chaude du chemin transactionnel critique.

Le niveau d'isolation n'est jamais un détail de configuration à laisser par défaut sans y penser. C'est un choix de conception qui dépend directement de la criticité métier de la donnée protégée et du volume de contention attendu, à décider avant d'écrire la requête, pas après avoir constaté l'incohérence en production.