Technologie

Wie kann ich ungewollte Sperren (Locks) in PostgreSQL erkennen und auflösen?

Melden
  1. Einleitung
  2. Erkennen von Sperren in PostgreSQL
  3. Analyse und Interpretation der Sperrinformationen
  4. Auflösen ungewollter Sperren
  5. Vorbeugung gegen ungewollte Sperren
  6. Zusammenfassung

Einleitung

In PostgreSQL können Sperren (Locks) genutzt werden, um die Konsistenz der Datenbank zu gewährleisten, indem sie konkurrierende Zugriffe auf dieselben Daten steuern. Allerdings können ungewollte oder lange gehaltene Sperren zu Problemen wie Blockierungen und Performanceeinbußen führen. Daher ist es wichtig, solche Sperren zu erkennen und gegebenenfalls aufzulösen.

Erkennen von Sperren in PostgreSQL

PostgreSQL bietet über das Systemkatalog-View pg_locks und die Ansicht pg_stat_activity verschiedene Möglichkeiten, aktive Sperren zu überwachen. Das View pg_locks zeigt alle aktuellen Sperren im System inklusive der Informationen über die Art der Sperre, das betroffene Objekt und den Besitzer der Sperre. Um zusätzliche Kontextinformationen wie die zugehörige Session oder das aktuelle SQL-Statement zu erhalten, kann man die pg_locks-Daten mit pg_stat_activity verknüpfen.

Ein typischer SQL-Befehl zur Erkennung von blockierenden und blockierten Prozessen könnte folgendermaßen aussehen:

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process, blocked_activity.state_change AS blocked_sinceFROM pg_catalog.pg_locks blocked_locksJOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pidJOIN 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.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pidJOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pidWHERE NOT blocked_locks.granted;

Diese Abfrage zeigt Prozesse an, die auf Sperren warten (blockiert sind), und die Prozesse, die diese Sperren aktuell halten (blockieren).

Analyse und Interpretation der Sperrinformationen

Nach dem Erkennen der blockierenden und blockierten Prozesse sollte man prüfen, welche Aktionen diese Prozesse durchführen. Sind blockierende Prozesse lang laufende Transaktionen oder Abfragen? Ist vielleicht ein Prozess in einer Transaktion hängen geblieben, die vergessen wurde zu Committen oder Rollbacken? Oft helfen hier die Angaben zu den zuletzt ausgeführten Statements und die ursprüngliche Anfrage, um die Ursache einzugrenzen.

Auflösen ungewollter Sperren

Das Auflösen von Sperren erfolgt letztlich dadurch, dass blockierende Sessions beendet oder deren Transaktionen abgeschlossen werden. Bevor man jedoch Prozesse abbricht, sollte man überlegen, ob dies den Datenbankzustand beeinträchtigen kann. Um einen Prozess gezielt zu beenden, kann man die PostgreSQL-Funktion pg_terminate_backend() verwenden, wobei als Parameter die PID des Prozesses übergeben wird.

Zum Beispiel:

SELECT pg_terminate_backend(12345);

Dies beendet die Session mit der Prozess-ID 12345 und löst deren Sperren auf. Eine mildere Alternative ist pg_cancel_backend(), welche die Ausführung des aktuellen Statements abbricht, jedoch die Verbindung offen lässt. Dies kann sinnvoll sein, wenn nur bestimmte langlaufende Abfragen ein Problem darstellen, ohne die gesamte Session zu beenden.

Vorbeugung gegen ungewollte Sperren

Langfristig hilft es, Sperren zu minimieren, indem Transaktionen so kurz wie möglich gehalten und komplexe Abfragen optimiert werden. Der Einsatz von Monitoring-Tools und regelmäßigem Auswerten von Locks und langen Transaktionen unterstützt dabei. Zusätzlich kann die Einstellung von Deadlock-Timeouts in PostgreSQL sinnvoll sein, um sogenannte Deadlocks automatisch zu erkennen und aufzulösen.

Zusammenfassung

Um ungewollte Sperren in PostgreSQL zu erkennen, sollten die Systemansichten pg_locks und pg_stat_activity genutzt und kombiniert abgefragt werden, um blockierte und blockierende Prozesse zu identifizieren. Die Analyse der aktiven Transaktionen und Statements ermöglicht die Ursachenfindung. Zum Auflösen von Sperren stehen Funktionen wie pg_terminate_backend() zur Verfügung, die Prozesse beenden und somit Sperren freigeben. Vorbeugend empfiehlt sich eine bewusste Gestaltung von Transaktionen und ein aktives Monitoring, um die Datenbank performant und konfliktfrei zu halten.

0

Kommentare