Voorkom dubbele invoegingen door gelijktijdige controles en invoegingen met een unieke beperking op de logische sleutel van het artikel en INSERT ... ON CONFLICT. Wanneer je ook de bestaande rij nodig hebt, gebruik dan een ophaalstrategie die past bij het isolatieniveau van je transactie.

Deze tutorial is bedoeld voor een ontwikkelaar die in macOS Terminal werkt en een Node.js-functie nodig heeft die één keer een synthetisch artikel aanmaakt en de rij ervan teruggeeft aan gelijktijdige aanroepers. De voorbeelden behouden de titel van het eerste artikel. Ze gebruiken de actuele documentatie van PostgreSQL en de officiële documentatie van node-postgres.

Waarom een transactie alleen dubbele records niet voorkomt

Stel dat twee aanroepen een artikel proberen aan te maken met de slug paper-shapes. Elk controleert eerst of die slug bestaat en voegt het artikel toe als de query niets oplevert. Op het standaardisolatieniveau Read Committed van PostgreSQL ziet een gewone query de gecommitte gegevens op basis van de snapshot van die instructie. Beide controles kunnen dus vaststellen dat het artikel ontbreekt. PostgreSQL: Transaction Isolation

Dit illustratieve schema toont de raceconditie op applicatieniveau:

Stap Aanroep A Aanroep B
1 Controleert paper-shapes: ontbreekt
2 Controleert paper-shapes: ontbreekt
3 Voegt het artikel in
4 Probeert dezelfde slug in te voegen
5 Commit Probeert te committen

Zonder een unieke beperking op de slug kunnen deze invoegingen afzonderlijke rijen aanmaken. Een primaire sleutel op een automatisch gegenereerde ID drukt de regel niet uit dat slugs uniek moeten zijn. PostgreSQL ondersteunt unieke beperkingen op één kolom of op een combinatie van kolommen. PostgreSQL: Constraints

De controle en invoeging in BEGIN en COMMIT plaatsen, reserveert de ontbrekende slug niet. FOR UPDATE toevoegen aan een lege opzoeking lost dat probleem ook niet op: het vergrendelt opgehaalde rijen, dus een ontbrekend artikel levert geen rij op om te vergrendelen. Dit volgt uit het gedocumenteerde gedrag van rijvergrendelingen in PostgreSQL. PostgreSQL: Explicit Locking

Bepaal precies wat als een duplicaat geldt

In dit voorbeeld identificeert één exacte slug één artikel. De titel is inhoud, dus een tweede verzoek met dezelfde slug moet het opgeslagen artikel ontvangen in plaats van de titel te vervangen.

Begin met een bestaande lokale PostgreSQL-installatie, de bijbehorende opdrachtregelprogramma’s en Node.js met npm. Maak in macOS Terminal een nieuwe demonstratiedatabase en open de SQL-prompt:

createdb article_concurrency_demo
psql -d article_concurrency_demo

Voer dit schema één keer uit in de nieuwe database:

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

De afzonderlijke beperking UNIQUE (slug) dwingt de artikelregel af. NOT NULL is ook van belang: gewone unieke beperkingen staan standaard meerdere null-waarden toe. PostgreSQL maakt automatisch een unieke index voor deze beperking. PostgreSQL: Constraints

Als je een bestaande tabel aanpast, controleer dan eerst op herhaalde sleutels in plaats van rijen automatisch te verwijderen. Deze diagnostische query gebruikt het bovenstaande schema en laat alle inhoud intact:

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

Bepaal welke opgeslagen inhoud behouden moet blijven voordat je de beperking toevoegt aan een bestaande tabel. Stel ook de bedoelde sleutel vast: als twee artikelen in verschillende collecties dezelfde slug mogen hebben, is een samengestelde sleutel nodig, bijvoorbeeld collectie plus slug. Zorg dat de opzoeking en het conflictdoel bij diezelfde regel passen.

Kies het gewenste gedrag bij een conflict

Een conflict is een normaal onderdeel van deze taak. Bepaal wat het moet betekenen voordat je de SQL kiest. PostgreSQL kan de conflicterende invoeging overslaan of de bestaande rij bijwerken; RETURNING geeft rijen terug die daadwerkelijk zijn ingevoegd of bijgewerkt. PostgreSQL: INSERT

Gewenst resultaat Aanpak Geretourneerd resultaat
Aanmaken als het ontbreekt; anders overslaan ON CONFLICT (slug) DO NOTHING Met RETURNING levert een overgeslagen invoeging geen rij op
Aanmaken als het ontbreekt; anders de titel vervangen ON CONFLICT (slug) DO UPDATE Met RETURNING de ingevoegde of bijgewerkte rij
Aanmaken als het ontbreekt; anders behouden en ophalen DO NOTHING, gevolgd door een afzonderlijke opzoeking Uitgewerkt in de volgende sectie

Voor een invoegbewerking zonder bijwerken werkt deze volledige SQL-instructie met het demoschema:

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

Als de titel vervangen moet worden, gebruik dan deze alternatieve instructie:

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 verwijst EXCLUDED.title naar de voorgestelde titel. Een onvoorwaardelijke DO UPDATE biedt de gedocumenteerde atomaire invoeg- of bijwerkbewerking, behoudens onafhankelijke fouten en foutafhandeling op basis van het isolatieniveau. Een voorwaardelijke update waarvan de voorwaarde WHERE de update afwijst, kan niets opleveren. Kies voor bijwerken als het passend is om het opgeslagen artikel te wijzigen. PostgreSQL: INSERT

Implementeer aanmaken-of-bestaand-teruggeven op Read Committed

Er is een valkuil rond zichtbaarheid: DO NOTHING kan de invoeging overslaan vanwege een gelijktijdige rij die onzichtbaar is voor de snapshot van de instructie. Daardoor kan een terugvalopzoeking in dezelfde instructie die rij ook missen. Een afzonderlijke Read Committed-query krijgt een nieuwe snapshot. Dat vormt de basis voor de implementatie hieronder. PostgreSQL: Transaction Isolation

Verlaat de SQL-prompt met \q. Installeer de Node.js-afhankelijkheid in een nieuwe werkmap:

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

Sla het volgende volledige programma op als demo.mjs. Het gaat uit van de hierboven aangemaakte database en tabel. De pool gebruikt de opgegeven databasenaam en de overige standaardverbindingsinstellingen; node-postgres ondersteunt omgevingsvariabelen voor PostgreSQL-verbindingen wanneer lokale instellingen afwijken. node-postgres: Connecting

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

Voer het vanuit die map uit:

node demo.mjs

Met de beschreven configuratie en zonder andere processen die de demonstratierijen wijzigen, is de verwachte uiteindelijke uitvoer:

{ calls: 8, matchingRows: 1 }

De JavaScript-syntaxis van het programma is gecontroleerd met Node.js. De database-demonstratie is hier niet uitgevoerd omdat PostgreSQL-gereedschap niet beschikbaar was; de bovenstaande uitvoer is een verwacht resultaat.

Elke transactie gebruikt voor alle instructies één ingecheckte client. node-postgres vereist dit expliciet: transacties via pool.query uitvoeren kan ertoe leiden dat ze naar verschillende clients worden gestuurd. De verificatiequery gebruikt pool.query pas nadat de transacties zijn voltooid. node-postgres: Transactions

De querytekst en waarden worden afzonderlijk doorgegeven met $1 en $2, volgens de geparametriseerde-query-interface van de driver. node-postgres: Queries

De lus behandelt een extra illustratieve interleaving: een andere transactie kan het conflicterende artikel verwijderen voordat de opzoeking het ophaalt. Als de opzoeking niets vindt, wordt de invoegpoging herhaald, met vijf pogingen als expliciete limiet in dit voorbeeld. Als die limiet wordt bereikt, volgt een fout in plaats van een ongedefinieerde rij.

Na het ophalen voorkomt FOR UPDATE dat een andere transactie de rij wijzigt of verwijdert totdat deze transactie eindigt. Het beschermt een bestaand resultaat; de unieke beperking handelt concurrerende invoegingen af. Na de commit kan een andere transactie de rij opnieuw wijzigen. Als afhankelijk databasewerk het vergrendelde artikel moet gebruiken, voer dat werk dan uit vóór COMMIT. PostgreSQL: Explicit Locking

Houd rekening met de grenzen van het isolatieniveau

Het voorbeeld kiest expliciet Read Committed. Repeatable Read gebruikt een transactiesnapshot die bij de eerste inhoudelijke instructie wordt ingesteld, dus een andere query kan die niet zomaar vernieuwen. Serializable zorgt voor gecommitte resultaten die gelijkwaardig zijn aan een seriële uitvoering, maar kan transacties afbreken en kan in gedocumenteerde overlappende gevallen nog steeds unieke-schendingen opleveren. MERGE biedt ook niet dezelfde garantie voor gelijktijdig invoegen of bijwerken. PostgreSQL: Transaction Isolation

Bereid je bij Repeatable Read of Serializable voor om de volledige transactie opnieuw te proberen na SQLSTATE 40001, inclusief de toepassingsbeslissingen die tot de instructies hebben geleid. Alleen de laatste query opnieuw proberen is onvoldoende. PostgreSQL noemt 40P01 ook als een mogelijk opnieuw te proberen deadlockfout. PostgreSQL: Serialization Failure Handling

Behandel niet elke unieke-schending 23505 automatisch als tijdelijk. Die kan een blijvend conflict vertegenwoordigen. Een beleid voor opnieuw proberen moet de verwachte situatie herkennen, het aantal pogingen begrenzen en het bereiken van de limiet melden; PostgreSQL garandeert niet dat nieuwe pogingen uiteindelijk slagen. De demonstratie handelt de verwachte slugbotsing af via ON CONFLICT, terwijl andere fouten worden doorgegeven. PostgreSQL: Serialization Failure Handling

Voor deze taak, waarbij een artikel wordt aangemaakt of teruggegeven, is de taakverdeling eenvoudig: de databasebeperking legt de artikelidentiteit vast, de conflictafhandeling bepaalt wat er met een bestaand artikel gebeurt en de transactie regelt het ophalen en eventueel afhankelijk werk. Houd die keuzes op elkaar afgestemd, zodat gelijktijdige aanroepers één artikel kunnen delen zonder de inhoud ervan stilzwijgend te vervangen.

Bronnen