Monitoring von Sperren und Konflikten

Stell dir vor, du arbeitest in einem Büro, wo alle Türen blockiert sind, weil jemand seinen Schlüssel im Raum vergessen hat. Genau so funktionieren Sperren in PostgreSQL. Wenn ein Query oder eine Transaktion eine Ressource sperrt, hängen alle anderen Operationen, die auf dieselbe Ressource zugreifen wollen, davon ab, dass die Sperre freigegeben wird. Das kann zu Verzögerungen, Konflikten und im schlimmsten Fall zum Stillstand des Systems führen.

Wann entstehen Sperren?

Sperren (Locks) in PostgreSQL werden genutzt, um den gleichzeitigen Zugriff auf Daten zu steuern. Sie entstehen:

1. Beim Ausführen von Schreiboperationen: UPDATE, DELETE, INSERT.
2. Wenn Transaktionen Ressourcen länger halten, als nötig wäre.
3. Bei Konflikten zwischen verschiedenen Transaktionen, die auf dieselbe Ressource zugreifen wollen.

Eine echte Datenbank ist ein "Schlachtfeld" um Ressourcen, und selbst wenn du denkst, dass dein System perfekt läuft, kann eine unvorsichtige Transaktion alles "lahmlegen", wie ein missglückter Merge in Git.

Tools zur Analyse von Sperren: pg_locks

pg_locks ist ein System-View in PostgreSQL, das die aktuellen Sperren zeigt, die von Transaktionen gehalten oder erwartet werden. Es beantwortet die Frage: "Wer hält die Sperre und wer wartet?"

Wichtige Felder in pg_locks:

• locktype: Typ der Sperre (zum Beispiel relation, transaction, page, tuple).
• database: Datenbank-ID.
• relation: Tabellen-ID (wenn die Sperre mit einer Tabelle zusammenhängt).
• mode: Sperrmodus (zum Beispiel RowExclusiveLock, AccessShareLock).
• granted: Flag, ob die Sperre vergeben wurde (true) oder ob die Transaktion noch wartet (false).

Hinweis: PostgreSQL verwendet einen sogenannten "hierarchischen Sperrmodus". Das bedeutet, dass verschiedene Operationen weniger strenge Sperren setzen können (zum Beispiel AccessShareLock fürs Lesen) oder strengere (ExclusiveLock für Strukturänderungen an Tabellen).

Beispiel: Alle aktuellen Sperren anzeigen

SELECT *
FROM pg_locks;

Aber wenn du einfach alles aus pg_locks ausgibst, bekommst du zu viel Lärm. Lass uns was Sinnvolleres probieren!

Beispiel: Sperren, die noch nicht vergeben wurden (also Transaktionen warten)

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

Was passiert hier?

• Wir filtern die Einträge, bei denen granted = false ist, also die Sperre noch nicht vergeben wurde.
• relation::regclass wandelt die Tabellen-ID in den Tabellennamen um, damit es lesbarer ist.

Die Ausgabe könnte so aussehen:

Mit diesen Queries kannst du herausfinden, welche Tabelle/Ressource blockiert ist und welche Transaktion der Grund sein könnte.

Konfliktanalyse: pg_blocking_pids()

Sperren sind schon nervig genug, aber was tun, wenn eine Transaktion eine andere blockiert? PostgreSQL bietet eine praktische Funktion, um den "Schuldigen" zu finden: pg_blocking_pids().

Die Funktion pg_blocking_pids() gibt eine Liste von Prozess-IDs (pid) zurück, die die aktuelle Transaktion blockieren.

Beispiel: Transaktionen finden, die andere blockieren

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Was passiert hier?

• Wir nutzen das View pg_stat_activity, um aktive Prozesse im System zu holen.
• Die Funktion pg_blocking_pids(pid) gibt für jede pid die blockierenden Prozesse zurück. Wenn die Liste nicht leer ist (Länge größer 0), ist der Prozess blockiert.

Beispiel-Ausgabe:

Die Transaktion mit pid = 4567 wird von den Prozessen 1234 und 5678 blockiert. Wir haben unsere "Schuldigen" gefunden.

Blockierende Prozesse beenden

Wenn du die blockierenden Prozesse identifiziert hast, kannst du sie mit pg_terminate_backend() beenden:

SELECT pg_terminate_backend(1234); -- Wir "killen" Prozess 1234

Aber Vorsicht! Das erzwungene Beenden eines Prozesses kann dazu führen, dass die aktuelle Transaktion zurückgesetzt wird. Nutze dieses Tool als "Nuklear-Button" nur im Notfall.

Praxis: Sperren-Analyse-Szenario

Stell dir vor, wir haben eine Uni-Datenbank mit den Tabellen students und enrollments. Mehrere Transaktionen versuchen gleichzeitig, Daten in die Tabelle enrollments zu schreiben, und wir stoßen auf Sperren.

1. Sperren identifizieren:

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

2. Blockierende Prozesse bestimmen:

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. Sperren beseitigen:

Wir beenden einen der Konflikt-Prozesse:

SELECT pg_terminate_backend(1234); -- Prozess 1234 beenden

Hinweis: Bevor du einen Prozess "killst", schau dir an, warum die Sperre entstanden ist. Vielleicht solltest du die Transaktionslogik überdenken.

Typische Fehler und wie du sie vermeidest

Sperren entstehen oft durch schlechtes Transaktionsmanagement. Zum Beispiel:

Fehler: Eine Transaktion hält eine Sperre zu lange, ohne etwas zu tun (Status "idle in transaction").

Lösung: Überwache den Status der Transaktionen mit pg_stat_activity und beende "hängende" Transaktionen.

SELECT pid, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Fehler: Du hast vergessen, Indexe in Queries zu nutzen, was zu Sperren auf ganzen Tabellen führt.

Lösung: Optimiere deine Queries, indem du Indexe für häufig genutzte Bedingungen hinzufügst.

Die "Wer wartet auf wen"-Tabelle

Zur besseren Diagnose kannst du einen Abhängigkeitsbaum bauen, der zeigt, welche Transaktion auf welche wartet:

WITH RECURSIVE blocking_tree AS (
  SELECT pid, pg_blocking_pids(pid) AS blocked_by
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid)
  FROM pg_stat_activity a
  JOIN blocking_tree b ON a.pid = ANY(b.blocked_by)
)
SELECT pid, blocked_by FROM blocking_tree;

Ergebnis:

Hier siehst du, dass Prozess 5678 Prozess 1234 blockiert, und der wiederum blockiert 4567.