Per prevenire i deadlock quando gli aggiornamenti concorrenti in PostgreSQL si sovrappongono, blocca l’intero insieme di righe interessate da ciascuna transazione nello stesso ordine basato su chiavi immutabili, prima di aggiornarle. Mantieni la transazione su un unico client node-postgres e ritenta dall’inizio gli errori idonei.

Questa guida è rivolta a uno sviluppatore che lavora nel Terminale di macOS e deve eseguire completamente gli aggiornamenti di due batch concorrenti su righe sintetiche di articoli. L’esempio usa la documentazione attuale di PostgreSQL, che identifica PostgreSQL 18, e la documentazione attuale di node-postgres. L’ambito è un elenco noto di ID di articoli esistenti, che restano invariati durante il lavoro concorrente. PostgreSQL: blocco esplicito

Perché gli aggiornamenti sovrapposti possono causare deadlock

Considera due transazioni: A aggiorna l’articolo 1 e poi l’articolo 2; B aggiorna l’articolo 2 e poi l’articolo 1. Se ciascuna ottiene il blocco sulla prima riga prima di richiederlo sulla seconda, entrambe restano in attesa di un blocco detenuto dall’altra. PostgreSQL rileva il ciclo e annulla una delle transazioni; l’applicazione non può scegliere quale. Gli aggiornamenti ordinari possono produrre questa situazione anche senza istruzioni di blocco esplicite. PostgreSQL: blocco esplicito

La distinzione utile è tra sovrapposizione e ciclo. Una sovrapposizione può richiedere un’attesa. Un ciclo impedisce a entrambe le transazioni di procedere. PostgreSQL consiglia alle applicazioni di acquisire i blocchi sempre nello stesso ordine e di acquisire per primi i blocchi nella modalità più restrittiva richiesta. PostgreSQL: blocco esplicito

Per il carico di lavoro descritto in questo articolo, applica una regola: acquisisci tutti i blocchi sulle righe degli articoli richiesti in ordine crescente di id prima di modificare qualsiasi articolo. Applica la regola all’intera transazione, comprese le eventuali istruzioni precedenti che accedono agli articoli.

Blocca l’intero insieme di righe interessate prima dell’aggiornamento

Ordinare un array JavaScript non indica a un aggiornamento SQL in blocco in quale ordine visitare le righe. La sintassi UPDATE di PostgreSQL non ha una clausola diretta ORDER BY. La documentazione mostra come eseguire blocchi ordinati tramite una query di selezione e spiega che un ordine condiviso può prevenire i deadlock tra operazioni di aggiornamento. PostgreSQL: UPDATE

Usa un’istruzione di blocco separata e attesa all’interno della transazione. Questa transazione SQL illustrativa interessa gli articoli 1, 2 e 3; la tabella di configurazione è mostrata nella sezione successiva.

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT id
FROM deadlock_demo_articles
WHERE id = ANY(ARRAY[3, 1, 2]::integer[])
ORDER BY id
FOR UPDATE;

UPDATE deadlock_demo_articles
SET revision = revision + 1
WHERE id = ANY(ARRAY[3, 1, 2]::integer[]);

COMMIT;

La prima istruzione stabilisce l’ordine di acquisizione. L’aggiornamento opera quindi su righe già bloccate dalla transazione. Normalmente, i blocchi sulle righe vengono mantenuti fino alla fine della transazione. Mantieni breve questo intervallo: prepara i dati prima di iniziare ed evita di attendere input da desktop mentre i blocchi sono attivi. PostgreSQL: blocco esplicito

Le seguenti condizioni definiscono il contratto d’ordinamento dell’esempio:

  • Ogni batch concorrente usa l’ordine crescente di id degli articoli.
  • Gli ID restano invariati durante il lavoro concorrente.
  • Ogni batch determina l’intero insieme di righe interessate prima di acquisire i blocchi.
  • Nessuna istruzione precedente ha già bloccato righe degli articoli in un ordine diverso.
  • La tabella dell’esempio non ha trigger né operazioni su tabelle correlate che introducano ulteriori dipendenze tra blocchi.

Queste sono condizioni preliminari per applicare questo ragionamento al carico di lavoro. Una transazione che blocca prima l’articolo 9 e poi richiede gli articoli da 1 a 3 ha già violato il contratto, anche se la query finale contiene ORDER BY id.

Esegui un esempio completo con node-postgres sul tuo Mac

I prerequisiti sono un server PostgreSQL locale in esecuzione, un database esistente per le prove, Node.js con npm e psql disponibile nel Terminale. Configura la connessione al database esistente tramite le variabili d’ambiente di PostgreSQL. I pool node-postgres e psql usano le stesse variabili di connessione; se necessario, imposta esplicitamente PGHOST affinché entrambi i comandi raggiungano lo stesso server. node-postgres: connessione

In una nuova cartella di lavoro, installa la dipendenza:

npm init -y
npm install pg

Salva la seguente configurazione completa come setup.sql. Crea una nuova tabella dimostrativa e tre articoli sintetici. Eseguila una volta nel database di prova. Se la tabella esiste già, la configurazione genera intenzionalmente un errore invece di sostituirne i contenuti.

BEGIN;

CREATE TABLE deadlock_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    revision integer NOT NULL DEFAULT 0
);

INSERT INTO deadlock_demo_articles (id, title)
VALUES
    (1, 'Paper Shapes'),
    (2, 'Color Notes'),
    (3, 'Desk Sketches');

COMMIT;

psql -v ON_ERROR_STOP=1 -f setup.sql

Salva il programma completo seguente come update-articles.mjs. Tutte le istruzioni di un tentativo usano il client restituito da pool.connect(). node-postgres richiede esplicitamente di usare un unico client per tutta la transazione; inviare le istruzioni della transazione tramite pool.query() non soddisfa questo requisito. node-postgres: transazioni

import pg from 'pg';
import { setTimeout as sleep } from 'node:timers/promises';

const { Pool } = pg;
const pool = new Pool({ max: 2, connectionTimeoutMillis: 5000 });
pool.on('error', error => {
  console.error('Idle database client error:', error.message);
  process.exitCode = 1;
});

function normalizeIds(input) {
  if (!Array.isArray(input) || input.length === 0 ||
      input.some(id => !Number.isInteger(id) ||
        id < 1 || id > 2147483647)) {
    throw new Error('Use a nonempty array of positive PostgreSQL integer IDs');
  }
  return [...new Set(input)].sort((a, b) => a - b);
}

async function incrementRevisions(input) {
  const ids = normalizeIds(input);
  const maxAttempts = 3;

  for (let attempt = 1; attempt <= maxAttempts; attempt += 1) {
    const client = await pool.connect();
    let discard = false;

    try {
      await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

      const locked = await client.query(
        `SELECT id
         FROM deadlock_demo_articles
         WHERE id = ANY($1::integer[])
         ORDER BY id
         FOR UPDATE`,
        [ids]
      );

      if (locked.rows.length !== ids.length) {
        throw new Error('A requested article is missing; batch cancelled');
      }

      const updated = await client.query(
        `UPDATE deadlock_demo_articles
         SET revision = revision + 1
         WHERE id = ANY($1::integer[])
         RETURNING id, revision`,
        [ids]
      );

      if (updated.rowCount !== ids.length) {
        throw new Error('Unexpected update count; batch cancelled');
      }

      await client.query('COMMIT');
      return updated.rows.sort((a, b) => a.id - b.id);
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch (rollbackError) {
        discard = true;
        throw new Error('Rollback failed; connection discarded', {
          cause: rollbackError
        });
      }

      const retryable = error.code === '40P01' || error.code === '40001';
      if (!retryable || attempt === maxAttempts) throw error;
    } finally {
      client.release(discard);
    }

    // Illustrative bounded backoff, after releasing the client.
    await sleep(50 * 2 ** (attempt - 1) + Math.floor(Math.random() * 50));
  }
}

try {
  const outcomes = await Promise.allSettled([
    incrementRevisions([3, 1, 2]),
    incrementRevisions([3, 2])
  ]);

  for (const outcome of outcomes) {
    if (outcome.status === 'rejected') throw outcome.reason;
  }

  const result = await pool.query(
    'SELECT id, title, revision FROM deadlock_demo_articles ORDER BY id'
  );
  console.table(result.rows);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

node update-articles.mjs

L’array viene passato come un unico parametro e convertito esplicitamente in integer[]; node-postgres documenta la conversione degli array JavaScript in valori array PostgreSQL. La rimozione dei duplicati fa sì che il conteggio richiesto corrisponda al numero di articoli distinti. È l’ORDER BY del database, e non l’ordine dell’array di input, a determinare l’ordine di acquisizione dei blocchi. node-postgres: query

Su una tabella inizializzata da poco, senza altri processi di scrittura e con entrambi i batch completati correttamente, le revisioni attese sono 1 per l’articolo 1 e 2 per gli articoli 2 e 3. Questi valori dipendono dall’appartenenza ai batch, indipendentemente dall’ordine in cui terminano. Le successive esecuzioni dello script incrementano di nuovo i valori esistenti. Se manca un ID richiesto, il batch viene annullato invece di aggiornare silenziosamente solo le righe trovate.

Ogni batch è una transazione separata. Se uno riesce e l’altro fallisce definitivamente, il batch riuscito resta confermato. Promise.allSettled() consente a entrambi i tentativi di terminare prima che il programma chiuda il pool. L’ultimo pool.query() viene eseguito al di fuori di entrambe le transazioni. L’API del pool documenta come rilasciare i client presi in prestito, distruggere un client quando necessario e terminare il pool una volta completato il lavoro dello script. node-postgres: pool

I controlli di normalizzazione e il calcolo illustrativo delle revisioni sono stati eseguiti con Node.js. Nell’ambiente di ricerca PostgreSQL non era disponibile, quindi le query SQL e il comportamento della concorrenza del database sono stati verificati tramite la documentazione, anziché eseguiti.

Mantieni il livello di isolamento richiesto dall’attività

Il programma usa esplicitamente READ COMMITTED per gli aggiornamenti con ID fissi. A questo livello, un’istruzione di blocco può attendere un processo di aggiornamento e poi bloccare la versione aggiornata della riga, purché questa soddisfi ancora la condizione. Ogni istruzione acquisisce uno snapshot nuovo. Di conseguenza, una riga eliminata prima dell’acquisizione può non essere presente; il programma gestisce il caso controllando il conteggio. PostgreSQL: isolamento delle transazioni

Non ridurre il livello di isolamento di una transazione esistente solo per usare questo esempio. Con REPEATABLE READ, tentare di bloccare o aggiornare una riga modificata dopo lo snapshot della transazione può causare un errore di serializzazione. Anche SERIALIZABLE richiede la gestione dei tentativi e impedisce anomalie di serializzazione nelle transazioni confermate; non sostituisce l’acquisizione dei normali blocchi sulle righe secondo un ordine coerente. PostgreSQL: isolamento delle transazioni

Il blocco di righe note non garantisce che l’appartenenza a un insieme definito da un predicato resti invariata. Per esempio, questo esempio non implementa «aggiorna tutti gli articoli il cui titolo corrisponde a una condizione, compresi quelli aggiunti contemporaneamente». È un problema di coerenza diverso. Prima di adattare la procedura per ID fissi, stabilisci i requisiti di isolamento e coordinamento.

Mantieni inoltre immutabile la colonna di ordinamento. PostgreSQL documenta che, a livello READ COMMITTED, l’ordinamento avviene prima del blocco; i valori modificati mentre l’istruzione è in attesa possono far apparire le righe restituite fuori ordine. Usare ID invariati evita questa complicazione specifica. PostgreSQL: SELECT

Ritenta le transazioni complete e analizza gli errori ricorrenti

PostgreSQL identifica i deadlock con il codice SQLSTATE 40P01 e gli errori di serializzazione con 40001. Ritenta l’intera transazione, comprese le decisioni basate sul database che determinano le istruzioni o i valori da usare. Ritentare solo l’aggiornamento finale non soddisfa questo requisito. PostgreSQL: gestione degli errori di serializzazione

L’esempio consente tre tentativi totali. Il breve ritardo esponenziale e la componente casuale sono scelte illustrative dell’applicazione, non impostazioni predefinite di PostgreSQL. Il client viene rilasciato prima dell’attesa, gli altri errori interrompono subito l’esecuzione e l’esaurimento dei tentativi viene segnalato. PostgreSQL avverte che i tentativi non garantiscono il completamento finale. PostgreSQL: gestione degli errori di serializzazione

Usa questa guida decisionale per adattare l’implementazione:

Situazione Azione
Un altro batch detiene già una riga richiesta Se l’attività richiede tutte le righe richieste, consenti che l’acquisizione ordinata resti in attesa.
Un ID richiesto è assente Esegui il rollback e segnala l’assenza della riga richiesta.
Il codice SQLSTATE è 40P01 o 40001 Esegui il rollback, poi ritenta l’intera transazione idonea entro un numero limitato di tentativi.
Gli errori continuano anche dopo aver ordinato l’acquisizione dei blocchi Controlla le istruzioni precedenti, gli altri processi di scrittura, i trigger e le tabelle aggiuntive per individuare violazioni del contratto d’ordinamento.
L’operazione interessa più tabelle Definisci un ordine condiviso delle tabelle, oltre a quello delle righe, per tutti i percorsi concorrenti.
Il batch deve essere confermato atomicamente Mantieni un’unica transazione; confermare blocchi separati cambierebbe il compito.

Evita di aggiungere SKIP LOCKED a questo batch che interessa tutte le righe: omette quelle che non possono essere bloccate immediatamente. PostgreSQL lo descrive come utile per i consumatori di code e inadatto a fornire una vista generale coerente. NOWAIT genera un errore invece di attendere un blocco conflittuale sulla riga; modifica il comportamento di attesa, ma non completa il batch. PostgreSQL: SELECT

L’implementazione è pronta per essere adattata quando puoi specificare l’intero insieme di righe interessate, mantenere invariate le relative chiavi d’ordinamento e applicare la stessa regola di acquisizione a tutte le transazioni concorrenti. Mantieni il controllo delle righe mancanti e i tentativi limitati dell’intera transazione, così la contesa o un input incompleto producono un esito esplicito.

Fonti