Verhindern Sie doppelte Einfügungen bei nebenläufigen Prüfungen mit einem Unique Constraint auf dem logischen Schlüssel des Artikels und INSERT ... ON CONFLICT. Wenn Sie außerdem die vorhandene Zeile benötigen, verwenden Sie eine Abrufstrategie, die zur Transaktionsisolationsstufe passt.

Dieses Tutorial richtet sich an Entwickler, die im macOS-Terminal arbeiten und eine Node.js-Funktion benötigen, die einen synthetischen Artikel einmalig erstellt und seine Zeile an nebenläufige Aufrufer zurückgibt. Die Beispiele behalten den Titel des zuerst erstellten Artikels bei. Sie verwenden die aktuelle PostgreSQL-Dokumentation und die offizielle node-postgres-Dokumentation.

Warum eine Transaktion allein Duplikate nicht verhindert

Stellen Sie sich zwei Aufrufe vor, die versuchen, einen Artikel mit dem Slug paper-shapes zu erstellen. Jeder prüft zunächst, ob dieser Slug existiert, und fügt den Artikel ein, wenn die Abfrage nichts zurückgibt. Bei der standardmäßigen Isolationsstufe Read Committed von PostgreSQL sieht eine normale Abfrage die festgeschriebenen Daten zum Zeitpunkt ihres Statement-Snapshots. Daher können beide Prüfungen melden, dass der Artikel nicht vorhanden ist. PostgreSQL: Transaktionsisolierung

Dieser beispielhafte Ablauf zeigt die Race Condition auf Anwendungsebene:

Schritt Aufruf A Aufruf B
1 Prüft paper-shapes: nicht vorhanden
2 Prüft paper-shapes: nicht vorhanden
3 Fügt den Artikel ein
4 Versucht, denselben Slug einzufügen
5 Schreibt die Transaktion fest Versucht, die Transaktion festzuschreiben

Ohne einen Unique Constraint für den Slug können diese Einfügungen separate Zeilen erzeugen. Ein Primärschlüssel für eine automatisch generierte ID bildet die Regel, dass Slugs eindeutig sein müssen, nicht ab. PostgreSQL unterstützt Unique Constraints für einzelne Spalten ebenso wie für eine Kombination von Spalten. PostgreSQL: Constraints

Wenn Sie die Prüfung und das Einfügen in BEGIN und COMMIT einschließen, wird der nicht vorhandene Slug dadurch nicht reserviert. Auch FOR UPDATE bei einer leeren Abfrage löst dieses Problem nicht: Es sperrt abgerufene Zeilen, sodass es bei einem nicht vorhandenen Artikel keine Zeile zum Sperren gibt. Das folgt aus dem dokumentierten Verhalten von Zeilensperren in PostgreSQL. PostgreSQL: Explizite Sperren

Legen Sie genau fest, was als Duplikat gilt

In diesem Beispiel identifiziert ein exakter Slug einen Artikel. Der Titel ist Inhalt. Daher sollte eine zweite Anfrage mit demselben Slug den gespeicherten Artikel erhalten, statt dessen Titel zu ersetzen.

Vorausgesetzt werden eine vorhandene lokale PostgreSQL-Installation mit den Befehlszeilenprogrammen sowie Node.js mit npm. Erstellen Sie im macOS-Terminal eine neue Demonstrationsdatenbank und öffnen Sie deren SQL-Eingabeaufforderung:

createdb article_concurrency_demo
psql -d article_concurrency_demo

Führen Sie dieses Schema einmal in der neuen Datenbank aus:

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

Der separate Constraint UNIQUE (slug) setzt die Artikelregel durch. Auch NOT NULL ist wichtig: Gewöhnliche Unique Constraints erlauben standardmäßig mehrere Nullwerte. PostgreSQL erstellt für diesen Constraint automatisch einen Unique Index. PostgreSQL: Constraints

Wenn Sie eine bestehende Tabelle anpassen, prüfen Sie zunächst, ob Schlüssel mehrfach vorkommen, statt Zeilen automatisch zu löschen. Diese Diagnoseabfrage verwendet das obige Schema und lässt alle Inhalte unverändert:

SELECT slug, count(*) AS row_count
FROM articles
GROUP BY slug
HAVING count(*) > 1;

Entscheiden Sie, welche gespeicherten Inhalte beibehalten werden sollen, bevor Sie den Constraint zu einer bestehenden Tabelle hinzufügen. Legen Sie außerdem den gewünschten Schlüssel fest: Wenn zwei Artikel in verschiedenen Sammlungen denselben Slug haben dürfen, wäre ein zusammengesetzter Schlüssel wie Sammlung plus Slug erforderlich. Richten Sie Abfrage und Konfliktziel an derselben Regel aus.

Wählen Sie das gewünschte Verhalten bei Konflikten

Ein Konflikt ist bei dieser Aufgabe ein normaler Fall. Legen Sie seine Bedeutung fest, bevor Sie das SQL auswählen. PostgreSQL unterstützt sowohl das Überspringen des kollidierenden Einfügevorgangs als auch das Aktualisieren der vorhandenen Zeile; RETURNING gibt Zeilen zurück, die tatsächlich eingefügt oder aktualisiert wurden. PostgreSQL: INSERT

Gewünschtes Ergebnis Vorgehensweise Zurückgegebenes Ergebnis
Erstellen, falls nicht vorhanden; andernfalls überspringen ON CONFLICT (slug) DO NOTHING Mit RETURNING gibt ein übersprungener Einfügevorgang keine Zeile zurück
Erstellen, falls nicht vorhanden; andernfalls den Titel ersetzen ON CONFLICT (slug) DO UPDATE Mit RETURNING die eingefügte oder aktualisierte Zeile
Erstellen, falls nicht vorhanden; andernfalls beibehalten und abrufen DO NOTHING, anschließend separate Abfrage Im nächsten Abschnitt umgesetzt

Für einen reinen Einfügevorgang funktioniert diese vollständige SQL-Anweisung mit dem Demonstrationsschema:

INSERT INTO articles (slug, title)
VALUES ('paper-shapes', 'Paper Shapes')
ON CONFLICT (slug) DO NOTHING
RETURNING id, slug, title;

Wenn der Titel ersetzt werden soll, verwenden Sie stattdessen diese Anweisung:

INSERT INTO articles (slug, title)
VALUES ('paper-shapes', 'Paper Shapes — Revised')
ON CONFLICT (slug) DO UPDATE
SET title = EXCLUDED.title
RETURNING id, slug, title;

Hier verweist EXCLUDED.title auf den vorgeschlagenen Titel. Ein bedingungsloses DO UPDATE bietet das dokumentierte atomare Ergebnis aus Einfügen oder Aktualisieren, vorbehaltlich unabhängiger Fehler und der Fehlerbehandlung für die Isolationsstufe. Eine bedingte Aktualisierung, bei der die Bedingung WHERE das Update ablehnt, gibt möglicherweise nichts zurück. Wählen Sie eine Aktualisierung, wenn es angemessen ist, den gespeicherten Artikel zu ändern. PostgreSQL: INSERT

„Erstellen oder vorhandenen Artikel zurückgeben“ mit Read Committed umsetzen

Es gibt eine Sichtbarkeitsfalle: DO NOTHING kann das Einfügen wegen einer nebenläufigen Zeile überspringen, die im Snapshot des Statements nicht sichtbar ist. Daher kann auch eine Fallback-Abfrage innerhalb desselben Statements diese Zeile übersehen. Eine separate Read-Committed-Abfrage erhält einen neuen Snapshot. Darauf basiert die folgende Implementierung. PostgreSQL: Transaktionsisolierung

Beenden Sie die SQL-Eingabeaufforderung mit \q. Installieren Sie in einem neuen Arbeitsordner die Node.js-Abhängigkeit:

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

Speichern Sie das folgende vollständige Programm als demo.mjs. Es setzt voraus, dass die oben erstellte Datenbank und Tabelle vorhanden sind. Der Pool verwendet den angegebenen Datenbanknamen sowie die übrigen standardmäßigen Verbindungseinstellungen; node-postgres unterstützt PostgreSQL-Verbindungsumgebungsvariablen für abweichende lokale Einstellungen. node-postgres: Verbindung herstellen

import pg from 'pg';
import assert from 'node:assert/strict';

const { Pool } = pg;
const pool = new Pool({
  database: 'article_concurrency_demo',
  max: 8
});

async function getOrCreateArticle(slug, title) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

    for (let attempt = 0; attempt < 5; attempt += 1) {
      const inserted = await client.query(`
        INSERT INTO articles (slug, title)
        VALUES ($1, $2)
        ON CONFLICT (slug) DO NOTHING
        RETURNING id, slug, title
      `, [slug, title]);

      if (inserted.rows.length === 1) {
        await client.query('COMMIT');
        return inserted.rows[0];
      }

      const existing = await client.query(`
        SELECT id, slug, title
        FROM articles
        WHERE slug = $1
        FOR UPDATE
      `, [slug]);

      if (existing.rows.length === 1) {
        await client.query('COMMIT');
        return existing.rows[0];
      }
    }

    throw new Error('Article kept disappearing; retry limit reached');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

try {
  const results = await Promise.allSettled(
    Array.from({ length: 8 }, () =>
      getOrCreateArticle('paper-shapes', 'Paper Shapes'))
  );

  const failures = results.filter(result => result.status === 'rejected');
  if (failures.length) {
    throw new AggregateError(failures.map(result => result.reason));
  }

  const rows = results.map(result => result.value);
  assert.equal(new Set(rows.map(row => row.id)).size, 1);

  const count = await pool.query(
    'SELECT count(*)::integer AS n FROM articles WHERE slug = $1',
    ['paper-shapes']
  );
  assert.equal(count.rows[0].n, 1);
  console.log({ calls: rows.length, matchingRows: count.rows[0].n });
} finally {
  await pool.end();
}

Führen Sie das Programm in diesem Ordner aus:

node demo.mjs

Bei der beschriebenen Einrichtung und sofern kein anderer Prozess die Demonstrationszeilen verändert, lautet die erwartete Ausgabe:

{ calls: 8, matchingRows: 1 }

Die JavaScript-Syntax des Programms wurde mit Node.js geprüft. Die Datenbankdemonstration wurde hier nicht ausgeführt, da PostgreSQL-Werkzeuge nicht verfügbar waren; die obige Ausgabe ist ein erwartetes Ergebnis.

Jede Transaktion verwendet für alle Statements denselben ausgecheckten Client. node-postgres verlangt dies ausdrücklich: Werden Transaktionsstatements über pool.query ausgeführt, können sie an verschiedene Clients verteilt werden. Die Verifizierungsabfrage verwendet pool.query erst, nachdem die Transaktionen abgeschlossen sind. node-postgres: Transaktionen

Abfragetext und Werte werden gemäß der parametrisierten Abfrageschnittstelle des Treibers separat mit $1 und $2 übergeben. node-postgres: Abfragen

Die Schleife berücksichtigt eine weitere beispielhafte Interleaving-Situation: Eine andere Transaktion könnte den kollidierenden Artikel löschen, bevor die Abfrage ihn abruft. Bei einer erfolglosen Abfrage wird der Einfügeversuch wiederholt; fünf Versuche dienen als konkrete Obergrenze. Wird diese erreicht, löst das Programm einen Fehler aus, statt eine undefinierte Zeile zurückzugeben.

Nach dem Abruf verhindert FOR UPDATE bis zum Ende dieser Transaktion, dass eine andere Transaktion die Zeile ändert oder löscht. Es schützt ein vorhandenes Ergebnis; der Unique Constraint behandelt konkurrierende Einfügungen. Nach dem Commit kann eine andere Transaktion die Zeile erneut ändern. Wenn abhängige Datenbankarbeit den gesperrten Artikel verwenden muss, führen Sie diese Arbeit vor COMMIT aus. PostgreSQL: Explizite Sperren

Grenzen der Isolationsstufen beachten

Das Beispiel wählt ausdrücklich Read Committed. Repeatable Read verwendet einen Transaktionssnapshot, der mit dem ersten inhaltlichen Statement festgelegt wird. Eine weitere Abfrage kann ihn daher nicht einfach aktualisieren. Serializable gewährleistet serialisierbare festgeschriebene Ergebnisse, kann Transaktionen jedoch abbrechen und in dokumentierten Fällen mit überlappenden Vorgängen weiterhin Unique-Verletzungen erzeugen. MERGE bietet außerdem nicht dieselbe Garantie für nebenläufiges Einfügen oder Aktualisieren. PostgreSQL: Transaktionsisolierung

Stellen Sie sich bei Repeatable Read oder Serializable darauf ein, die gesamte Transaktion nach SQLSTATE 40001 zu wiederholen, einschließlich der Anwendungsentscheidungen, aus denen ihre Statements hervorgegangen sind. Nur die letzte Abfrage zu wiederholen, reicht nicht aus. PostgreSQL nennt außerdem 40P01 als potenziell wiederholbaren Deadlock-Fehler. PostgreSQL: Behandlung von Serialisierungsfehlern

Behandeln Sie nicht automatisch jede Unique-Verletzung 23505 als vorübergehend. Sie kann einen dauerhaften Konflikt darstellen. Eine Wiederholungsrichtlinie sollte den erwarteten Fall erkennen, die Anzahl der Versuche begrenzen und das Erreichen der Obergrenze melden; PostgreSQL garantiert nicht, dass Wiederholungen irgendwann erfolgreich sind. Die Demonstration behandelt die erwartete Slug-Kollision mit ON CONFLICT; andere Fehler werden weitergegeben. PostgreSQL: Behandlung von Serialisierungsfehlern

Für diese Aufgabe „Erstellen oder vorhandenen Artikel zurückgeben“ ist die Aufgabenteilung einfach: Der Datenbank-Constraint legt die Artikelidentität fest, die Konfliktaktion bestimmt den Umgang mit einem vorhandenen Artikel, und die Transaktion steuert den Abruf sowie davon abhängige Arbeit. Stimmen Sie diese Entscheidungen aufeinander ab, können nebenläufige Aufrufer denselben Artikel erhalten, ohne seinen Inhalt unbemerkt zu ersetzen.

Quellen