Schlüsselpunkte
- Ein Deadlock entsteht, wenn sich zwei oder mehr Transaktionen gegenseitig blockieren, weil jede eine Sperre hält und gleichzeitig auf dieselben gesperrten Ressourcen wartet.
- Deadlocks entstehen meist durch gleichzeitige Transaktionen, die um gesperrte Ressourcen konkurrieren. Treten sie wiederholt auf, lohnt sich eine genauere Analyse.
- Deadlocks lassen sich mit Extended Events, Ablaufverfolgungsflags, DMVs und Überwachungstools erkennen. Richten Sie Alarmierungen und Protokollierung ein, um Vorfälle schnell zu erfassen und darauf zu reagieren.
- Reagieren Sie auf Deadlocks, indem Sie Deadlock-Diagramme auswerten, Muster erkennen und Korrekturen abstimmen.
- Vermeiden Sie erneutes Auftreten durch Indizierung, Query-Tuning und einheitliche Zugriffsmuster.
Ein Problem, das durch Parallelität und Sperren in relationalen Datenbanken auftreten kann, sind SQL Server Deadlocks. Ein Deadlock entsteht, wenn sich zwei oder mehr Transaktionen gegenseitig unbegrenzt blockieren, weil jede Transaktion auf eine Ressource wartet, die von einer anderen Transaktion gehalten wird. In der Regel bricht ein SQL Server eine Transaktion ab, um die Situation aufzulösen. Treten Deadlocks jedoch wiederholt auf, sollten Sie das Design des Systems genauer prüfen.
In diesem Artikel sehen wir uns an, was Deadlocks sind und wie sie mit Parallelität zusammenhängen. Außerdem erfahren Sie, welche praktischen Strategien Sie anwenden können, um Deadlocks zu erkennen, darauf zu reagieren und sie künftig zu vermeiden.
Das steckt hinter einem Deadlock
Ein Deadlock ist eine Situation, in der Transaktionen eine zirkuläre Abhängigkeit von gesperrten Ressourcen erzeugen. SQL Server kann einen Deadlock automatisiert auflösen, indem er ein Deadlock-Opfer auswählt. Dabei wird eine Transaktion zurückgerollt, um die Ressourcen freizugeben, sodass die verbleibende Transaktion sie nutzen und abgeschlossen werden kann.
Hier ein Beispiel für einen Deadlock:
- Transaktion A hält Sperre 1 und fordert Sperre 2 an
- Transaktion B hält Sperre 2 und fordert Sperre 1 an
- Keine der beiden Transaktionen kann fortgesetzt werden
So läuft die Auflösung durch SQL Server ab:
- Erkennung des Deadlocks über einen internen Sperrenmonitor
- Auswahl des Deadlock-Opfers (im Beispiel entweder Transaktion A oder B)
- Rollback der Transaktion des ausgewählten Deadlock-Opfers
- Freigabe der Ressource durch das Deadlock-Opfer, damit die andere Transaktion sie nutzen kann
Dieser Mechanismus wirkt zwar effizient, kann aber folgende Nachteile mit sich bringen:
- Fehlgeschlagene Transaktionen
- Anwendungsfehler
- Schlechte Benutzererfahrung
So tragen Parallelität und Sperren zu Deadlocks bei
Parallelität bedeutet, dass Prozesse in einer Datenbank gleichzeitig ablaufen. Deadlocks hängen eng damit zusammen, wie SQL dies über Sperren abbildet. Um die Datenkonsistenz zu wahren, nutzt SQL Server:
- Gemeinsame Sperren (S) für Lesevorgänge
- Exklusive Sperren (X) für Schreibvorgänge
- Absichtssperren für die hierarchische Steuerung
Deadlocks treten eher auf, wenn:
- mehrere Transaktionen auf dieselben Daten zugreifen
- Sperren über längere Zeit gehalten werden
- uneinheitliche Zugriffsmuster auftreten
Zusätzlich können mehrere weitere Faktoren das Deadlock-Risiko erhöhen:
- Isolationsstufen: Je höher die Isolation, desto mehr Sperren sind nötig
- Sperrgranularität: Zeilen- vs. Seiten- vs. Tabellensperren
- Sperreskalation: Viele Zeilensperren eskalieren zu Tabellensperren
- Zugriffsreihenfolge: Unterschiedliche Abfragesequenzen erhöhen die Zahl der Konflikte
Deadlocks im SQL Server erkennen
Da Deadlocks im Hintergrund entstehen, braucht es passende Tools und Methoden, um sie aufzuspüren.
- Extended Events: Ein in SQL Server integriertes Tool, das Deadlock-Diagramme automatisiert erfasst. Es ist ressourcenschonend und über die System-Health-Session verfügbar.
- Ablaufverfolgungsflags: Ein Diagnosemechanismus, der Deadlock-Informationen (zum Beispiel 1204 und 1222) in Protokolle schreibt. Nützlich für eine schnelle Diagnose.
- Dynamische Verwaltungssichten (DMVs): Systemsichten, die Sie abfragen können, um das Verhalten der Datenbank zu untersuchen und Muster über die Zeit hinweg zu erkennen.
- SQL Server Management Studio (SSMS): Eine Benutzeroberfläche für die Arbeit mit SQL Server, die Deadlock-Diagramme visuell darstellt.
- Profiling- und Überwachungstools: Damit lassen sich Deadlocks in Echtzeit erfassen und historisch auswerten.
Deadlock-Diagramme sind besonders wertvoll, weil Sie damit sehen, welche Abfragen beteiligt waren, welche Ressourcen gesperrt wurden und in welcher genauen Reihenfolge die Ereignisse abliefen.
Alarmierung und Protokollierung für Deadlocks einrichten
Deadlocks nur zu erkennen, reicht nicht aus. Es braucht immer ein belastbares System, das Transparenz schafft und eine zeitnahe Reaktion auf Deadlocks ermöglicht. Best Practices wie die folgenden verbessern den operativen Überblick:
- Deadlocks erfassen: Nutzen Sie Extended Events (event_file oder Ringpuffer)
- Protokolle zentralisieren: Speichern Sie Deadlock-Diagramme in einer zentralen Datenbank, damit sie später ausgewertet werden können.
- Schwellenwerte festlegen: Konfigurieren Sie die Alarmierung so, dass sie auslöst, wenn Deadlocks den Basiswert überschreiten.
- In Überwachungstools einbinden: Nehmen Sie Deadlocks immer in Dashboards und Berichte auf, um Transparenz zu schaffen.
- Benachrichtigungen automatisieren: Richten Sie automatisierte Alarmierungen an die IT- oder DevOps-Teams ein.
Auf Deadlocks reagieren
Tritt ein Deadlock auf, sollten Sie folgendermaßen vorgehen:
- Führen Sie Sofortmaßnahmen durch: das Deadlock-Opfer identifizieren, Fehlerprotokolle und Alarmierungen prüfen und das Deadlock-Diagramm erfassen.
- Gehen Sie zur Analyse über: die beteiligten Abfragen untersuchen, die gesperrten Ressourcen ermitteln, die Ausführungsreihenfolge der Transaktionen prüfen und nach kürzlichen Schema- oder Codeänderungen suchen.
- Untersuchen Sie Muster: Sind immer dieselben Tabellen beteiligt? Treten die Deadlocks bei Spitzenlast auf? Sind bestimmte Abfragen stets Teil des Konflikts?
- Arbeiten Sie mit den Entwickler:innen zusammen, wenn die Anwendungslogik beteiligt ist. Gegebenenfalls können Sie auch ORM-generierte Abfragen prüfen.
Deadlocks vorbeugen
Sobald Sie die Ursache wiederkehrender Deadlocks kennen, lassen sie sich leichter dauerhaft vermeiden. Diese Strategien helfen dabei:
- Zugriffsmuster standardisieren, sodass sie Teil einer gemeinsamen Ressource sind
- Indizierung optimieren, um zeitaufwendige vollständige Tabellenscans zu reduzieren
- Passende Isolationsstufen wählen, um den Sperr-Overhead zu minimieren
- Transaktionsdauer verkürzen, etwa durch schnelles Commit oder Rollback
- Sperreskalation verringern, indem Arbeit gebündelt oder partitioniert wird
Deadlocks wirksam in den Griff bekommen
Deadlocks sind eine natürliche Folge der Parallelität in SQL Server. Die Datenbank-Engine löst sie zwar unmittelbar auf, doch bei wiederkehrenden Deadlocks sollten Sie Ihr System genauer prüfen, bevor es instabil wird. Wenn Sie verstehen, wie Sperren und Parallelität zusammenspielen, und bei häufigen Deadlocks die richtigen Maßnahmen ergreifen, verbessern Sie die Datenbankleistung, verringern Transaktionskonflikte und halten das System im Gleichgewicht.
Verwandte Themen:
