Um Deadlocks bei überlappenden gleichzeitigen PostgreSQL-Updates zu verhindern, sperren Sie vor dem Aktualisieren die vollständige Zielmenge jeder Transaktion in derselben Reihenfolge nach unveränderlichen Schlüsseln. Verwenden Sie für die Transaktion einen einzigen node-postgres-Client und wiederholen Sie geeignete fehlgeschlagene Versuche von Anfang an.

Diese Anleitung richtet sich an Entwickler, die im macOS-Terminal arbeiten und zwei gleichzeitige Stapel vollständig auf synthetische Artikelzeilen anwenden müssen. Das Beispiel verwendet die aktuelle PostgreSQL-Dokumentation, in der PostgreSQL 18 als aktuelle Version angegeben ist, sowie die aktuelle node-postgres-Dokumentation. Der Anwendungsbereich ist eine bekannte Liste vorhandener Artikel-IDs, die während gleichzeitiger Vorgänge unverändert bleiben. PostgreSQL: Explizite Sperrung

Warum sich überlappende Updates zu Deadlocks führen können

Betrachten Sie zwei Transaktionen: A aktualisiert zuerst Artikel 1 und danach Artikel 2; B aktualisiert zuerst Artikel 2 und danach Artikel 1. Wenn jede Transaktion ihre erste Zeilensperre erhält, bevor sie die zweite anfordert, wartet jede auf eine Sperre, die von der anderen gehalten wird. PostgreSQL erkennt den Zyklus und bricht eine der Transaktionen ab; die Anwendung kann nicht wählen, welche. Gewöhnliche Updates können diese Situation auch ohne explizite Sperranweisungen verursachen. PostgreSQL: Explizite Sperrung

Entscheidend ist der Unterschied zwischen Überlappung und einem Zyklus. Bei Überlappung kann Warten nötig sein. Bei einem Zyklus kann keine der Transaktionen fortfahren. PostgreSQL empfiehlt, dass Anwendungen Sperren stets in derselben Reihenfolge anfordern und dabei den jeweils stärksten erforderlichen Sperrmodus zuerst erwerben. PostgreSQL: Explizite Sperrung

Für die in diesem Artikel betrachtete Arbeitslast gilt folgende Regel: Erwerben Sie alle Sperren für angeforderte Artikelzeilen in aufsteigender Reihenfolge nach id, bevor Sie einen Artikel ändern. Wenden Sie diese Regel auf die gesamte Transaktion an, einschließlich früherer Anweisungen, die Artikel betreffen.

Sperren Sie die vollständige Zielmenge vor dem Update

Das Sortieren eines JavaScript-Arrays weist ein SQL-Massenupdate nicht an, die Zeilen in dieser Reihenfolge zu bearbeiten. Die PostgreSQL-Syntax UPDATE verfügt über keine direkte ORDER BY-Klausel. Die Dokumentation zeigt die geordnete Sperrung anhand einer abfragenden Anweisung und erläutert, dass eine gemeinsame Reihenfolge Deadlocks zwischen Update-Vorgängen verhindern kann. PostgreSQL: UPDATE

Verwenden Sie innerhalb der Transaktion eine separate, abgewartete Sperranweisung. Diese illustrative SQL-Transaktion betrifft die Artikel 1, 2 und 3; die Einrichtungstabelle wird im nächsten Abschnitt gezeigt.

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;

Die erste Anweisung legt die Reihenfolge für den Sperrerwerb fest. Das Update bearbeitet anschließend Zeilen, die von dieser Transaktion bereits gesperrt wurden. Zeilensperren bleiben normalerweise bis zum Ende der Transaktion bestehen. Halten Sie diesen Zeitraum kurz: Bereiten Sie Eingaben vor Beginn vor und warten Sie nicht auf Desktop-Eingaben, während Sperren gehalten werden. PostgreSQL: Explizite Sperrung

Die folgenden Bedingungen definieren den Ordnungsvertrag für dieses Beispiel:

  • Jeder konkurrierende Stapel verwendet aufsteigende Reihenfolge nach Artikel-id.
  • IDs bleiben während gleichzeitiger Vorgänge unverändert.
  • Jeder Stapel bestimmt seine vollständige Zielmenge vor dem Sperren.
  • Keine frühere Anweisung hat Artikelzeilen bereits in einer anderen Reihenfolge gesperrt.
  • Die Beispieltabelle enthält keine Trigger oder Vorgänge für verknüpfte Tabellen, die zusätzliche Sperrabängigkeiten verursachen.

Diese Voraussetzungen sind nötig, um die Überlegungen auf diese Arbeitslast anzuwenden. Eine Transaktion, die zuerst Artikel 9 sperrt und später Artikel 1 bis 3 anfordert, hat den Vertrag bereits verletzt, selbst wenn ihre abschließende Abfrage ORDER BY id enthält.

Führen Sie ein vollständiges node-postgres-Beispiel auf Ihrem Mac aus

Voraussetzung sind ein laufender lokaler PostgreSQL-Server, eine vorhandene temporäre Datenbank, Node.js mit npm und psql im Terminal. Konfigurieren Sie Ihre bestehende Datenbankverbindung über die PostgreSQL-Umgebungsvariablen. node-postgres-Pools und psql verwenden dieselben Verbindungsvariablen; setzen Sie PGHOST bei Bedarf explizit, damit beide Befehle denselben Server erreichen. node-postgres: Verbindung herstellen

Installieren Sie die Abhängigkeit in einem neuen Arbeitsordner:

npm init -y
npm install pg

Speichern Sie die folgende vollständige Einrichtung als setup.sql. Sie erstellt eine neue Demonstrationstabelle und drei synthetische Artikel. Führen Sie sie einmal in der temporären Datenbank aus. Sie schlägt absichtlich fehl, wenn diese Tabelle bereits vorhanden ist, statt vorhandene Inhalte zu ersetzen.

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

Speichern Sie das nächste vollständige Programm als update-articles.mjs. Alle zu einem Versuch gehörenden Anweisungen verwenden den von pool.connect() zurückgegebenen Client. node-postgres verlangt ausdrücklich, während einer Transaktion denselben Client zu verwenden; Transaktionsanweisungen über pool.query() auszuführen, erfüllt diese Anforderung nicht. node-postgres: Transaktionen

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

Das Array wird als einzelner Parameter übergeben und explizit nach integer[] umgewandelt; node-postgres dokumentiert die Konvertierung von JavaScript-Arrays in PostgreSQL-Arraywerte. Durch das Entfernen von Duplikaten gibt die angeforderte Anzahl die Zahl unterschiedlicher Artikel an. Die ORDER BY-Klausel der Datenbank, nicht die Reihenfolge des Eingabearrays, legt die Reihenfolge für den Sperrerwerb fest. node-postgres: Abfragen

Bei einer frisch initialisierten Tabelle, ohne andere schreibende Vorgänge und bei Erfolg beider Stapel sind die erwarteten Revisionsnummern 1 für Artikel 1 und 2 für die Artikel 2 und 3. Diese Werte ergeben sich aus der Zugehörigkeit zu den Stapeln, unabhängig davon, welcher Stapel zuerst abgeschlossen wird. Bei weiteren Skriptausführungen werden die vorhandenen Werte erneut erhöht. Fehlt eine angeforderte ID, wird der Stapel abgebrochen, statt stillschweigend nur die gefundenen Zeilen zu aktualisieren.

Jeder Stapel ist eine separate Transaktion. Wenn eine Transaktion erfolgreich ist und die andere dauerhaft fehlschlägt, bleibt der erfolgreiche Stapel bestätigt. Promise.allSettled() ermöglicht, dass beide Versuche beendet werden, bevor das Programm den Pool schließt. Das abschließende pool.query() wird außerhalb beider Transaktionen ausgeführt. Die Pool-API dokumentiert, wie ausgecheckte Clients freigegeben, bei Bedarf Clients zerstört und der Pool nach Abschluss der Skriptarbeit beendet werden. node-postgres: Pool

Die Normalisierungsprüfungen und die beispielhafte Revisionsberechnung wurden mit Node.js ausgeführt. In der Rechercheumgebung war PostgreSQL nicht verfügbar. Daher wurden SQL und das Verhalten der Datenbank bei gleichzeitigen Vorgängen anhand der Dokumentation geprüft, aber nicht ausgeführt.

Behalten Sie die für Ihre Aufgabe erforderliche Isolationsebene bei

Für Updates mit festen IDs verwendet das Programm ausdrücklich READ COMMITTED. Auf dieser Ebene kann eine Sperranweisung auf einen Aktualisierungsvorgang warten und anschließend die aktualisierte Zeilenversion sperren, sofern sie weiterhin der Bedingung entspricht. Jede Anweisung verwendet einen neuen Snapshot. Eine Zeile, die vor dem Erwerb der Sperre gelöscht wird, kann daher fehlen; das Programm behandelt diesen Fall mit einer Prüfung der Anzahl. PostgreSQL: Transaktionsisolation

Senken Sie die Isolationsstufe einer bestehenden Transaktion nicht nur deshalb, um dieses Beispiel zu verwenden. Bei REPEATABLE READ kann der Versuch, eine Zeile zu sperren oder zu aktualisieren, die seit dem Transaktions-Snapshot geändert wurde, einen Serialisierungsfehler verursachen. Auch SERIALIZABLE erfordert eine Behandlung von Wiederholungen und verhindert bestätigte Serialisierungsanomalien; es ersetzt nicht die einheitliche Reihenfolge gewöhnlicher Zeilensperren. PostgreSQL: Transaktionsisolation

Das Sperren bekannter Zeilen stellt nicht sicher, dass sich die Zugehörigkeit zu einer Prädikatmenge nicht ändert. Dieses Beispiel implementiert etwa nicht „Aktualisiere jeden Artikel, dessen Titel einer Bedingung entspricht, einschließlich gleichzeitig hinzugefügter Artikel“. Das ist eine andere Konsistenzaufgabe. Legen Sie die Anforderungen an Isolation und Koordination fest, bevor Sie das Rezept für feste IDs dafür anpassen.

Halten Sie außerdem die Ordnungsspalte unveränderlich. PostgreSQL dokumentiert, dass bei READ COMMITTED die Sortierung vor dem Sperren erfolgt; Werte, die sich während des Wartens ändern, können dazu führen, dass zurückgegebene Zeilen nicht geordnet erscheinen. Unveränderte IDs vermeiden diese besondere Komplikation. PostgreSQL: SELECT

Wiederholen Sie vollständige Transaktionen und untersuchen Sie wiederholte Fehler

PostgreSQL weist Deadlock-Fehlern den SQLSTATE 40P01 und Serialisierungsfehlern 40001 zu. Wiederholen Sie die vollständige Transaktion einschließlich datenbankabhängiger Entscheidungen, die ihre Anweisungen oder Werte bestimmen. Nur das abschließende Update erneut auszuführen, erfüllt diese Anforderung nicht. PostgreSQL: Behandlung von Serialisierungsfehlern

Das Beispiel erlaubt insgesamt drei Versuche. Die kurze exponentielle Verzögerung mit Zufallsanteil ist eine illustrative Entscheidung auf Anwendungsebene und kein PostgreSQL-Standard. Der Client wird vor der Verzögerung freigegeben; bei anderen Fehlern wird sofort abgebrochen und nach Ausschöpfung der Versuche eine Meldung ausgegeben. PostgreSQL weist darauf hin, dass Wiederholungen keinen erfolgreichen Abschluss garantieren. PostgreSQL: Behandlung von Serialisierungsfehlern

Nutzen Sie bei der Anpassung der Implementierung die folgende Entscheidungshilfe:

Situation Vorgehen
Ein anderer Stapel hält bereits eine angeforderte Zeile Lassen Sie den geordneten Sperrerwerb warten, wenn die Aufgabe jede angeforderte Zeile erfordert.
Eine angeforderte ID fehlt Rollen Sie zurück und melden Sie die fehlende Zielzeile.
SQLSTATE ist 40P01 oder 40001 Rollen Sie zurück und wiederholen Sie anschließend die gesamte geeignete Transaktion innerhalb einer begrenzten Anzahl von Versuchen.
Fehler treten trotz geordneter Sperrung weiter auf Prüfen Sie frühere Anweisungen, andere schreibende Vorgänge, Trigger und zusätzliche Tabellen auf Abweichungen vom Ordnungsvertrag.
Der Vorgang betrifft mehrere Tabellen Legen Sie für alle konkurrierenden Pfade sowohl eine gemeinsame Tabellenreihenfolge als auch eine Zeilenreihenfolge fest.
Ein Stapel muss atomar bestätigt werden Behalten Sie eine Transaktion bei; unabhängig bestätigte Teilstapel würden die Aufgabe verändern.

Fügen Sie für diesen Stapel, der alle Zielzeilen betreffen soll, kein SKIP LOCKED hinzu: Es lässt Zeilen aus, die nicht sofort gesperrt werden können. PostgreSQL beschreibt diese Option als nützlich für Verbraucher, die Warteschlangen verarbeiten, aber als ungeeignet für eine allgemeine konsistente Ansicht. NOWAIT meldet einen Fehler, statt auf eine in Konflikt stehende Zeilensperre zu warten; dadurch ändert sich das Warteverhalten, aber der Stapel wird nicht vollständig ausgeführt. PostgreSQL: SELECT

Die Implementierung kann angepasst werden, sobald Sie die vollständige Zielmenge benennen, ihre Sortierschlüssel unverändert lassen und für konkurrierende Transaktionen dieselbe Sperrregel anwenden können. Behalten Sie die Prüfung auf fehlende Zeilen und die begrenzten Wiederholungen der vollständigen Transaktion bei, damit Sperrkonflikte oder unvollständige Eingaben zu einem eindeutigen Ergebnis führen.

Quellen