Użyj FOR UPDATE SKIP LOCKED w zapytaniu, które zmienia wybrany wiersz z ready na processing. Pozwala to współbieżnym pracownikom na roszczenie różnych syntetycznych wierszy artykułów, podczas gdy zapisany stan zapobiega ponownemu roszczeniu po zakończeniu blokady.

Ten przewodnik jest przeznaczony dla dewelopera korzystającego z Terminala na Macu z działającym połączeniem PostgreSQL. Utworzysz trzy syntetyczne artykuły, zweryfikujesz współbieżne roszczenia w dwóch sesjach bazy danych i uruchomisz kompletny przykład node-postgres.

Dlaczego dwóch pracowników może wybrać ten sam artykuł?

Zwykłe SELECT nie rezerwuje swojego wyniku. W domyślnej izolacji Read Committed PostgreSQL, dwóch pracowników może odczytać ten sam zatwierdzony wiersz ready zanim którykolwiek z nich go zmieni. Owinięcie tego zwykłego odczytu i późniejszej aktualizacji w transakcji samo w sobie nie czyni wyboru wyłącznym. PostgreSQL dokumentuje zachowanie migawek w Transaction Isolation.

Roszczenie wymaga dwóch zabezpieczeń: blokady wiersza podczas wyboru artykułu oraz zmiany stanu przed zwolnieniem tej blokady. PostgreSQL zwalnia blokady wierszy po zakończeniu transakcji. W konsekwencji, samodzielny odczyt z blokadą następujący po przetwarzaniu sprawiłby, że wiersz stałby się ponownie uprawniony, gdy transakcja odczytu się zakończy. Zobacz Explicit Locking.

Używaj tego samego protokołu roszczenia w każdym pracowniku. Ścieżka aplikacji, która odczytuje artykuł ready i rozpoczyna przetwarzanie bez pomyślnego złożenia roszczenia, nadal może zduplikować pracę.

Utwórz małą syntetyczną kolejkę artykułów

Wymagania wstępne to uruchomiona baza danych PostgreSQL, polecenie psql dostępne w Terminalu oraz istniejąca konfiguracja połączenia. Dla sekcji JavaScript potrzebne jest również obecnie wspierane wydanie Node.js i zainstalowany npm. Artykuł zakłada działające połączenie z bazą danych; instalacja zależy od Twojej obecnej konfiguracji Maca.

Wymagana funkcja SKIP LOCKED pojawiła się w PostgreSQL 9.5. Jest to minimum składniowe, a nie rekomendacja uruchamiania niewspieranego wydania. Używaj wspieranej wersji PostgreSQL; pobrana aktualna dokumentacja wskazuje PostgreSQL 18 jako aktualne. Zobacz informacje o wydaniu 9.5 i aktualną dokumentację SELECT.

Otwórz Terminal i uruchom psql używając swoich zwyczajowych ustawień połączenia. Zapewnia on interaktywny monit do wykonywania SQL i przeglądania wyników, jak opisano w dokumentacji psql. Uruchom poniższą konfigurację raz w bazie testowej, gdzie article_claim_demo jeszcze nie istnieje. Celowo nie zastępuje ona istniejącej tabeli.

SHOW server_version;
SHOW transaction_isolation;

CREATE TABLE article_claim_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    state text NOT NULL DEFAULT 'ready'
        CHECK (state IN ('ready', 'processing', 'done')),
    rendered_title text
);

INSERT INTO article_claim_demo (id, title) VALUES
    (1, 'Paper shapes'),
    (2, 'Desk arrangements'),
    (3, 'Color combinations');

Przykłady zakładają izolację Read Committed i tę prostą tabelę bez dodatkowych wyzwalaczy. Zachowaj trzy przykładowe wiersze bez zmian, aż zakończysz test dwóch sesji.

Roszcz jeden artykuł pojedynczym zapytaniem SQL

Uruchamiaj to zapytanie zawsze, gdy pracownik potrzebuje jednego artykułu. Zapytanie kandydujące blokuje dostępny wiersz; aktualizacja rejestruje jego roszczenie i zwraca pola wejściowe. Dokumentacja UPDATE PostgreSQL opisuje ten wzorzec CTE-i-aktualizację i potwierdza, że RETURNING raportuje faktycznie zaktualizowane wiersze.

WITH candidate AS (
    SELECT id
    FROM article_claim_demo
    WHERE state = 'ready'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE article_claim_demo AS a
SET state = 'processing'
FROM candidate AS c
WHERE a.id = c.id
  AND a.state = 'ready'
RETURNING a.id, a.title;

Blokada należy do wewnątrz CTE kandydata. SKIP LOCKED pozwala innemu pracownikowi pominąć kandydata, którego nie może natychmiast zablokować. LIMIT 1 ogranicza każde roszczenie, a złączenie klucza głównego identyfikuje jeden cel aktualizacji. PostgreSQL jawnie dokumentuje blokadę wewnątrz zapytania WITH i użycie pominiętych wierszy w stylu kolejki w SELECT: The Locking Clause.

Bez jawnego BEGIN, PostgreSQL wykonuje zapytanie w swojej własnej transakcji. Pomyślne zapytanie zatwierdza zarówno aktualizację skojarzoną z wyborem, jak i zmianę stanu przed rozpoczęciem późniejszego przetwarzania. Po zatwierdzeniu blokada wiersza znika, ale state = 'ready' wyklucza ten artykuł. Zobacz Transactions.

Przetwarzaj tylko artykuł zwrócony przez zapytanie roszczenia. Pusty wynik oznacza, że ta próba nie uzyskała żadnego artykułu; nie ustala to, że cała praca jest zakończona. Inni pracownicy mogą w tej chwili trzymać uprawnione wiersze. Ponadto, SKIP LOCKED omija oczekiwanie na blokady wierszy, podczas gdy blokady na poziomie tabeli nadal mogą powodować czekanie. Te granice są udokumentowane w SELECT.

Zweryfikuj zachowanie roszczenia w dwóch sesjach Terminala

Użyj dwóch okien Terminala połączonych przez psql z tą samą bazą danych. Zacznij od oryginalnych trzech wierszy ready. Poniższa kontrolowana sekwencja utrzymuje każdą transakcję otwartą, abyś mógł bezpośrednio obserwować nakładanie się.

  1. W sesji A wykonaj BEGIN ISOLATION LEVEL READ COMMITTED;, a następnie wklej kompletne zapytanie roszczenia powyżej. Pozostaw transakcję otwartą. Z nienaruszonymi przykładowymi danymi, zwrócony ID powinien wynosić 1.
  2. W sesji B wykonaj BEGIN ISOLATION LEVEL READ COMMITTED;, a następnie wklej to samo zapytanie roszczenia. Powinno zwrócić ID 2, podczas gdy A nadal trzyma ID 1.
  3. Wykonaj ROLLBACK; w sesji B, a następnie ROLLBACK; w sesji A.
  4. W dowolnej sesji uruchom poniższe zapytanie inspekcyjne. Oba roszczenia zostały cofnięte, więc wszystkie trzy artykuły powinny znowu być ready.
SELECT id, title, state, rendered_title
FROM article_claim_demo
ORDER BY id;

To oczekiwane wyniki wywodzące się z udokumentowanego zachowania blokowania i cofania, a nie z zgłoszonego testu bazy danych. Cofanie anuluje każdą aktualizację stanu i zwalnia jej blokady. Jeśli zamiast tego użyjesz COMMIT, te artykuły pozostaną processing i nie zostaną wybrane przez późniejsze roszczenia. Zobacz Explicit Locking i Transactions.

Ten test rozróżnia dwie użyteczne obserwacje: sesja B przechodzi mimo aktualnie zablokowanego artykułu, a zatwierdzona zmiana stanu utrzymuje artykuł poza przyszłymi roszczeniami. Przetestuj obie właściwości przy dostosowywaniu istniejącego pracownika.

Uruchom trzech pracowników z node-postgres

Po cofnięciu obu testowych transakcji, utwórz nowy lokalny katalog projektu w Terminalu. Zainstaluj pakiet za pomocą udokumentowanego polecenia instalacji node-postgres:

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

Zapisz poniższy kompletny program jako workers.mjs. Używa on tego samego środowiska połączenia z bazą danych co psql; node-postgres dokumentuje swoje wspólne zmienne połączenia w Connecting. Każdy pracownik próbuje jedno roszczenie, zamienia syntetyczny tytuł na wielkie litery i zapisuje wynik.

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool({ max: 3 });

const claimSQL = `
WITH candidate AS (
  SELECT id
  FROM article_claim_demo
  WHERE state = 'ready'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE article_claim_demo AS a
SET state = 'processing'
FROM candidate AS c
WHERE a.id = c.id AND a.state = 'ready'
RETURNING a.id, a.title;
`;

async function worker(label) {
  const claimed = await pool.query(claimSQL);

  if (claimed.rows.length === 0) {
    console.log(`${label}: no row available now`);
    return;
  }

  const article = claimed.rows[0];
  const renderedTitle = article.title.toUpperCase();

  const completed = await pool.query(
    `UPDATE article_claim_demo
     SET rendered_title = $2, state = 'done'
     WHERE id = $1 AND state = 'processing'
     RETURNING id`,
    [article.id, renderedTitle]
  );

  if (completed.rowCount !== 1) {
    throw new Error(`${label}: completion did not update one row`);
  }

  console.log(`${label}: completed article ${article.id}`);
}

try {
  const results = await Promise.allSettled([
    worker('A'),
    worker('B'),
    worker('C')
  ]);

  for (const result of results) {
    if (result.status === 'rejected') {
      console.error(result.reason);
      process.exitCode = 1;
    }
  }
} finally {
  await pool.end();
}

Uruchom node workers.mjs, a następnie ponownie uruchom zapytanie inspekcyjne w psql. Z trzema nienaruszonymi gotowymi artykułami i bez innej aktywności, oczekuj, że wszystkie trzy stany staną się done, z wartościami wielkimi literami w rendered_title. Etykiety pracowników i kolejność konsoli nie muszą odpowiadać kolejności artykułów.

Użycie pool.query jest odpowiednie tutaj, ponieważ każde roszczenie to jedno kompletne zapytanie, a każde ukończenie to osobne zapytanie. Pula zarządza wypożyczaniem i zwrotem połączeń; pool.end() zamyka pulę po ustabilizowaniu prób. Zobacz Pooling.

Zapytanie ukończenia przekazuje swoje wartości oddzielnie używając $1 i $2, zgodnie z Queries. Program sprawdza zwróconą tablicę rows pod kątem pustego roszczenia i sprawdza rowCount pod kątem liczby zaktualizowanych wierszy ukończenia, zgodnie z pg.Result.

JavaScript przeszedł sprawdzanie składniowe podczas przygotowania. Pliki wykonywalne PostgreSQL były niedostępne w środowisku badawczym, więc SQL i współbieżne zachowanie bazy danych zostały sprawdzone pod kątem dokumentacji, ale nie zostały tu wykonane.

Wybierz odpowiednią granicę transakcji i obsługuj niedokończoną pracę

Przykład zatwierdza krótkie roszczenie przed wykonaniem transformacji tytułu. To skraca życie blokady, ale tworzy celową granicę odzyskiwania: jeśli pracownik zatrzyma się po złożeniu roszczenia i przed ukończeniem, artykuł pozostaje processing. Ponowne uruchomienie programu nie zrobi go ponownie.

W tej demonstracji na biurku, zatrzymaj wszystkich pracowników i zbadaj wszelkie artykuły processing przed celowym sprawieniem, by stały się one ponownie uprawnione. Nie resetuj ich automatycznie, dopóki wcześniejszy pracownik może jeszcze skończyć: przykład nie ma pola generowania roszczenia, aby odróżnić wcześniejszą próbę od późniejszej. Samo zapobieganie zduplikowanym roszczeniom nie zapewnia automatycznego odzyskiwania ani przetwarzania dokładnie-raz.

Użyj tego pomocnika decyzyjnego przy dostosowywaniu przykładu:

Twoje zadanie Podejście do transakcji Konsekwencja
Roszcz artykuł, a potem wykonaj dłuższą pracę aplikacyjną Najpierw zatwierdź roszczenie jednoliniowe Zachowaj widoczny stan processing i zaprojektuj odzyskiwanie oddzielnie
Wykonaj małą transformację w całości w PostgreSQL Roszcz i ukończ w ramach jednej transakcji Zmiany bazy danych mogą zostać zatwierdzone lub cofnięte razem
Uruchom kilka zapytań SQL jako jedną transakcję w Node.js Pobierz jednego klienta i używaj go przez cały czas Zachowaj to samo połączenie przez BEGIN, zapytania, i COMMIT lub ROLLBACK

Dla ostatniego podejścia, node-postgres wymaga, aby każde zapytanie w transakcji używało tego samego wypożyczonego klienta. Oddzielne wywołania pool.query mogą używać różnych połączeń, więc nie mogą tworzyć tej transakcji. Zwolnij klienta po zakończeniu lub cofnięciu. Zobacz Transactions i Pooling.

Utrzymuj załozenia izolacji zademonstrowane wprost. Repeatable Read i Serializable mogą generować błędy, gdy wiersz do zablokowania zmienił się od początku transakcji; aplikacje używające tych poziomów potrzebują obsługi ponownych prób transakcji. PostgreSQL opisuje tę granicę w Explicit Locking.

Dla tego zadania syntetycznego artykułu, kluczowa sekwencja jest konkretna: wybierz i zablokuj uprawniony wiersz, zmień jego stan w tym samym zapytaniu, i przetwarzaj tylko zwrócony artykuł po udanym roszczeniu. Zweryfikuj nakładanie się w dwóch sesjach, a następnie zdecyduj, jak Twoja aplikacja poradzi sobie z artykułem, którego pracownik nigdy nie zarejestruje ukończenia.

Źródła