Wenn die Datenbank blockiert — PostgreSQL Lock-Stürme und wie man sie löst

Red Panda Mechanic

Incident

Letzten Dienstag, 14:32 Uhr — die Produktions-API meldete plötzlich ERROR: canceling statement due to statement timeout und ERROR: deadlock detected. Antwortzeiten, die sonst bei 20ms liegen, schnellten auf über 12 Sekunden. Im PostgreSQL-Log tauchten Dutzende gleichzeitige Transaktionen auf, die aufeinander warteten. Kein Deployment, keine Code-Änderung in den letzten Stunden. Der einzige Unterschied: Zwei Cronjobs waren zur selben Minute gestartet.

Zuerst dachte ich an einen Angriff. Die Logs zeigten aber keine Brute-Force-Muster, sondern reguläre UPDATE-Statements. Also begann ich, pg_stat_activity zu lesen — und wurde nicht schlau daraus. Dutzende Sessions im Zustand active, alle mit langlaufenden Updates auf derselben Tabelle. idle in transaction? Fehlanzeigt. Die Transaktionen waren tatsächlich aktiv — und blockierten sich gegenseitig.

Root Cause

Was war passiert? Zwei Hintergrundjobs aktualisierten parallel dieselbe Tabelle. Beide lasen zunächst Zeilen mit WHERE processed = false, sperrten diese exklusiv (FOR UPDATE) und aktualisierten den Status. Weil beide Jobs keine konsistente Reihenfolge einhielten, griff Transaktion A auf Zeile 1 und dann auf Zeile 2 zu — während Transaktion B bereits Zeile 2 gesperrt hatte und auf Zeile 1 wartete. Ein klassischer Deadlock.

PostgreSQL erkennt solche Zyklen, aber erst nach Ablauf von deadlock_timeout (Standard: 1 Sekunde). In dieser Sekunde blockieren beide Transaktionen nicht nur sich selbst, sondern auch alle nachfolgenden Zugriffe auf die betroffenen Zeilen. Nach wenigen Minuten hatte sich eine Sperr-Kaskade aufgebaut: Immer mehr Connections hingen in Updates fest, neue Queries warteten auf Locks, die Timeouts feuerten — und das System reagierte nicht mehr.

Zwei weitere Faktoren verschärften das Problem:

  1. Kein Statement-Timeout. Die Queries liefen ohne Zeitbegrenzung. Ein einzelner Langläufer reichte, um den Connection-Pool zu füllen.
  2. Kein lock_timeout. Selbst wenn ein Deadlock nicht eintrat, konnte eine einzelne Transaktion problemlos 30 Sekunden oder länger auf einen Lock warten — genug, um den gesamten Batch zu blockieren.

Lösung

Das Ziel war nicht, Deadlocks vollständig zu verhindern (das ist bei hohem Schreibaufkommen praktisch unmöglich), sondern ihre Auswirkungen zu begrenzen und die Wahrscheinlichkeit zu senken.

1. Statement- und Lock-Timeout setzen

Jede Verbindung bekommt eine harte Obergrenze:

-- Timeout für einzelne Statements
SET statement_timeout = '3s';

-- Timeout für das Warten auf einen Lock
SET lock_timeout = '1s';

-- Optional: Transaktion nach 10s abbrechen, falls sie offen bleibt
SET idle_in_transaction_session_timeout = '10s';

Erklärung: Mit statement_timeout bricht PostgreSQL nach 3 Sekunden jedes Query ab — auch wenn es gerade auf einen Lock wartet. lock_timeout ist noch strenger: Er greift bereits, wenn ein einzelnes Lock nicht innerhalb von 1 Sekunde verfügbar ist. idle_in_transaction_session_timeout fängt vergessene BEGIN-Blöcke ab.

2. Batch-Updates mit konsistenter Reihenfolge

Statt alle Zeilen auf einmal zu sperren, arbeite ich in kleinen Blöcken — und zwar immer in der gleichen Reihenfolge:

-- Schlecht: Unsortiertes Bulk-Update ohne Begrenzung
UPDATE orders 
SET status = 'processing' 
WHERE processed = false 
  AND created_at < now() - interval '1 hour';

-- Gut: Kleine Blöcke, deterministische Reihenfolge, Skip Locked
BEGIN;
UPDATE orders 
SET status = 'processing' 
WHERE id IN (
    SELECT id 
    FROM orders 
    WHERE processed = false 
      AND created_at < now() - interval '1 hour'
    ORDER BY id ASC
    LIMIT 500
    FOR UPDATE SKIP LOCKED
);
COMMIT;

Erklärung: ORDER BY id ASC sorgt dafür, dass alle Transaktionen die Zeilen in der gleichen Reihenfolge sperren — das eliminiert die häufigste Deadlock-Ursache. LIMIT 500 hält die Transaktion kurz. SKIP LOCKED überspringt bereits gesperrte Zeilen statt zu warten. Die übersprungenen Zeilen werden im nächsten Durchlauf verarbeitet.

3. Monitoring: Sperren in Echtzeit sichtbar machen

Wer nicht misst, merkt nicht, wann es brennt. Diese Query zeigt alle aktuell blockierten Statements:

SELECT 
    pg_locks.pid,
    pg_stat_activity.state,
    pg_stat_activity.query_start,
    pg_stat_activity.query,
    pg_locks.mode,
    pg_locks.locktype,
    pg_locks.relation::regclass AS tabelle
FROM pg_locks
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE NOT pg_locks.granted
  AND pg_stat_activity.state != 'idle'
ORDER BY pg_stat_activity.query_start;

Erklärung: Die Verknüpfung von pg_locks und pg_stat_activity zeigt, welches Query welche Sperre hält und welche Statements darauf warten. Ich habe daraus ein einfaches Shell-Skript gebaut, das alle 10 Sekunden prüft und alarmiert, sobald mehr als 5 Queries gleichzeitig warten.

4. Connection-Pool konfigurieren

In der Anwendung (PgBouncer oder HikariCP) setze ich max_client_conn und default_pool_size so, dass nie mehr Connections als max_connections in PostgreSQL genutzt werden. Ein voller Connection-Pool bei blockierenden Queries ist das schnellste Weg in die Deadlock-Kaskade.

# pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
max_client_conn = 200
default_pool_size = 20
reserve_pool_size = 5

Verifikation

  1. Timeouts aktiv?

    SHOW statement_timeout;     -- sollte 3s oder ähnlich sein
SHOW lock_timeout;          -- sollte 1s oder ähnlich sein

  2. Keine langlaufenden Queries mehr:

    psql -c "SELECT pid, now() - query_start AS laufzeit, query FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '5 seconds';"

    Erwartung: Leere Ergebnismenge oder nur gelegentliche Langläufer unter 5s.

  3. Keine blockierten Sperren:

    psql -c "SELECT count(*) FROM pg_locks WHERE NOT granted;"

    Erwartung: 0 oder sehr niedrig (einzelne interne Locks sind normal).

  4. Lasttest mit parallelen Updates:

    # Zwei parallel laufende Update-Jobs starten
psql -c "BEGIN; UPDATE orders SET status = 'processing' WHERE processed = false FOR UPDATE SKIP LOCKED; COMMIT;" &
psql -c "BEGIN; UPDATE orders SET status = 'processing' WHERE processed = false FOR UPDATE SKIP LOCKED; COMMIT;" &
wait

    Erwartung: Beide Jobs laufen durch, keine Deadlock-Fehler im Log.

Fazit

PostgreSQL ist ACID — aber das schützt nicht vor schlecht designten Transaktionen. Der Lock-Sturm letzten Dienstag war kein PostgreSQL-Bug, sondern ein Architekturproblem: Lange, unsortierte Bulk-Updates ohne Zeitbegrenzung.

Die Lehre ist einfach: Jede Datenbank-Transaktion sollte so kurz und so vorhersagbar wie möglich sein. statement_timeout und lock_timeout sind keine Krücken, sondern Pflicht. FOR UPDATE SKIP LOCKED ist kein Hack, sondern das Standardmuster für Warteschlangen. Und Monitoring ist kein Extra — es ist die einzige Möglichkeit, Sperr-Probleme zu erkennen, bevor sie das System erwürgen.

Hast du schon einmal einen Lock-Sturm erlebt? Wie hast du es gelöst? Ich freue mich auf deine Erfahrungen in den Kommentaren.

Siehe auch

  • [Weg A oder Weg B — SQLite oder PostgreSQL?](/blog/weg-a-oder-weg-b-sqlite-oder-postgresql/)
  • [Wenn die CI/CD-Pipeline streikt – ein praxisnaher Leitfaden zur Reparatur](/blog/ci-cd-pipeline-reparatur-leitfaden/)
  • [Nginx Rate Limiting gegen Brute Force](/blog/nginx-rate-limiting-brute-force-schutz/)