Voorkom deadlocks bij gelijktijdige overlappende PostgreSQL-updates door vóór het bijwerken de volledige doelverzameling van elke transactie in dezelfde volgorde op basis van een onveranderlijke sleutel te vergrendelen. Houd de transactie op één node-postgres-client en probeer in aanmerking komende fouten opnieuw vanaf het begin.

Deze handleiding is bedoeld voor een ontwikkelaar die in macOS Terminal werkt en twee gelijktijdige batches volledig synthetische artikelrijen wil laten bijwerken. Het voorbeeld gebruikt de huidige PostgreSQL-documentatie, waarin PostgreSQL 18 wordt vermeld, en de huidige node-postgres-documentatie. De reikwijdte is een bekende lijst met bestaande artikel-ID's, die tijdens gelijktijdige werkzaamheden ongewijzigd blijven. PostgreSQL: expliciete vergrendeling

Waarom overlappende updates deadlocks kunnen veroorzaken

Neem twee transacties: A werkt eerst artikel 1 en daarna artikel 2 bij; B werkt eerst artikel 2 en daarna artikel 1 bij. Als beide de vergrendeling van hun eerste rij verkrijgen voordat ze de tweede aanvragen, wacht elke transactie op een vergrendeling die de andere vasthoudt. PostgreSQL detecteert de cyclus en breekt een van de transacties af; de applicatie kan niet kiezen welke. Gewone updates kunnen deze situatie veroorzaken zonder expliciete vergrendelingsinstructies. PostgreSQL: expliciete vergrendeling

Het nuttige onderscheid is dat tussen overlap en een cyclus. Overlap kan wachten vereisen. Door een cyclus kan geen van beide transacties verdergaan. PostgreSQL adviseert applicaties om een consistente volgorde voor het verkrijgen van vergrendelingen te hanteren en eerst de sterkste vereiste vergrendelingsmodus te verkrijgen. PostgreSQL: expliciete vergrendeling

Hanteer voor de workload in dit artikel één regel: verkrijg alle gevraagde artikelrijvergrendelingen in oplopende volgorde van id voordat je artikelen wijzigt. Pas die regel toe op de volledige transactie, inclusief eerdere instructies die artikelen aanraken.

Vergrendel de volledige doelverzameling vóór de update

Een JavaScript-array sorteren instrueert een bulk-SQL-update niet om de rijen in die volgorde te bezoeken. De PostgreSQL-syntaxis UPDATE heeft geen directe ORDER BY-clausule. De documentatie laat zien hoe je vergrendelingen in volgorde verkrijgt via een selecterende query en legt uit dat een gedeelde volgorde deadlocks tussen updatebewerkingen kan voorkomen. PostgreSQL: UPDATE

Gebruik binnen de transactie een aparte, afgewachte vergrendelingsinstructie. Deze illustratieve SQL-transactie richt zich op artikelen 1, 2 en 3; de opzet van de tabel staat in de volgende sectie.

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;

De eerste instructie bepaalt de volgorde waarin de vergrendelingen worden verkregen. De update werkt vervolgens op rijen die al door die transactie zijn vergrendeld. Rijvergrendelingen blijven normaal gesproken bestaan tot het einde van de transactie. Houd deze periode kort: bereid invoer voor voordat je begint en wacht niet op desktopinvoer terwijl vergrendelingen worden vastgehouden. PostgreSQL: expliciete vergrendeling

De volgende voorwaarden bepalen het volgordecontract van het voorbeeld:

  • Elke concurrerende batch gebruikt oplopende volgorde van artikel-id.
  • ID's blijven ongewijzigd tijdens gelijktijdige werkzaamheden.
  • Elke batch bepaalt de volledige doelverzameling voordat er vergrendelingen worden verkregen.
  • Geen eerdere instructie heeft artikelrijen al in een andere volgorde vergrendeld.
  • De voorbeeldtabel heeft geen triggers of bewerkingen op gerelateerde tabellen die extra vergrendelingsafhankelijkheden introduceren.

Dit zijn voorwaarden om de redenering op deze workload toe te passen. Een transactie die eerst artikel 9 vergrendelt en daarna artikelen 1 tot en met 3 aanvraagt, heeft het contract al geschonden, zelfs als de uiteindelijke query ORDER BY id bevat.

Voer een volledig node-postgres-voorbeeld uit op je Mac

Voorwaarden zijn een actieve lokale PostgreSQL-server, een bestaande tijdelijke database, Node.js met npm en psql beschikbaar in Terminal. Configureer de verbinding met je bestaande database via de PostgreSQL-omgevingsvariabelen. node-postgres-pools en psql gebruiken dezelfde verbindingsvariabelen; stel PGHOST zo nodig expliciet in, zodat beide opdrachten dezelfde server bereiken. node-postgres: verbinding maken

Installeer de afhankelijkheid in een nieuwe werkmap:

npm init -y
npm install pg

Sla de volgende volledige opzet op als setup.sql. Deze maakt een nieuwe demonstratietabel en drie synthetische artikelen aan. Voer het bestand één keer uit in de tijdelijke database. Het bestand mislukt bewust als die tabel al bestaat, in plaats van de bestaande inhoud te vervangen.

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

Sla het volgende volledige programma op als update-articles.mjs. Alle instructies die bij een poging horen, gebruiken de client die door pool.connect() wordt teruggegeven. node-postgres vereist expliciet één client voor de volledige transactie; transactie-instructies via pool.query() versturen voldoet niet aan die eis. node-postgres: transacties

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

De array wordt als één parameter doorgegeven en expliciet naar integer[] gecast; node-postgres documenteert de omzetting van JavaScript-arrays naar PostgreSQL-arraywaarden. Duplicaten verwijderen zorgt ervoor dat het gevraagde aantal unieke artikelen betreft. De ORDER BY van de database, niet de volgorde van de invoerarray, bepaalt de volgorde waarin vergrendelingen worden verkregen. node-postgres: queries

Bij een pas geïnitialiseerde tabel, zonder andere schrijvers en als beide batches slagen, zijn de verwachte revisies 1 voor artikel 1 en 2 voor artikelen 2 en 3. Deze totalen volgen uit het lidmaatschap van de batches, ongeacht welke batch als eerste klaar is. Bij volgende scriptuitvoeringen worden de bestaande waarden opnieuw verhoogd. Een ontbrekende gevraagde ID annuleert die batch in plaats van stilzwijgend alleen de gevonden rijen bij te werken.

Elke batch is een aparte transactie. Als de ene slaagt en de andere definitief mislukt, blijft de geslaagde batch vastgelegd. Promise.allSettled() laat beide pogingen afronden voordat het programma de pool sluit. De laatste pool.query() wordt buiten beide transacties uitgevoerd. De pool-API documenteert hoe je uitgeleende clients vrijgeeft, zo nodig een client vernietigt en de pool beëindigt nadat het scriptwerk klaar is. node-postgres: pool

De controles op normalisatie en de illustratieve berekening van revisies zijn uitgevoerd met Node.js. PostgreSQL was niet beschikbaar in de onderzoeksomgeving, dus de SQL en het databasegedrag bij gelijktijdige toegang zijn aan de hand van documentatie gecontroleerd, niet uitgevoerd.

Behoud het isolatieniveau dat je taak vereist

Het programma gebruikt expliciet READ COMMITTED voor updates met vaste ID's. Op dit niveau kan een vergrendelingsinstructie wachten op een updater en vervolgens de bijgewerkte rijversie vergrendelen, mits die nog steeds aan de voorwaarde voldoet. Elke instructie krijgt een nieuwe snapshot. Een rij die vóór het verkrijgen van de vergrendeling is verwijderd, kan daardoor ontbreken; het programma vangt dit op met de aantalscontrole. PostgreSQL: transactie-isolatie

Verlaag het isolatieniveau van een bestaande transactie niet alleen om dit voorbeeld te kunnen gebruiken. Onder REPEATABLE READ kan een poging om een rij te vergrendelen of bij te werken die sinds de transactiesnapshot is gewijzigd, een serialisatiefout veroorzaken. SERIALIZABLE vereist ook afhandeling van nieuwe pogingen en voorkomt gecommitteerde serialisatieafwijkingen; het vervangt niet de consistente volgorde van gewone rijvergrendelingen. PostgreSQL: transactie-isolatie

Vergrendeling van bekende rijen garandeert niet dat het lidmaatschap van een predicaat ongewijzigd blijft. Dit voorbeeld voert bijvoorbeeld niet de bewerking “werk elk artikel bij waarvan de titel aan een voorwaarde voldoet, inclusief gelijktijdig toegevoegde artikelen” uit. Dat is een andere consistentietaak. Bepaal de vereisten voor isolatie en coördinatie voordat je het recept voor vaste ID's aanpast.

Houd ook de sorteerkolom onveranderlijk. PostgreSQL documenteert dat onder READ COMMITTED het sorteren plaatsvindt vóór het vergrendelen; waarden die veranderen terwijl de instructie wacht, kunnen ervoor zorgen dat teruggegeven rijen niet op volgorde lijken te staan. Ongewijzigde ID's gebruiken voorkomt die complicatie. PostgreSQL: SELECT

Probeer volledige transacties opnieuw en onderzoek herhaalde fouten

PostgreSQL duidt deadlockfouten aan met SQLSTATE 40P01 en serialisatiefouten met 40001. Probeer de volledige transactie opnieuw, inclusief alle databaseafhankelijke beslissingen die bepalen welke instructies of waarden worden gebruikt. Alleen de laatste update opnieuw proberen voldoet niet aan die eis. PostgreSQL: afhandeling van serialisatiefouten

Het voorbeeld staat in totaal drie pogingen toe. De korte exponentiële vertraging en jitter zijn illustratieve keuzes voor de applicatie, geen PostgreSQL-standaardwaarden. De client wordt vrijgegeven voordat de vertraging begint; bij andere fouten wordt direct gestopt en bij uitputting van de pogingen wordt dit gemeld. PostgreSQL waarschuwt dat opnieuw proberen geen uiteindelijke voltooiing garandeert. PostgreSQL: afhandeling van serialisatiefouten

Gebruik deze besliswijzer bij het aanpassen van de implementatie:

Situatie Actie
Een andere batch houdt een gevraagde rij al vergrendeld Laat het verkrijgen van vergrendelingen in de afgesproken volgorde wachten als de taak alle gevraagde rijen vereist.
Een gevraagde ID ontbreekt Draai terug en meld dat het doel ontbreekt.
SQLSTATE is 40P01 of 40001 Draai terug en probeer daarna de volledige in aanmerking komende transactie binnen een begrensd aantal pogingen opnieuw.
Fouten blijven optreden na vergrendeling in de afgesproken volgorde Controleer eerdere instructies, andere schrijvers, triggers en extra tabellen op afwijkingen van het volgordecontract.
De bewerking raakt meerdere tabellen Bepaal voor alle concurrerende uitvoeringspaden zowel de gedeelde tabelvolgorde als de rijvolgorde.
Een batch moet atomair worden vastgelegd Behoud één transactie; afzonderlijk vastgelegde batches zouden de taak veranderen.

Voeg voor deze batch met alle doelen geen SKIP LOCKED toe: daarmee worden rijen overgeslagen die niet direct kunnen worden vergrendeld. PostgreSQL beschrijft deze optie als nuttig voor wachtrijachtige verbruikers en ongeschikt voor een algemeen consistente weergave. NOWAIT geeft een foutmelding in plaats van te wachten op een conflicterende rijvergrendeling; dit verandert het wachtgedrag, maar voltooit de batch niet. PostgreSQL: SELECT

De implementatie is klaar om aan te passen zodra je de volledige doelverzameling kunt benoemen, de sorteersleutels ongewijzigd kunt houden en dezelfde regel voor het verkrijgen van vergrendelingen op concurrerende transacties kunt toepassen. Behoud de controle op ontbrekende rijen en de begrensde pogingen van de volledige transactie, zodat conflicten of onvolledige invoer een expliciete uitkomst opleveren.

Bronnen