SQL Server Deadlocks erkennen und beheben: Extended Events Schritt für Schritt

Eine Anwendung meldet plötzlich Fehler 1205, obwohl die betroffenen SQL-Abfragen für sich genommen funktionieren? Häufig steckt ein Deadlock dahinter. Zwei oder mehr Sitzungen warten dabei gegenseitig auf Ressourcen, die jeweils von der anderen Sitzung gesperrt sind. SQL Server beendet eine der beteiligten Transaktionen, damit die übrigen weiterlaufen können.

Wer SQL Server Deadlocks erkennen und dauerhaft beheben möchte, braucht den konkreten Deadlock-Graphen. Er zeigt, welche Anweisungen beteiligt waren, auf welche Ressourcen sie warteten und welche Transaktion zurückgesetzt wurde. In dieser Anleitung liest du den Graphen mit Extended Events aus, wertest ihn aus und leitest passende Maßnahmen ab.

Was ist ein Deadlock – und was ist normales Blocking?

Beim normalen Blocking wartet eine Sitzung auf eine Sperre, die eine andere Sitzung noch hält. Sobald die erste Transaktion ihre Sperre freigibt, kann die wartende Sitzung weitermachen. Ein Deadlock entsteht erst, wenn sich die beteiligten Sitzungen gegenseitig blockieren und keine von ihnen allein fortfahren kann.

SQL Server erkennt diesen Kreislauf automatisch. Eine Transaktion wird als sogenanntes Deadlock-Opfer ausgewählt und zurückgesetzt. Die Anwendung erhält dafür in der Regel die Fehlermeldung 1205. Das behebt den akuten Stillstand, beseitigt aber nicht die Ursache im Ablauf der Transaktionen.

Wenn du die Grundlagen zu BEGIN TRANSACTION, COMMIT und ROLLBACK auffrischen möchtest, findest du sie im Beitrag Arbeiten mit Transaktionen in MSSQL.

Schritt 1: Vorhandene Deadlock-Daten in system_health prüfen

Für die erste Analyse musst du meistens keine eigene Überwachung einrichten. Die standardmäßig aktive Extended-Events-Sitzung system_health erfasst auf SQL Server Deadlock-Graphen über das Ereignis sqlserver.xml_deadlock_report.

1. Öffne SQL Server Management Studio (SSMS) und verbinde dich mit der betroffenen SQL-Server-Instanz.
2. Klappe im Objekt-Explorer Verwaltung → Erweiterte Ereignisse → Sitzungen → system_health auf.
3. Öffne das Ziel package0.event_file mit Zieldaten anzeigen. Je nach SSMS-Version können einzelne Bezeichnungen leicht abweichen.
4. Filtere die Ereignisse nach xml_deadlock_report.
5. Öffne das passende Ereignis. SSMS kann den enthaltenen Deadlock-Graphen grafisch darstellen; zusätzlich stehen die XML-Daten zur Verfügung.

Notiere zunächst den Zeitpunkt des Vorfalls und vergleiche ihn mit der Meldung der Anwendung. Bei mehreren Deadlocks ist nur der zeitlich passende Graph eine verlässliche Grundlage für die weitere Analyse.

Auch das ring_buffer-Ziel von system_health enthält Deadlock-Ereignisse. Es hält Daten jedoch nur begrenzt vor. Für zurückliegende Vorfälle ist das event_file-Ziel häufig die bessere erste Anlaufstelle. Beende oder lösche die integrierte Sitzung system_health nicht für diese Untersuchung.

Schritt 2: Den Deadlock-Graphen lesen

Ein Deadlock-Graph sieht auf den ersten Blick umfangreich aus. Für die Ursachenanalyse reichen zunächst drei Bereiche:

Untersuche immer alle beteiligten Sitzungen. Das Deadlock-Opfer ist nicht automatisch die Ursache. SQL Server wählt es aus, um den Wartekreis aufzulösen; die problematische Zugriffsreihenfolge kann in einer anderen beteiligten Transaktion liegen.

Besonders hilfreich sind die ausgeführte Anweisung beziehungsweise inputbuf, die Angaben zum Ausführungsstapel, der Sperrmodus und – sofern vorhanden – Objekt- und Indexnamen. Der Graph zeigt allerdings nur einen Ausschnitt des Vorgangs. Prüfe deshalb auch den Anwendungscode oder die gespeicherte Prozedur, um die Reihenfolge aller Anweisungen innerhalb der Transaktion zu verstehen.

Schritt 3: Bei Bedarf eine eigene Extended-Events-Sitzung anlegen

Reicht die Aufbewahrungsdauer von system_health für deine Untersuchung nicht aus, kannst du eine eigene Sitzung für Deadlock-Graphen anlegen. Das folgende Beispiel schreibt Ereignisse in XEL-Dateien und startet die Sitzung nach einem Neustart des SQL Servers erneut.

Vor dem Ausführen: Passe den Dateipfad an. Das Verzeichnis muss auf dem SQL-Server-System vorhanden sein, und das SQL-Server-Dienstkonto benötigt dort Schreibrechte. Führe das Skript mit einem Konto aus, das Extended-Events-Sitzungen erstellen und starten darf.

CREATE EVENT SESSION [Deadlocks_Audit]
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
(
    SET filename = N'D:\XE\Deadlocks_Audit.xel',
        max_file_size = 20,
        max_rollover_files = 5
)
WITH
(
    STARTUP_STATE = ON
);
GO

ALTER EVENT SESSION [Deadlocks_Audit]
ON SERVER
STATE = START;
GO

Das Beispiel begrenzt die Dateigröße pro Datei auf 20 MB und die Zahl der Rollover-Dateien auf fünf. Passe diese Werte an deine Umgebung und die gewünschte Aufbewahrungsdauer an. Die XEL-Dateien können SQL-Text und weitere Betriebsinformationen enthalten; berücksichtige das bei Speicherort und Zugriffsrechten.

Ob die Sitzung läuft, prüfst du mit:

SELECT
    name,
    create_time
FROM sys.dm_xe_sessions
WHERE name = N'Deadlocks_Audit';

Bleibt das Ergebnis leer, ist die Sitzung aktuell nicht aktiv. Prüfe dann insbesondere den Start der Sitzung, den Dateipfad und die Berechtigungen.

Schritt 4: Deadlocks aus der XEL-Datei auslesen

Die Datei kannst du in SSMS über Datei → Öffnen → Datei auswählen. Alternativ liest du die Ereignisse per T-SQL aus. Das folgende Beispiel setzt SQL Server 2017 oder neuer voraus:

WITH DeadlockEvents AS
(
    SELECT
        timestamp_utc,
        CAST(event_data AS xml) AS event_xml
    FROM sys.fn_xe_file_target_read_file
    (
        N'D:\XE\Deadlocks_Audit*.xel',
        NULL,
        NULL,
        NULL
    )
    WHERE object_name = N'xml_deadlock_report'
)
SELECT
    timestamp_utc,
    event_xml.query(
        '/event/data[@name="xml_report"]/value/deadlock'
    ) AS deadlock_graph
FROM DeadlockEvents
ORDER BY timestamp_utc DESC;

timestamp_utc ist ein UTC-Zeitstempel. Rechne ihn beim Abgleich mit Anwendungsprotokollen gegebenenfalls in die dort verwendete Zeitzone um. Der Platzhalter im Dateinamen berücksichtigt die von Extended Events erzeugten Dateinamen einschließlich der Rollover-Dateien.

Erhältst du keine Zeilen, prüfe zuerst, ob im gewählten Zeitraum überhaupt ein Deadlock auftrat. Kontrolliere anschließend, ob die Sitzung aktiv ist und ob du den tatsächlichen Speicherort der XEL-Dateien verwendest.

Typisches Beispiel: Zwei Transaktionen greifen in unterschiedlicher Reihenfolge zu

Angenommen, ein Bestellvorgang aktualisiert zuerst die Tabelle Bestellungen und danach Lagerbestand. Ein anderer Vorgang bearbeitet dieselben Daten in umgekehrter Reihenfolge:

Beide Transaktionen halten bereits eine benötigte Sperre und warten auf die jeweils andere. Im Deadlock-Graphen erkennst du diesen Kreislauf an den beteiligten Prozessen und Ressourcen. Eine naheliegende Korrektur ist, beide Abläufe so zu gestalten, dass sie die Tabellen in derselben Reihenfolge bearbeiten.

Die konkrete Lösung hängt vom Graphen ab. Deadlocks können auch innerhalb derselben Tabelle, zwischen unterschiedlichen Indizes oder durch konkurrierende Lese- und Schreibvorgänge entstehen. Deshalb solltest du keine Änderung allein anhand der Fehlermeldung 1205 vornehmen.

SQL Server Deadlocks beheben: Die wichtigsten Maßnahmen

1. Zugriffsreihenfolge vereinheitlichen

Wenn mehrere Transaktionen dieselben Objekte benötigen, sollten sie diese möglichst in derselben Reihenfolge ansprechen. Prüfe dabei nicht nur einzelne SQL-Anweisungen, sondern den vollständigen Ablauf in Anwendung und gespeicherten Prozeduren.

2. Transaktionen kurz halten

Zwischen dem Beginn einer Transaktion und COMMIT sollten nur die Arbeitsschritte liegen, die tatsächlich gemeinsam abgeschlossen werden müssen. Benutzerinteraktion, längere Berechnungen oder externe Aufrufe innerhalb einer offenen Transaktion verlängern die Zeit, in der Sperren gehalten werden.

3. Abfragen und Indizes gezielt prüfen

Ein ungünstiger Ausführungsplan kann dazu führen, dass eine Abfrage mehr Zeilen liest und sperrt als nötig. Prüfe für die im Graphen genannten Anweisungen die Suchbedingungen, den tatsächlichen Ausführungsplan und vorhandene Indizes. Ein passender Index kann den Zugriff eingrenzen. Ein zusätzlicher Index ist aber nur sinnvoll, wenn er zur konkreten Abfrage und ihrer Schreiblast passt.

Die Grundlagen dazu findest du im Artikel Primary Key und Indizes im MS SQL Server.

4. Isolationsverhalten bewusst wählen

Bei Deadlocks zwischen lesenden und schreibenden Transaktionen kann zeilenversionsbasiertes Lesen helfen, etwa über READ_COMMITTED_SNAPSHOT. Eine solche Umstellung verändert jedoch das Leseverhalten der Datenbank und sollte vorab getestet werden. Sie löst auch nicht jede Form von Deadlock, insbesondere nicht automatisch Konflikte zwischen mehreren Schreibvorgängen.

5. Fehler 1205 in der Anwendung behandeln

Auch bei sorgfältiger Gestaltung lassen sich Deadlocks in konkurrierenden Systemen nicht immer vollständig ausschließen. Die Anwendung sollte Fehler 1205 erkennen und den gesamten betroffenen Transaktionsvorgang bei Bedarf erneut ausführen. Begrenze die Anzahl der Versuche und verwende eine kurze, möglichst leicht variierende Wartezeit. Prüfe außerdem, ob der Vorgang bei einer Wiederholung fachlich sicher ist, etwa bei Zahlungen oder externen Aufrufen.

Wiederholt sich derselbe Deadlock regelmäßig, ist eine Retry-Logik allein keine ausreichende Lösung. Dann musst du die im Graphen sichtbare Ursache beheben.

Häufige Fehler bei der Deadlock-Analyse

• Nur die Opfer-Abfrage betrachten: Der Graph zeigt mehrere beteiligte Abläufe. Untersuche ihre vollständigen Transaktionen.
• Blocking mit einem Deadlock verwechseln: Lange Wartezeiten ohne Fehler 1205 können eine andere Ursache haben und benötigen eine eigene Blocking-Analyse.
• Den falschen Vorfall auswerten: Gleiche UTC-Zeitstempel der Extended Events mit den Zeiten deiner Anwendungsprotokolle ab.
• Nur den ring_buffer prüfen: Ältere Ereignisse können dort bereits fehlen. Suche auch im event_file-Ziel.
• Eine Sitzung anlegen, aber nicht starten: STARTUP_STATE = ON sorgt für den Start nach einem künftigen Serverneustart. Für die sofortige Erfassung ist zusätzlich ALTER EVENT SESSION ... STATE = START nötig.
• Indizes auf Verdacht hinzufügen: Entscheidend sind die beteiligten Abfragen, ihre Ausführungspläne und die im Graphen genannten Ressourcen.

Fazit

Der zuverlässigste Weg zur Behebung eines Deadlocks beginnt beim konkreten Deadlock-Graphen. Prüfe zuerst die bereits aktive Sitzung system_health. Wenn du Ereignisse länger oder gezielt aufbewahren musst, richte eine eigene Extended-Events-Sitzung mit Dateiziel ein. Analysiere anschließend beide Seiten des Wartekreises und verbessere die betroffenen Transaktionen, Abfragen oder Zugriffsreihenfolgen. So behebst du die Ursache, statt lediglich die Fehlermeldung 1205 abzufangen.

Weiterführende Dokumentation

• Microsoft Learn: SQL Server Deadlocks Guide
• Microsoft Learn: Die system_health-Sitzung verwenden
• Microsoft Learn: CREATE EVENT SESSION
• Microsoft Learn: XEL-Dateien mit sys.fn_xe_file_target_read_file lesen