So identifizieren und beheben Sie PostgreSQL-Deadlocks und Sperrkonflikte

PostgreSQL-Deadlocks und Sperrkonflikte äußern sich meist darin, dass eine Anwendung plötzlich festzustecken scheint. Anfragen stauen sich, Worker sitzen im Zustand active oder idle in transaction, und die Datenbank selbst hat möglicherweise noch reichlich CPU übrig. Das Problem ist nicht die reine Kapazität. Eine Sitzung wartet darauf, dass eine andere Sitzung eine Sperre freigibt, und manchmal bildet sich eine ganze Warteschlange dahinter.

Der schnellste Weg, dies zu handhaben, ist die Unterscheidung zweier Fälle. Sperrkonflikt bedeutet, dass eine Sitzung wartet, aber möglicherweise irgendwann fortfahren kann. Ein Deadlock bedeutet, dass zwei oder mehr Sitzungen in einer zyklischen Abhängigkeit aufeinander warten, sodass PostgreSQL eine Transaktion abbrechen muss. Sie debuggen beide mit denselben grundlegenden Werkzeugen, aber die Behebung ist oft unterschiedlich.

Grundlagen der PostgreSQL-Sperren

PostgreSQL verwendet Sperren, um Tabellen, Zeilen, Transaktionen und andere interne Objekte zu schützen, während viele Sitzungen gleichzeitig arbeiten. Es verwendet auch MVCC, sodass gewöhnliche Lese- und Schreibvorgänge sich oft nicht gegenseitig blockieren. Deshalb kann PostgreSQL gut mit hoher Parallelität umgehen, aber auch deshalb können Sperrprobleme verwirrend sein: Das Problem ist normalerweise ein bestimmtes Anweisungsmuster, nicht "zu viele Benutzer" im Allgemeinen.

Arten von Sperren

PostgreSQL verwendet verschiedene Sperrstufen, die jeweils ein unterschiedliches Maß an Schutz bieten. Das Verständnis dieser Stufen ist der Schlüssel zur Diagnose von Problemen:

AccessShareLock: Wird von gewöhnlichen SELECT-Anweisungen verwendet. Es kollidiert hauptsächlich mit ACCESS EXCLUSIVE, weshalb viele Lesevorgänge ausgeführt werden können, während Schreibvorgänge stattfinden.
RowExclusiveLock: Üblich für INSERT, UPDATE und DELETE auf einer Tabelle. Der Name ist leicht falsch zu verstehen; es bedeutet nicht, dass jede Zeile in der Tabelle exklusiv gesperrt ist.
ShareUpdateExclusiveLock: Wird von Operationen wie VACUUM ohne FULL, ANALYZE und einigen Indexoperationen verwendet. Es erlaubt normale Lese- und Schreibvorgänge, kollidiert jedoch mit mehreren Wartungsoperationen.
ShareLock / ShareRowExclusiveLock / ExclusiveLock: Stärkere Tabellenmodi, die von bestimmten DDL- und Constraint-bezogenen Operationen verwendet werden.
AccessExclusiveLock: Die restriktivste Tabellensperre. ALTER TABLE, DROP TABLE, TRUNCATE und VACUUM FULL können diese Art von Sperre anfordern. Sie blockiert sowohl gewöhnliche Lese- als auch Schreibvorgänge.

Sperren auf Zeilenebene sind getrennt von den Sperrmodi auf Tabellenebene. Ein UPDATE nimmt eine RowExclusiveLock auf Tabellenebene und Zeilensperren auf den Zeilen, die es ändert. Wenn Leute sagen "diese Zeile ist gesperrt", meinen sie normalerweise, dass eine andere Transaktion diese Zeile geändert oder mit FOR UPDATE ausgewählt hat und noch nicht festgeschrieben hat.

Sperrmodi

Sperrmodi geben die Art des Zugriffs an, den eine Transaktion benötigt. Sie werden oft durch Namen wie RowExclusiveLock, ShareLock, ExclusiveLock usw. dargestellt.

Identifizieren von Sperrkonflikten und blockierenden Sitzungen

Sperrkonflikte treten auf, wenn mehrere Transaktionen auf Sperren warten, die von anderen Transaktionen gehalten werden. Dies kann Ihre Anwendung erheblich verlangsamen. Die Systemansicht pg_locks ist Ihr primäres Werkzeug zur Diagnose dieser Probleme.

Verwenden von `pg_locks`

Die Ansicht pg_locks liefert Informationen über alle aktiven Sperren im Datenbanksystem. Sie ist unschätzbar, um zu verstehen, welche Sitzungen Sperren halten und welche warten.

Hier ist eine häufige Abfrage zur Identifizierung blockierender Sitzungen:

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_locks.locktype AS blocked_locktype,
    blocked_locks.virtualtransaction AS blocked_vtx,
    blocked_locks.mode AS blocked_mode,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_locks.locktype AS blocking_locktype,
    blocking_locks.virtualtransaction AS blocking_vtx,
    blocking_locks.mode AS blocking_mode,
    blocking_activity.query AS blocking_statement
FROM
    pg_catalog.pg_locks blocked_locks
JOIN
    pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN
    pg_catalog.pg_locks blocking_locks
ON
    blocking_locks.locktype = blocked_locks.locktype AND
    blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND
    blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND
    blocking_locks.offset IS NOT DISTINCT FROM blocked_locks.offset AND
    blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND
    blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN
    pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted
    AND blocking_locks.pid != blocked_locks.pid;

Erklärung der Abfrage:

Wir verbinden pg_locks zweimal mit pg_stat_activity: einmal für den blockierten Prozess und einmal für den blockierenden Prozess.
Die WHERE NOT blocked_locks.granted-Klausel filtert nach Sperren, auf die derzeit gewartet wird.
blocking_locks.pid != blocked_locks.pid stellt sicher, dass wir keine Sitzung melden, die sich selbst blockiert.
Die Join-Bedingungen auf pg_locks gleichen Sperren auf derselben Ressource ab.

Interpretation der Ausgabe

blocked_pid / blocking_pid: Die Prozess-IDs (PIDs) der beteiligten Sitzungen.
blocked_user / blocking_user: Die Benutzer, die diesen PIDs zugeordnet sind.
blocked_statement / blocking_statement: Die SQL-Abfragen, die derzeit ausgeführt werden oder warten.
blocked_mode / blocking_mode: Die angeforderten und gehaltenen Sperrmodi.

Wenn diese Abfrage Zeilen zurückgibt, liegt ein Sperrkonflikt vor. Die blocking_pid hält eine Sperre, auf die die blocked_pid wartet.

Verstehen und Beheben von Deadlocks

Ein Deadlock tritt auf, wenn zwei oder mehr Transaktionen jeweils auf eine Sperre warten, die von einer anderen Transaktion im Zyklus gehalten wird, wodurch eine zirkuläre Abhängigkeit entsteht, die keine von ihnen allein auflösen kann. PostgreSQL erkennt Deadlocks und löst sie automatisch auf, indem es eine der Transaktionen abbricht, normalerweise diejenige, die den Deadlock verursacht und die geringste Arbeit geleistet hat.

Häufige Deadlock-Szenarien

Zwei Transaktionen aktualisieren verschiedene Zeilen in verschiedenen Tabellen in umgekehrter Reihenfolge:
- Transaktion A: Aktualisiert Zeile X in Tabelle 1, versucht dann, Zeile Y in Tabelle 2 zu aktualisieren.
- Transaktion B: Aktualisiert Zeile Y in Tabelle 2, versucht dann, Zeile X in Tabelle 1 zu aktualisieren. Wenn Transaktion A Zeile X sperrt und Transaktion B Zeile Y sperrt, kommt es zu einem Deadlock, wenn sie versuchen, die vom anderen gehaltene Sperre zu erwerben.
UPDATE gefolgt von SELECT ... FOR UPDATE:
- Transaktion A: Aktualisiert eine Zeile.
- Transaktion B: Führt SELECT ... FOR UPDATE auf derselben Zeile aus. Wenn das UPDATE immer noch eine exklusive Zeilensperre hält, während das SELECT FOR UPDATE versucht, eine gemeinsame Sperre zu erwerben, und andere Abhängigkeiten bestehen, kann ein Deadlock auftreten.

Erkennen von Deadlocks

PostgreSQL protokolliert Deadlock-Informationen in seinem Server-Log. Sie sehen normalerweise Nachrichten wie:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5679.
Process 5679 waits for ExclusiveLock on tuple (0,1) of relation 12345; blocked by process 1234.
HINT:  See server log for detail.

PostgreSQL wählt automatisch eine Transaktion zum Abbrechen aus, damit die anderen fortfahren können. Verlassen Sie sich nicht auf pg_stat_activity im Nachhinein für die vollständige Geschichte; wenn Sie nachschauen, ist die abgebrochene Anweisung möglicherweise bereits verschwunden. Das Server-Log ist normalerweise die beste Aufzeichnung des Zyklus.

Beheben von Deadlocks

Wenn ein Deadlock erkannt wird und PostgreSQL ihn durch Abbrechen einer Transaktion auflöst:

Identifizieren Sie das Opfer: Überprüfen Sie die PostgreSQL-Logs auf die Meldung deadlock detected. Sie gibt an, welcher Prozess abgebrochen wurde.
Wiederholen Sie die abgebrochene Transaktion: Die Anwendung, die den SQLSTATE 40P01 (deadlock_detected) erhält, sollte die gesamte Transaktion wiederholen, wenn die Operation sicher wiederholt werden kann. Das Wiederholen nur der letzten Anweisung kann den Anwendungszustand inkonsistent hinterlassen.
Analysieren Sie die Ursache: Der Schlüssel zur Lösung besteht darin, zukünftige Deadlocks zu verhindern. Dies beinhaltet das Verständnis, warum der Deadlock aufgetreten ist (wie in den häufigen Szenarien beschrieben), und die Anpassung der Anwendungslogik oder des Datenbankdesigns.

Techniken zur Vermeidung von Sperrkonflikten und Deadlocks

Vorbeugen ist besser als Heilen. Die Implementierung von Strategien zur Minimierung von Sperrkonflikten und zur Vermeidung von Deadlock-Situationen ist entscheidend für eine leistungsstarke PostgreSQL-Datenbank.

1. Konsistente Transaktionsreihenfolge

Regel: Greifen Sie immer in derselben Reihenfolge auf Ressourcen (Tabellen, Zeilen) zu und ändern Sie sie in allen Transaktionen. Wenn mehrere Transaktionen TableA und TableB aktualisieren müssen, stellen Sie sicher, dass sie immer zuerst TableA und dann TableB aktualisieren, oder umgekehrt, auf konsistente Weise.
Beispiel: Wenn eine Transaktion Datensätze in users und orders aktualisieren muss, führen Sie Operationen immer zuerst an users und dann an orders durch. Vermeiden Sie Szenarien, in denen eine Transaktion zuerst users und dann orders aktualisiert, während eine andere zuerst orders und dann users aktualisiert.

2. Transaktionsdauer minimieren

Regel: Halten Sie Transaktionen so kurz wie möglich. Je länger eine Transaktion geöffnet ist, desto mehr Sperren hält sie, was die Wahrscheinlichkeit von Konflikten erhöht.
Aktion: Führen Sie nur notwendige Datenbankoperationen innerhalb einer Transaktion durch. Verlagern Sie nicht-datenbankbezogene Arbeiten (z. B. externe API-Aufrufe, komplexe Berechnungen, die nicht vom Transaktionszustand abhängen) außerhalb der Transaktionsgrenze.

3. Angemessene Isolationsstufen verwenden

Regel: Verstehen und wählen Sie die richtige Transaktionsisolationsstufe. PostgreSQL bietet:
- READ UNCOMMITTED (in PostgreSQL durch READ COMMITTED simuliert)
- READ COMMITTED (Standard)
- REPEATABLE READ
- SERIALIZABLE
Aktion: Der Standard READ COMMITTED bietet gute Leistung und verhindert Dirty Reads. REPEATABLE READ und SERIALIZABLE bieten stärkere Konsistenz, können aber zu mehr serialization_failure-Fehlern (die im Wesentlichen Deadlocks für Snapshot-Isolation sind) und möglicherweise mehr Sperrkonflikten führen. Verwenden Sie sie nur, wenn es unbedingt notwendig ist.

4. Abfragen und Indizes optimieren

Regel: Langsame Abfragen halten Sperren länger. Stellen Sie sicher, dass Ihre Abfragen effizient und gut indiziert sind.
Aktion: Verwenden Sie EXPLAIN ANALYZE, um langsame Abfragen zu identifizieren. Fügen Sie geeignete Indizes hinzu, um den Datenabruf zu beschleunigen, insbesondere für WHERE-Klauseln und JOIN-Bedingungen.

5. `SELECT ... FOR UPDATE` sparsam verwenden

Regel: SELECT ... FOR UPDATE sperrt Zeilen für die Dauer der Transaktion. Dies ist leistungsstark, um Wettlaufsituationen zu verhindern, kann aber auch eine Hauptquelle für Konflikte sein.
Aktion: Verwenden Sie es nur, wenn Sie Zeilen wirklich sperren müssen, um zu verhindern, dass sie von anderen Transaktionen geändert werden, bevor Ihre Transaktion ihre Arbeit abgeschlossen hat. Überlegen Sie, ob Advisory Locks für bestimmte Szenarien besser geeignet sein könnten.

6. Advisory Locks

Regel: Für Sperren auf Anwendungsebene oder komplexere Synchronisationsanforderungen, die nicht direkt Datenbankobjektsperren entsprechen, können die Advisory Locks von PostgreSQL ein leistungsstarkes Werkzeug sein.
Aktion: Verwenden Sie Funktionen wie pg_advisory_lock(), pg_advisory_lock_shared() und pg_advisory_unlock(), um benutzerdefinierte Sperrmechanismen zu implementieren. Diese Sperren werden nicht automatisch vom Deadlock-Erkennungsmechanismus erkannt, daher muss die Anwendungslogik sie sorgfältig verwalten.

7. Batch-Operationen

Regel: Anstatt viele einzelne UPDATE- oder DELETE-Anweisungen auszugeben, sollten Sie erwägen, sie in einer einzigen Anweisung zu bündeln oder COPY für Bulk-Lade-/Update-Vorgänge zu verwenden, wo möglich.
Aktion: Eine einzelne UPDATE-Anweisung kann Sperren möglicherweise effizienter erwerben als eine Schleife einzelner UPDATEs. Analysieren Sie das Sperrverhalten Ihrer Batch-Operationen.

Ein praktischer Triage-Ablauf

Wenn ein Vorfall aktiv ist, beginnen Sie mit den wartenden Sitzungen, nicht mit der ältesten Theorie in Ihrem Kopf:

SELECT
    now() - a.query_start AS waiting_for,
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

Wenn eine blockierende PID immer wieder auftaucht, überprüfen Sie sie:

SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE pid = 12345;

Der Ausdruck, auf den Sie achten sollten, ist idle in transaction. Diese Sitzung führt keine aktive nützliche Datenbankarbeit durch, kann aber dennoch Sperren halten. Dies kommt oft von Anwendungscode, der eine Transaktion öffnet, eine Abfrage durchführt, eine externe API aufruft und erst nach der Rückkehr der API festschreibt. Verlagern Sie den externen Aufruf nach Möglichkeit außerhalb der Transaktion.

Brechen Sie vorsichtig ab. SELECT pg_cancel_backend(pid) fordert die aktuelle Abfrage zum Stoppen auf. SELECT pg_terminate_backend(pid) beendet die Sitzung und rollt ihre offene Transaktion zurück. In einem Produktionsvorfall kann das Beenden des Blockers die richtige Entscheidung sein, aber erfassen Sie zuerst die Abfrage und das Transaktionsalter, damit Sie später den Codepfad reparieren können.

Eine weitere Gewohnheit hilft nach dem Vorfall: Speichern Sie die blockierende Abfrage, die blockierte Abfrage und die Transaktionsgrenzen aus den Anwendungsprotokollen. Die SQL-Anweisung allein reicht oft nicht aus. Ein harmloses UPDATE users SET last_seen_at = now() kann zum Blocker werden, wenn es in einer Transaktion sitzt, die auch auf eine Zahlungs-API wartet. Deadlock-Behebungen liegen normalerweise auf der Ebene des Transaktionsflusses, nicht innerhalb einer isolierten Anweisung.

Die dauerhaften Lösungen sind normalerweise einfach: Halten Sie Transaktionen kurz, greifen Sie auf gemeinsam genutzte Ressourcen in einer konsistenten Reihenfolge zu, fügen Sie Indizes hinzu, die verhindern, dass Updates zu viel scannen, und stellen Sie sicher, dass Anwendungswiederholungen 40P01 und Serialisierungsfehler sauber behandeln. PostgreSQL kann einen Deadlock erkennen, aber es kann das Transaktionsmuster nicht für Sie neu entwerfen. Dieser Teil gehört in die Anwendungs- und Schemaüberprüfung, nachdem der Vorfall abgeklungen ist.