Verrouillage optimiste et pessimiste dans les bases de données

#sql#databases#concurrency

Deux requêtes lisent la même ligne, calculent toutes deux une nouvelle valeur à partir de ce qu'elles ont lu, et la réécrivent : la seconde écriture écrase silencieusement la première comme si elle n'avait jamais eu lieu. C'est une mise à jour perdue (lost update), et c'est la raison pour laquelle l'accès concurrent à une base exige une forme de verrouillage. Les deux stratégies classiques, optimiste et pessimiste, font des paris opposés sur la fréquence réelle des conflits.

La mise à jour perdue, concrètement

Supposons que vous décrémentiez un stock lors du paiement.

-- request A reads stock = 5
SELECT stock FROM products WHERE id = 42;

-- request B reads stock = 5, at nearly the same time
SELECT stock FROM products WHERE id = 42;

-- both compute 5 - 1 = 4 and write it back
UPDATE products SET stock = 4 WHERE id = 42;
UPDATE products SET stock = 4 WHERE id = 42;

Deux unités vendues, mais le stock n'a baissé que d'une. Aucune requête n'a échoué, aucune transaction n'a été annulée : les données sont simplement fausses. Le verrouillage existe pour obliger l'une de ces deux requêtes à attendre l'autre, ou à échouer face à elle.

Verrouillage pessimiste : supposer qu'un conflit va se produire

Le verrouillage pessimiste prend un verrou sur la ligne avant d'y toucher et le conserve jusqu'à la fin de la transaction, bloquant toute autre transaction qui veut le même verrou.

BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- stock is now locked until this transaction commits or rolls back
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;

FOR UPDATE (pris en charge par Postgres, MySQL/InnoDB et la plupart des autres moteurs relationnels, ou par un équivalent) indique à la base : toute autre transaction qui essaie de lire cette ligne avec FOR UPDATE, ou de la mettre à jour, doit attendre la fin de celle-ci. La requête B ci-dessus se met simplement en file derrière la requête A au lieu de lui faire concurrence.

C'est simple à raisonner (il n'y a aucune possibilité de mise à jour perdue, point final), mais cela a un coût : chaque ligne verrouillée est un point de contention. Si votre flux de paiement garde ce verrou pendant un appel lent à une API de paiement, tous ceux qui essaient d'acheter le même produit attendent aussi.

Verrouillage optimiste : supposer qu'un conflit est rare

Le verrouillage optimiste ne verrouille rien d'avance. Il marque à la place chaque ligne d'un numéro de version et vérifie que la version n'a pas changé au moment d'écrire.

-- add a version column
ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 0;

-- read the row along with its current version
SELECT stock, version FROM products WHERE id = 42;
-- suppose stock = 5, version = 7

-- write back conditioned on the version being unchanged
UPDATE products
SET stock = 4, version = 8
WHERE id = 42 AND version = 7;

Vérifiez le nombre de lignes affectées par UPDATE. S'il vaut 1, votre écriture a réussi et personne d'autre n'a touché la ligne entre-temps. S'il vaut 0, quelqu'un d'autre a mis la ligne à jour (et incrémenté sa version) entre votre lecture et votre écriture : vos données sont périmées, et l'application doit décider quoi faire : relire et réessayer, ou signaler un conflit à l'utilisateur.

async function decrementStock(productId) {
  const { stock, version } = await db.one(
    'SELECT stock, version FROM products WHERE id = $1',
    [productId],
  );

  const result = await db.result(
    'UPDATE products SET stock = $1, version = $2 WHERE id = $3 AND version = $4',
    [stock - 1, version + 1, productId, version],
  );

  if (result.rowCount === 0) {
    throw new ConflictError('Product was modified concurrently, retry');
  }
}

Aucun verrou n'est jamais conservé pendant la logique applicative, les appels réseau ou le temps de réflexion de l'utilisateur. La contention ne devient visible qu'au moment de l'écriture, et seulement lorsqu'un conflit s'est réellement produit.

Choisir entre les deux

Pessimiste quand les conflits sont fréquents et peu coûteux à sérialiser : un petit nombre de lignes chaudes (le stock d'un produit populaire, un compteur partagé, un siège dans un système de réservation) où l'on préfère faire attendre les requêtes plutôt que de les faire réessayer.
Optimiste quand les conflits sont rares et que le travail entre lecture et écriture est coûteux ou long : modification d'un document, mise à jour d'un profil utilisateur, tout ce qui implique un humain remplissant un formulaire entre la lecture et l'écriture. Garder un verrou de base de données pendant que « l'utilisateur réfléchit » n'est pas viable.

Une heuristique utile : si vous verrouillez autour d'un appel réseau, d'une interaction d'interface ou de tout ce qui n'est pas une transaction de base de données courte, vous voulez presque certainement du verrouillage optimiste.

Bien gérer le conflit

Une mise à jour à 0 ligne avec verrouillage optimiste n'est pas une erreur à avaler : c'est une information. Trois façons courantes de la traiter :

Réessayer : relire la ligne, réappliquer le changement, retenter l'écriture. Fonctionne bien pour de simples mises à jour numériques comme la décrémentation de stock ci-dessus, puisque la relecture donne un point de départ frais et correct.
Fusionner : si la mise à jour est plus complexe (modification d'un document à plusieurs champs), relire et fusionner les parties non conflictuelles, puis réessayer.
Le signaler : pour les modifications côté utilisateur, dire à l'utilisateur « ceci a été modifié par quelqu'un d'autre depuis votre chargement » et le laisser décider, plutôt que d'écraser en silence ou de jeter son travail en silence.

Ce qu'il ne faut jamais faire, c'est intercepter le cas du nombre de lignes à zéro et passer à autre chose : ce serait de nouveau la mise à jour perdue, avec quelques étapes en plus.

À retenir

Le verrouillage pessimiste évite les conflits en faisant attendre son tour à l'accès concurrent ; le verrouillage optimiste détecte les conflits après coup et laisse l'application y répondre. Aucun n'est universellement meilleur : tout dépend de la fréquence prévue des collisions, et de ce qui coûte le plus cher entre faire attendre une requête et la faire réessayer.