Aby zapobiec zakleszczeniom, gdy równoległe aktualizacje PostgreSQL obejmują te same wiersze, przed aktualizacją zablokuj pełny zbiór wierszy docelowych każdej transakcji, używając tej samej, niezmiennej kolejności kluczy. Obsługuj transakcję na jednym kliencie node-postgres i ponawiaj kwalifikujące się niepowodzenia od początku.

Ten poradnik jest przeznaczony dla programisty pracującego w Terminalu macOS, który potrzebuje, aby dwie równoległe partie w pełni zaktualizowały syntetyczne wiersze artykułów. Przykład korzysta z aktualnej dokumentacji PostgreSQL, wskazującej PostgreSQL 18, oraz aktualnej dokumentacji node-postgres. Zakres obejmuje znaną listę istniejących identyfikatorów artykułów, które pozostają niezmienione podczas równoległych operacji. PostgreSQL: Jawne blokowanie

Dlaczego nakładające się aktualizacje mogą prowadzić do zakleszczeń

Rozważmy dwie transakcje: A aktualizuje artykuł 1, a następnie artykuł 2; B aktualizuje artykuł 2, a następnie artykuł 1. Jeśli każda uzyska blokadę pierwszego wiersza przed zażądaniem drugiej, obie będą czekać na blokadę utrzymywaną przez drugą transakcję. PostgreSQL wykrywa cykl i przerywa jedną transakcję; aplikacja nie może wybrać, którą. Taka sytuacja może wystąpić przy zwykłych aktualizacjach, nawet bez jawnych instrukcji blokowania. PostgreSQL: Jawne blokowanie

Warto odróżnić nakładanie się operacji od cyklu. Nakładanie się może wymagać oczekiwania. Cykl uniemożliwia obu transakcjom dalsze działanie. PostgreSQL zaleca spójną kolejność uzyskiwania blokad we wszystkich aplikacjach oraz uzyskanie najpierw blokady o najsilniejszym wymaganym trybie. PostgreSQL: Jawne blokowanie

W przypadku obciążenia opisanego w tym artykule przyjmij jedną regułę: przed zmianą dowolnego artykułu uzyskaj blokady wszystkich żądanych wierszy artykułów w rosnącej kolejności id. Stosuj tę regułę w całej transakcji, również do wcześniejszych instrukcji, które dotykają artykułów.

Zablokuj pełny zbiór docelowy przed aktualizacją

Posortowanie tablicy JavaScript nie nakazuje zbiorczej instrukcji SQL odwiedzania wierszy w tej kolejności. Składnia PostgreSQL UPDATE nie ma bezpośredniej klauzuli ORDER BY. Dokumentacja pokazuje uporządkowane blokowanie za pomocą zapytania odczytującego i wyjaśnia, że wspólna kolejność może zapobiec zakleszczeniom między operacjami aktualizacji. PostgreSQL: UPDATE

Użyj osobnej, oczekiwanej instrukcji blokującej wewnątrz transakcji. Ta przykładowa transakcja SQL obejmuje artykuły 1, 2 i 3; tabelę przygotowawczą przedstawiono w następnej sekcji.

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;

Pierwsza instrukcja ustala kolejność uzyskiwania blokad. Następnie aktualizacja działa na wierszach, które są już zablokowane przez tę transakcję. Blokady wierszy są zwykle utrzymywane do końca transakcji. Zadbaj o krótki czas trwania tego okresu: przygotuj dane wejściowe przed rozpoczęciem i nie czekaj na dane z pulpitu, gdy blokady są utrzymywane. PostgreSQL: Jawne blokowanie

Poniższe warunki określają zasady kolejności w przykładzie:

  • Każda konkurująca partia używa rosnącej kolejności id artykułów.
  • Identyfikatory pozostają niezmienione podczas równoległych operacji.
  • Każda partia ustala cały zbiór docelowy przed uzyskaniem blokad.
  • Żadna wcześniejsza instrukcja nie zablokowała już wierszy artykułów w innej kolejności.
  • Przykładowa tabela nie ma wyzwalaczy ani operacji na powiązanych tabelach, które wprowadzałyby dodatkowe zależności między blokadami.

Są to warunki konieczne, aby zastosować to rozumowanie do opisanego obciążenia. Transakcja, która najpierw blokuje artykuł 9, a później żąda artykułów od 1 do 3, naruszyła już te zasady, nawet jeśli jej końcowe zapytanie zawiera ORDER BY id.

Uruchom pełny przykład node-postgres na Macu

Wymagania wstępne to działający lokalny serwer PostgreSQL, istniejąca tymczasowa baza danych, Node.js z npm oraz dostępność psql w Terminalu. Skonfiguruj połączenie z istniejącą bazą za pomocą zmiennych środowiskowych PostgreSQL. Pula node-postgres i psql korzystają z tych samych zmiennych połączenia; w razie potrzeby jawnie ustaw PGHOST, aby oba polecenia łączyły się z tym samym serwerem. node-postgres: Łączenie

W nowym folderze roboczym zainstaluj zależność:

npm init -y
npm install pg

Zapisz poniższy kompletny skrypt przygotowawczy jako setup.sql. Tworzy on nową tabelę demonstracyjną i trzy syntetyczne artykuły. Uruchom go raz w tymczasowej bazie danych. Celowo zakończy się błędem, jeśli taka tabela już istnieje, zamiast zastępować jej zawartość.

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

Zapisz następny kompletny program jako update-articles.mjs. Wszystkie instrukcje należące do danej próby korzystają z klienta zwróconego przez pool.connect(). node-postgres wyraźnie wymaga używania jednego klienta przez całą transakcję; wysyłanie instrukcji transakcji przez pool.query() nie spełnia tego wymogu. node-postgres: Transakcje

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

Tablica jest przekazywana jako jeden parametr i jawnie rzutowana na integer[]; dokumentacja node-postgres opisuje konwersję tablic JavaScript na wartości tablicowe PostgreSQL. Usuwanie duplikatów sprawia, że żądana liczba oznacza liczbę unikatowych artykułów. Kolejność uzyskiwania blokad ustala ORDER BY w bazie danych, a nie kolejność elementów tablicy wejściowej. node-postgres: Zapytania

W świeżo zainicjowanej tabeli, przy braku innych zapisujących procesów i powodzeniu obu partii, oczekiwane rewizje wynoszą 1 dla artykułu 1 oraz 2 dla artykułów 2 i 3. Te wartości wynikają z przynależności do partii, niezależnie od tego, która partia zakończy się pierwsza. Kolejne uruchomienia skryptu ponownie zwiększą istniejące wartości. Brak żądanego identyfikatora anuluje daną partię, zamiast po cichu aktualizować tylko znalezione wiersze.

Każda partia jest osobną transakcją. Jeśli jedna zakończy się powodzeniem, a druga na stałe nie powiedzie, udana partia pozostanie zatwierdzona. Promise.allSettled() umożliwia zakończenie obu prób, zanim program zamknie pulę. Końcowe pool.query() jest wykonywane poza obiema transakcjami. Interfejs API puli opisuje zwalnianie pobranych klientów, niszczenie klienta w razie potrzeby oraz zamykanie puli po zakończeniu pracy skryptu. node-postgres: Pula

Kontrole normalizacji i przykładowe obliczenia rewizji wykonano za pomocą Node.js. PostgreSQL nie był dostępny w środowisku badawczym, dlatego instrukcje SQL i zachowanie współbieżności bazy sprawdzono na podstawie dokumentacji, a nie przez ich wykonanie.

Zachowaj wymagany poziom izolacji

Program jawnie używa READ COMMITTED do aktualizacji według stałych identyfikatorów. Na tym poziomie instrukcja blokująca może poczekać na aktualizujący proces, a następnie zablokować zaktualizowaną wersję wiersza, o ile nadal spełnia on warunek. Każda instrukcja pobiera nowy obraz danych. Wiersz usunięty przed uzyskaniem blokady może więc być nieobecny; program obsługuje to przez sprawdzenie liczby wierszy. PostgreSQL: Izolacja transakcji

Nie obniżaj poziomu izolacji istniejącej transakcji tylko po to, by użyć tego przykładu. Przy REPEATABLE READ próba zablokowania lub zaktualizowania wiersza zmienionego od czasu obrazu transakcji może spowodować błąd serializacji. SERIALIZABLE również wymaga obsługi ponowień i zapobiega anomaliom serializacji w zatwierdzonych transakcjach; nie zastępuje spójnej kolejności zwykłych blokad wierszy. PostgreSQL: Izolacja transakcji

Blokowanie znanych wierszy nie gwarantuje, że elementy spełniające predykat pozostaną bez zmian. Na przykład ten przykład nie realizuje zadania „aktualizuj każdy artykuł, którego tytuł spełnia warunek, uwzględniając również równoległe dodawanie artykułów”. To odrębne zadanie związane ze spójnością. Przed dostosowaniem przepisu dla stałych identyfikatorów określ wymagania dotyczące izolacji i koordynacji.

Zadbaj również o niezmienność kolumny używanej do ustalania kolejności. PostgreSQL dokumentuje, że przy READ COMMITTED sortowanie następuje przed blokowaniem; wartości zmienione podczas oczekiwania instrukcji mogą sprawić, że zwrócone wiersze będą wyglądać na nieuporządkowane. Używanie niezmienionych identyfikatorów pozwala uniknąć tej komplikacji. PostgreSQL: SELECT

Ponawiaj całe transakcje i badaj powtarzające się błędy

PostgreSQL identyfikuje błędy zakleszczenia kodem SQLSTATE 40P01, a błędy serializacji — 40001. Ponów całą transakcję, w tym wszystkie zależne od bazy decyzje określające jej instrukcje lub wartości. Ponowienie tylko końcowej aktualizacji nie spełnia tego wymogu. PostgreSQL: Obsługa błędów serializacji

Przykład dopuszcza łącznie trzy próby. Krótkie opóźnienie wykładnicze i losowy element opóźnienia to przykładowe decyzje dotyczące aplikacji, a nie wartości domyślne PostgreSQL. Przed opóźnieniem zwalnia klienta, natychmiast przerywa działanie przy innych błędach i zgłasza wyczerpanie prób. PostgreSQL przestrzega, że ponowienia nie gwarantują ostatecznego powodzenia. PostgreSQL: Obsługa błędów serializacji

Podczas dostosowywania implementacji skorzystaj z poniższego przewodnika decyzyjnego:

Sytuacja Działanie
Inna partia utrzymuje już blokadę żądanego wiersza W razie potrzeby poczekaj na uporządkowane uzyskanie blokady, jeśli zadanie wymaga wszystkich żądanych wierszy.
Brakuje żądanego identyfikatora Wycofaj transakcję i zgłoś brak elementu docelowego.
SQLSTATE to 40P01 lub 40001 Wycofaj transakcję, a następnie w ograniczonej liczbie prób ponów całą kwalifikującą się transakcję.
Błędy nadal występują po uporządkowanym uzyskiwaniu blokad Sprawdź wcześniejsze instrukcje, inne procesy zapisujące, wyzwalacze i dodatkowe tabele pod kątem naruszeń zasad kolejności.
Operacja obejmuje kilka tabel Ustal wspólną kolejność tabel oraz wierszy dla wszystkich konkurujących ścieżek.
Partia musi zostać zatwierdzona atomowo Zachowaj jedną transakcję; niezależne zatwierdzanie fragmentów zmieniłoby zadanie.

Nie dodawaj SKIP LOCKED do tej partii obejmującej wszystkie elementy docelowe: pomija wiersze, których nie da się natychmiast zablokować. PostgreSQL opisuje tę opcję jako przydatną dla konsumentów przetwarzających kolejki, ale nieodpowiednią do uzyskania ogólnego, spójnego widoku. NOWAIT zgłasza błąd zamiast czekać na kolidującą blokadę wiersza; zmienia sposób oczekiwania, ale nie zapewnia ukończenia partii. PostgreSQL: SELECT

Implementacja jest gotowa do dostosowania, gdy możesz wskazać pełny zbiór docelowy, zachować niezmienność kluczy używanych do porządkowania i stosować tę samą regułę uzyskiwania blokad we wszystkich konkurujących transakcjach. Zachowaj kontrolę brakujących wierszy i ograniczone ponawianie całej transakcji, aby rywalizacja o blokady lub niepełne dane wejściowe prowadziły do jednoznacznego wyniku.

Źródła