PostgreSQL: Transakcje, Blokady, Poziomy Izolacji, MVCC, VACUUM

PostgreSQL: Dogłębna analiza transakcji, blokad i specyfiki Serializable

Deweloperzy często spotykają się z nieoczywistym zachowaniem zapytań w PostgreSQL: zawieszanie się, nieoczekiwane wyniki UPDATE podczas pracy równoległej lub błędy transakcji. Zrozumienie wewnętrznych mechanizmów, takich jak poziomy izolacji, blokady i działanie VACUUM, jest kluczowe dla tworzenia niezawodnych i wydajnych aplikacji. Ten artykuł szczegółowo omawia te koncepcje, ilustrując je praktycznymi przykładami SQL, oraz przedstawia najnowsze usprawnienia w PostgreSQL.

Czas w transakcjach: `now()` kontra `clock_timestamp()`

W PostgreSQL istnieją dwie główne funkcje do pobierania bieżącego czasu, których zachowanie różni się w kontekście transakcji. Funkcja now() zwraca czas rozpoczęcia bieżącej transakcji i pozostaje niezmieniona przez cały czas jej trwania. Zapewnia to spójność znaczników czasu dla operacji w ramach jednej transakcji, na przykład podczas ustawiania created_at i updated_at.

BEGIN;
SELECT now();
-- ... inne operacje ...
SELECT now(); -- Zwróci ten sam czas, co pierwsze wywołanie
SELECT clock_timestamp(); -- Może się różnić
COMMIT;

W przeciwieństwie do now(), funkcja clock_timestamp() dostarcza rzeczywisty bieżący czas wykonania, który może się zmieniać nawet w ramach jednego zapytania lub jednej transakcji. Zrozumienie tej różnicy pozwala unikać błędów podczas pracy z danymi czasowymi.

Poziomy izolacji transakcji w PostgreSQL

PostgreSQL wyróżnia się uproszczonym modelem poziomów izolacji w porównaniu z innymi systemami zarządzania bazami danych, oferując tylko trzy:

Read Committed: Poziom domyślny, gdzie każde polecenie w transakcji widzi świeżą migawkę danych. Odczyty niezatwierdzone (dirty reads) są w PostgreSQL niemożliwe z zasady, dlatego Read Uncommitted jest równoważne z Read Committed.
Repeatable Read: Zapewnia, że wszystkie operacje odczytu w ramach transakcji widzą tę samą migawkę danych, zatwierdzoną w momencie pierwszego zapytania. Zapobiega to odczytom fantomowym i odczytom niepowtarzalnym.
Serializable: Najbardziej rygorystyczny poziom, który gwarantuje, że równolegle wykonywane transakcje dadzą taki sam wynik, jak ich sekwencyjne wykonanie. Osiąga się to za pomocą mechanizmu Serializable Snapshot Isolation (SSI).

Istotną cechą PostgreSQL jest obsługa DDL (Data Definition Language) w transakcjach. Pozwala to na wykonywanie operacji tworzenia, modyfikowania lub usuwania struktur danych i, w razie potrzeby, ich wycofywanie, co zwiększa bezpieczeństwo migracji i złożonych zmian schematu.

Mechanizmy blokad i zarządzanie współbieżnym dostępem

Pomimo stosowania wielowersyjnego sterowania współbieżnością (MVCC), PostgreSQL aktywnie wykorzystuje blokady dla operacji zapisu. Gdy dwie transakcje próbują zaktualizować ten sam wiersz, druga transakcja będzie oczekiwać na zakończenie pierwszej. Rozważmy przykład:

-- Sesja 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- ... nie zatwierdzamy ...

-- Sesja 2:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- To zapytanie zawiesi się, oczekując na zakończenie Sesji 1.

Poniższa tabela przedstawia główne typy blokad w PostgreSQL, ich przeznaczenie i tryby kolidujące:

| Blokada | Kiedy jest nakładana | Kolizje z |

| :------------------------ | :------------------------------------------- | :---------------------------- |

| ACCESS SHARE | SELECT | ACCESS EXCLUSIVE |

| ROW SHARE | SELECT FOR UPDATE/SHARE | EXCLUSIVE, ACCESS EXCLUSIVE |

| ROW EXCLUSIVE | INSERT, UPDATE, DELETE | SHARE i wyższe |

| SHARE UPDATE EXCLUSIVE | CREATE INDEX CONCURRENTLY, ANALYZE, VACUUM | SHARE UPDATE EXCLUSIVE i wyższe |

| SHARE | CREATE INDEX | ROW EXCLUSIVE i wyższe |

| SHARE ROW EXCLUSIVE | CREATE TRIGGER, ALTER TABLE (niektóre) | ROW SHARE i wyższe |

| EXCLUSIVE | Dopuszcza tylko odczyt | ROW SHARE i wyższe |

| ACCESS EXCLUSIVE | DROP TABLE, ALTER TABLE, VACUUM FULL | Wszystkie |

Im bardziej restrykcyjna blokada, tym wyższy jej numer w tabeli i szerszy zakres operacji kolidujących. Operacje z kolidującymi blokadami nie mogą być wykonywane jednocześnie na jednym obiekcie; jedna z nich będzie oczekiwać. Ważne jest, aby zauważyć, że CREATE INDEX CONCURRENTLY pozwala budować indeksy bez blokowania operacji DML, w przeciwieństwie do standardowego CREATE INDEX.

Anomalie odczytu: dane fantomowe i niepowtarzalne

Na poziomie izolacji Read Committed mogą występować anomalie odczytu, które prowadzą do niespójnych wyników:

Odczyt fantomowy (Phantom Reads): Kiedy transakcja wykonuje to samo zapytanie dwukrotnie, a za drugim razem widzi nowe wiersze, wstawione przez inną zatwierdzoną transakcję między tymi zapytaniami.

```sql

-- Sesja 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT sum(value) FROM t_test2; -- Na przykład, 330

-- Sesja 2 (równolegle):

INSERT INTO t_test2 VALUES (1, 30); COMMIT;

-- Sesja 1:

SELECT sum(value) FROM t_test2; -- Teraz 360, pojawiły się nowe wiersze.

COMMIT;

```

Odczyt niepowtarzalny (Non-Repeatable Reads): Kiedy transakcja odczytuje te same dane dwukrotnie, a za drugim razem widzi zmienione dane, zmodyfikowane przez inną zatwierdzoną transakcję.

```sql

-- Sesja 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Zwraca wiersz

-- Sesja 2 (równolegle):

UPDATE t_test2 SET value = 15 WHERE class = 1 AND value = 10; COMMIT;

-- Sesja 1:

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Pusto! Dane się zmieniły.

COMMIT;

```

Aby zapobiec tym anomaliom, stosuje się poziom izolacji Repeatable Read. Na tym poziomie transakcja pracuje z ustaloną migawką danych, która nie zmienia się do jej zakończenia, niezależnie od zmian wprowadzonych przez inne transakcje.

Specyfika poziomu izolacji Serializable

Poziom Serializable ma na celu zapewnienie maksymalnej izolacji, symulując sekwencyjne wykonywanie transakcji. Może to jednak prowadzić do błędów serializacji (ERROR: could not serialize access), jeśli PostgreSQL wykryje potencjalny konflikt, który mógłby naruszyć sekwencję wykonania. W takich przypadkach jedna z transakcji zostanie anulowana i będzie wymagała ponownego wykonania.

Ciekawym aspektem Serializable są fałszywe konflikty (false conflicts), spowodowane blokadami predykatowymi (SSI). PostgreSQL przechowuje te blokady na poziomie stron, a nie pojedynczych wierszy. Jeśli dwie transakcje pracują z logicznie różnymi zestawami danych, ale te dane fizycznie znajdują się na tej samej stronie dysku (co często ma miejsce w przypadku małych tabel), może wystąpić konflikt serializacji, choć z logicznego punktu widzenia go nie ma. W przypadku dużych tabel, gdzie dane są rozłożone na różnych stronach, takie konflikty występują rzadziej.

Rozwiązywanie problemów współbieżności

Rozważmy klasyczny problem wyścigu danych: dwie transakcje jednocześnie próbują zmienić dane, bazując na nieaktualnych odczytach. Na przykład, Alicja i Bob pełnią dyżur, i każdy z nich chce odejść, jeśli pozostaje co najmniej jeden dyżurny. Jeśli obie transakcje używają Repeatable Read i widzą dwóch dyżurnych, obie zostają pomyślnie zatwierdzone, co prowadzi do całkowitego braku dyżurnych.

Do rozwiązania podobnych zadań stosuje się następujące podejścia:

Serializable: Druga transakcja zakończy się błędem serializacji i będzie mogła zostać ponowiona. Gwarantuje to poprawny wynik, ale wymaga obsługi błędów po stronie aplikacji.
SELECT ... FOR UPDATE: Użycie FOR UPDATE podczas odczytu jawnie blokuje wybrane wiersze, zapobiegając ich zmianie przez inne transakcje do czasu zakończenia bieżącej. Druga sesja będzie czekać, aż pierwsza się zakończy, a następnie zobaczy aktualne dane.

```sql

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM d_test WHERE on_call = true FOR UPDATE; -- Blokuje wiersze

-- ... dalsze operacje ...

COMMIT;

```

Operacja atomowa: Połączenie sprawdzenia warunku i działania w jedno zapytanie. Jeśli warunek nie zostanie spełniony w momencie wykonania UPDATE, zapytanie nie wpłynie na żaden wiersz, eliminując okno dla wyścigu.

```sql

UPDATE d_test

SET on_call = false

WHERE name = 'Alice'

AND (SELECT count(*) FROM d_test WHERE on_call = true) > 1;

```

Blokady doradcze (Advisory Locks)

PostgreSQL udostępnia mechanizm blokad doradczych (pg_advisory_lock, pg_advisory_xact_lock), które pozwalają zarządzać współbieżnym dostępem do dowolnych zasobów, niepowiązanych bezpośrednio z tabelami czy wierszami. Blokady te działają na poziomie liczb całkowitych i mogą być używane do koordynacji procesów.

pg_advisory_lock: Blokada na poziomie sesji. Utrzymuje się do jawnego zwolnienia (pg_advisory_unlock) lub do zamknięcia połączenia.
pg_advisory_xact_lock: Blokada na poziomie transakcji. Automatycznie zwalniana przy COMMIT lub ROLLBACK.

Warto pamiętać, że blokady doradcze działają tylko w ramach jednej bazy danych. Dla systemów rozproszonych z wieloma instancjami PostgreSQL wymagane są zewnętrzne rozwiązania (np. Redis, ZooKeeper, etcd).

Rola VACUUM w działaniu MVCC

MVCC (Multi-Version Concurrency Control) w PostgreSQL oznacza, że przy każdym UPDATE lub DELETE stare wersje wierszy nie są usuwane natychmiast, lecz oznaczane jako „martwe krotki”. Te martwe krotki zajmują miejsce i muszą zostać usunięte, aby zwolnić przestrzeń dla nowych danych i zapobiec rozrostowi tabel. Za to zadanie odpowiada proces VACUUM.

Domyślnie VACUUM nie zmniejsza fizycznego rozmiaru pliku tabeli. Jedynie oznacza zwolnione miejsce jako dostępne do ponownego wykorzystania. Oznacza to, że kolejne INSERT lub UPDATE mogą zająć tę przestrzeń bez zwiększania rozmiaru pliku.

CREATE TABLE t_test (id int) WITH (autovacuum_enabled = off);
INSERT INTO t_test SELECT * FROM generate_series(1, 100000);
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 MB

UPDATE t_test SET id = id + 1; -- Tworzy nowe wersje wierszy
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~7 MB (rozmiar podwoił się)

VACUUM t_test; -- Czyści martwe krotki, ale rozmiar pliku się nie zmienia
SELECT pg_size_pretty(pg_relation_size('t_test')); -- Nadal ~7 MB

Fizyczne zmniejszenie rozmiaru pliku tabeli następuje tylko wtedy, gdy VACUUM może „przyciąć ogon” — to znaczy, jeśli zwolnione strony znajdują się na końcu pliku. Na przykład, po usunięciu dużej liczby wierszy z wysokimi id:

DELETE FROM t_test WHERE id > 50000;
VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 MB (rozmiar zmniejszył się)

Automatyczny autovacuum to krytycznie ważny proces działający w tle, który stale monitoruje zmiany w tabelach i uruchamia VACUUM w celu utrzymania zdrowia bazy danych. Jego wyłączenie bez głębokiego zrozumienia konsekwencji jest antywzorcem w nowoczesnych wersjach PostgreSQL.

Ewolucja PostgreSQL: co zmieniło się w ciągu ostatnich 5 lat

Chociaż podstawowe zasady transakcji i blokad pozostają niezmienne, PostgreSQL stale się rozwija, oferując usprawnienia ukierunkowane na wydajność i wygodę użytkowania:

Optymalizacja VACUUM: W PostgreSQL 13 pojawiło się równoległe przetwarzanie indeksów podczas VACUUM, a w PostgreSQL 16 — pomijanie stron, które nie uległy zmianie od ostatniego przejścia. Te usprawnienia znacznie przyspieszyły działanie VACUUM na dużych tabelach, czyniąc ręczne zarządzanie nim mniej istotnym.
REINDEX CONCURRENTLY (PG 12): Pozwala na przebudowę indeksów bez blokowania operacji DML na tabeli, co jest kluczowe dla systemów o wysokim obciążeniu.
Ulepszone monitorowanie blokad: Widoki systemowe pg_stat_activity i pg_locks zostały uzupełnione o nowe pola, a rozszerzenie pg_wait_sampling dostarcza szczegółowych statystyk dotyczących oczekiwań, upraszczając diagnozowanie problemów z wydajnością.

Kluczowe wnioski:

now() vs clock_timestamp(): now() ustala czas rozpoczęcia transakcji dla spójności, clock_timestamp() podaje rzeczywisty bieżący czas.
Poziomy izolacji: PostgreSQL oferuje Read Committed (domyślnie), Repeatable Read (do zapobiegania anomaliom odczytu) i Serializable (dla maksymalnej spójności z ryzykiem błędów serializacji).
Blokady: Pomimo MVCC, PostgreSQL używa blokad zapisu. Zrozumienie tabeli blokad i ich konfliktów jest niezbędne do optymalizacji współbieżnego dostępu.
Serializable i fałszywe konflikty: Poziom Serializable może powodować błędy z powodu blokad predykatowych na poziomie stron, wymagając ponownego wykonania transakcji.
VACUUM i MVCC: VACUUM czyści martwe krotki, ale zazwyczaj nie zmniejsza rozmiaru pliku, a jedynie oznacza miejsce do ponownego wykorzystania. Fizyczne zmniejszenie następuje, jeśli zwolnione strony znajdują się na końcu pliku.