Deadlocks rekonstruieren statt einfach neu starten
Die Fehlermeldung nennt nur das Opfer. Wer die beiden beteiligten Anweisungen und ihre Sperrarten kennt, sieht das Zugriffsmuster, das die Verklemmung überhaupt möglich macht.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die Fehlermeldung sagt am wenigsten über die Ursache
Im Anwendungsprotokoll steht eine Zeile: die Transaktion wurde als Opfer eines Deadlocks abgebrochen. Was fehlt, ist alles, was zur Aufklärung nötig wäre. Welche zweite Transaktion beteiligt war, welche Zeilen oder Indexbereiche gesperrt wurden und in welcher Reihenfolge zugegriffen wurde, steht nicht in der Meldung, sondern an einer Stelle, die je nach System woanders liegt und manchmal erst eingeschaltet werden muss.
Deshalb endet die Untersuchung oft bei der Maßnahme, die am schnellsten geht: Die Anwendung wiederholt die Transaktion. Das ist nicht falsch, aber es beantwortet die Frage nicht, ob hier ein seltener Zufall abgefangen wird oder ein Zugriffsmuster, das unter Last jede Nacht zuschlägt. Im zweiten Fall wächst die Zahl der Wiederholungen mit der Last, und irgendwann wiederholen sich die Transaktionen gegenseitig ins Zeitlimit.
Verschärfend kommt hinzu, dass viele Deadlocks nicht durch das offensichtliche UPDATE entstehen, sondern durch Sperren, die niemand angefordert hat: Sperren auf Indexbereiche, Sperren auf die Elternzeile eines Fremdschlüssels oder eine ganze Reihe gesperrter Zeilen, weil für die WHERE-Bedingung kein Index existiert.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Symptom, Ursache, Lösung
Symptom
Der nächtliche Importjob bricht gegen zwei Uhr mit einem Deadlock ab, tagsüber läuft derselbe Code monatelang ohne Auffälligkeit.
Ursache
Nachts läuft parallel ein zweiter Massenlauf, und beide fassen dieselben Tabellen in unterschiedlicher Reihenfolge an. Bei einzelnen Sätzen tagsüber ist die Wahrscheinlichkeit gering, bei zwei gleichzeitigen Massenläufen ist der Zusammenstoß fast sicher.
Lösung
Den Deadlock-Bericht heraussuchen und die Reihenfolge beider Läufe vergleichen. Danach entweder eine gemeinsame Reihenfolge festlegen oder die Läufe zeitlich entzerren. Bei großen Aktualisierungen zusätzlich in kleineren Blöcken arbeiten, damit der SQL Server nicht auf Tabellensperren eskaliert.
Symptom
Zwei Sitzungen aktualisieren nachweislich verschiedene Zeilen und verklemmen sich trotzdem regelmäßig.
Ursache
In MySQL sperrt InnoDB ohne passenden Index jede beim Suchen berührte Zeile, unter REPEATABLE READ zusätzlich die Bereiche dazwischen. Damit überschneiden sich Sperrbereiche, die auf Zeilenebene disjunkt aussehen.
Lösung
Im Deadlock-Auszug den genannten Index prüfen. Steht dort der Primärschlüssel, obwohl nach einem anderen Feld gesucht wird, fehlt der Index auf dieses Feld. Erst danach lohnt die Überlegung, ob READ COMMITTED für diese Anwendung tragbar ist.
Symptom
Nach dem Deadlock steht die Anwendung, obwohl nur eine von zwei Transaktionen abgebrochen wurde.
Ursache
Das System ist Oracle. Dort wird bei ORA-00060 nur die auslösende Anweisung zurückgenommen, die Transaktion bleibt offen und hält ihre Sperren weiter. Ohne ausdrückliches ROLLBACK der Anwendung blockiert sie alle nachfolgenden Zugriffe.
Lösung
Die Ausnahmebehandlung so ergänzen, dass ORA-00060 zu einem vollständigen Zurückrollen der Transaktion führt und erst danach der Wiederholungsversuch startet. In allen anderen Systemen ist die Transaktion an dieser Stelle bereits beendet.
Symptom
Der Deadlock trifft immer dieselbe Auswertung, die ausschließlich liest.
Ursache
Auch Lesevorgänge nehmen Sperren, sobald sie in einer Transaktion mit höherer Isolationsstufe laufen oder anschließend geschrieben wird. Im SQL Server ist das der klassische Umwandlungs-Deadlock aus zwei gemeinsamen Sperren, die beide zu exklusiven werden sollen.
Lösung
Beim Lesen bereits die schreibende Absicht anmelden, im SQL Server mit WITH (UPDLOCK), sonst mit SELECT ... FOR UPDATE. Wo wirklich nur gelesen wird, ist die Umstellung auf Zeilenversionierung der bessere Weg, im SQL Server über READ_COMMITTED_SNAPSHOT.
Symptom
Es gibt kein Protokoll, und der Vorfall lässt sich nicht nachstellen.
Ursache
Die Aufzeichnung war nicht eingeschaltet. InnoDB behält ohne innodb_print_all_deadlocks nur den letzten Fall, und im SQL Server können die Dateien der Sitzung system_health bei hoher Ereignislast schnell überschrieben sein.
Lösung
Die Aufzeichnung dauerhaft einschalten und den nächsten Fall abwarten, statt zu raten. Parallel in der Anwendung Fehlercode, Zeitstempel und fachlichen Vorgang protokollieren, damit sich beide Seiten später zusammenführen lassen.
Fünf Schritte von der Meldung zur Ursache
- 01 Prüfen, ob es eine Verklemmung war oder nur eine lange Blockierung.
- 02 Den Deadlock-Bericht des Systems heraussuchen, notfalls erst einschalten.
- 03 Die beiden beteiligten Anweisungen und ihre Sperrarten notieren.
- 04 Im Code nachsehen, in welcher Reihenfolge die Objekte angefasst werden.
- 05 Entscheiden: Reihenfolge ändern, Index ergänzen oder Wiederholung einbauen.
Was du nach dieser Seite über einen Deadlock sagen kannst
Am Ende der Untersuchung stehen drei Angaben: die beiden Anweisungen, die Sperrarten und Objekte, um die es ging, und die Entscheidung, ob die Reihenfolge im Code geändert wird oder ein begrenzter Wiederholungsversuch die richtige Antwort ist.
Blockierung und Verklemmung trennen
Eine Blockierung löst sich von selbst, sobald die andere Transaktion beendet ist. Ein Deadlock löst sich nie von selbst, deshalb greift das System ein. Beide Fälle brauchen völlig verschiedene Maßnahmen.
Die Protokollquelle je System kennen
Im SQL Server liegt der Deadlock-Bericht in der Sitzung system_health, PostgreSQL schreibt die beteiligten Anweisungen ins Serverprotokoll, Oracle legt eine Trace-Datei an, InnoDB zeigt den letzten Fall in SHOW ENGINE INNODB STATUS.
Die Zugriffsreihenfolge lesen
Zwei Transaktionen, die dieselben Zeilen in verschiedener Reihenfolge anfassen, sind der Klassiker. Eine feste Reihenfolge über alle Programmteile hinweg, etwa immer aufsteigend nach Schlüssel, beseitigt diesen Fall vollständig.
Sperren erkennen, die niemand angefordert hat
Ohne passenden Index sperrt InnoDB jede Zeile, die es beim Suchen anfasst. Ein Fremdschlüssel sperrt zusätzlich die Elternzeile. Beides vergrößert den Bereich, in dem sich zwei Transaktionen begegnen können.
Die Aufwertung von Lese- auf Schreibsperre vermeiden
Erst SELECT, dann UPDATE auf dieselbe Zeile führt dazu, dass zwei Sitzungen gleichzeitig lesen und beide schreiben wollen. Wer schon beim Lesen die schreibende Absicht anmeldet, verhindert diesen Deadlock.
Den Wiederholungsversuch richtig bauen
Wiederholt wird die ganze Transaktion, nicht die abgebrochene Anweisung, mit begrenzter Anzahl und wachsender Wartezeit. Alles, was außerhalb der Datenbank passiert ist, muss dabei wiederholbar sein.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Blockieren und Verklemmen sind zwei verschiedene Dinge
Eine Blockierung ist der Normalfall im Mehrbenutzerbetrieb: Eine Transaktion wartet, bis eine andere ihre Sperre freigibt, und läuft danach weiter. Wie lange sie wartet, ist je System verschieden. MySQL bricht die Wartezeit nach innodb_lock_wait_timeout ab, in der Voreinstellung nach 50 Sekunden. Der SQL Server wartet in der Voreinstellung unbegrenzt, bis eine Sitzung mit SET LOCK_TIMEOUT eine Grenze setzt. Oracle wartet ebenfalls ohne Begrenzung, sofern nicht ausdrücklich NOWAIT oder WAIT angegeben ist, und PostgreSQL so lange, wie lock_timeout nicht gesetzt wurde.
Ein Deadlock ist etwas anderes: ein geschlossener Kreis von Wartebeziehungen, der sich nicht mehr auflösen kann. Kein Wartezeitlimit der Welt löst ihn, weil beide Seiten auf den jeweils anderen warten. Deshalb suchen alle vier Systeme aktiv danach und brechen eine Transaktion ab. PostgreSQL prüft, nachdem eine Sitzung länger als deadlock_timeout gewartet hat, in der Voreinstellung also nach einer Sekunde. Der SQL Server hat einen eigenen Überwachungsprozess, InnoDB erkennt den Kreis beim Anfordern der Sperre, und diese Erkennung lässt sich mit innodb_deadlock_detect sogar abschalten, dann greift stattdessen das Wartezeitlimit.
Die Unterscheidung ist wichtig, weil sie zu verschiedenen Maßnahmen führt. Gegen Blockierungen helfen kürzere Transaktionen, ein anderer Zeitpunkt für den Batchlauf und manchmal eine andere Isolationsstufe. Gegen Deadlocks hilft das alles nur mittelbar, dort geht es um die Reihenfolge der Zugriffe.
Wo jedes System die Verklemmung hinschreibt
Im SQL Server ist die Erweiterte-Ereignisse-Sitzung system_health standardmäßig aktiv und enthält das Ereignis xml_deadlock_report. Den Bericht findest du im Management Studio unter Verwaltung, Erweiterte Ereignisse, oder du liest die Zieldateien mit sys.fn_xe_file_target_read_file aus. Der enthaltene Deadlock-Graph zeigt für jeden Beteiligten die Sitzung, die zuletzt ausgeführte Anweisung im Eingabepuffer, die Sperrart und das betroffene Objekt bis hinunter zum Index. Die älteren Ablaufverfolgungsflags 1204 und 1222 schreiben dieselbe Information ins Fehlerprotokoll und werden für neue Installationen nicht mehr gebraucht.
PostgreSQL macht es am einfachsten: Die Fehlermeldung mit dem SQLSTATE 40P01 nennt im DETAIL-Abschnitt beide Prozesse und die Sperren, auf die sie warten, und verweist für die Anweisungen selbst auf das Serverprotokoll. Dort steht der vollständige Bericht mit den Abfragen beider Sitzungen, und zwar dauerhaft. Zusätzlich lohnt sich log_lock_waits, denn damit protokolliert PostgreSQL auch lange Wartezeiten, die noch keine Verklemmung sind, und genau die sind die Vorstufe.
Oracle meldet ORA-00060 und schreibt eine Trace-Datei in das Diagnoseverzeichnis, mit dem Deadlock-Graph, der Wartematrix und den aktuellen Anweisungen der Sitzungen. Ein Unterschied fällt dabei stark ins Gewicht: Oracle nimmt nur die eine Anweisung zurück, nicht die ganze Transaktion. Die Sitzung lebt weiter, hält ihre bisherigen Sperren und blockiert unter Umständen weiter, bis die Anwendung selbst ein ROLLBACK abschickt. MySQL wiederum zeigt unter SHOW ENGINE INNODB STATUS den Abschnitt LATEST DETECTED DEADLOCK, und zwar ausschließlich den letzten. Für eine Auswertung über die Zeit setzt du innodb_print_all_deadlocks auf ON, dann landet jeder Fall im Fehlerprotokoll, mit beiden Transaktionen, der wartenden und der gehaltenen Sperre, dem betroffenen Index und der Sperrart.
Die Muster im Code, die Deadlocks erst möglich machen
Das Grundmuster ist die uneinheitliche Reihenfolge. Ein Programmteil bucht erst vom Konto A auf B, ein anderer im selben Moment von B nach A. Beide halten eine Zeile und wollen die zweite. Die Lösung ist unspektakulär und wirksam: Alle Programmteile fassen die Objekte in derselben festen Reihenfolge an, etwa aufsteigend nach Primärschlüssel oder immer erst Kopf, dann Position. Das lässt sich in einer Codeprüfung nachhalten und beseitigt diesen Fall vollständig.
Das zweite Muster ist die Aufwertung von der Lese- zur Schreibsperre. Zwei Sitzungen lesen dieselbe Zeile, beide halten eine gemeinsame Sperre, beide wollen anschließend schreiben, und keine kann, solange die andere liest. Im SQL Server heißt die Gegenmaßnahme WITH (UPDLOCK) schon beim SELECT, in PostgreSQL, Oracle und MySQL SELECT ... FOR UPDATE. Damit meldet die erste Sitzung ihre Absicht sofort an, die zweite wartet, und der Kreis entsteht gar nicht.
Das dritte Muster ist der zu große Sperrbereich, und dafür gibt es in jedem System einen eigenen Auslöser. InnoDB sperrt jede Zeile, die es beim Auswerten der WHERE-Bedingung anfasst, nicht nur die getroffenen, weshalb ohne passenden Index aus einem UPDATE auf eine Zeile eine Sperre auf sehr viele wird. Hinzu kommt, dass die voreingestellte Isolationsstufe REPEATABLE READ mit Gap-Sperren arbeitet, die auch Bereiche zwischen vorhandenen Werten belegen, und dass beim Einfügen eines Kindsatzes die Elternzeile des Fremdschlüssels mitgesperrt wird. Der SQL Server wandelt ab einer bestimmten Zahl von Einzelsperren auf eine Sperre für Partition oder Tabelle um, wodurch Transaktionen zusammenstoßen, die sich zeilenweise nie begegnet wären. Gegen den zu großen Sperrbereich in InnoDB hilft, den fehlenden Index anzulegen und die Isolationsstufe zu prüfen, gegen die Eskalation im SQL Server die Aufteilung großer Aktualisierungen in kleinere Blöcke.
Wann der Wiederholungsversuch die richtige Antwort ist
Ganz vermeiden lassen sich Deadlocks in einem System mit echter Nebenläufigkeit nicht, deshalb gehört die Behandlung in jede Anwendung, die schreibt. Die Fehlercodes sind bekannt und stabil: 1205 im SQL Server, ORA-00060 in Oracle, SQLSTATE 40P01 in PostgreSQL, 1213 in MySQL. Wichtig ist, dass die Anwendung genau darauf reagiert und nicht auf jeden Datenbankfehler pauschal.
Wiederholt wird die vollständige Transaktion. Nach dem Abbruch sind alle Änderungen zurückgenommen, und wer nur die letzte Anweisung erneut schickt, arbeitet auf einem Zustand, den es nicht mehr gibt. Bei Oracle gilt das mit der beschriebenen Besonderheit: Dort muss die Anwendung selbst zurückrollen, bevor sie neu beginnt. Sinnvoll sind eine begrenzte Zahl von Versuchen, eine wachsende Wartezeit dazwischen und ein Zufallsanteil, damit die beiden Beteiligten nicht im Gleichtakt erneut zusammenstoßen.
Die Wiederholung muss außerdem folgenlos wiederholbar sein. Wenn zwischen Beginn und Abbruch eine Nachricht verschickt, eine Datei geschrieben oder ein Fremdsystem aufgerufen wurde, passiert das beim zweiten Anlauf ein zweites Mal. Solche Nebenwirkungen gehören deshalb hinter das erfolgreiche COMMIT, nicht mitten in die Transaktion. Und es bleibt eine Zähleraufgabe: Wenn derselbe Job jede Nacht dutzendfach wiederholen muss, ist die Wiederholung ein Verband über einer offenen Wunde, und der Aufwand für eine einheitliche Zugriffsreihenfolge oder den fehlenden Index lohnt sich.
Was du vorher einrichtest, damit die Analyse später gelingt
Deadlock-Informationen sind flüchtig. InnoDB behält nur den letzten Fall, PostgreSQL schreibt nur, was die Protokolleinstellungen zulassen, und in Oracle liegt die Trace-Datei so lange im Diagnoseverzeichnis, wie die Aufbewahrungsregeln es erlauben. Wenn der Vorfall erst zwei Tage später gemeldet wird, entscheidet sich an diesen Einstellungen, ob überhaupt noch etwas zu untersuchen ist.
Ein kleines Paket reicht dafür aus: innodb_print_all_deadlocks auf ON in MySQL, log_lock_waits eingeschaltet in PostgreSQL, in der Anwendung ein Protokolleintrag mit Zeitstempel, Fehlercode und fachlichem Vorgang, und im SQL Server die Gewissheit, dass die Sitzung system_health tatsächlich läuft und ihre Dateien nicht nach wenigen Stunden überschrieben sind.
Hilfreich ist außerdem, die Anwendung erkennbar zu machen. Ein gesetzter Anwendungsname in der Verbindungszeichenfolge oder ein Kommentar im SQL mit Modul und Vorgang sorgt dafür, dass im Deadlock-Bericht nicht nur ein anonymes UPDATE steht, sondern der Programmteil, aus dem es kam. Das verkürzt die Suche von Stunden auf Minuten.
Dazu passende Kurse
Wie sich Isolationsstufen, Sperrarten und Zugriffsreihenfolge gegenseitig bedingen, lässt sich am System nachstellen, und genau darum geht es in Kurse zu Sperren, Transaktionen und Nebenläufigkeit .
Wer den Deadlock-Graphen aus der Sitzung system_health regelmäßig auswertet, findet den passenden Rahmen in die Kurse rund um den Microsoft SQL Server .
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Wo du genau das übst
Super Schulung, in der der Trainer auch auf eigene Fragestellungen eingegangen ist.
Super Grundlagenschulung mit allen wichtigen Punkten und hilfreichen Tipps!
Schön, sich 5 Tage ungestört mit Postgres beschäftigen zu können.
Häufige Fragen
Wer wird zum Opfer, und lässt sich das steuern?
Hilft eine niedrigere Isolationsstufe gegen Deadlocks?
Können auch Lesevorgänge einen Deadlock verursachen?
Reicht es, die Transaktion einfach zu wiederholen?
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Datenbanken-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Sperrkonflikte versteht man am besten, wenn man sie erzeugt
Zwei Sitzungen nebeneinander, ein absichtlich falsch gebauter Ablauf und der Blick ins Protokoll: So wird das Thema bei cmt greifbar.