Utilisez FOR UPDATE SKIP LOCKED dans une requête qui fait également passer la ligne sélectionnée de ready à processing. Cela permet aux workers concurrents de réclamer différentes lignes d’articles synthétiques, tandis que l’état enregistré empêche une nouvelle réclamation après la fin du verrouillage.

Ce guide s’adresse à un développeur utilisant Terminal sur un Mac avec une connexion PostgreSQL fonctionnelle. Vous allez créer trois articles synthétiques, vérifier les réclamations concurrentes dans deux sessions de base de données et exécuter un exemple complet avec node-postgres.

Pourquoi deux workers peuvent-ils sélectionner le même article ?

Un simple SELECT ne réserve pas son résultat. Avec le niveau d’isolation Read Committed par défaut de PostgreSQL, deux workers peuvent lire la même ligne ready validée avant que l’un ou l’autre ne la modifie. Encadrer cette lecture simple et une mise à jour ultérieure dans une transaction ne rend pas à lui seul la sélection exclusive. PostgreSQL décrit le comportement des instantanés dans Isolation des transactions.

La réclamation nécessite deux protections : un verrou de ligne pendant la sélection de l’article, et un changement d’état avant la libération de ce verrou. PostgreSQL libère les verrous de ligne à la fin de la transaction. Par conséquent, une lecture avec verrouillage autonome suivie d’un traitement rendrait de nouveau la ligne admissible une fois la transaction de lecture terminée. Voir Verrouillage explicite.

Utilisez le même protocole de réclamation dans chaque worker. Un chemin d’application qui lit un article ready et commence à le traiter sans l’avoir réclamé avec succès peut toujours entraîner un traitement en double.

Créer une petite file d’attente d’articles synthétiques

Prérequis : une base de données PostgreSQL en cours d’exécution, la commande psql disponible dans Terminal et une configuration de connexion existante. Pour la section JavaScript, installez également une version actuellement prise en charge de Node.js et npm. Cet article part du principe que la connexion à la base de données fonctionne ; l’installation varie selon la configuration existante de votre Mac.

La fonctionnalité SKIP LOCKED requise a été introduite dans PostgreSQL 9.5. Il s’agit de la version minimale pour cette syntaxe, et non d’une recommandation d’utiliser une version qui n’est plus prise en charge. Utilisez une version de PostgreSQL prise en charge ; la documentation actuelle consultée indique que PostgreSQL 18 est la version actuelle. Voir les notes de publication de la version 9.5 et la documentation SELECT actuelle.

Ouvrez Terminal et exécutez psql avec vos paramètres de connexion habituels. Cette commande ouvre une invite interactive permettant d’exécuter des instructions SQL et de consulter les résultats, comme l’explique la documentation de psql. Exécutez la configuration suivante une seule fois dans une base de données de test où article_claim_demo n’existe pas déjà. Elle ne remplace volontairement aucune table existante.

SHOW server_version;
SHOW transaction_isolation;

CREATE TABLE article_claim_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    state text NOT NULL DEFAULT 'ready'
        CHECK (state IN ('ready', 'processing', 'done')),
    rendered_title text
);

INSERT INTO article_claim_demo (id, title) VALUES
    (1, 'Paper shapes'),
    (2, 'Desk arrangements'),
    (3, 'Color combinations');

Les exemples supposent le niveau d’isolation Read Committed et cette table simple sans déclencheurs supplémentaires. Ne modifiez pas les trois lignes d’exemple avant d’avoir terminé le test sur deux sessions.

Réclamer un article avec une seule instruction SQL

Exécutez cette instruction chaque fois qu’un worker a besoin d’un article. La requête candidate verrouille une ligne disponible ; la mise à jour enregistre sa réclamation et renvoie ses champs. La documentation UPDATE de PostgreSQL décrit ce modèle CTE et mise à jour et confirme que RETURNING renvoie les lignes effectivement mises à jour.

WITH candidate AS (
    SELECT id
    FROM article_claim_demo
    WHERE state = 'ready'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE article_claim_demo AS a
SET state = 'processing'
FROM candidate AS c
WHERE a.id = c.id
  AND a.state = 'ready'
RETURNING a.id, a.title;

Le verrou doit se trouver dans la CTE candidate. SKIP LOCKED permet à un autre worker de passer une candidate qu’il ne peut pas verrouiller immédiatement. LIMIT 1 limite chaque réclamation, et la jointure sur la clé primaire identifie une cible de mise à jour. PostgreSQL documente explicitement le verrouillage dans une requête WITH ainsi que l’utilisation des lignes ignorées pour les files d’attente dans SELECT : clause de verrouillage.

Sans BEGIN explicite, PostgreSQL exécute l’instruction dans sa propre transaction. Une instruction réussie valide la mise à jour associée à la sélection ainsi que le changement d’état avant le début du traitement ultérieur. Après la validation, le verrou de ligne disparaît, mais state = 'ready' exclut cet article. Voir Transactions.

Ne traitez que les articles renvoyés par l’instruction de réclamation. Un résultat vide signifie que cette tentative n’a obtenu aucun article ; cela ne signifie pas que tout le travail est terminé. D’autres workers peuvent actuellement détenir des verrous sur des lignes admissibles. De plus, SKIP LOCKED évite d’attendre les verrous de ligne, mais les verrous au niveau des tables peuvent toujours entraîner une attente. Ces limites sont documentées dans SELECT.

Vérifier le comportement des réclamations dans deux sessions Terminal

Utilisez deux fenêtres Terminal connectées à la même base de données via psql. Commencez avec les trois lignes ready d’origine. La séquence contrôlée suivante maintient chaque transaction ouverte afin que vous puissiez observer directement leur chevauchement.

  1. Dans la session A, exécutez BEGIN ISOLATION LEVEL READ COMMITTED;, puis collez l’instruction de réclamation complète ci-dessus. Laissez la transaction ouverte. Avec les données d’exemple intactes, l’ID renvoyé devrait être 1.
  2. Dans la session B, exécutez BEGIN ISOLATION LEVEL READ COMMITTED;, puis collez la même instruction de réclamation. Elle devrait renvoyer l’ID 2 alors que A détient toujours l’ID 1.
  3. Exécutez ROLLBACK; dans la session B, puis ROLLBACK; dans la session A.
  4. Dans l’une ou l’autre session, exécutez la requête d’inspection ci-dessous. Les deux réclamations ayant été annulées, les trois articles devraient de nouveau être ready.
SELECT id, title, state, rendered_title
FROM article_claim_demo
ORDER BY id;

Il s’agit de résultats attendus déduits du comportement documenté des verrous et des annulations, et non d’un test de base de données effectivement exécuté. L’annulation révoque chaque mise à jour d’état et libère les verrous correspondants. Si vous utilisez plutôt COMMIT, ces articles restent processing et ne seront pas sélectionnés par les réclamations ultérieures. Voir Verrouillage explicite et Transactions.

Ce test permet d’observer deux propriétés utiles : la session B passe à l’article suivant lorsqu’un article est verrouillé, et un changement d’état validé empêche qu’un article soit inclus dans de futures réclamations. Vérifiez ces deux propriétés lors de l’adaptation d’un worker existant.

Exécuter trois workers avec node-postgres

Après avoir annulé les deux transactions de test, créez un nouveau répertoire de projet local dans Terminal. Installez le paquet avec la commande d’installation documentée de node-postgres :

mkdir article-claim-demo
cd article-claim-demo
npm init -y
npm install pg

Enregistrez le programme complet suivant sous le nom workers.mjs. Il utilise le même environnement de connexion à la base de données que psql ; node-postgres documente les variables de connexion partagées dans la section Connexion. Chaque worker tente une réclamation, convertit le titre synthétique en majuscules et enregistre le résultat.

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool({ max: 3 });

const claimSQL = `
WITH candidate AS (
  SELECT id
  FROM article_claim_demo
  WHERE state = 'ready'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE article_claim_demo AS a
SET state = 'processing'
FROM candidate AS c
WHERE a.id = c.id AND a.state = 'ready'
RETURNING a.id, a.title;
`;

async function worker(label) {
  const claimed = await pool.query(claimSQL);

  if (claimed.rows.length === 0) {
    console.log(`${label}: no row available now`);
    return;
  }

  const article = claimed.rows[0];
  const renderedTitle = article.title.toUpperCase();

  const completed = await pool.query(
    `UPDATE article_claim_demo
     SET rendered_title = $2, state = 'done'
     WHERE id = $1 AND state = 'processing'
     RETURNING id`,
    [article.id, renderedTitle]
  );

  if (completed.rowCount !== 1) {
    throw new Error(`${label}: completion did not update one row`);
  }

  console.log(`${label}: completed article ${article.id}`);
}

try {
  const results = await Promise.allSettled([
    worker('A'),
    worker('B'),
    worker('C')
  ]);

  for (const result of results) {
    if (result.status === 'rejected') {
      console.error(result.reason);
      process.exitCode = 1;
    }
  }
} finally {
  await pool.end();
}

Exécutez node workers.mjs, puis relancez la requête d’inspection dans psql. Si les trois articles prêts sont intacts et qu’aucune autre activité n’a lieu, les trois états devraient devenir done, avec des valeurs en majuscules dans rendered_title. L’ordre des étiquettes des workers et celui de la sortie console ne correspondent pas nécessairement à l’ordre des articles.

pool.query convient ici, car chaque réclamation est une instruction complète et chaque achèvement constitue une instruction distincte. Le pool gère l’acquisition et la restitution des connexions ; pool.end() ferme le pool une fois les tentatives terminées. Voir Mise en pool.

La requête d’achèvement transmet ses valeurs séparément à l’aide de $1 et $2, conformément à Requêtes. Le programme vérifie si le tableau rows renvoyé est vide et contrôle rowCount pour connaître le nombre de lignes d’achèvement mises à jour, conformément à pg.Result.

Le code JavaScript a passé une vérification syntaxique lors de sa préparation. Les exécutables PostgreSQL n’étaient pas disponibles dans l’environnement de recherche ; les instructions SQL et le comportement des accès concurrents à la base de données ont donc été examinés à partir de la documentation, mais n’ont pas été exécutés ici.

Choisir la bonne limite de transaction et gérer le travail inachevé

L’exemple valide une réclamation brève avant d’effectuer la transformation du titre. Cela réduit la durée du verrou, mais crée un point de récupération explicite : si un worker s’arrête après la réclamation et avant l’achèvement, l’article reste processing. La réexécution du programme ne le réclamera pas de nouveau.

Pour cette démonstration sur ordinateur, arrêtez tous les workers et inspectez les articles processing avant de les rendre de nouveau admissibles de manière délibérée. Ne les réinitialisez pas automatiquement lorsqu’un worker précédent pourrait encore terminer : l’exemple ne comporte aucun champ de génération de réclamation permettant de distinguer une tentative précédente d’une tentative ultérieure. La prévention des réclamations en double ne garantit ni la récupération automatique ni un traitement exactement une fois.

Utilisez cet aide à la décision pour adapter l’exemple :

Votre tâche Approche transactionnelle Conséquence
Réclamer un article, puis effectuer un travail applicatif plus long Valider d’abord la réclamation en une seule instruction Conserver un état processing visible et concevoir séparément la récupération
Effectuer une petite transformation entièrement dans PostgreSQL Réclamer et terminer dans une seule transaction Les modifications de la base de données peuvent être validées ou annulées ensemble
Exécuter plusieurs instructions SQL dans une seule transaction Node.js Emprunter un client et l’utiliser pendant toute l’opération Conserver la même connexion pour BEGIN, les instructions et COMMIT ou ROLLBACK

Pour la dernière approche, node-postgres exige que chaque instruction de la transaction utilise le même client emprunté. Des appels distincts à pool.query peuvent utiliser des connexions différentes et ne peuvent donc pas former cette transaction. Libérez le client après avoir terminé ou annulé la transaction. Voir Transactions et Mise en pool.

Précisez les hypothèses d’isolation présentées. Avec les niveaux Repeatable Read et Serializable, des erreurs peuvent survenir si une ligne à verrouiller a été modifiée depuis le début de la transaction ; les applications utilisant ces niveaux doivent gérer les nouvelles tentatives de transaction. PostgreSQL décrit cette limite dans Verrouillage explicite.

Pour cette tâche portant sur des articles synthétiques, la séquence essentielle est concrète : sélectionner et verrouiller une ligne admissible, modifier son état dans la même instruction, puis ne traiter l’article renvoyé qu’une fois la réclamation réussie. Vérifiez le chevauchement dans deux sessions, puis décidez comment votre application gérera un article dont le worker n’enregistre jamais l’achèvement.

Sources