Wenn eine Anwendung plötzlich mit einem Fehler abbricht, obwohl die Datenbank normalerweise funktioniert, kann ein Deadlock die Ursache sein. SQL Server Deadlocks entstehen, wenn sich mindestens zwei gleichzeitig laufende Transaktionen gegenseitig blockieren und keine davon fortfahren kann. SQL Server löst die Situation auf, indem es eine Transaktion als Opfer beendet. Die gute Nachricht: Mit Extended Events lässt sich der Ablauf meist detailliert nachvollziehen. Die nachhaltige Lösung besteht aber darin, die konkrete Ursache zu finden – nicht einfach überall Sperren zu erzwingen oder die Isolation zu ändern.

Inhaltsverzeichnis

Blockierung oder Deadlock: Was ist der Unterschied?

Eine Blockierung (blocking) ist zunächst ein normaler Bestandteil des Mehrbenutzerbetriebs. Eine Transaktion hält eine Sperre, die mit einer angeforderten Sperre einer anderen Sitzung nicht vereinbar ist. Die zweite Sitzung muss warten. Sobald die erste Transaktion committet oder zurückgerollt wird, kann die wartende Sitzung weiterarbeiten.

Ein Deadlock (Verklemmung) ist dagegen ein Zyklus gegenseitigen Wartens: Sitzung A hält eine Ressource, die Sitzung B benötigt, während B eine andere Ressource hält, auf die A wartet. Keine der beiden Transaktionen kann aus eigener Kraft fortfahren. SQL Server erkennt den Zyklus automatisch und beendet eine Transaktion als Deadlock-Opfer. Die Anwendung erhält in der Regel den Fehler 1205; die betroffene Transaktion wird zurückgerollt.

Vereinfacht dargestellt:

  • Blockierung: A hält X, B wartet auf X. A kann fertig werden – danach läuft B weiter.
  • Deadlock: A hält X und wartet auf Y; B hält Y und wartet auf X. SQL Server muss eine Transaktion abbrechen.

Eine lange Blockierung kann sich für Anwender ähnlich wie ein Deadlock anfühlen, ist aber technisch etwas anderes. Bei einer Blockierung gibt es keinen Wartungszyklus, den SQL Server auflösen müsste. Deshalb sollte man bei langsamen Abfragen nicht automatisch einen Deadlock vermuten: Prüfe zuerst, ob tatsächlich ein Deadlock-Ereignis aufgezeichnet wurde.

SQL Server Deadlocks mit Extended Events erkennen

Extended Events (erweiterte Ereignisse) sind SQL Servers leichtgewichtige Infrastruktur, um Ereignisse gezielt aufzuzeichnen. Für Deadlocks ist das Ereignis sqlserver.xml_deadlock_report relevant: Es enthält den Deadlock-Graphen mit den beteiligten Sitzungen, Ressourcen und Anweisungen. SQL Server zeichnet Deadlock-Berichte außerdem standardmäßig in der Sitzung system_health auf.

Schnellstart: system_health in SSMS prüfen

  1. Öffne in SQL Server Management Studio den Objekt-Explorer und gehe zu Management > Extended Events > Sessions.
  2. Öffne system_health und rufe das Ziel package0.event_file beziehungsweise die aufgezeichneten Ereignisse auf. Je nach SSMS-Version kannst du die Daten über View Target Data anzeigen.
  3. Filtere nach xml_deadlock_report und öffne das XML des Ereignisses. Im Ereignis-Viewer kann der Deadlock-Graph oft direkt grafisch dargestellt werden.

Alternativ kannst du – sofern die Sitzung und ihr Ringpuffer noch Daten enthalten – die Deadlock-Graphen mit folgender Abfrage auslesen. Die DMV-Abfrage benötigt passende Serverberechtigungen; der Ringpuffer ist begrenzt und keine dauerhafte Historie.

;WITH SystemHealth AS
(
    SELECT CAST(target_data AS xml) AS target_data
    FROM sys.dm_xe_session_targets AS t
    INNER JOIN sys.dm_xe_sessions AS s
        ON s.address = t.event_session_address
    WHERE s.name = N'system_health'
      AND t.target_name = N'ring_buffer'
)
SELECT
    XEvent.value('(@timestamp)[1]', 'datetime2') AS utc_time,
    XEvent.query('(data/value/deadlock)[1]') AS deadlock_graph
FROM SystemHealth
CROSS APPLY target_data.nodes(
    '//RingBufferTarget/event[@name="xml_deadlock_report"]'
) AS Events(XEvent)
ORDER BY utc_time DESC;

Für eine längerfristige oder gezielte Aufzeichnung empfiehlt sich eine eigene Extended-Events-Sitzung mit dem Ziel event_file. So bleiben Ereignisse über Neustarts hinweg verfügbar, bis die Dateien nach deiner Aufbewahrungsrichtlinie entfernt werden.

Eigene Extended-Events-Sitzung für Deadlocks

Das folgende Beispiel zeichnet ausschließlich Deadlock-Berichte auf. Erstelle das Verzeichnis D:\XEvents vorher auf dem SQL-Server und gib dem SQL-Server-Dienstkonto Schreibrechte darauf. Passe den Pfad an deine Umgebung an. Das Beispiel legt eine serverweite Sitzung an und benötigt dafür entsprechende Berechtigungen.

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

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

Mit STARTUP_STATE = ON startet die Sitzung nach einem Neustart des SQL-Server-Dienstes automatisch. Die Konfiguration allein startet sie nicht sofort; dafür sorgt das anschließende ALTER EVENT SESSION ... STATE = START. In einer Testumgebung kannst du die Sitzung später so stoppen und entfernen:

ALTER EVENT SESSION [DeadlockReports]
ON SERVER
STATE = STOP;
GO

DROP EVENT SESSION [DeadlockReports]
ON SERVER;
GO

Die aufgezeichneten Ereignisse lassen sich mit der Dateifunktion lesen. Der Pfad muss zum Speicherort der XEL-Dateien auf dem SQL-Server passen:

SELECT
    event_xml.value('(/event/@timestamp)[1]', 'datetime2') AS utc_time,
    event_xml.query(
        '(/event/data[@name="xml_report"]/value/deadlock)[1]'
    ) AS deadlock_graph
FROM
(
    SELECT CAST(event_data AS xml) AS event_xml
    FROM sys.fn_xe_file_target_read_file(
        N'D:\XEvents\DeadlockReports*.xel',
        NULL,
        NULL,
        NULL
    )
) AS DeadlockEvents
ORDER BY utc_time DESC;

Die XEL-Dateien liegen auf dem Server, nicht notwendigerweise auf deinem Arbeitsplatzrechner. Gib den tatsächlichen Dateipfad an und beachte, dass die Dateifunktion passende Berechtigungen voraussetzt. In produktiven Umgebungen solltest du außerdem Aufbewahrungsdauer und Speicherverbrauch überwachen.

Einen Deadlock-Graphen analysieren

Ein Deadlock-Graph (Deadlock graph) zeigt, wer welche Ressource besitzt und worauf die beteiligten Prozesse warten. Bei der Analyse helfen insbesondere diese Bereiche:

  • Victim list: Welche Sitzung wurde als Opfer ausgewählt? Das erklärt den Fehler 1205, aber nicht automatisch die Ursache.
  • Process list: Welche Abfragen, Datenbanken, Transaktionen und Sperrmodi sind beteiligt? Achte auf inputbuf und executionStack; sie können die ausgeführte Anweisung und den Prozedurkontext zeigen.
  • Resource list: Auf welchen Tabellen-, Schlüssel-, Seiten- oder anderen Ressourcen entsteht der Konflikt? Vergleiche jeweils den Besitzer und den wartenden Prozess.
  • Lock mode: Welche Sperrmodi werden gehalten oder angefordert? Beispielsweise steht X für exklusiv und S für gemeinsam lesend.

Verfolge die Pfeile zwischen Prozessen und Ressourcen, bis der Zyklus sichtbar wird. Ordne anschließend die Ressourcen den konkreten Tabellen, Indizes und Abfragen zu. Ein Deadlock auf Schlüsseln eines Indexes kann auch durch eine Abfrage entstehen, die scheinbar eine andere Spalte ändert – SQL Server muss beim Ändern eines Datensatzes gegebenenfalls mehrere Indizes aktualisieren.

Ein Graph ist eine Momentaufnahme. Prüfe daher mehrere Berichte: Wiederholen sich dieselben Prozeduren, Ressourcen und Zugriffsmuster? Ergänze die Analyse mit Ausführungsplänen und Laufzeitdaten. Der Beitrag Indexanalyse im SQL Server mit Query Store, Ausführungsplänen und DMVs kann dir dabei helfen, problematische Abfragen und passende Indizes einzuordnen.

Deadlock in T-SQL reproduzieren

Das folgende Beispiel zeigt die klassische Ursache: Zwei Transaktionen aktualisieren dieselben Datensätze, aber in unterschiedlicher Reihenfolge. Führe zuerst den Einrichtungsblock einmal in einer Testdatenbank aus. Die Tabellen sind absichtlich klein und dienen nur der Demonstration.

CREATE TABLE dbo.DeadlockDemo
(
    Id int NOT NULL CONSTRAINT PK_DeadlockDemo PRIMARY KEY,
    Balance int NOT NULL
);
GO

INSERT INTO dbo.DeadlockDemo (Id, Balance)
VALUES (1, 100), (2, 200);
GO

Öffne zwei Abfragefenster mit derselben Testdatenbank. Starte zuerst das Skript für Sitzung A. Während es fünf Sekunden wartet, starte Sitzung B. Die Wartezeit soll nur dafür sorgen, dass sich die Sperren im Beispiel zuverlässig überlappen.

Sitzung A: zuerst Datensatz 1, dann Datensatz 2

BEGIN TRANSACTION;

UPDATE dbo.DeadlockDemo
SET Balance = Balance + 10
WHERE Id = 1;

WAITFOR DELAY '00:00:05';

UPDATE dbo.DeadlockDemo
SET Balance = Balance + 10
WHERE Id = 2;

COMMIT TRANSACTION;

Sitzung B: zuerst Datensatz 2, dann Datensatz 1

BEGIN TRANSACTION;

UPDATE dbo.DeadlockDemo
SET Balance = Balance + 20
WHERE Id = 2;

WAITFOR DELAY '00:00:05';

UPDATE dbo.DeadlockDemo
SET Balance = Balance + 20
WHERE Id = 1;

COMMIT TRANSACTION;

Wenn sich die Ausführung wie geplant überschneidet, hält Sitzung A eine Sperre auf Datensatz 1 und Sitzung B eine Sperre auf Datensatz 2. Anschließend wartet A auf die von B gehaltene Ressource und B auf die von A gehaltene Ressource. SQL Server erkennt den Zyklus, rollt eine Transaktion zurück und meldet in dieser Sitzung Fehler 1205. Je nach Timing kann das Beispiel auch ohne Deadlock durchlaufen; starte die Skripte dann erneut mit engerem zeitlichem Abstand. Führe es ausschließlich in einer geeigneten Testdatenbank aus.

In einer Anwendung sollte der Fehler 1205 kontrolliert behandelt werden. Falls die Operation wiederholt werden darf, wiederhole die gesamte fachliche Transaktion begrenzt und mit kurzer Verzögerung. Prüfe vorher, dass ein erneuter Versuch keine externen Nebenwirkungen doppelt ausführt – etwa eine Zahlung oder eine Nachricht, die außerhalb der SQL-Transaktion bereits versendet wurde.

Typische Ursachen für SQL Server Deadlocks

Inkonsistente Zugriffreihenfolge

Die beiden Transaktionen im Beispiel greifen auf dieselben Datensätze in umgekehrter Reihenfolge zu. Das passiert auch bei verschiedenen Tabellen: Eine Prozedur aktualisiert erst Bestellung und dann Lagerbestand, eine andere zuerst Lagerbestand und anschließend Bestellung. Eine einheitliche Reihenfolge für alle Codepfade reduziert solche Zyklen oft deutlich.

Lange oder unnötig umfangreiche Transaktionen

Je länger eine Transaktion Sperren hält, desto größer ist das Zeitfenster für konkurrierende Zugriffe. Häufige Gründe sind umfangreiche Verarbeitung innerhalb eines BEGIN TRANSACTION-Blocks, Benutzerinteraktionen während einer offenen Transaktion, langsame externe Aufrufe oder das Aktualisieren vieler Zeilen auf einmal. Halte die Transaktion auf die notwendigen Datenbankarbeiten begrenzt.

Fehlende oder ungeeignete Indizes

Findet SQL Server die betroffenen Zeilen nicht gezielt, muss er unter Umständen mehr Zeilen oder Schlüssel untersuchen und länger Sperren halten. Ein passender Index kann die Arbeit und damit die Sperrdauer verringern. Zusätzliche Indizes kosten jedoch Speicherplatz und machen Schreibvorgänge aufwendiger, weil sie mitgepflegt werden müssen. Prüfe daher Ausführungsplan, Abfrage und Schreiblast gemeinsam. Weitere Informationen findest du unter SQL Server: Beiträge zu Indizes und Datenbanken.

Unterschiedliche Abfragepläne und Sperrressourcen

Auch wenn zwei Abfragen Tabellen in derselben logischen Reihenfolge verwenden, können unterschiedliche Pläne verschiedene Indizes oder Schlüssel zuerst ansteuern. Trigger, Fremdschlüsselprüfungen und Indexpflege können weitere Ressourcen einbeziehen. Analysiere deshalb den konkreten Graphen und den zum Ereignis passenden Ausführungsplan, statt allein aus dem Anwendungscode auf die Zugriffreihenfolge zu schließen.

Gegenmaßnahmen – und warum es keine Pauschallösung gibt

1. Zugriffreihenfolge vereinheitlichen

Wenn mehrere Abläufe dieselben Ressourcen ändern, sollten sie diese möglichst in derselben Reihenfolge bearbeiten. Im Beispiel würden beide Transaktionen zuerst Id = 1 und danach Id = 2 aktualisieren. Das beseitigt genau diesen Zyklus, löst aber nicht automatisch Deadlocks mit anderen Tabellen, Indizes oder Zugriffspfaden.

2. Transaktionen verkürzen und Arbeit gezielt begrenzen

Bereite Werte vor einer Transaktion vor, vermeide Wartezeiten und Netzwerkaufrufe innerhalb des Transaktionsblocks und ändere nur die benötigten Zeilen. Bei großen Verarbeitungsläufen können kleinere, fachlich sichere Batches die Dauer einzelner Sperrphasen reduzieren. Ein Batch-Ansatz verändert allerdings die Atomarität: Nicht mehr zwingend der gesamte Lauf wird als eine Einheit committet. Das muss zur fachlichen Anforderung passen.

3. Abfragen und Indizes prüfen

Nutze den Deadlock-Graphen, Query Store und Ausführungspläne, um unnötige Scans, breite Änderungen oder häufige Zugriffspfade zu erkennen. Ergänze Indizes nur, wenn Messungen den Nutzen stützen. Jeder Index hat auch Nachteile: mehr Speicherbedarf sowie zusätzliche Arbeit bei INSERT, UPDATE und DELETE.

4. Isolation gezielt bewerten

READ_COMMITTED_SNAPSHOT kann bestimmte Konflikte zwischen lesenden und schreibenden Transaktionen reduzieren, weil Lesezugriffe versionierte Daten lesen können. Die Option verhindert jedoch nicht alle Deadlocks, insbesondere nicht Konflikte zwischen konkurrierenden Änderungen. Sie verändert das Leseverhalten und nutzt den Versionsspeicher; teste Auswirkungen und Kapazität, bevor du sie datenbankweit aktivierst. Auch SNAPSHOT-Isolation ist kein universeller Deadlock-Schalter und kann bei konkurrierenden Änderungen zu Update-Konflikten führen.

Lock-Hints wie NOLOCK, UPDLOCK oder ROWLOCK sollten nicht reflexartig hinzugefügt werden. Sie können Datenkonsistenz, Sperrverhalten oder Ausführungspläne verändern und neue Probleme schaffen. Insbesondere ist NOLOCK keine zuverlässige Reparatur für Deadlocks: Es erlaubt unter anderem inkonsistente Leseergebnisse und verhindert keine Deadlocks zwischen schreibenden Transaktionen.

5. Deadlock-Priorität und Wiederholungen bewusst einsetzen

Mit SET DEADLOCK_PRIORITY lässt sich beeinflussen, welche Transaktion SQL Server bei einem Deadlock bevorzugt als Opfer auswählt. Das verhindert den Deadlock nicht, sondern steuert nur die Auswahl. Eine begrenzte Wiederholung nach Fehler 1205 ist für geeignete, wiederholbare Operationen eine sinnvolle Resilienzmaßnahme – aber kein Ersatz für die Ursachenanalyse. Verzögerung, maximale Versuchszahl und Protokollierung gehören zu einer robusten Umsetzung.

Checkliste: Deadlocks systematisch angehen

  • Ist ein xml_deadlock_report vorhanden, oder handelt es sich um eine Blockierung?
  • Welche Prozesse und Ressourcen bilden den Zyklus im Graphen?
  • Welche Prozeduren und Anweisungen stehen in inputbuf und executionStack?
  • Greifen die beteiligten Transaktionen in unterschiedlicher Reihenfolge auf dieselben Tabellen oder Schlüssel zu?
  • Sind Transaktionen unnötig lang oder ändern sie mehr Zeilen als erforderlich?
  • Gibt es passende Ausführungspläne und Indizes, und wurde deren Wirkung unter realer Last getestet?
  • Wird Fehler 1205 in der Anwendung sicher und begrenzt behandelt?
  • Sind Extended-Events-Dateien, Berechtigungen und Aufbewahrung für den Betrieb angemessen konfiguriert?

Fazit: Erst den Deadlock verstehen, dann gezielt ändern

SQL Server Deadlocks sind kein Zeichen dafür, dass die Datenbank grundsätzlich fehlerhaft arbeitet. Sie sind ein Hinweis darauf, dass konkurrierende Transaktionen sich in einem bestimmten Ablauf gegenseitig blockieren. Extended Events – insbesondere xml_deadlock_report und die Sitzung system_health – liefern die nötigen Informationen, um diesen Ablauf zu untersuchen.

Beginne mit dem Deadlock-Graphen, prüfe Zugriffreihenfolge, Transaktionsdauer, Ausführungspläne und Indizes und ändere anschließend gezielt. Pauschale Maßnahmen wie überall NOLOCK einzusetzen oder die Isolation ohne Tests umzustellen, können neue Fehler verursachen. Und auch wenn Backups Deadlocks nicht verhindern: Eine verlässliche Sicherungs- und Wiederherstellungsstrategie bleibt für den Betrieb wichtig. Dazu passt der IT-Fundus-Beitrag MSSQL Backup und Restore sowie weitere SQL-Server-Beiträge.