Les race conditions : deux requêtes correctes, un résultat faux

Ce que vous saurez faire

Écrire une modification concurrente sûre, comprendre ce que garantit réellement le niveau d’isolation par défaut de PostgreSQL, et reconnaître le cas qu’il ne couvre pas. L’atelier demande une base d’exercice et deux sessions psql ouvertes côte à côte.

1. Pourquoi l’UPDATE conditionnel fonctionne

Contexte : deux fenêtres psql sur la même base d’exercice. La première crée le jeu d’essai.

-- Session 1
CREATE TABLE products (
  id integer PRIMARY KEY,
  stock integer NOT NULL
);
INSERT INTO products VALUES (42, 1);

Le niveau d’isolation par défaut est READ COMMITTED : chaque instruction voit les données validées au moment où elle commence. Le point décisif concerne l’écriture. Si deux UPDATE visent la même ligne, le second attend le verrou détenu par le premier, puis réévalue sa condition sur la version validée de la ligne.

-- Session 1, puis session 2 pendant que la première n’a pas encore validé
UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;

Résultat attendu : la première session obtient UPDATE 1, la seconde UPDATE 0. Après validation de la première, stock vaut 0 et la condition stock > 0 devient fausse. Le stock ne descend jamais à -1. C’est exactement pour cette raison que la vérification doit vivre dans l’UPDATE, et non dans un SELECT qui le précède.

Ce paragraphe ne résume pas toute la théorie de l’isolation ; il décrit le comportement précis sur lequel repose la technique.

Référence : contrôle de concurrence.

2. Verrouiller quand la décision ne tient pas dans une instruction

Certains traitements enchaînent plusieurs étapes sur la même ligne. SELECT … FOR UPDATE réserve alors la ligne pour la transaction en cours.

-- Session 1, contexte psql
BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- vérification métier, puis écritures
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;

Résultat attendu : une session 2 exécutant le même SELECT … FOR UPDATE reste bloquée jusqu’au COMMIT de la session 1, puis lit la valeur à jour. Le verrou appartient à la transaction et disparaît au COMMIT comme au ROLLBACK. Gardez donc ces transactions très courtes, et prenez toujours les verrous dans le même ordre pour éviter les interblocages.

Référence : verrouillage explicite.

3. Ce que READ COMMITTED ne couvre pas

Le mécanisme du point 1 protège une décision portant sur la ligne que vous modifiez. Il ne protège pas une décision prise à partir d’une lecture puis appliquée ailleurs, ni une règle portant sur plusieurs lignes.

Prenons la règle « au moins une personne de garde ». Chaque transaction compte les personnes de garde avec un SELECT, en trouve deux, conclut qu’elle peut se retirer, puis fait son UPDATE sur sa propre ligne. Les deux UPDATE touchent des lignes différentes : aucun n’attend l’autre, aucun n’est refusé, et il ne reste plus personne de garde. Ce défaut porte un nom, l’anomalie d’écriture asymétrique, souvent appelée write skew.

La réponse de PostgreSQL est le niveau SERIALIZABLE, qui détecte les combinaisons impossibles à obtenir avec une exécution séquentielle.

-- Contexte : psql, à la place de BEGIN
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- lectures, décision, écritures
COMMIT;

Sa contrepartie est essentielle : la base peut annuler votre transaction, généralement au COMMIT, avec une erreur de sérialisation (SQLSTATE 40001). Ce n’est pas une panne, c’est le fonctionnement prévu. L’application doit être capable de rejouer la transaction entière, ce qui suppose de n’avoir envoyé aucun courriel et encaissé aucun paiement au milieu.

Référence : niveaux d’isolation.

Pièges et limites

• Croire qu’une transaction suffit. BEGIN, SELECT, décision, UPDATE, COMMIT reste vulnérable : l’atomicité n’est pas l’isolation. La protection vient de la condition dans l’UPDATE, du verrou, de la contrainte ou du niveau d’isolation choisi.
• Utiliser un verrou en mémoire du serveur applicatif. Un booléen, une carte d’objets ou un mutex de processus ne protègent rien dès qu’il existe deux instances du serveur, ce qui est la situation normale en production. La coordination doit vivre dans la base ou dans un service partagé.
• Ignorer le nombre de lignes modifiées. Un UPDATE conditionnel qui ne modifie rien réussit du point de vue SQL. Si l’application ne lit pas ce compteur, elle confirme une réservation qui n’a jamais eu lieu.
• Adopter SERIALIZABLE sans gérer le rejeu. Sans reprise sur l’erreur 40001, vous remplacez un bug rare de données par des échecs visibles sous charge.
• Laisser une règle d’unicité au seul code applicatif. Elle repose alors sur l’espoir que deux requêtes ne se croiseront jamais.

À vous de jouer

Sur une base d’exercice, une personne ne doit pouvoir réserver un événement qu’une seule fois. Créez la table reservations correspondante et écrivez l’insertion. Deux requêtes identiques arrivent exactement au même moment : comment garantir qu’une seule ligne existe au final, et que faire de la seconde requête ?

Correction commentée

La garantie vient de la base, pas du code applicatif.

-- Contexte : psql, base d’exercice vide
CREATE TABLE reservations (
  user_id integer NOT NULL,
  event_id integer NOT NULL,
  CONSTRAINT reservations_uniques UNIQUE (user_id, event_id)
);

INSERT INTO reservations (user_id, event_id)
VALUES (42, 8)
ON CONFLICT ON CONSTRAINT reservations_uniques DO NOTHING;

Résultat attendu : la première insertion répond INSERT 0 1, la seconde INSERT 0 0.

Pourquoi c’est la bonne réponse. Une vérification préalable en SQL, un SELECT suivi d’un INSERT conditionnel, échouerait : les deux requêtes peuvent lire « aucune réservation » avant que l’une d’elles n’écrive. La contrainte, elle, est évaluée par la base au moment de l’écriture, quelles que soient les requêtes concurrentes ; c’est la seule vérification qu’aucun intervalle ne sépare de sa décision.

Le nombre de lignes insérées porte l’information utile : 1 signifie « réservation créée », 0 signifie « elle existait déjà ». Sans ON CONFLICT, la seconde insertion lèverait une erreur de violation de contrainte unique qu’il faudrait intercepter. Les deux approches sont correctes, à condition de traiter réellement le cas plutôt que de laisser l’erreur remonter telle quelle à l’utilisateur.