Pour rejeter l’enregistrement d’un document obsolète, incluez dans la condition de mise à jour la révision chargée initialement par l’éditeur, puis incrémentez-la de façon atomique. SERIALIZABLE à lui seul ne permet pas d’exprimer cette exigence lorsque le document a été chargé au cours d’une transaction antérieure.

Ce tutoriel s’adresse à un développeur qui travaille dans Terminal sur un Mac et qui a besoin qu’un éditeur d’articles PostgreSQL rejette les textes de remplacement obsolètes. Il s’appuie sur des lignes d’articles synthétiques et présente une démonstration complète avec node-postgres. L’objectif est précis : enregistrer le texte uniquement si la révision stockée correspond toujours à celle sur laquelle repose le brouillon envoyé.

Pourquoi SERIALIZABLE peut-il autoriser le remplacement d’un texte obsolète ?

PostgreSQL définit l’exécution sérialisable comme produisant un effet équivalent à l’exécution une par une, dans un certain ordre, des transactions validées. Cette garantie concerne les transactions de base de données et leurs opérations. PostgreSQL : isolation des transactions.

Considérons la séquence illustrative suivante :

  1. Un éditeur charge l’article 1 à la révision 1, puis la transaction de lecture prend fin.
  2. Un autre enregistrement remplace le texte de l’article et valide la révision 2.
  3. L’éditeur initial envoie un texte de remplacement basé sur la révision 1.
  4. Une nouvelle transaction sérialisable met à jour l’article 1 en utilisant uniquement son ID.

La dernière transaction peut réussir : les transactions se sont déjà exécutées séquentiellement. Rien dans une mise à jour conditionnée uniquement par id = 1 n’indique que le texte envoyé exige la révision 1. Il s’agit d’une application de la définition documentée de la sérialisabilité, et non d’une exception à celle-ci.

Les transactions qui se chevauchent sont différentes. PostgreSQL peut rejeter une transaction sérialisable si une mise à jour concurrente entre en conflit avec son instantané ou si des dépendances empêchent une exécution séquentielle. L’instantané est établi par la première instruction qui ne contrôle pas la transaction ; exécuter simplement BEGIN plus tôt ne conserve pas l’ancienne lecture de l’éditeur. PostgreSQL : isolation des transactions.

La question diagnostique est donc : Où l’enregistrement exige-t-il explicitement la révision chargée par l’éditeur ? Si la réponse est « nulle part », changer le niveau d’isolation ne fournit pas cette condition manquante.

Quelle garantie la vérification de révision doit-elle fournir ?

Considérez chaque brouillon chargé comme une paire : son texte modifiable et sa révision de base. L’enregistrement transmet l’ID de l’article, le texte de remplacement et la révision de base inchangée. La mise à jour de la base de données doit correspondre à la fois à l’ID et à cette révision, remplacer le texte et incrémenter la révision stockée dans la même instruction.

Par exemple, la condition WHERE id = $1 AND revision = $2 exprime le prérequis, tandis que SET body = $3, revision = revision + 1 effectue la transition. PostgreSQL met à jour les lignes qui satisfont la condition, et RETURNING fournit les valeurs des lignes effectivement mises à jour. Une mise à jour qui ne touche aucune ligne reste une commande SQL réussie ; l’application doit donc interpréter explicitement ce résultat. PostgreSQL : UPDATE.

Ce modèle est souvent appelé contrôle optimiste de la concurrence ou comparaison-échange (compare-and-swap). Dans la conception de cet article, toute opération qui modifie le contenu éditable d’un article doit faire avancer la révision. Les révisions doivent rester non nulles, ne doivent pas être réinitialisées, et une identité d’article ne doit pas être réutilisée avec un ancien jeton de révision. Sinon, une égalité pourrait laisser croire à tort qu’un ancien brouillon correspond toujours à la version actuelle.

Une lecture séparée suivie d’une mise à jour utilisant uniquement l’ID omet cette exigence de l’écriture elle-même. De même, récupérer la révision la plus récente juste avant l’enregistrement et la substituer à celle envoyée change le sens de la demande : cela autorise le remplacement d’une version que l’éditeur n’a pas utilisée comme base.

Exécuter un exemple synthétique complet sur votre Mac

Vous devez disposer d’une instance PostgreSQL en cours d’exécution, d’une base de données de test existante et d’une installation actuelle ou prise en charge de Node.js LTS avec npm. Ouvrez Terminal, créez un dossier de travail et installez le pilote à l’aide de la commande documentée npm install pg. node-postgres : présentation et installation.

mkdir revision-write-demo
cd revision-write-demo
npm install pg

Enregistrez le programme complet ci-dessous sous le nom revision-demo.mjs dans ce dossier. Il utilise l’importation par défaut compatible avec ESM, conformément à la documentation. Client et Pool peuvent tous deux lire les paramètres de connexion PostgreSQL depuis des variables d’environnement ; configurez ces paramètres pour votre base de données de test existante avant de lancer le programme. node-postgres : ESM, node-postgres : connexion.

Le programme compare deux articles synthétiques. L’article 1 reçoit deux enregistrements sérialisables sans condition. L’article 2 reçoit deux enregistrements avec vérification de révision, tous deux basés sur la même révision initiale. Sa table temporaire permet de séparer la démonstration des tables d’articles permanentes. La syntaxe JavaScript a été vérifiée avec Node.js ; la démonstration avec la base de données n’a pas été exécutée dans l’environnement de recherche.

import pg from 'pg';
import assert from 'node:assert/strict';
const { Client } = pg;
const client = new Client();

async function saveArticle(id, expectedRevision, body) {
  for (let attempt = 1; attempt <= 3; attempt += 1) {
    let active = false;
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      active = true;
      const result = await client.query(
        `UPDATE revision_demo_articles
         SET body = $3, revision = revision + 1
         WHERE id = $1 AND revision = $2
         RETURNING id, body, revision`,
        [id, expectedRevision, body]
      );
      if (result.rowCount === 0) {
        await client.query('ROLLBACK');
        active = false;
        return { status: 'stale-or-missing' };
      }
      await client.query('COMMIT');
      active = false;
      return { status: 'saved', article: result.rows[0] };
    } catch (error) {
      if (active) await client.query('ROLLBACK');
      if (error.code !== '40001' || attempt === 3) throw error;
    }
  }
}

await client.connect();
try {
  await client.query(`
    CREATE TEMP TABLE revision_demo_articles (
      id integer PRIMARY KEY,
      body text NOT NULL,
      revision integer NOT NULL CHECK (revision > 0)
    )
  `);
  await client.query(
    `INSERT INTO revision_demo_articles (id, body, revision)
     VALUES (1, 'Opening draft', 1), (2, 'Opening draft', 1)`
  );
  const loaded = await client.query(
    'SELECT id, body, revision FROM revision_demo_articles ORDER BY id'
  );
  const expectedRevision = loaded.rows[1].revision;

  // Illustrative blind saves: each is a separate transaction.
  for (const body of ['First edit', 'Stale replacement']) {
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
    try {
      await client.query(
        `UPDATE revision_demo_articles
         SET body = $1, revision = revision + 1 WHERE id = 1`,
        [body]
      );
      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    }
  }

  const first = await saveArticle(2, expectedRevision, 'First edit');
  const stale = await saveArticle(2, expectedRevision, 'Stale replacement');
  assert.equal(first.status, 'saved');
  assert.equal(first.article.revision, 2);
  assert.equal(stale.status, 'stale-or-missing');

  const final = await client.query(
    'SELECT id, body, revision FROM revision_demo_articles ORDER BY id'
  );
  assert.deepEqual(final.rows, [
    { id: 1, body: 'Stale replacement', revision: 3 },
    { id: 2, body: 'First edit', revision: 2 }
  ]);
  console.table(final.rows);
  console.log(stale.status);
} finally {
  await client.end();
}

Si votre base de données de test existante s’appelle revision_demo, exécutez la commande ci-dessous. Remplacez ce nom par celui de votre base de données et conservez tous les paramètres de connexion à l’environnement requis par votre instance locale. node-postgres : connexion.

PGDATABASE=revision_demo node revision-demo.mjs

Les valeurs finales attendues sont les suivantes :

Article Texte Révision Signification
1 Stale replacement 3 Les deux enregistrements sans condition ont été validés
2 First edit 2 Le deuxième enregistrement avec vérification de révision a été rejeté

Le statut final affiché doit être stale-or-missing.

La séquence évite délibérément les dépendances temporelles liées à la concurrence. Elle montre directement le point essentiel : un remplacement obsolète peut réussir même si chaque transaction d’enregistrement est sérialisable, car les enregistrements s’exécutent dans un ordre séquentiel valide.

Comment node-postgres doit-il gérer chaque résultat ?

Le programme transmet les valeurs séparément du texte SQL au moyen de l’API de requêtes paramétrées documentée du pilote. Il vérifie aussi rowCount, qui indique le nombre de lignes traitées par une mise à jour ; à lui seul, un tableau rows vide ne remplace pas de façon générale la vérification du nombre de lignes mises à jour. node-postgres : requêtes, node-postgres : pg.Result.

Utilisez le tableau de décision suivant pour adapter la fonction :

Résultat Action de l’application
Une ligne mise à jour et validation réussie Accepter l’enregistrement et conserver la révision renvoyée
Aucune ligne mise à jour Rejeter l’enregistrement comme obsolète ou inexistant ; conserver le brouillon envoyé
SQLSTATE 40001 Annuler la transaction et réessayer la transaction complète, selon une politique avec un nombre maximal de tentatives
Autre erreur ou limite de tentatives atteinte Signaler l’échec ; ne pas indiquer que l’enregistrement a réussi

Un résultat sans ligne mise à jour ne permet pas de savoir si l’article a été supprimé ou si sa révision a changé. La démonstration regroupe volontairement ces deux cas. Si l’interface doit fournir davantage de détails, chargez l’article actuel par la suite et présentez ce résultat comme une observation ultérieure. Ne le présentez pas comme une preuve de l’état précis au moment de l’échec de la mise à jour.

Les échecs de sérialisation utilisent le SQLSTATE 40001. PostgreSQL exige de réessayer la transaction complète, y compris la logique qui détermine les instructions et les valeurs. Plusieurs tentatives peuvent être nécessaires, sans garantie d’aboutir. L’exemple utilise trois tentatives à titre de politique illustrative de l’application. PostgreSQL : gestion des échecs de sérialisation.

Conservez expectedRevision sans le modifier pendant ces tentatives. Il représente le prérequis du brouillon envoyé. Si un autre enregistrement a fait avancer la révision, une nouvelle transaction peut alors rejeter l’ancien jeton en ne mettant à jour aucune ligne. Remplacer automatiquement le jeton par la révision la plus récente supprimerait la protection contre les écritures obsolètes.

La fonction ne renvoie un résultat positif qu’après la réussite de COMMIT. Toutes les instructions de transaction utilisent le même Client, comme l’exige node-postgres. Pour adapter le code à un pool, récupérez un client avec pool.connect(), utilisez ce client pendant toute la transaction, puis libérez-le. node-postgres : transactions, node-postgres : pg.Pool.

Avez-vous encore besoin de SERIALIZABLE ou de SELECT FOR UPDATE ?

Pour cette mise à jour conditionnelle d’une seule ligne, précisément délimitée, Read Committed peut aussi faire respecter le prérequis de révision : après avoir attendu la validation d’une mise à jour concurrente, PostgreSQL réévalue la condition de mise à jour sur la ligne mise à jour. Cela n’étend pas Read Committed à une garantie applicable à des règles complexes portant sur plusieurs lignes. PostgreSQL : isolation des transactions.

Conservez SERIALIZABLE lorsque la transaction qui l’entoure dépend de lectures et d’écritures plus larges auxquelles ses garanties sont nécessaires. La condition de révision exprime le prérequis de l’éditeur ; le niveau d’isolation régit les interactions entre les opérations de base de données de la transaction. Choisissez les deux en fonction de la tâche réelle.

SELECT FOR UPDATE peut servir lorsqu’une transaction doit examiner une ligne et effectuer plusieurs opérations qui en dépendent. Le verrou de ligne bloque les écritures concurrentes jusqu’à la fin de la transaction. Avec Repeatable Read ou Serializable, verrouiller une ligne modifiée depuis l’instantané de la transaction peut entraîner une erreur. PostgreSQL : verrouillage explicite.

Acquérir ce verrou uniquement à l’arrivée d’un enregistrement laisse l’application responsable de la comparaison avec la révision de base envoyée. Verrouiller la ligne actuelle ne permet pas à la base de données de savoir quelle révision antérieure a servi à produire le brouillon. Pour le remplacement d’une seule ligne présenté ici, la mise à jour conditionnelle regroupe déjà la comparaison et la modification.

Avant d’intégrer ce modèle, vérifiez tous les chemins d’écriture du contenu, conservez le brouillon de l’éditeur en cas de rejet et ne mettez à jour sa révision de base qu’après confirmation de l’enregistrement. Lorsqu’un enregistrement obsolète est rejeté, proposez l’article actuel à côté du brouillon conservé afin que le lecteur puisse rapprocher les textes et envoyer un nouvel enregistrement basé sur la révision effectivement examinée.

Sources