Wenn ein Benutzer zum ersten Mal die folgende Meldung sieht, die das Ergebnis eines unbehandelten Deadlock-Fehlers in SQL Server ist, kann das ein ziemlicher Schock sein.

Zwei oder mehr Sitzungen warteten darauf, eine Sperre für eine Ressource zu erhalten, die bereits von einer anderen Sitzung in derselben Sperrkette gesperrt war. Das Ergebnis ist eine "zirkuläre Kette", in der keine Sitzung abgeschlossen werden kann, so dass SQL Server gezwungen ist, einzugreifen, um das Durcheinander zu beseitigen. Er wählt automatisch eine der Sitzungen als "Opfer" aus, beendet sie und rollt ihre Transaktion zurück. Das bedeutet, dass die anderen Sitzungen fortgesetzt werden können, aber das ist ein schwacher Trost für das unglückliche Opfer, und wenn es sich bei diesem Opfer um eine Sitzung handelt, in der ein kritischer Geschäftsprozess abläuft, dann kann das auch für den DBA ein schwacher Trost sein.

Behandlung von Deadlock-Fehlern

Idealerweise sollte kein Benutzer jemals mit der Fehlermeldung 1205 konfrontiert werden, selbst wenn ein Deadlock auftritt. Jeder Anwendungscode, der Datenbankabfragen ausführt, sollte mit einer Fehlerbehandlung ausgestattet sein, die das Problem angemessen behandelt und dem aufrufenden Client eine "benutzerfreundliche" Meldung sendet. Außerdem sollte er über eine Wiederholungslogik verfügen, die eine erneute Ausführung der Opfertransaktion ermöglicht, sobald die Blockierung aufgehoben ist.

Dennoch führen Deadlock-Fehler zu Ressourcenkonflikten, Unterbrechungen und Verzögerungen und dürfen nicht ignoriert werden. DBAs müssen sofort wissen, wenn ein Deadlock auftritt, indem sie bei 1205-Fehlern eine Warnung ausgeben, und sie müssen dann alle notwendigen Informationen zur Hand haben, um den Deadlock zu beheben und sicherzustellen, dass er nicht wieder auftritt.

Auflösen von Deadlocks mit SQL Server-Leistungsüberwachung

Was können Sie tun, wenn Sie vermuten, dass auf Ihren SQL Server-Instanzen Deadlocks auftreten? Wir werden drei Ansätze für die Erkennung und Auflösung von Deadlocks erläutern. Bei jedem dieser Ansätze werden die Informationen, die den Deadlock beschreiben, nämlich der Deadlock-Graph, von SQL Server abgerufen. Dieser gibt Aufschluss über die blockierten Sitzungen, die ausgeführten Anweisungen, die Datenbank, die Objekte und Seiten, auf denen die Sitzungen blockiert waren, und mehr.

Traceflag 1222 - Methode 1

Es gab eine Zeit, in der die einzige Möglichkeit, das Deadlock-Diagramm zu erhalten, darin bestand, traceflag 1222 (oder 1204 auf SQL 2000) zu aktivieren, um es im Fehlerprotokoll zu erfassen. Abbildung 1 zeigt den Inhalt des Fehlerprotokolls, wobei der als Deadlock-Opfer ausgewählte Prozess hervorgehoben wird, und darüber die Ausgabe des Deadlock-Diagramms.

Abbildung 1 - das Fehlerprotokoll, wobei das Opfer eines blockierten Prozesses rot hervorgehoben ist

Wenn Sie das Traceflag jedoch nicht dauerhaft aktiviert haben, müssen Sie es aktivieren und warten, bis der Deadlock wieder auftritt. Wie Sie sehen, füllt SQL Server das Fehlerprotokoll mit einer Vielzahl von Informationen, und auch für den DBA ist hier noch eine Menge manueller Arbeit zu leisten, um die an der Blockierung beteiligten Sitzungen und Ressourcen (Tabellen, Indizes) zu ermitteln. Verwenden Sie diese Technik nur, wenn Sie keine andere Wahl haben. Für SQL Server 2008 und spätere Versionen gibt es viel bessere Möglichkeiten.

Erweiterte Ereignisse - Methode 2

Auf SQL Server 2008 oder höher ist die erweiterte Ereignissitzung system_health standardmäßig aktiviert und erfasst automatisch den Deadlock-Graphen. Sie können es im Nachhinein als Reaktion auf eine 1205-Fehlermeldung abrufen, indem Sie entweder eine T-SQL/XPath-Abfrage ausführen (siehe z. B. Listing 1 in Gail Shaws Artikel) oder den Zieldatenviewer der erweiterten Ereignis-UI in SSMS verwenden (nur SQL Server 2012 und höher), wie in Abbildung 2 dargestellt.

Abbildung 2 - Anzeigen von Zieldaten über die Benutzeroberfläche von SQL Server Management Studio

products.redgateMonitor.articles.monitorSQLDeadlock.p13

Durch Klicken auf ein xml_deadlock_report-Ereignis im Datenbetrachter können wir das Deadlock-Diagramm entweder als XML (Registerkarte Details) oder in grafischer Form (Registerkarte Deadlock) sehen.

Abbildung 3 - ein XML-Sperrbericht

Dieser Ansatz ist besser als die Verwendung von Traceflags, aber er erfordert immer noch, dass der DBA versiert im Lesen von XML-Sperrdiagrammen ist (die grafische Ausgabe "verbirgt" zu viele Informationen), und es fehlt immer noch das vollständige Bild dessen, was auf dem Server zum Zeitpunkt des Auftretens des Deadlocks geschah. Wurde das Problem verschlimmert, weil der Server zu diesem Zeitpunkt unter CPU-, IO- oder Speicherdruck stand? Welche anderen gleichzeitigen Aktivitäten fanden auf dem Server statt?

Redgate Monitor - Methode 3

Ein Tool zur Leistungsüberwachung von SQL Server wie Redgate Monitor soll dem Datenbankadministrator genügend Informationen zur Verfügung stellen, um einen Deadlock zu beheben, ohne dass er sich zu tief in ein XML-Deadlock-Diagramm vertiefen muss, und stellt diese Informationen in den Kontext des allgemeinen Aktivitätsmusters auf dem Server zu diesem Zeitpunkt. Dies erleichtert nicht nur die Fehlerbehebung bei Deadlocks, sondern macht auch die Fehlerbehebung bei der Leistung insgesamt viel effektiver.

Es erkennt eine Blockierung automatisch und löst eine Warnung aus, die per E-Mail an Ihren Posteingang gesendet wird. In der oberen Hälfte der Registerkarte Details auf dem Warnbildschirm in Redgate Monitor werden die Sitzungen, Abfragen und Datenbankobjekte, die an der Blockierung beteiligt sind, in verständlicher Form dargestellt.

Abbildung 4 - ein festgefahrener Prozess in Redgate Monitor, dem Tool von Redgate zur Überwachung der SQL-Serverleistung

Dieses Beispiel zeigt eine klassische Leser-Schreiber-Sperre, die am häufigsten auftritt, wenn wir Code haben, der Datenänderungen gefolgt von einer SELECT-Abfrage innerhalb einer expliziten Transaktion enthält. Sitzung 57 erhält eine exklusive (X) Sperre auf die Tabelle Person.Address, um sie zu aktualisieren, und liest dann aus Person.Person. Sitzung 62 erhält eine X-Sperre für die Tabelle Person.Person, um sie zu aktualisieren, und liest dann aus Person.Adresse. Der SELECT von Sitzung 57 kann wegen der X-Sperre von 62 nicht ausgeführt werden, und der SELECT von Sitzung 62 kann wegen der X-Sperre von 57 nicht ausgeführt werden. Dies ist die tödliche Umarmung, die als Deadlock bekannt ist; Sitzung 62 wird das Opfer.

Dies sind zwar alle Informationen, die Sie benötigen, aber auf der Registerkarte Ausgabe dieses Bildschirms sehen Sie bei Bedarf immer noch das vollständige XML-Sperrdiagramm.

Neben Informationen zu den beteiligten Prozessen liefert Redgate Monitor auch Informationen zu den Vorgängen, die zu diesem Zeitpunkt stattfanden. In der unteren Hälfte des Bildschirms werden Leistungszählerdaten, laufende Maschinenprozesse und deren Ressourcenverbrauch, teure Abfragen sowie die damit verbundenen Wartezeiten angezeigt. Die Linie in den Diagrammen gibt den Zeitpunkt an, zu dem der Deadlock auftrat.

Abbildung 5 - Redgate Monitor's Leistungsdatengrafiken

Auf der Registerkarte Top-Abfragen werden die Planhandles für die an der Blockade beteiligten Anweisungen angezeigt, so dass Sie deren Ausführungspläne abrufen können, falls ein Abfrage-Tuning erforderlich ist, um das Problem zu lösen (was häufig der Fall ist!).

Mögliche Lösungen in diesem Fall sind das Umschreiben der Transaktionen, so dass sie in der gleichen Reihenfolge auf die Tabellen zugreifen, oder vielleicht die Ausführung der Transaktionen unter Verwendung der Isolationsebene READ COMMITTED SNAPSHOT oder SNAPSHOT, wo die Leser keine gemeinsamen Sperren verwenden.

Zusammenfassung

Das Überprüfen von Fehlerprotokollen auf Deadlock-Informationen ist ein veralteter und zeitaufwändiger Weg, um das Problem zu beheben. Ein Tool wie Extended Events kann dem DBA bei der Lösung von Deadlocks helfen, erfordert jedoch zusätzliche Arbeit bei der Einrichtung von Warnmeldungen sowie eine Reihe anderer Ressourcen und Berichte zur Leistungsüberwachung von SQL Server, um die erforderlichen Leistungskennzahlen zu erfassen.

Ein Tool zur Leistungsüberwachung von SQL Server wie Redgate Monitor ermöglicht es einem DBA, den Schwerpunkt von der Sammlung und Verwaltung diagnostischer Daten auf die Problemlösung auf der Grundlage von Warnmeldungen zu verlagern, die genügend Informationen liefern, um den Stillstand schnell zu beheben, und ein umfassenderes Verständnis der Serveraktivität zum Zeitpunkt des Problems bieten.