Lock-Waits und Deadlocks in PostgreSQL diagnostizieren mit pg_locks, pg_blocking_pids und lock_timeout

Ziel

„Die Datenbank hängt" innerhalb von Minuten in eine benannte blockierende Sitzung, die Anweisung, die das Lock hält, und eine Entscheidung darüber, was zu tun ist, verwandeln.

Voraussetzungen

Eine Rolle mit pg_read_all_stats oder Superuser-Rechten, um die Anweisungen anderer Sitzungen zu sehen; log_lock_waits = on, damit Wartezeiten, die länger als deadlock_timeout (standardmässig eine Sekunde) dauern, mit beiden benannten Parteien ins Server-Log geschrieben werden.

Schritte

1. Die Wartenden auflisten: SELECT pid, wait_event_type, wait_event, state, xact_start, query FROM pg_stat_activity WHERE wait_event_type = 'Lock'.
2. Deren Blockierer finden: SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0. Die Dokumentation empfiehlt diese Funktion gegenüber einem Self-Join von pg_locks, weil eine solche Abfrage kodieren müsste, welche Lock-Modi in Konflikt stehen, und die View die Reihenfolge der Warteschlange nicht offenlegt.
3. Die Blockierer in pg_stat_activity nachschlagen: state (idle in transaction bedeutet, der Client hält die Transaktion offen, während er etwas anderes tut), xact_start, und query, was die letzte Anweisung ist, nicht notwendigerweise diejenige, die das Lock genommen hat.
4. Bei Bedarf ermitteln, welches Lock umstritten ist: SELECT locktype, relation::regclass, mode, granted, waitstart FROM pg_locks WHERE pid IN (...). ACCESS EXCLUSIVE, das von den meisten Formen von ALTER TABLE genommen wird, steht mit jedem anderen Modus in Konflikt, einschliesslich eines einfachen SELECT, sodass eine wartende DDL-Anweisung jeden späteren Leser der Tabelle blockiert.
5. Entscheiden: pg_cancel_backend(pid) stoppt die aktuelle Anweisung des Blockierers; pg_terminate_backend(pid) beendet dessen Sitzung und macht die Transaktion rückgängig. Beides zeigt sich in der Anwendung als Fehler, daher festhalten, wer aus welchem Grund abgebrochen wurde.
6. Bei Deadlocks das Server-Log lesen: Der Fehler benennt beide Prozesse und ihre Anweisungen. Die Dokumentation hält fest, dass PostgreSQL Deadlocks automatisch erkennt und eine der Transaktionen abbricht, dass nicht vorhersehbar ist, welche, und dass die beste Abwehr darin besteht, Locks auf mehreren Objekten in einer konsistenten Reihenfolge zu nehmen. Die abgebrochene Transaktion in der Anwendung erneut versuchen.
7. Dem nächsten vorbeugen: Migrationen und Wartung mit SET lock_timeout = '...' und einer Wiederholungsschleife ausführen, idle_in_transaction_session_timeout für Anwendungsrollen setzen und mehrzeilige Updates in Batch-Jobs nach Primärschlüssel sortieren.

Erwartetes Ergebnis

Jedes Hängenbleiben wird einem Halter und einer Anweisung zugeordnet; DDL wartet nicht mehr unbegrenzt hinter langen Transaktionen; Deadlock-Fehler werden erneut versucht, statt die Nutzer zu erreichen.

Grenzen und Prüfbasis

pg_blocking_pids spiegelt den Moment des Aufrufs wider, und Ketten ändern sich schnell. Das Warten auf ein Zeilen-Lock erscheint in pg_locks als Warten auf eine transactionid, was ohne die Anweisung des Blockierers verwirrend ist. Das Vorgehen folgt der zitierten Dokumentation; zu Zeiten wird keine Aussage gemacht.