Zum Inhalt

MVCC, Concurrency & Autovacuum-Tuning: Transaktionsisolation unter Linux

Erstellt: 08.11.2026  ·  Zuletzt aktualisiert: 08.11.2026  ·  Nejat Hakan

Management Summary: Relationale Datenbanken müssen hunderte parallele Lese- und Schreibtransaktionen verarbeiten, ohne dass Daten korrumpiert werden oder das System durch Sperren zum Stillstand kommt. Während frühe Datenbanksysteme auf restriktive Zwei-Phasen-Sperren (2PL) setzten, nutzt PostgreSQL Multi-Version Concurrency Control (MVCC): Leser blockieren niemals Schreiber, und Schreiber blockieren niemals Leser. Da MVCC jedoch historische Zeilenversionen direkt im Tabellen-Heap ablegt, drohen ohne präzise Wartung massiver Tabellen-Bloat und im schlimmsten Fall ein katastrophaler Transaction-ID (XID) Wraparound. Dieser Leitfaden beschreibt die MVCC-Interna, Snapshot-Isolationsstufen, blockierungsfreie Worker-Queues (SKIP LOCKED) und aggressives autovacuum-Tuning.

  • MVCC-Snapshot-Isolation

    Tupel-Sichtbarkeit verstehen: Wie xmin, xmax und aktive Transaktionslisten festlegen, welche Zeilenversionen eine Verbindung sehen darf.

  • ANSI-Isolationsstufen

    READ COMMITTED, REPEATABLE READ und Serializable Snapshot Isolation (SSI) unterscheiden und Dirty Reads, Phantom-Zeilen und Write Skew eliminieren.

  • Blockierungsfreie Queues

    Durchsatzstarke Job-Queues in reinem SQL realisieren: Aufgabenzeilen sicher sperren und abarbeiten mit SELECT ... FOR UPDATE SKIP LOCKED.

  • Aggressives Autovacuum-Tuning

    Tabellen-Bloat verhindern: Tabellenspezifische Schwellenwerte und I/O-Kostenlimits tunen, um toten Speicherplatz frühzeitig zu recyceln.

1. Die Architektur von Multi-Version Concurrency Control (MVCC)

Statt Tabellenzeilen mit geteilten Lesesperren (Shared Locks) zu blockieren, erzeugt PostgreSQL bei jeder Änderung eine neue Zeilenversion (Tupel) im Tabellen-Heap:

%%{init: {'flowchart': {'wrappingWidth': 350}}}%%
flowchart TB
    Writer["Schreiber TX 105:<br/>UPDATE balance = 350<br/>(Schreibt neue Tupel-Version)"]

    subgraph HeapPage ["1. 8 KiB Heap-Page (MVCC-Layout)"]
        direction TB
        T1["Tupel v1 (Alte Version)<br/>xmin: 101 | xmax: 105<br/>balance: 500 (Tot)"]
        T2["Tupel v2 (Neue Version)<br/>xmin: 105 | xmax: 0<br/>balance: 350 (Live)"]
        T1 -.->|"t_ctid Zeiger"| T2
    end

    subgraph Readers ["2. Parallele Leser (Null Locks)"]
        direction TB
        TX_OLD["Leser TX 102<br/>Snapshot vor TX 105<br/>Liest v1: balance = 500"]
        TX_NEW["Leser TX 106<br/>Snapshot nach TX 105<br/>Liest v2: balance = 350"]
        VAC["Autovacuum-Worker<br/>Gibt totes Tupel v1 frei"]
        TX_OLD --> TX_NEW --> VAC
    end

    Writer --> HeapPage
    HeapPage ==> Readers

Die Sichtbarkeits-Felder im Tupel-Header

Jede Zeilenversion auf der Festplatte enthält zwei entscheidende Transaktionskennungen: - xmin: Die Transaktions-ID, die diese Zeilenversion eingefügt hat. - xmax: Die Transaktions-ID, die diese Zeilenversion gelöscht oder aktualisiert hat. Ist die Zeile aktuell gültig, ist xmax gleich 0.

Wird eine Abfrage gestartet, erhält sie einen MVCC-Snapshot mit folgenden Grenzen: 1. xmin: Die niedrigste Transaktions-ID, die zum Startzeitpunkt noch aktiv lief. 2. xmax: Die höchste bisher bestätigte Transaktions-ID + 1. 3. xip_list: Eine Liste aller Transaktions-IDs, die zum Erstellungszeitpunkt des Snapshots noch nicht committet waren.

Ein Tupel ist für eine Transaktion genau dann sichtbar, wenn sein xmin committet ist und nicht in der xip_list liegt, und sein xmax entweder 0, abgebrochen oder jünger als der Snapshot ist.

2. Transaktions-Isolationsstufen in PostgreSQL

PostgreSQL unterstützt drei standardkonforme Isolationsstufen:

Isolationsstufe Dirty Read Non-Repeatable Read Phantom Read Serialization Anomaly (Write Skew) Typischer Einsatzzweck
READ COMMITTED (Standard) Verhindert Möglich Möglich Möglich Standard-Web-APIs, CRUD-Abfragen, einfache Updates.
REPEATABLE READ Verhindert Verhindert Verhindert (via Snapshot) Möglich (in seltenen Randfällen) Mehrstufige Finanzberichte und konsistente Datenexporte.
SERIALIZABLE (SSI) Verhindert Verhindert Verhindert Verhindert (SQLSTATE 40001) Doppelte Buchführung, Kontostand-Transfers, Ticket-Reservierungen.

Der Unterschied zwischen READ COMMITTED und REPEATABLE READ

  • Unter READ COMMITTED erstellt PostgreSQL zu Beginn jeder einzelnen SQL-Anweisung einen frischen Snapshot. Committet eine parallele Verbindung zwischen Abfrage 1 und Abfrage 2 eine Änderung, sieht Abfrage 2 sofort die neuen Daten.
  • Unter REPEATABLE READ erstellt die Engine nur einen einzigen Snapshot zu Beginn der ersten Anweisung und friert diesen für die gesamte Lebensdauer der Transaktion ein.

3. Explizite Sperren: Das SKIP LOCKED-Muster

Bei der Implementierung asynchroner Hintergrund-Job-Queues in PostgreSQL müssen mehrere Worker gleichzeitig Aufgaben abholen, ohne sich gegenseitig zu blockieren oder Deadlocks zu verursachen.

%%{init: {'flowchart': {'wrappingWidth': 350}}}%%
flowchart TB
    Q["Job-Queue Tabelle<br/>(100 PENDING Jobs)"]
    W1["Worker 1: SKIP LOCKED<br/>Sperrt & holt Job 1"]
    W2["Worker 2: SKIP LOCKED<br/>Überspringt 1, holt Job 2"]
    W3["Worker 3: SKIP LOCKED<br/>Überspringt 1 & 2, holt Job 3"]

    Q --> W1
    W1 --> W2
    W2 --> W3

Durch die Kombination von FOR UPDATE mit SKIP LOCKED überspringen Worker bereits gesperrte Datensätze verzögerungsfrei:

-- Datei: /opt/db-ops/non_blocking_worker_queue.sql
BEGIN;

-- Ältesten offenen Job atomar sperren und abholen
WITH next_job AS (
    SELECT job_id, payload
    FROM job_queue
    WHERE status = 'PENDING'
    ORDER BY created_at ASC
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE job_queue
SET 
    status = 'PROCESSING',
    locked_at = clock_timestamp(),
    locked_by = 'worker-node-01'
FROM next_job
WHERE job_queue.job_id = next_job.job_id
RETURNING job_queue.job_id, job_queue.payload;

COMMIT;

4. Tabellen-Bloat & Aggressives Autovacuum-Tuning

In PostgreSQL überschreibt ein UPDATE keine Zeilen an Ort und Stelle; die alte Zeile verbleibt als toter Datensatz auf der 8 KiB-Seite. Sobald keine Transaktion mehr auf diese alte Zeilenversion zugreifen kann, wird sie zum toten Tupel (Dead Tuple).

Was ein reguläres VACUUM bewirkt

  1. Es scannt die Datenblöcke und entfernt Zeiger auf tote Tupel.
  2. Es gibt den Speicherplatz innerhalb der 8 KiB-Seite frei und vermerkt ihn in der Free Space Map (FSM) für zukünftige Inserts.
  3. Es aktualisiert die Visibility Map (VM), was zukünftige Index-Only-Scans ermöglicht.
  4. Es gibt keinen Speicherplatz an das Betriebssystem zurück. (Die Dateigröße auf der NVMe-SSD bleibt gleich).

Die Falle der Standard-Autovacuum-Konfiguration

Standardmäßig berechnet PostgreSQL den Start eines Vacuums nach folgender Formel:

Schwelle = autovacuum_vacuum_threshold + (scale_factor × reltuples)

Mit Standardwerten (scale_factor = 0.2, threshold = 50) startet der Autovacuum auf einer Tabelle mit 50 Millionen Zeilen erst, wenn 10 Millionen tote Zeilen angefallen sind! Auf stark beschriebenen Tabellen führt dies zu massivem Tabellen-Bloat und verlangsamten Indizes.

Tabellenspezifisches Tuning für Schreiblast-Tabellen

Überschreibe die Standard-Schwellenwerte für stark frequentierte Tabellen:

-- Datei: /opt/db-ops/tune_autovacuum_hot_table.sql
ALTER TABLE customer_orders SET (
    -- Vacuum bereits ab 1 % toten Zeilen starten (statt Standard 20 %)
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000,

    -- Planer-Statistiken bereits bei 0,5 % Änderungen neu berechnen
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_analyze_threshold = 500,

    -- Mehr I/O-Kapazität für die Bereinigung dieser Tabelle freigeben
    autovacuum_vacuum_cost_limit = 2000,
    autovacuum_vacuum_cost_delay = 2
);

5. Schutz vor dem Transaction-ID (XID) Wraparound

PostgreSQL verwendet für Transaktions-IDs 32-Bit-Ganzzahlen, was rund 4,29 × 109 eindeutige IDs ermöglicht. Das System arbeitet intern mit Modulo 231: - Jede Transaktion kann Änderungen sehen, die bis zu 2,1 Milliarden Transaktionen in der „Vergangenheit“ liegen. - Jede Transaktions-ID, die mehr als 2,1 Milliarden Schritte entfernt ist, gilt als in der „Zukunft“ liegend.

Würde eine Tabelle niemals bereinigt und stiege der Transaktionszähler um mehr als 2,1 Milliarden, würden alte Transaktionen plötzlich als in der „Zukunft“ liegend interpretiert – und alle historischen Daten würden schlagartig unsichtbar.

Um diesen Datenverlust zu verhindern, erzwingt PostgreSQL das Freeze-Verfahren: 1. Erreicht ein Tupel ein gewisses Alter, setzt autovacuum das Flag HEAP_XMIN_FROZEN im Tupel-Header. 2. Ein eingefrorenes Tupel gilt für alle aktuellen und zukünftigen Transaktionen dauerhaft als sichtbar, womit seine Transaktions-ID entfällt. 3. Nähert sich die Datenbank der Grenze von autovacuum_freeze_max_age (Standard: 200 Millionen Transaktionen), schaltet PostgreSQL in den Notfall-Schutzmodus und blockiert alle Schreibtransaktionen, bis der Freeze-Vacuum abgeschlossen ist.