Wer zum ersten Mal die folgende Meldung sieht, die bei einem unbehandelten Deadlock-Fehler in SQL Server auftritt, ist meist ziemlich überrascht - oder sogar geschockt.

Ein Deadlock entsteht, wenn zwei oder mehr Sessions gleichzeitig auf eine Ressource warten, die jeweils von einer anderen Session in derselben Kette blockiert ist. Es entsteht also eine „Sackgasse“, aus der keine Session mehr herauskommt. In so einem Fall greift SQL Server ein: Er beendet automatisch eine der Sessions - das sogenannte „Opfer“ - und setzt deren Transaktion zurück. Dadurch können die übrigen Sessions weitermachen. Für den betroffenen Prozess ist das allerdings ein schwacher Trost. Und wenn gerade ein geschäftskritischer Ablauf getroffen wird, ist auch der DBA nicht gerade begeistert.

Umgang mit Deadlock-Fehlern

Im besten Fall bekommen Anwender die Fehlermeldung 1205 gar nicht erst zu Gesicht, selbst wenn ein Deadlock passiert. Jeder Anwendungscode, der mit der Datenbank arbeitet, sollte deshalb eine saubere Fehlerbehandlung haben. Diese gibt dem aufrufenden Client eine verständliche Rückmeldung und enthält eine Wiederholungslogik, damit die unterbrochene Transaktion automatisch erneut ausgeführt werden kann, sobald die Blockierung vorbei ist.

Trotzdem sind Deadlock-Fehler alles andere als harmlos: Sie sorgen für Ressourcenkonflikte, Unterbrechungen und Verzögerungen - und sollten niemals ignoriert werden. DBAs müssen sofort mitbekommen, wenn ein Deadlock passiert. Das heißt: Bei jedem 1205-Fehler sollte eine Warnung ausgelöst werden. Und gleichzeitig braucht man alle wichtigen Infos, um die Ursache zu finden, den Deadlock zu beheben und langfristig zu verhindern.

Deadlocks mit SQL Server Performance Monitoring auflösen

Aber was tun, wenn Sie den Verdacht haben, dass auf Ihren SQL Server-Instanzen Deadlocks auftreten? Es gibt im Grunde drei gängige Wege, Deadlocks zu erkennen und aufzulösen. Allen gemeinsam ist: SQL Server stellt dafür den sogenannten Deadlock-Graphen bereit. Darin finden sich Details zu den betroffenen Sessions, den ausgeführten Statements, der Datenbank, den Objekten und Pages, auf denen blockiert wurde - kurz: alles, was Sie brauchen, um das Problem einzugrenzen.

Traceflag 1222 - Methode 1

Früher gab es nur eine Möglichkeit, an diesen Deadlock-Graphen zu kommen: Man musste Traceflag 1222 aktivieren (oder 1204 bei SQL Server 2000), damit der Graph im Fehlerprotokoll aufgezeichnet wurde. In Abbildung 1 sehen Sie so einen Eintrag - der Prozess, der als Deadlock-Opfer ausgewählt wurde, ist dort hervorgehoben, zusammen mit der Ausgabe des Deadlock-Diagramms.

Abbildung 1 - Fehlerprotokoll mit rot markiertem Deadlock-Opfer

Der Nachteil: Wenn das Traceflag nicht dauerhaft aktiv ist, müssen Sie es einschalten und warten, bis der Deadlock das nächste Mal auftritt. Außerdem füllt SQL Server das Fehlerprotokoll mit einer ganzen Menge an Informationen - und der DBA hat dann einiges an Handarbeit vor sich, um die beteiligten Sessions, Tabellen oder Indizes herauszufiltern. Diese Methode sollten Sie daher wirklich nur einsetzen, wenn es keine bessere Option gibt. Ab SQL Server 2008 stehen zum Glück deutlich komfortablere Möglichkeiten bereit.

Extended Events - Methode 2

Ab SQL Server 2008 ist die Extended-Event-Session `system_health` standardmäßig aktiv und zeichnet automatisch Deadlock-Graphen auf. Wenn also ein Fehler 1205 auftritt, können Sie die Details im Nachhinein abrufen - entweder über eine T-SQL-/XPath-Abfrage (siehe z. B. Listing 1 in Gail Shaws Artikel) oder über den Zieldaten-Viewer in der Extended-Event-Oberfläche von SSMS (ab SQL Server 2012), wie in Abbildung 2 gezeigt.

Abbildung 2 - Deadlock-Daten im SQL Server Management Studio anzeigen

In der Praxis können hier Tausende von Events auftauchen. Am besten klicken Sie im Datenviewer mit der rechten Maustaste und wählen „Filter by this Value“ - oder Sie nutzen das Menü Extended Events | Filter. Damit richten Sie einen Filter für die Spalte name ein, sodass nur Events mit dem Wert deadlock angezeigt werden.

Klicken Sie dann auf ein `xml_deadlock_report`-Event. Im Tab Details sehen Sie den Deadlock-Graphen in XML-Form, im Tab Deadlock bekommen Sie die grafische Ansicht.

Abbildung 3 - Ein XML-Lock-Report

Diese Methode ist deutlich komfortabler als die alten Traceflags. Allerdings muss man als DBA immer noch gut darin sein, XML-Deadlock-Diagramme zu lesen, denn die grafische Ansicht blendet viele Details aus. Außerdem fehlt auch hier der Gesamtüberblick: War der Server zum Zeitpunkt des Deadlocks vielleicht zusätzlich unter CPU-, IO- oder Speicherlast? Welche anderen Aktivitäten liefen parallel?

Redgate Monitor - Methode 3

Ein Performance-Monitoring-Tool wie Redgate Monitor nimmt Ihnen genau diese Arbeit ab. Es liefert alle relevanten Infos zu einem Deadlock, ohne dass Sie sich mühsam durch XML-Diagramme arbeiten müssen. Gleichzeitig setzt es die Deadlock-Daten in den Kontext der gesamten Serveraktivität. Das macht nicht nur die Deadlock-Analyse einfacher, sondern auch die generelle Performance-Fehleranalyse deutlich effizienter.

Sobald eine Blockade erkannt wird, schickt Redgate Monitor automatisch eine Warnung per E-Mail. Im oberen Bereich des Tabs „Details“ auf dem Warnbildschirm sehen Sie übersichtlich, welche Sessions, Queries und Datenbankobjekte an der Blockade beteiligt sind.

Abbildung 4 - Ein Deadlock in Redgate Monitor, dem Tool von Redgate zur Überwachung der SQL Server-Performance

Das gezeigte Beispiel ist ein klassischer Leser-Schreiber-Deadlock, wie er oft entsteht, wenn Code innerhalb einer Transaktion zuerst Daten ändert und danach eine `SELECT`-Abfrage ausführt. Session 57 setzt eine exklusive (X)-Sperre auf `Person.Address`, um sie zu aktualisieren, und liest anschließend aus `Person.Person`. Gleichzeitig sperrt Session 62 exklusiv die Tabelle `Person.Person` und will danach Daten aus `Person.Address` lesen. Das Ergebnis: Session 57 blockiert am Lock von 62, während Session 62 am Lock von 57 hängenbleibt. Diese „tödliche Umarmung“ ist der klassische Deadlock - und in diesem Fall wird Session 62 als Opfer beendet.

Wenn Sie es ganz genau wissen möchten, können Sie sich im Tab „Output“ zusätzlich das vollständige XML-Sperrdiagramm anzeigen lassen.

Neben den Informationen zu den beteiligten Prozessen zeigt Redgate Monitor auch, was auf dem Server zum Zeitpunkt des Deadlocks los war. In der unteren Bildschirmhälfte sehen Sie Leistungszähler, laufende Maschinenprozesse mit ihrem Ressourcenverbrauch, auffällige Queries und deren Wartezeiten. Eine Markierung in den Diagrammen zeigt exakt den Moment, an dem der Deadlock aufgetreten ist.

Abbildung 5 - Diagramme der Performance-Daten in Redgate Monitor

Im Tab „Top Queries“ sehen Sie die Plan Handles der beteiligten Statements. Damit lassen sich die Ausführungspläne abrufen - oft ein entscheidender Schritt, wenn Query-Tuning notwendig ist, um das Problem zu beseitigen.

Typische Lösungen sind, die Transaktionen so umzuschreiben, dass sie die Tabellen in derselben Reihenfolge ansprechen, oder die Isolationsebene `READ COMMITTED SNAPSHOT` bzw. `SNAPSHOT` zu verwenden. Bei diesen Modi setzen Leseabfragen keine Shared Locks mehr, was Deadlocks oft verhindert.

Zusammenfassung

Deadlocks über Fehlerprotokolle zu analysieren, ist mühsam und nicht mehr zeitgemäß. Extended Events liefern zwar wertvolle Infos, erfordern aber zusätzliche Einrichtung und die Kombination mit weiteren Reports und Monitoring-Daten, um ein klares Bild der Performance zu bekommen.

Mit einem Monitoring-Tool wie Redgate Monitor kann sich der DBA ganz auf die Problemlösung konzentrieren. Statt Daten manuell zu sammeln, erhält man rechtzeitig Warnmeldungen mit allen wichtigen Informationen und gleichzeitig einen umfassenden Einblick in die Serveraktivität zum Zeitpunkt des Deadlocks.