Usa FOR UPDATE SKIP LOCKED in una query che cambia anche lo stato della riga selezionata da ready a processing. In questo modo, i worker concorrenti possono reclamare righe diverse di articoli sintetici, mentre lo stato salvato impedisce un altro reclamo una volta terminato il lock.

Questa guida è pensata per uno sviluppatore che usa Terminal su un Mac con una connessione PostgreSQL funzionante. Creerai tre articoli sintetici, verificherai i reclami concorrenti in due sessioni del database ed eseguirai un esempio completo con node-postgres.

Perché due worker possono selezionare lo stesso articolo?

Un semplice SELECT non riserva il risultato. Con il livello di isolamento predefinito Read Committed di PostgreSQL, due worker possono leggere la stessa riga ready sottoposta a commit prima che uno dei due la modifichi. Racchiudere quella semplice lettura e un successivo aggiornamento in una transazione non rende di per sé esclusiva la selezione. PostgreSQL documenta il comportamento degli snapshot in Isolamento delle transazioni.

Il reclamo richiede due protezioni: un lock sulla riga durante la selezione dell'articolo e una modifica dello stato prima del rilascio del lock. PostgreSQL rilascia i lock sulle righe al termine della transazione. Di conseguenza, una lettura di blocco isolata, seguita dall'elaborazione, lascerebbe di nuovo idonea la riga una volta terminata la transazione di lettura. Consulta Blocco esplicito.

Usa lo stesso protocollo di reclamo in ogni worker. Un percorso dell'applicazione che legge un articolo ready e avvia l'elaborazione senza averlo reclamato con successo può comunque duplicare il lavoro.

Crea una piccola coda di articoli sintetici

Come prerequisiti, servono un database PostgreSQL in esecuzione, il comando psql disponibile in Terminal e una configurazione di connessione esistente. Per la sezione JavaScript, installa anche una versione di Node.js attualmente supportata e npm. Questa guida parte da una connessione al database funzionante; l'installazione varia in base alla configurazione Mac esistente.

La funzionalità SKIP LOCKED richiesta è stata introdotta in PostgreSQL 9.5. Questa è la versione minima per la sintassi, non una raccomandazione a usare una versione non più supportata. Usa una versione supportata di PostgreSQL; la documentazione attuale consultata indica PostgreSQL 18 come versione corrente. Consulta le note di rilascio della 9.5 e la documentazione corrente di SELECT.

Apri Terminal ed esegui psql usando le consuete impostazioni di connessione. Si aprirà un prompt interattivo per eseguire SQL e visualizzare i risultati, come descritto nella documentazione di psql. Esegui una volta la seguente configurazione in un database di prova in cui article_claim_demo non esiste già. Il comando non sostituisce intenzionalmente una tabella esistente.

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');

Gli esempi presuppongono il livello di isolamento Read Committed e questa semplice tabella, senza trigger aggiuntivi. Lascia invariate le tre righe di esempio finché non avrai completato il test con due sessioni.

Reclama un articolo con una singola istruzione SQL

Esegui questa istruzione ogni volta che un worker ha bisogno di un articolo. La query dei candidati applica un lock a una riga disponibile; l'aggiornamento registra il reclamo e restituisce i campi di input. La documentazione di UPDATE di PostgreSQL descrive questo schema CTE e aggiornamento e conferma che RETURNING restituisce le righe effettivamente aggiornate.

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;

Il lock va applicato all'interno della CTE dei candidati. SKIP LOCKED permette a un altro worker di passare oltre un candidato che non può bloccare immediatamente. LIMIT 1 limita ogni reclamo e la join sulla chiave primaria individua una riga da aggiornare. PostgreSQL documenta esplicitamente il blocco all'interno di una query WITH e l'uso delle righe saltate per le code in SELECT: la clausola di blocco.

Senza un BEGIN esplicito, PostgreSQL esegue l'istruzione nella propria transazione. Un'istruzione riuscita applica il commit sia dell'aggiornamento associato alla selezione sia della modifica di stato prima che inizi l'elaborazione successiva. Dopo il commit, il lock sulla riga viene rimosso, ma state = 'ready' esclude quell'articolo. Consulta Transazioni.

Elabora solo un articolo restituito dall'istruzione di reclamo. Un risultato vuoto significa che questo tentativo non ha ottenuto alcun articolo; non indica che tutto il lavoro sia terminato. Altri worker potrebbero avere righe idonee bloccate in quel momento. Inoltre, SKIP LOCKED evita le attese sui lock delle righe, mentre i lock a livello di tabella possono comunque causare attese. Questi limiti sono documentati in SELECT.

Verifica il comportamento di reclamo in due sessioni di Terminal

Usa due finestre di Terminal connesse tramite psql allo stesso database. Parti dalle tre righe ready originali. La seguente sequenza controllata mantiene aperta ogni transazione, così potrai osservare direttamente la sovrapposizione.

  1. Nella sessione A, esegui BEGIN ISOLATION LEVEL READ COMMITTED;, quindi incolla l'istruzione di reclamo completa riportata sopra. Lascia aperta la transazione. Con i dati di esempio intatti, l'ID restituito dovrebbe essere 1.
  2. Nella sessione B, esegui BEGIN ISOLATION LEVEL READ COMMITTED;, quindi incolla la stessa istruzione di reclamo. Dovrebbe restituire l'ID 2, mentre A mantiene ancora bloccato l'ID 1.
  3. Esegui ROLLBACK; nella sessione B, poi ROLLBACK; nella sessione A.
  4. In una delle due sessioni, esegui la query di ispezione seguente. Entrambi i reclami sono stati annullati, quindi tutti e tre gli articoli dovrebbero essere di nuovo ready.
SELECT id, title, state, rendered_title
FROM article_claim_demo
ORDER BY id;

Questi sono i risultati attesi, dedotti dal comportamento documentato dei lock e del rollback; non derivano da un test del database eseguito. Il rollback annulla ogni aggiornamento di stato e rilascia i lock. Se usi COMMIT, quegli articoli rimangono processing e non saranno selezionati dai reclami successivi. Consulta Blocco esplicito e Transazioni.

Questo test distingue due osservazioni utili: la sessione B passa oltre un articolo attualmente bloccato e una modifica di stato sottoposta a commit mantiene un articolo fuori dai reclami successivi. Quando adatti un worker esistente, verifica entrambe le proprietà.

Esegui tre worker con node-postgres

Dopo aver annullato entrambe le transazioni di test, crea una nuova directory di progetto locale in Terminal. Installa il pacchetto usando il comando di installazione documentato di node-postgres:

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

Salva il programma completo seguente come workers.mjs. Usa lo stesso ambiente di connessione al database di psql; node-postgres documenta le variabili di connessione condivise in Connessione. Ogni worker tenta un reclamo, converte in maiuscolo il titolo sintetico e salva il risultato.

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();
}

Esegui node workers.mjs, poi ripeti la query di ispezione in psql. Con tre articoli pronti e invariati e senza altre attività, aspettati che tutti e tre gli stati diventino done, con i valori in maiuscolo in rendered_title. Le etichette dei worker e l'ordine in console non devono necessariamente corrispondere all'ordine degli articoli.

L'uso di pool.query è appropriato qui, perché ogni reclamo è un'istruzione completa e ogni completamento è un'istruzione separata. Il pool gestisce il prelievo e la restituzione delle connessioni; pool.end() chiude il pool una volta conclusi i tentativi. Consulta Pooling.

La query di completamento passa i valori separatamente usando $1 e $2, secondo quanto descritto in Query. Il programma controlla se l'array rows restituito è vuoto e verifica rowCount per il numero di righe di completamento aggiornate, in linea con pg.Result.

Durante la preparazione, il JavaScript ha superato un controllo sintattico. Nell'ambiente di ricerca non erano disponibili gli eseguibili PostgreSQL, quindi l'SQL e il comportamento del database in concorrenza sono stati verificati consultando la documentazione, ma non sono stati eseguiti qui.

Scegli il confine della transazione corretto e gestisci il lavoro non completato

L'esempio applica il commit di un breve reclamo prima di trasformare il titolo. In questo modo, la durata del lock è breve, ma si crea un confine di ripristino intenzionale: se un worker si arresta dopo il reclamo e prima del completamento, l'articolo rimane processing. Eseguire di nuovo il programma non permetterà di reclamarlo.

Per questa dimostrazione su desktop, arresta tutti i worker e ispeziona gli eventuali articoli processing prima di renderli nuovamente idonei. Non reimpostarli automaticamente mentre un worker precedente potrebbe ancora terminare: l'esempio non ha un campo per la generazione del reclamo che distingua un tentativo precedente da uno successivo. La sola prevenzione dei reclami duplicati non offre né ripristino automatico né elaborazione esattamente una volta.

Usa questa guida decisionale per adattare l'esempio:

Il tuo compito Approccio alla transazione Conseguenza
Reclama un articolo, poi svolgi attività applicative più lunghe Applica prima il commit al reclamo con un'unica istruzione Mantieni uno stato processing visibile e progetta separatamente il ripristino
Esegui interamente in PostgreSQL una piccola trasformazione Reclama e completa all'interno di una transazione Le modifiche al database possono essere sottoposte a commit o annullate insieme
Esegui in Node.js più istruzioni SQL come un'unica transazione Preleva un client e usalo per tutta la durata Mantieni la stessa connessione durante BEGIN, le istruzioni e COMMIT o ROLLBACK

Per quest'ultimo approccio, node-postgres richiede che ogni istruzione della transazione usi lo stesso client prelevato. Chiamate separate a pool.query possono usare connessioni diverse, quindi non possono far parte di quella transazione. Rilascia il client dopo aver completato o annullato la transazione. Consulta Transazioni e Pooling.

Mantieni esplicite le ipotesi sull'isolamento illustrate. Con Repeatable Read e Serializable possono verificarsi errori se una riga da bloccare è stata modificata dall'inizio della transazione; le applicazioni che usano questi livelli devono gestire i tentativi di ripetizione della transazione. PostgreSQL descrive questo limite in Blocco esplicito.

Per questa coda di articoli sintetici, la sequenza essenziale è concreta: seleziona e blocca una riga idonea, cambiane lo stato nella stessa istruzione ed elabora solo l'articolo restituito dopo che il reclamo è riuscito. Verifica la sovrapposizione in due sessioni, poi decidi come la tua applicazione gestirà un articolo il cui worker non registra mai il completamento.

Fonti