PostgreSQL: Transaktionen, Sperren, Isolationsstufen, MVCC, VACUUM

PostgreSQL: Ein tiefer Einblick in Transaktionen, Sperren und Isolation

Entwickler stoßen in PostgreSQL oft auf nicht offensichtliches Abfrageverhalten: Blockaden, unerwartete UPDATE-Ergebnisse bei gleichzeitigen Operationen oder Transaktionsfehler. Das Verständnis interner Mechanismen wie Isolationsebenen, Sperren und der Funktionsweise von VACUUM ist entscheidend für den Aufbau zuverlässiger und performanter Anwendungen. Dieser Artikel analysiert diese Konzepte ausführlich, illustriert sie mit praktischen SQL-Beispielen und beleuchtet aktuelle PostgreSQL-Verbesserungen.

Zeit in Transaktionen: `now()` vs `clock_timestamp()`

PostgreSQL bietet zwei primäre Funktionen zum Abrufen der aktuellen Zeit, deren Verhalten sich im Kontext von Transaktionen unterscheidet. Die Funktion now() gibt die Startzeit der aktuellen Transaktion zurück und bleibt während ihrer Ausführung unverändert. Dies gewährleistet die Konsistenz von Zeitstempeln für Operationen innerhalb einer einzelnen Transaktion, beispielsweise beim Setzen von created_at und updated_at.

BEGIN;
SELECT now();
-- ... andere Operationen ...
SELECT now(); -- Gibt dieselbe Zeit wie der erste Aufruf zurück
SELECT clock_timestamp(); -- Kann abweichen
COMMIT;

Im Gegensatz zu now() liefert die Funktion clock_timestamp() die tatsächlich aktuelle Ausführungszeit, die sich sogar innerhalb einer einzelnen Abfrage oder Transaktion ändern kann. Das Verständnis dieses Unterschieds hilft, Fehler bei der Arbeit mit Zeitdaten zu vermeiden.

Transaktions-Isolationsebenen in PostgreSQL

PostgreSQL verfügt über ein vereinfachtes Modell von Isolationsebenen im Vergleich zu anderen RDBMS und bietet nur drei:

Read Committed: Die Standardebene, bei der jeder Befehl innerhalb einer Transaktion einen frischen Schnappschuss der Daten sieht. Dirty Reads sind in PostgreSQL grundsätzlich unmöglich, daher ist Read Uncommitted äquivalent zu Read Committed.
Repeatable Read: Stellt sicher, dass alle Leseoperationen innerhalb einer Transaktion denselben Datenschnappschuss sehen, der zum Zeitpunkt der ersten Abfrage fixiert wurde. Dies verhindert Phantom Reads und Non-Repeatable Reads.
Serializable: Die strengste Ebene, die garantiert, dass gleichzeitig ausgeführte Transaktionen dasselbe Ergebnis liefern, als ob sie sequentiell ausgeführt worden wären. Dies wird durch den Mechanismus der Serializable Snapshot Isolation (SSI) erreicht.

Ein wichtiges Merkmal von PostgreSQL ist die Unterstützung von DDL (Data Definition Language) innerhalb von Transaktionen. Dies ermöglicht das Erstellen, Ändern oder Löschen von Datenstrukturen und deren Zurückrollen bei Bedarf, was die Sicherheit von Migrationen und komplexen Schemaänderungen erhöht.

Sperrmechanismen und Nebenläufigkeitskontrolle

Trotz der Verwendung von Multi-Version Concurrency Control (MVCC) setzt PostgreSQL aktiv Sperren für Schreiboperationen ein. Wenn zwei Transaktionen versuchen, dieselbe Zeile zu aktualisieren, wartet die zweite Transaktion, bis die erste abgeschlossen ist. Betrachten Sie dieses Beispiel:

-- Session 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- ... nicht committen ...

-- Session 2:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- Diese Abfrage wird hängen bleiben und auf den Abschluss von Session 1 warten.

Die folgende Tabelle zeigt die wichtigsten Sperrtypen in PostgreSQL, ihren Zweck und konfligierende Modi:

| Sperrtyp | Wann erworben | Konflikte mit |

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

| ACCESS SHARE | SELECT | ACCESS EXCLUSIVE |

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

| ROW EXCLUSIVE | INSERT, UPDATE, DELETE | SHARE und höher |

| SHARE UPDATE EXCLUSIVE | CREATE INDEX CONCURRENTLY, ANALYZE, VACUUM | SHARE UPDATE EXCLUSIVE und höher |

| SHARE | CREATE INDEX | ROW EXCLUSIVE und höher |

| SHARE ROW EXCLUSIVE | CREATE TRIGGER, ALTER TABLE (einige) | ROW SHARE und höher |

| EXCLUSIVE | Erlaubt nur Lesevorgänge | ROW SHARE und höher |

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

Je strenger die Sperre, desto höher ihre Nummer in der Tabelle und desto breiter der Bereich der konfligierenden Operationen. Operationen mit konfligierenden Sperren können nicht gleichzeitig auf dasselbe Objekt zugreifen; eine wird warten. Es ist wichtig zu beachten, dass CREATE INDEX CONCURRENTLY das Erstellen von Indizes ermöglicht, ohne DML-Operationen zu blockieren, im Gegensatz zu standardmäßigem CREATE INDEX.

Leseanomalien: Phantom- und nicht wiederholbare Daten

Auf der Isolationsebene Read Committed können Leseanomalien auftreten, die zu inkonsistenten Ergebnissen führen:

Phantom Reads: Wenn eine Transaktion dieselbe Abfrage zweimal ausführt und beim zweiten Mal neue Zeilen sieht, die von einer anderen Transaktion zwischen diesen Abfragen eingefügt und committet wurden.

```sql

-- Session 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT sum(value) FROM t_test2; -- Zum Beispiel, 330

-- Session 2 (gleichzeitig):

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

-- Session 1:

SELECT sum(value) FROM t_test2; -- Jetzt 360, neue Zeilen sind erschienen.

COMMIT;

```

Non-Repeatable Reads: Wenn eine Transaktion dieselben Daten zweimal liest und beim zweiten Mal geänderte Daten sieht, die von einer anderen Transaktion geändert und committet wurden.

```sql

-- Session 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Gibt eine Zeile zurück

-- Session 2 (gleichzeitig):

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

-- Session 1:

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Leer! Daten geändert.

COMMIT;

```

Um diese Anomalien zu verhindern, wird die Isolationsebene Repeatable Read verwendet. Auf dieser Ebene operiert eine Transaktion auf einem festen Datenschnappschuss, der sich bis zu ihrem Abschluss nicht ändert, unabhängig von Änderungen, die von anderen Transaktionen vorgenommen wurden.

Besonderheiten der serialisierbaren Isolationsebene

Die Ebene Serializable ist darauf ausgelegt, maximale Isolation zu bieten und die sequentielle Ausführung von Transaktionen zu simulieren. Dies kann jedoch zu Serialisierungsfehlern (ERROR: could not serialize access) führen, wenn PostgreSQL einen potenziellen Konflikt erkennt, der die Ausführungsreihenfolge verletzen könnte. In solchen Fällen wird eine der Transaktionen abgebrochen und muss wiederholt werden.

Ein interessanter Aspekt von Serializable sind falsche Konflikte, die durch Prädikatssperren (SSI) verursacht werden. PostgreSQL speichert diese Sperren auf Seitenebene, nicht für einzelne Zeilen. Wenn zwei Transaktionen mit logisch unterschiedlichen Datensätzen arbeiten, diese Daten aber physisch auf derselben Disk-Seite liegen (was oft bei kleinen Tabellen der Fall ist), kann ein Serialisierungskonflikt auftreten, obwohl logisch keiner vorhanden ist. Bei großen Tabellen, wo Daten auf verschiedene Seiten verteilt sind, sind solche Konflikte seltener.

Lösung von Nebenläufigkeitsproblemen

Betrachten wir ein klassisches Data-Race-Problem: Zwei Transaktionen versuchen gleichzeitig, Daten basierend auf veralteten Lesevorgängen zu ändern. Zum Beispiel sind Alice und Bob im Dienst, und jeder möchte gehen, wenn mindestens eine Person im Dienst bleibt. Wenn beide Transaktionen Repeatable Read verwenden und zwei Personen im Dienst sehen, committen beide erfolgreich, was dazu führt, dass niemand mehr im Dienst ist.

Um solche Probleme zu lösen, werden folgende Ansätze verwendet:

Serializable: Die zweite Transaktion schlägt mit einem Serialisierungsfehler fehl und kann dann wiederholt werden. Dies garantiert ein korrektes Ergebnis, erfordert aber eine Fehlerbehandlung in der Anwendung.
SELECT ... FOR UPDATE: Die Verwendung von FOR UPDATE während eines Lesevorgangs sperrt die ausgewählten Zeilen explizit und verhindert, dass andere Transaktionen sie ändern, bis die aktuelle abgeschlossen ist. Die zweite Session wartet, bis die erste beendet ist, und sieht dann die aktualisierten Daten.

```sql

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM d_test WHERE on_call = true FOR UPDATE; -- Sperrt Zeilen

-- ... weitere Operationen ...

COMMIT;

```

Atomare Operation: Die Kombination der Bedingungsprüfung und der Aktion in einer einzigen Abfrage. Wenn die Bedingung zum Zeitpunkt des UPDATE nicht erfüllt ist, beeinflusst die Abfrage keine Zeilen, wodurch das Race-Fenster eliminiert wird.

```sql

UPDATE d_test

SET on_call = false

WHERE name = 'Alice'

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

```

Advisory Locks

PostgreSQL bietet einen beratenden Sperrmechanismus (pg_advisory_lock, pg_advisory_xact_lock), der die Steuerung des gleichzeitigen Zugriffs auf beliebige Ressourcen ermöglicht, die nicht direkt an Tabellen oder Zeilen gebunden sind. Diese Sperren operieren auf Integer-Werten und können zur Koordination von Prozessen verwendet werden.

pg_advisory_lock: Sperre auf Session-Ebene. Bleibt bestehen, bis sie explizit freigegeben (pg_advisory_unlock) oder die Verbindung geschlossen wird.
pg_advisory_xact_lock: Sperre auf Transaktions-Ebene. Wird bei COMMIT oder ROLLBACK automatisch freigegeben.

Es ist wichtig zu bedenken, dass Advisory Locks nur innerhalb einer einzigen Datenbank funktionieren. Für verteilte Systeme mit mehreren PostgreSQL-Instanzen sind externe Lösungen erforderlich (z. B. Redis, ZooKeeper, etcd).

Die Rolle von VACUUM in MVCC

MVCC (Multi-Version Concurrency Control) in PostgreSQL impliziert, dass bei jedem UPDATE oder DELETE alte Versionen von Zeilen nicht sofort entfernt, sondern als 'tote Tupel' markiert werden. Diese toten Tupel belegen Speicherplatz und müssen bereinigt werden, um Speicherplatz für neue Daten freizugeben und Tabellen-Bloat zu verhindern. Der VACUUM-Prozess ist für diese Aufgabe verantwortlich.

Standardmäßig reduziert VACUUM nicht die physische Größe der Tabellendatei. Es markiert lediglich den freigegebenen Speicherplatz als zur Wiederverwendung verfügbar. Dies bedeutet, dass nachfolgende INSERT- oder UPDATE-Operationen diesen Platz belegen können, ohne die Dateigröße zu erhöhen.

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; -- Erstellt neue Zeilenversionen
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~7 MB (Größe verdoppelt)

VACUUM t_test; -- Bereinigt tote Tupel, aber Dateigröße ändert sich nicht
SELECT pg_size_pretty(pg_relation_size('t_test')); -- Immer noch ~7 MB

Eine physische Reduzierung der Tabellendateigröße tritt nur auf, wenn VACUUM 'den Schwanz abschneiden' kann – das heißt, wenn die freigegebenen Seiten am Ende der Datei liegen. Zum Beispiel nach dem Löschen einer großen Anzahl von Zeilen mit hohen ids:

DELETE FROM t_test WHERE id > 50000;
VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 MB (Größe reduziert)

Das automatische autovacuum ist ein kritisch wichtiger Hintergrundprozess, der ständig Änderungen in Tabellen überwacht und VACUUM ausführt, um die Datenbankgesundheit zu erhalten. Es ohne tiefes Verständnis der Konsequenzen zu deaktivieren, ist in modernen PostgreSQL-Versionen ein Anti-Pattern.

PostgreSQL Evolution: Was sich in den letzten 5 Jahren geändert hat

Während die Grundprinzipien von Transaktionen und Sperren konstant bleiben, entwickelt sich PostgreSQL kontinuierlich weiter und bietet Verbesserungen, die auf Leistung und Benutzerfreundlichkeit abzielen:

VACUUM-Optimierung: PostgreSQL 13 führte die parallele Indexverarbeitung während VACUUM ein, und PostgreSQL 16 fügte das Überspringen von unveränderten Seiten seit dem letzten Durchlauf hinzu. Diese Verbesserungen beschleunigten VACUUM-Operationen auf großen Tabellen erheblich und machten ein manuelles Management weniger relevant.
REINDEX CONCURRENTLY (PG 12): Ermöglicht das Neuerstellen von Indizes ohne DML-Operationen auf der Tabelle zu blockieren, was für Hochlastsysteme entscheidend ist.
Verbessertes Sperrmonitoring: Die Systemansichten pg_stat_activity und pg_locks wurden um neue Felder erweitert, und die pg_wait_sampling-Erweiterung bietet detaillierte Wartestatistiken, was die Diagnose von Leistungsproblemen vereinfacht.

Wichtige Erkenntnisse:

now() vs clock_timestamp(): now() fixiert die Transaktionsstartzeit für Konsistenz, clock_timestamp() liefert die tatsächlich aktuelle Zeit.
Isolationsebenen: PostgreSQL bietet Read Committed (Standard), Repeatable Read (zur Verhinderung von Leseanomalien) und Serializable (für maximale Konsistenz mit dem Risiko von Serialisierungsfehlern).
Sperren: Trotz MVCC verwendet PostgreSQL Schreibsperren. Das Verständnis der Sperrtabelle und ihrer Konflikte ist entscheidend für die Optimierung des gleichzeitigen Zugriffs.
Serializable und falsche Konflikte: Die Serializable-Ebene kann aufgrund von Prädikatssperren auf Seitenebene Fehler verursachen, die ein Wiederholen von Transaktionen erfordern.
VACUUM und MVCC: VACUUM bereinigt tote Tupel, reduziert aber typischerweise nicht die Dateigröße; es markiert lediglich Speicherplatz zur Wiederverwendung. Eine physische Reduzierung tritt auf, wenn freie Seiten am Ende der Datei liegen.