PostgreSQL: Transakce, Zámky, Úrovně izolace, MVCC, VACUUM

PostgreSQL: Hluboký ponor do transakcí, zámků a izolace Serializable

Vývojáři se často setkávají s neintuitivním chováním dotazů v PostgreSQL: zamrzání, neočekávané výsledky UPDATE při paralelní práci nebo selhání transakcí. Pochopení interních mechanismů, jako jsou úrovně izolace, zámky a fungování VACUUM, je klíčové pro tvorbu spolehlivých a výkonných aplikací. Tento článek podrobně rozebírá tyto koncepty, ilustruje je na praktických SQL příkladech a osvětluje nejnovější vylepšení v PostgreSQL.

Čas v transakcích: `now()` vs `clock_timestamp()`

V PostgreSQL existují dvě hlavní funkce pro získání aktuálního času, jejichž chování se liší v kontextu transakcí. Funkce now() vrací čas zahájení aktuální transakce a zůstává nezměněna po celou dobu jejího provádění. To zajišťuje konzistenci časových značek pro operace v rámci jedné transakce, například při nastavení created_at a updated_at.

BEGIN;
SELECT now();
-- ... další operace ...
SELECT now(); -- Vrátí stejný čas jako první volání
SELECT clock_timestamp(); -- Může se lišit
COMMIT;

Na rozdíl od now() funkce clock_timestamp() poskytuje skutečný aktuální čas provedení, který se může měnit i v rámci jednoho dotazu nebo jedné transakce. Pochopení tohoto rozdílu pomáhá předcházet chybám při práci s časovými daty.

Úrovně izolace transakcí v PostgreSQL

PostgreSQL se vyznačuje zjednodušeným modelem úrovní izolace ve srovnání s jinými databázovými systémy (DBMS), nabízející pouze tři:

Read Committed: Výchozí úroveň, kde každý příkaz v transakci vidí aktuální snímek dat. Špinavé čtení (dirty reads) je v PostgreSQL principiálně nemožné, proto je Read Uncommitted ekvivalentní Read Committed.
Repeatable Read: Zajišťuje, že všechny operace čtení v rámci transakce vidí stejný snímek dat, který byl pořízen v okamžiku prvního dotazu. To zabraňuje fantomovým čtením a neopakovatelným čtením.
Serializable: Nejpřísnější úroveň, která zaručuje, že souběžně prováděné transakce poskytnou stejný výsledek jako jejich sekvenční provedení. Toho je dosaženo mechanismem Serializable Snapshot Isolation (SSI).

Důležitou vlastností PostgreSQL je podpora DDL (Data Definition Language) v transakcích. To umožňuje provádět operace vytváření, změny nebo mazání datových struktur a v případě potřeby je vrátit zpět (rollback), což zvyšuje bezpečnost migrací a komplexních změn schématu.

Mechanismy zámků a řízení souběžného přístupu

Navzdory použití víceverzího řízení souběžnosti (MVCC) PostgreSQL aktivně využívá zámky pro operace zápisu. Když se dvě transakce pokusí aktualizovat stejný řádek, druhá transakce bude čekat na dokončení první. Podívejme se na příklad:

-- Relace 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- ... necommitujeme ...

-- Relace 2:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- Tento dotaz se zablokuje a bude čekat na dokončení Relace 1.

Níže uvedená tabulka demonstruje hlavní typy zámků v PostgreSQL, jejich účel a konfliktní režimy:

| Zámek | Kdy se aplikuje | Konfliktuje s |

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

| ACCESS SHARE | SELECT | ACCESS EXCLUSIVE |

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

| ROW EXCLUSIVE | INSERT, UPDATE, DELETE | SHARE a vyšší |

| SHARE UPDATE EXCLUSIVE | CREATE INDEX CONCURRENTLY, ANALYZE, VACUUM | SHARE UPDATE EXCLUSIVE a vyšší |

| SHARE | CREATE INDEX | ROW EXCLUSIVE a vyšší |

| SHARE ROW EXCLUSIVE | CREATE TRIGGER, ALTER TABLE (některé) | ROW SHARE a vyšší |

| EXCLUSIVE | Povoluje pouze čtení | ROW SHARE a vyšší |

| ACCESS EXCLUSIVE | DROP TABLE, ALTER TABLE, VACUUM FULL | Všechny |

Čím přísnější je zámek, tím vyšší je jeho úroveň v tabulce a širší je spektrum konfliktních operací. Operace s konfliktními zámky nemohou být prováděny souběžně na stejném objektu; jedna z nich bude čekat. Je důležité poznamenat, že CREATE INDEX CONCURRENTLY umožňuje vytvářet indexy bez blokování DML operací, na rozdíl od standardního CREATE INDEX.

Anomálie čtení: fantomové a neopakovatelné čtení

Na úrovni izolace Read Committed mohou nastat anomálie čtení, které vedou k nekonzistentním výsledkům:

Fantomové čtení (Phantom Reads): Když transakce provede stejný dotaz dvakrát a podruhé vidí nové řádky, které byly vloženy jinou commitnutou transakcí mezi těmito dotazy.

```sql

-- Relace 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT sum(value) FROM t_test2; -- Například 330

-- Relace 2 (paralelně):

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

-- Relace 1:

SELECT sum(value) FROM t_test2; -- Nyní 360, objevily se nové řádky.

COMMIT;

```

Neopakovatelné čtení (Non-Repeatable Reads): Když transakce čte stejná data dvakrát a podruhé vidí změněná data, modifikovaná jinou commitnutou transakcí.

```sql

-- Relace 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Vrátí řádek

-- Relace 2 (paralelně):

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

-- Relace 1:

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Prázdné! Data se změnila.

COMMIT;

```

Pro zamezení těchto anomálií se používá úroveň izolace Repeatable Read. Na této úrovni transakce pracuje s fixním snímkem dat, který se nemění až do jejího dokončení, bez ohledu na změny provedené jinými transakcemi.

Specifika úrovně izolace Serializable

Úroveň Serializable je navržena tak, aby zajistila maximální izolaci, simulující sekvenční provádění transakcí. To však může vést k chybám serializace (ERROR: could not serialize access), pokud PostgreSQL detekuje potenciální konflikt, který by mohl narušit sekvenční provádění. V takových případech bude jedna z transakcí zrušena a je nutné ji opakovat.

Zajímavým aspektem Serializable jsou falešné konflikty (false conflicts), způsobené predikátovými zámky (SSI). PostgreSQL ukládá tyto zámky na úrovni stránek, nikoli jednotlivých řádků. Pokud dvě transakce pracují s logicky odlišnými sadami dat, ale tato data jsou fyzicky umístěna na jedné diskové stránce (což se často stává u malých tabulek), může dojít ke konfliktu serializace, ačkoli z logického hlediska žádný neexistuje. U velkých tabulek, kde jsou data distribuována na různých stránkách, se takové konflikty projevují méně často.

Řešení problémů souběžnosti

Podívejme se na klasický problém souběhu dat (race condition): dvě transakce se současně pokoušejí změnit data na základě zastaralých informací. Například Alice a Bob drží službu a každý z nich chce odejít, pokud zůstane alespoň jeden sloužící. Pokud obě transakce používají Repeatable Read a vidí dva sloužící, obě se úspěšně commitnou, což vede k tomu, že nezůstane žádný sloužící.

Pro řešení podobných úloh se používají následující přístupy:

Serializable: Druhá transakce selže s chybou serializace a bude ji možné opakovat. To zaručuje správný výsledek, ale vyžaduje zpracování chyb na straně aplikace.
SELECT ... FOR UPDATE: Použití FOR UPDATE při čtení explicitně uzamkne vybrané řádky, čímž zabrání jejich změně jinými transakcemi až do dokončení aktuální. Druhá relace bude čekat, dokud se první nedokončí, a poté uvidí aktuální data.

```sql

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM d_test WHERE on_call = true FOR UPDATE; -- Uzamkne řádky

-- ... další operace ...

COMMIT;

```

Atomická operace: Spojení kontroly podmínky a akce do jednoho dotazu. Pokud podmínka není splněna v okamžiku provedení UPDATE, dotaz neovlivní žádný řádek, čímž se eliminuje okno pro souběh.

```sql

UPDATE d_test

SET on_call = false

WHERE name = 'Alice'

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

```

Poradní zámky (Advisory Locks)

PostgreSQL poskytuje mechanismus poradních zámků (pg_advisory_lock, pg_advisory_xact_lock), které umožňují řídit souběžný přístup k libovolným zdrojům, jež nejsou přímo spojeny s tabulkami nebo řádky. Tyto zámky pracují na úrovni celých čísel a mohou být použity pro koordinaci procesů.

pg_advisory_lock: Zámek na úrovni relace. Zůstává aktivní až do explicitního uvolnění (pg_advisory_unlock) nebo do uzavření spojení.
pg_advisory_xact_lock: Zámek na úrovni transakce. Automaticky se uvolní při COMMIT nebo ROLLBACK.

Je důležité si uvědomit, že poradní zámky fungují pouze v rámci jedné databáze. Pro distribuované systémy s několika instancemi PostgreSQL jsou zapotřebí externí řešení (například Redis, ZooKeeper, etcd).

Role VACUUM v práci MVCC

MVCC (Multi-Version Concurrency Control) v PostgreSQL znamená, že při každém UPDATE nebo DELETE nejsou staré verze řádků okamžitě odstraněny, ale jsou označeny jako „mrtvé n-tice“ (dead tuples). Tyto mrtvé n-tice zabírají místo a musí být odstraněny, aby se uvolnil prostor pro nová data a zabránilo se růstu tabulek. Za tento úkol odpovídá proces VACUUM.

Ve výchozím nastavení VACUUM nezmenšuje fyzickou velikost souboru tabulky. Pouze označí uvolněné místo jako dostupné pro opětovné použití. To znamená, že následné INSERT nebo UPDATE mohou toto místo obsadit, aniž by se zvětšila velikost souboru.

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; -- Vytvoří nové verze řádků
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~7 MB (velikost se zdvojnásobila)

VACUUM t_test; -- Vyčistí mrtvé n-tice, ale velikost souboru se nemění
SELECT pg_size_pretty(pg_relation_size('t_test')); -- Stále ~7 MB

Fyzické zmenšení velikosti souboru tabulky nastane pouze v případě, že VACUUM může „odříznout konec“ – tedy pokud se uvolněné stránky nacházejí na konci souboru. Například po smazání velkého počtu řádků s vysokými id:

DELETE FROM t_test WHERE id > 50000;
VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 MB (velikost se zmenšila)

Automatický autovacuum je kriticky důležitý proces na pozadí, který neustále sleduje změny v tabulkách a spouští VACUUM pro udržení zdraví databáze. Jeho vypnutí bez hlubokého pochopení důsledků je v moderních verzích PostgreSQL považováno za anti-pattern.

Evoluce PostgreSQL: co se změnilo za posledních 5 let

Ačkoli základní principy transakcí a zámků zůstávají nezměněny, PostgreSQL se neustále vyvíjí a nabízí vylepšení zaměřená na výkon a uživatelskou přívětivost:

Optimalizace VACUUM: V PostgreSQL 13 se objevilo paralelní zpracování indexů při VACUUM a v PostgreSQL 16 přeskočení stránek, které se nezměnily od posledního průchodu. Tato vylepšení výrazně urychlila práci VACUUM na velkých tabulkách, čímž se ruční správa stala méně relevantní.
REINDEX CONCURRENTLY (PG 12): Umožňuje přestavbu indexů bez blokování DML operací na tabulce, což je kriticky důležité pro vysoce zatížené systémy.
Vylepšené monitorování zámků: Systémová zobrazení pg_stat_activity a pg_locks byla doplněna o nová pole a rozšíření pg_wait_sampling poskytuje detailní statistiky o čekáních, což zjednodušuje diagnostiku problémů s výkonem.

Co je důležité:

now() vs clock_timestamp(): now() fixuje čas začátku transakce pro konzistenci, clock_timestamp() poskytuje skutečný aktuální čas.
Úrovně izolace: PostgreSQL nabízí Read Committed (výchozí), Repeatable Read (pro zamezení anomálií čtení) a Serializable (pro maximální konzistenci s rizikem chyb serializace).
Zámky: Navzdory MVCC PostgreSQL používá zámky pro zápis. Pochopení tabulky zámků a jejich konfliktů je nezbytné pro optimalizaci souběžného přístupu.
Serializable a falešné konflikty: Úroveň Serializable může způsobovat chyby kvůli predikátovým zámkům na úrovni stránek, což vyžaduje opakované provádění transakcí.
VACUUM a MVCC: VACUUM čistí mrtvé n-tice, ale obvykle nezmenšuje velikost souboru, pouze označuje místo pro opětovné použití. Fyzické zmenšení nastane, pokud se uvolněné stránky nacházejí na konci souboru.