Lock Contention diagnostizieren: blockierende Sessions finden

1. Was Lock Contention von langsamen Queries unterscheidet

Lock Contention entsteht, wenn mehrere Transaktionen gleichzeitig auf dieselben Datenzeilen oder Tabellen zugreifen wollen und sich dabei gegenseitig blockieren. Anders als bei einer schlicht langsamen Query, bei der die Datenbank tatsaechlich rechnet, wartet eine von Lock Contention betroffene Query untaetig darauf, dass eine andere Transaktion ihre Sperre freigibt. Das Symptom sieht aehnlich aus, die Ursache und damit auch die Behebung sind aber grundverschieden.

Der entscheidende diagnostische Unterschied: Bei Lock Contention zeigt ein Explain-Plan meist einen effizienten Zugriffspfad, die Query waere also isoliert betrachtet schnell. Erst der Blick auf gleichzeitig laufende Sessions zeigt, dass eine andere Transaktion dieselbe Zeile oder Tabelle bereits gesperrt haelt. Wer Lock Contention diagnostizieren will, muss deshalb weg vom Blick auf die einzelne Query hin zum Blick auf die gesamte Session-Landschaft zum Zeitpunkt des Vorfalls.

In der Praxis zeigt sich Lock Contention oft als ploetzlicher Anstieg der Antwortzeit bei niedriger CPU- und I/O-Auslastung. Die Datenbank ist nicht ueberlastet, sie wartet. Genau dieses Muster, hohe Latenz bei niedriger Ressourcenauslastung, ist das verlaesslichste erste Indiz dafuer, dass Sperren statt Rechenleistung das eigentliche Problem sind.

2. Blockierende Sessions in MySQL finden

MySQL mit der InnoDB-Engine bietet ueber das performance_schema direkten Einblick in aktuelle Sperren und deren Beziehungen. Die Tabelle data_lock_waits zeigt genau, welche Transaktion auf welche andere wartet, inklusive der jeweiligen Thread- und Transaktions-IDs. Kombiniert mit data_locks laesst sich daraus die exakt gesperrte Ressource ablesen, sei es eine einzelne Zeile oder eine ganze Tabelle.

Die aeltere, aber weiterhin nuetzliche Alternative ist SHOW ENGINE INNODB STATUS, dessen Abschnitt TRANSACTIONS laufende Transaktionen mit ihren gehaltenen und angeforderten Sperren auflistet. Fuer eine schnelle Live-Diagnose bei Lock Contention ist dieser Befehl oft der pragmatischste erste Schritt, weil er ohne zusaetzliche Konfiguration sofort verfuegbar ist.

-- MySQL: wer blockiert wen? (performance_schema, MySQL 8.0+)
SELECT
    waiting_pid.thread_id      AS waiting_thread,
    waiting_pid.processlist_id AS waiting_connection,
    blocking_pid.thread_id     AS blocking_thread,
    blocking_pid.processlist_id AS blocking_connection,
    w.blocking_engine_transaction_id AS blocking_trx_id,
    w.requesting_engine_transaction_id AS waiting_trx_id
FROM performance_schema.data_lock_waits w
JOIN performance_schema.threads waiting_pid
  ON w.requesting_thread_id = waiting_pid.thread_id
JOIN performance_schema.threads blocking_pid
  ON w.blocking_thread_id = blocking_pid.thread_id;

-- Die konkrete gesperrte Ressource anzeigen
SELECT object_schema, object_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE engine_transaction_id IN (
    SELECT blocking_engine_transaction_id FROM performance_schema.data_lock_waits
);

3. pg_locks und pg_stat_activity in PostgreSQL

PostgreSQL loest dieselbe Diagnoseaufgabe ueber die Systemsicht pg_locks in Kombination mit pg_stat_activity. pg_locks listet alle aktuell gehaltenen und angeforderten Sperren im System, waehrend pg_stat_activity die zugehoerigen Session-Details wie Query-Text, Nutzer und Verbindungszeit liefert. Der Self-Join ueber pg_locks zeigt, welche wartende Sperre von welcher haltenden Sperre blockiert wird, ein Muster, das jeder erfahrene PostgreSQL-Administrator kennt, um Lock Contention zu diagnostizieren.

Ein besonders nuetzlicher Kunstgriff ist die Funktion pg_blocking_pids(), die seit PostgreSQL 9.6 direkt eine Liste der blockierenden Prozess-IDs fuer eine gegebene Prozess-ID zurueckgibt, ohne den manuellen Self-Join schreiben zu muessen. Damit laesst sich in wenigen Sekunden feststellen, ob eine haengende Session tatsaechlich durch Lock Contention blockiert wird oder ob eine andere Ursache vorliegt.

-- PostgreSQL: blockierende Sessions mit vollem Kontext finden
SELECT
    blocked.pid       AS blocked_pid,
    blocked.query     AS blocked_query,
    blocking.pid      AS blocking_pid,
    blocking.query    AS blocking_query,
    now() - blocked.query_start AS waiting_since
FROM pg_stat_activity blocked
JOIN pg_locks bl
  ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks kl
  ON kl.locktype = bl.locktype
 AND kl.database IS NOT DISTINCT FROM bl.database
 AND kl.relation IS NOT DISTINCT FROM bl.relation
 AND kl.page IS NOT DISTINCT FROM bl.page
 AND kl.tuple IS NOT DISTINCT FROM bl.tuple
 AND kl.pid != bl.pid
 AND kl.granted
JOIN pg_stat_activity blocking
  ON blocking.pid = kl.pid
ORDER BY waiting_since DESC;

-- Kompakter mit pg_blocking_pids() (PostgreSQL 9.6+)
SELECT pid, query, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

4. Row-Level-Locks vs. Table-Level-Locks

Sowohl InnoDB als auch PostgreSQL bevorzugen feingranulare Row-Level-Locks gegenueber Table-Level-Locks, weil sie parallele Zugriffe auf unterschiedliche Zeilen derselben Tabelle erlauben. Trotzdem eskalieren bestimmte Operationen zu breiteren Sperren: Ein ALTER TABLE ohne Online-DDL-Unterstuetzung, ein fehlender Index auf einer Fremdschluesselspalte, oder ein Full-Table-Scan innerhalb einer Transaktion mit SELECT FOR UPDATE koennen weit mehr Zeilen sperren, als die Anwendungslogik eigentlich benoetigt.

Ein haeufig uebersehener Fall bei InnoDB: Gap-Locks und Next-Key-Locks sperren nicht nur existierende Zeilen, sondern auch Luecken zwischen Indexwerten, um Phantom Reads im Isolation Level Repeatable Read zu verhindern. Das bedeutet, dass eine scheinbar harmlose WHERE-Bedingung mit Bereichspruefung deutlich mehr Lock Contention erzeugen kann, als der Entwickler beim Schreiben der Query erwartet hat. Wer das nicht kennt, diagnostiziert stundenlang die falsche Zeile als Ursache.

5. Wartezeiten messen und Trends erkennen

Einzelne Momentaufnahmen von pg_locks oder data_lock_waits zeigen nur den aktuellen Zustand, nicht die Entwicklung ueber Zeit. Um Lock Contention systematisch zu diagnostizieren, muss die Wartezeit kontinuierlich erfasst werden, etwa durch periodisches Sampling alle paar Sekunden mit Speicherung in einer History-Tabelle. So entsteht ein Bild davon, ob Lock-Wartezeiten zu bestimmten Tageszeiten systematisch ansteigen, etwa waehrend eines naechtlichen Batch-Jobs, der mit dem Live-Traffic um dieselben Zeilen konkurriert.

PostgreSQL bietet mit log_lock_waits eine eingebaute Alternative zum manuellen Sampling: Aktiviert, protokolliert der Server automatisch jede Wartezeit, die deadlock_timeout ueberschreitet, direkt ins Server-Log. Das liefert eine passiv gesammelte, aber sehr zuverlaessige Historie von Lock Contention-Vorfaellen, ohne dass ein externes Monitoring-Tool noetig waere.

-- postgresql.conf: Wartezeiten automatisch protokollieren
log_lock_waits = on
deadlock_timeout = '1s'

-- Periodisches Sampling in eine History-Tabelle (z.B. per Cron-Job alle 5s)
INSERT INTO lock_wait_history (sampled_at, blocked_pid, blocking_pid, wait_seconds)
SELECT
    now(),
    blocked.pid,
    blocking.pid,
    EXTRACT(EPOCH FROM (now() - blocked.query_start))
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

6. Lange Transaktionen als haeufigste Ursache

Die mit Abstand haeufigste Ursache fuer Lock Contention in der Praxis sind lange offene Transaktionen. Eine Transaktion, die eine Zeile fruehzeitig sperrt und danach auf einen externen API-Aufruf, eine Nutzerinteraktion oder ein weiteres, unabhaengiges Statement wartet, haelt die Sperre viel laenger, als es die eigentliche Datenaenderung erfordern wuerde. Jede weitere Transaktion, die dieselbe Zeile beruehren will, muss in dieser Zeit warten.

Das Gegenmittel ist konzeptionell einfach, in der Umsetzung aber diszipliniert: Transaktionen so kurz wie moeglich halten, alle externen Aufrufe (HTTP-Requests, Dateisystem-Operationen, Warteschlangen-Publish) ausserhalb der Transaktion ausfuehren, und Sperren so spaet wie moeglich im Transaktionsverlauf anfordern. Wer Lock Contention diagnostizieren will und dabei wiederholt dieselbe Transaktion als Blocker findet, sollte als Erstes deren Laufzeit und die darin enthaltenen Operationen pruefen, nicht die Query selbst optimieren.

7. Fehlende Indizes als versteckter Lock-Verstaerker

Ein fehlender Index verstaerkt Lock Contention auf zwei Arten gleichzeitig. Erstens dauert die Query laenger, weil ein Full-Table-Scan statt eines Index-Lookups noetig ist, was die Sperrzeit automatisch verlaengert. Zweitens sperrt InnoDB ohne passenden Index tendenziell mehr Zeilen als noetig, weil es waehrend des Scans jede geprueft Zeile zumindest kurzzeitig sperren muss, um konsistente Ergebnisse innerhalb der Transaktion zu garantieren.

Besonders kritisch sind fehlende Indizes auf Fremdschluesselspalten: Ein DELETE oder UPDATE auf der Elterntabelle prueft bei referentieller Integritaet die Kindtabelle, und ohne Index auf der Fremdschluesselspalte geschieht das mit einem Full-Table-Scan der Kindtabelle, was die Sperrzeit drastisch verlaengert und die Wahrscheinlichkeit fuer Lock Contention mit parallelen Schreiboperationen deutlich erhoeht.

8. Lock Contention im Anwendungscode aufloesen

Diagnose allein loest das Problem nicht, sie liefert nur die Grundlage fuer die richtige Massnahme. Die wirksamsten Gegenmassnahmen gegen Lock Contention setzen im Anwendungscode an: eine feste, konsistente Reihenfolge beim Sperren mehrerer Zeilen ueber alle Transaktionen hinweg, kuerzere Transaktionsgrenzen, und der bewusste Einsatz von SELECT ... FOR UPDATE SKIP LOCKED in Queue-aehnlichen Workloads, um wartende Sessions komplett zu vermeiden, statt sie nur zu verkuerzen.

Fuer Reporting- oder Analyse-Queries, die keine transaktionale Konsistenz mit dem aktuellsten Schreibstand benoetigen, ist READ UNCOMMITTED oder ein expliziter Snapshot-Read ueber Replikas eine wirksame Strategie, um Lock Contention mit dem primaeren Schreibpfad vollstaendig zu vermeiden. Diese Trennung von transaktionalem OLTP-Traffic und Lesezugriffen fuer Reporting ist einer der zuverlaessigsten architekturellen Hebel gegen chronische Sperrprobleme.

9. Diagnosewerkzeuge im Vergleich

Je nach Datenbanksystem stehen unterschiedliche eingebaute Werkzeuge zur Verfuegung, um Lock Contention zu diagnostizieren. Die folgende Tabelle vergleicht die wichtigsten Optionen nach Aufwand und Detailtiefe.

Fuer akute Vorfaelle sind die Live-Sichten der erste Anlaufpunkt, fuer die langfristige Trendanalyse ist historisches Sampling oder log_lock_waits unverzichtbar. Beide Ebenen gemeinsam ergeben ein vollstaendiges Bild, um Lock Contention nicht nur einmalig zu beheben, sondern strukturell zu reduzieren.

10. Zusammenfassung

Lock Contention zu diagnostizieren beginnt mit dem Verstaendnis, dass es sich nicht um ein Performance-Problem der Query selbst handelt, sondern um Wartezeit auf eine andere Transaktion. performance_schema.data_lock_waits in MySQL und pg_locks in Kombination mit pg_stat_activity in PostgreSQL zeigen direkt, welche Session welche andere blockiert. Lange Transaktionen und fehlende Indizes sind die haeufigsten Ursachen, kurze Transaktionsgrenzen und passende Indizes die zuverlaessigste Gegenmassnahme.

Historisches Sampling oder log_lock_waits verwandeln einmalige Diagnosen in wiederholbare Erkenntnisse ueber wiederkehrende Muster, etwa Konflikte zwischen Batch-Jobs und Live-Traffic. Wer Lock Contention nicht nur punktuell loescht, sondern strukturell versteht, reduziert die Haeufigkeit zukuenftiger Vorfaelle erheblich und macht die gesamte Datenbank unter Last vorhersagbarer.

11. FAQ: Lock Contention diagnostizieren