MySQL Deadlocks erkennen, analysieren und vermeiden

1. Was ein Deadlock in InnoDB konkret ist

Ein Deadlock entsteht in InnoDB, wenn zwei oder mehr Transaktionen jeweils auf eine Sperre warten, die von einer anderen der beteiligten Transaktionen gehalten wird, und dieser wechselseitige Wartezustand keinen Ausweg mehr zulässt. Transaktion A hält eine Sperre auf Zeile 1 und wartet auf Zeile 2, während Transaktion B gleichzeitig Zeile 2 hält und auf Zeile 1 wartet. Ohne Eingriff würden beide Transaktionen unendlich lange warten.

InnoDB löst diese Situation nicht durch Zufall, sondern durch einen aktiven Erkennungsmechanismus. Ein interner Deadlock-Detector durchläuft periodisch den Wait-for-Graph aller wartenden Transaktionen und erkennt Zyklen. Wird ein Zyklus gefunden, wählt InnoDB die Transaktion mit dem geringsten geschätzten Rollback-Aufwand, üblicherweise die mit weniger geänderten Zeilen, als Opfer aus und rollt sie automatisch mit einem internen ROLLBACK zurück. Die andere Transaktion kann anschließend fortfahren.

Ein Deadlock ist damit kein Zeichen einer defekten Datenbank, sondern ein normales, wenn auch unerwünschtes Ergebnis paralleler Schreibzugriffe. Die betroffene Anwendung erhält den Fehler 1213 (ER_LOCK_DEADLOCK) und muss selbst entscheiden, wie sie darauf reagiert. Dieser Artikel zeigt, wie man Deadlocks systematisch analysiert und durch bewusstes Transaktionsdesign deutlich seltener macht.

2. SHOW ENGINE INNODB STATUS: den Deadlock-Report lesen

Der wichtigste Befehl zur Diagnose eines Deadlocks ist SHOW ENGINE INNODB STATUS. Im Abschnitt LATEST DETECTED DEADLOCK protokolliert InnoDB die beiden beteiligten Transaktionen, die jeweils gehaltenen und angeforderten Sperren, sowie welche Transaktion als Opfer zurückgerollt wurde. Dieser Report zeigt für jede Transaktion die genaue SQL-Anweisung, den betroffenen Index und den Sperrtyp, was die Rekonstruktion der Ursache erheblich erleichtert.

Wichtig: InnoDB speichert nur den zuletzt erkannten Deadlock, ältere Vorfälle werden überschrieben. Für eine dauerhafte Historie sollte innodb_print_all_deadlocks = ON gesetzt werden, wodurch jeder Deadlock zusätzlich in die Error-Log-Datei geschrieben wird. Ohne diese Einstellung geht die Information verloren, sobald der nächste Deadlock auftritt, was die nachträgliche Analyse von sporadisch auftretenden Deadlocks erschwert.

-- Read the deadlock report immediately after an application
-- reports error 1213 (ER_LOCK_DEADLOCK)
SHOW ENGINE INNODB STATUS\G

-- Excerpt from the LATEST DETECTED DEADLOCK section:
-- ------------------------
-- LATEST DETECTED DEADLOCK
-- ------------------------
-- *** (1) TRANSACTION:
-- TRANSACTION 421589, ACTIVE 2 sec starting index read
-- mysql tables in use 1, locked 1
-- LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
-- MySQL thread id 42, query id 88123 updating
-- UPDATE accounts SET balance = balance - 50 WHERE id = 2
-- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-- RECORD LOCKS space id 45 page no 4 n bits 80 index PRIMARY
-- of table `shop`.`accounts` trx id 421589 lock_mode X locks rec but not gap
-- waiting
-- *** (2) TRANSACTION:
-- TRANSACTION 421590, ACTIVE 1 sec starting index read
-- UPDATE accounts SET balance = balance + 50 WHERE id = 1
-- *** (2) HOLDS THE LOCK(S):
-- RECORD LOCKS space id 45 page no 3 n bits 80 index PRIMARY
-- of table `shop`.`accounts` trx id 421590 lock_mode X locks rec but not gap
-- *** WE ROLL BACK TRANSACTION (1)

-- Persist every deadlock into the error log, not just the last one
SET GLOBAL innodb_print_all_deadlocks = ON;

3. Typische Deadlock-Muster: Cross-Order-Updates

Das häufigste Deadlock-Muster in der Praxis ist das Cross-Order-Update: Zwei Transaktionen greifen auf dieselben zwei Zeilen zu, aber in unterschiedlicher Reihenfolge. Transaktion A aktualisiert zuerst Konto 1, dann Konto 2. Transaktion B aktualisiert gleichzeitig zuerst Konto 2, dann Konto 1. Läuft beides parallel, hält jede Transaktion eine Sperre, die die andere Transaktion für ihren zweiten Schritt benötigt, ein klassischer Deadlock-Zyklus.

Dieses Muster tritt besonders häufig bei Geldtransfers, Bestandsupdates in Bestellsystemen oder Batch-Jobs auf, die mehrere Zeilen einer Tabelle in der Reihenfolge verarbeiten, in der sie in den Eingabedaten stehen, statt in einer stabilen, deterministischen Reihenfolge wie der Primary-Key-Reihenfolge. Ein zweites häufiges Muster entsteht bei UPDATE-Anweisungen mit unterschiedlichen WHERE-Bedingungen, die dieselbe Zeilenmenge über verschiedene Indizes ansprechen und dadurch in unterschiedlicher Reihenfolge sperren, selbst wenn der Anwendungscode auf den ersten Blick konsistent wirkt.

-- Session A
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- ... application logic runs here ...
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Session B, running concurrently, updates in reverse order
START TRANSACTION;
UPDATE accounts SET balance = balance - 20 WHERE id = 2;
-- ... application logic runs here ...
UPDATE accounts SET balance = balance + 20 WHERE id = 1;
COMMIT;

-- Classic deadlock: A holds id=1, waits for id=2
-- B holds id=2, waits for id=1

4. Deadlocks bei Fremdschlüsseln und Gap Locks

Eine weniger offensichtliche Quelle für Deadlocks sind Fremdschlüssel-Constraints. Beim Einfügen eines abhängigen Datensatzes setzt InnoDB automatisch eine Sperre auf die referenzierte Zeile in der übergeordneten Tabelle, um sicherzustellen, dass diese Zeile nicht parallel gelöscht wird. Fügen mehrere Transaktionen gleichzeitig Datensätze ein, die auf dieselbe übergeordnete Zeile verweisen, konkurrieren sie um diese implizite Sperre, was in Kombination mit anderen Sperren zu Deadlocks führen kann, die auf den ersten Blick nichts mit der eigentlichen Anwendungslogik zu tun haben.

Eine zweite, häufig übersehene Ursache sind Gap Locks unter REPEATABLE READ. Wenn mehrere Transaktionen parallel Zeilen in denselben Indexbereich einfügen, etwa aufsteigende Werte in eine Spalte mit sekundärem Index, können sich die dabei gesetzten Gap Locks und Insert Intention Locks gegenseitig blockieren. Dieses Verhalten wird im Detail in einem separaten Artikel dieser Serie zu Gap Locks und Next-Key Locks behandelt, ist aber als Deadlock-Ursache wichtig genug, um hier erwähnt zu werden: Unter READ COMMITTED treten diese speziellen Deadlocks deutlich seltener auf, weil dort weniger Gap Locks gesetzt werden.

5. Retry-Logik in der Anwendung implementieren

Da InnoDB einen Deadlock durch das Zurückrollen einer der beteiligten Transaktionen auflöst, muss die Anwendung darauf vorbereitet sein, den Fehler 1213 abzufangen und die betroffene Transaktion erneut auszuführen. Ohne diese Retry-Logik führt ein Deadlock zu einem sichtbaren Fehler beim Nutzer, obwohl die Operation bei einem zweiten Versuch mit hoher Wahrscheinlichkeit erfolgreich wäre, da die konkurrierende Transaktion inzwischen abgeschlossen ist.

Eine robuste Retry-Strategie begrenzt die Anzahl der Versuche, üblicherweise auf drei bis fünf, und fügt zwischen den Versuchen einen kurzen, idealerweise leicht zufälligen Backoff ein, um zu verhindern, dass mehrere gleichzeitig fehlgeschlagene Transaktionen sofort wieder aufeinandertreffen. Wichtig ist, dass die komplette Transaktion, nicht nur die letzte fehlgeschlagene Anweisung, wiederholt wird, da InnoDB beim Deadlock-Rollback die gesamte Transaktion verwirft.

-- Pseudocode-style application logic (PHP-like)
-- Retry the entire transaction on deadlock, not just the failed statement

max_attempts = 4
attempt = 0

while attempt < max_attempts:
    try:
        db.query("START TRANSACTION")
        db.query("UPDATE accounts SET balance = balance - 50 WHERE id = 1")
        db.query("UPDATE accounts SET balance = balance + 50 WHERE id = 2")
        db.query("COMMIT")
        break  -- success, exit retry loop
    except DeadlockError as e:  -- MySQL error 1213
        db.query("ROLLBACK")
        attempt += 1
        sleep(random_between(50, 200) * attempt)  -- ms, exponential-ish backoff
        continue
    except Exception as e:
        db.query("ROLLBACK")
        raise e  -- do not retry on non-deadlock errors

6. Konsistente Lock-Reihenfolge als Präventionsstrategie

Die wirksamste Präventionsstrategie gegen Deadlocks ist, Zeilen in jeder Transaktion konsequent in derselben Reihenfolge zu sperren, üblicherweise sortiert nach Primary Key. Statt Zeilen in der Reihenfolge zu aktualisieren, in der sie im Anwendungscode oder in den Eingabedaten erscheinen, sortiert man sie vor dem Zugriff explizit. Wenn alle Transaktionen konsequent aufsteigend nach ID sperren, kann der zyklische Wartezustand, der einen Deadlock ausmacht, gar nicht erst entstehen.

Bei Batch-Verarbeitungen, die mehrere Zeilen derselben Tabelle betreffen, lohnt sich zusätzlich eine explizite ORDER BY-Klausel in den zugrunde liegenden SELECT-Abfragen, bevor die Zeilen einzeln aktualisiert werden. Das erzwingt eine deterministische Zugriffsreihenfolge über alle Transaktionen hinweg und reduziert die Zahl der beobachtbaren Deadlocks in vielen Fällen um mehr als 90 Prozent, ohne dass Anwendungslogik oder Datenmodell geändert werden müssen.

7. innodb_deadlock_detect und innodb_lock_wait_timeout

InnoDB bietet mit innodb_deadlock_detect einen Parameter, der die aktive Deadlock-Erkennung standardmäßig aktiviert lässt. In Systemen mit extrem hoher Parallelität, bei denen der Deadlock-Detector selbst durch das Durchlaufen des Wait-for-Graphs spürbaren Overhead erzeugt, kann diese Erkennung deaktiviert werden. In diesem Fall greift stattdessen ausschließlich innodb_lock_wait_timeout, standardmäßig 50 Sekunden: Eine wartende Transaktion, die diese Zeit überschreitet, wird mit dem Fehler 1205 abgebrochen, selbst wenn eigentlich kein echter Deadlock, sondern nur eine lang gehaltene Sperre vorliegt.

Für die überwiegende Mehrheit produktiver Systeme sollte innodb_deadlock_detect aktiv bleiben, da ein sofort erkannter Deadlock deutlich schneller aufgelöst wird als das Warten auf einen langen Timeout. Nur bei sehr spezifischen Hochlast-Szenarien mit tausenden gleichzeitigen Transaktionen, in denen der Detector selbst zum Flaschenhals wird, ist eine Deaktivierung eine sinnvolle Option, kombiniert mit einem deutlich niedrigeren Lock-Wait-Timeout.

-- Inspect deadlock detection and lock wait timeout settings
SHOW VARIABLES LIKE 'innodb_deadlock_detect';
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- Lower the timeout for latency-sensitive OLTP workloads
SET GLOBAL innodb_lock_wait_timeout = 10;

-- Disable active detection only for very high concurrency systems
-- where the detector itself becomes a bottleneck
-- SET GLOBAL innodb_deadlock_detect = OFF;

8. Monitoring über Performance Schema und INNODB_TRX

Neben der reaktiven Analyse über SHOW ENGINE INNODB STATUS lässt sich Deadlock-Risiko proaktiv über information_schema.INNODB_TRX beobachten. Diese Tabelle listet alle aktiv laufenden Transaktionen samt Startzeit, aktuellem Status und der Anzahl gehaltener Sperren. Lange laufende Transaktionen mit vielen gehaltenen Sperren sind die wahrscheinlichsten Kandidaten für zukünftige Deadlocks und lassen sich damit erkennen, bevor der erste Vorfall überhaupt auftritt.

Der Performance Schema bietet mit der Tabelle data_lock_waits eine noch detailliertere Sicht auf aktuelle Wartesituationen zwischen Transaktionen, inklusive der jeweils blockierenden und blockierten Thread-ID. In Kombination mit einem regelmäßigen Monitoring-Job, der diese Tabelle abfragt und bei ungewöhnlich vielen gleichzeitigen Wartesituationen alarmiert, lassen sich Deadlock-Hotspots identifizieren, lange bevor sie zu einem produktiven Problem werden.

9. Deadlocks vs. Lock Wait Timeouts unterscheiden

Ein Deadlock und ein Lock Wait Timeout sind zwei unterschiedliche Fehler, die in der Praxis oft verwechselt werden. Ein Deadlock (Fehler 1213) entsteht bei einem echten zyklischen Wartezustand und wird von InnoDB aktiv und meist innerhalb von Millisekunden erkannt und aufgelöst. Ein Lock Wait Timeout (Fehler 1205) entsteht dagegen, wenn eine Transaktion einfach zu lange auf eine Sperre wartet, ohne dass ein Zyklus vorliegt, etwa weil die sperrhaltende Transaktion selbst extrem lange läuft oder vergessen wurde, sie zu committen.

Diese Unterscheidung ist für die Fehlerbehandlung wichtig: Ein Deadlock lässt sich fast immer durch einfaches Wiederholen der Transaktion lösen, da die blockierende Transaktion bereits zurückgerollt wurde. Ein Lock Wait Timeout deutet dagegen häufig auf ein strukturelles Problem hin, etwa eine vergessene Transaktion in einer anderen Session, und ein einfacher Retry führt oft erneut zum selben Timeout, wenn die Ursache nicht behoben wird.

10. Zusammenfassung

Ein Deadlock in InnoDB ist ein normaler, aktiv erkannter und automatisch aufgelöster Vorfall bei parallelen Transaktionen, kein Systemfehler. SHOW ENGINE INNODB STATUS liefert im Abschnitt LATEST DETECTED DEADLOCK die notwendigen Details zur Analyse, ergänzt durch innodb_print_all_deadlocks für eine dauerhafte Historie. Die häufigste Ursache ist die inkonsistente Reihenfolge, in der Transaktionen dieselben Zeilen sperren, die sich durch konsequentes Sortieren nach Primary Key vermeiden lässt.

Anwendungsseitige Retry-Logik ist keine Notlösung, sondern ein notwendiger Bestandteil jeder robusten Transaktionsverarbeitung, da InnoDB Deadlocks grundsätzlich nicht verhindern, sondern nur auflösen kann. Wer Deadlocks systematisch über SHOW ENGINE INNODB STATUS analysiert, konsistente Lock-Reihenfolgen im Anwendungscode etabliert und Retry-Logik mit Backoff implementiert, reduziert die Häufigkeit von Deadlocks in der Praxis auf ein vernachlässigbares Niveau.

11. FAQ: MySQL Deadlocks erkennen und vermeiden