3 Arten von SQL Server-Deadlocks
Betrachten wir nun Beispiele für 3 Arten von Deadlocks. Die dargestellten Screenshots stammen aus Deadlock-Diagrammen in SolarWinds Plan Explorer. Plan Explorer ist ein kostenloses Abfrageanalyse- und Optimierungstool, das mit erweitertem Funktionsumfang Teil von SolarWinds SQL Sentry ist.
Nr. 1: Vorgangsreihenfolge-Deadlock
Die meisten Menschen denken bei SQL Server-Deadlocks an Vorgangsreihenfolge-Deadlocks. Hierbei handelt es sich auch um einen vermeidbaren Deadlock. Dieser SQL Server-Deadlock tritt auf, wenn exklusive Sperren verwendet werden oder Sperren zwischen verschiedenen Prozessen, die die jeweiligen Ressourcen benötigen, eskalieren.
Das folgende Beispiel stammt aus dem Plan Explorer Demo Kit, das Aaron Bertrand zusammengestellt hat. Sie können das Kit herunterladen, um es selbst auszuprobieren.
Im Screenshot rechts sind die Wiedergabesteuerelemente hervorgehoben, um zu verdeutlichen, dass der Screenshot gegen Ende der „Wiedergabe“ des Deadlocks aufgenommen wurde. Anhand der nachfolgenden Beispiele werden Sie feststellen, dass SQL Server-Deadlocks weitaus komplizierter sein können als dieser SQL Server-Deadlock. Die Möglichkeit, die angeforderte Reihenfolge der Sperroperationen wiederzugeben, kann beim Verhindern eines Deadlocks sehr hilfreich sein.
Anhand dieses Beispiels können Sie sehen, dass SPID 63 eine exklusive Sperre für den Primärschlüssel in „InvoiceLines“ eingerichtet hat. SPID 63 erfordert zudem eine exklusive Sperre für den Primärschlüssel in „Invoices“. SPID 64 hat eine exklusive Sperre für den Primärschlüssel in „Invoices“ eingerichtet und benötigt eine exklusive Sperre für den Primärschlüssel in „InvoiceLines“. Weder SPID 63 noch SPID 64 können fortgesetzt werden, und SPID 63 wurde als „Opfer“ ausgewählt.
Wird im Diagramm SPID 63 ausgewählt, werden die Details sofort im Rasterformat angezeigt. So können Sie besser verstehen, wo beim Opfer-Prozess der Fehler aufgetreten ist und welche Aktion der Prozess auszuführen versucht hat. Zudem können Sie Details zu den anderen Prozessen im Raster anzeigen, um einen Überblick über die beteiligten Objekte und SQL-Anweisungen zu erhalten.
Ansicht der Deadlock-Opfer-Details im SQL Sentry Deadlock-Detailraster
Wie kann ich einen Vorgangsreihenfolge-Deadlock verhindern?
Auf diese Frage gibt es keine eindeutige Antwort. Dieser Deadlock könnte durch unterschiedliches Timing von Operationen, die Optimierung von Abfragen mit dem Ziel der schnelleren Ausführung, eine Änderung der Transaktionsisolationsebene oder eine Reihe anderer Methoden vermieden werden.
Hier ist eine Methode: Wenn Deadlocks bei Transaktionen auftreten, die Sperren eskalieren, kann es helfen, nur die Datenzeilen zu isolieren, die geändert werden müssen. Legen Sie alle „Listen“-Informationen, die Sie außerhalb des Transaktionsbereichs benötigen, in einer temporären Tabelle ab. Auf diese Weise können Sie Sperren bei Objekten vermeiden, die nicht als Teil der Transaktion gesperrt werden müssen. Dies beschleunigt zudem den gesamten Vorgang, da die Komplexität von Aktualisierungsabfragen reduziert wird, die sich mit Tabellen verbinden, die nicht aktualisiert werden. Stattdessen können sie eine Verbindung zu Ihrer temporären Haltetabelle herstellen.
Ein Vorgangsreihenfolge-Deadlock kann behoben oder vermieden werden, es kann jedoch etwas Zeit und heuristische Fehlersuche erfordern, den Idealzustand zu erreichen.
Nr. 2: Lookup-Deadlock
Greg Gonzalez schätzt, dass der Großteil der SQL Server-Deadlocks, die er erlebt hat, Lookup-Deadlocks sind. Wie Greg jedoch in „Resolving Key Lookup Deadlocks with Plan Explorer" erwähnt, kann dieser Deadlock-Typ in der Regel einfach behoben werden.
Lesen Sie unbedingt Gregs Blogbeitrag zu Lookup-Deadlocks, aber lassen Sie uns zunächst die Grundlagen besprechen. Lookup-Deadlocks treten auf, wenn eine Abfrage einen Lookup-Vorgang im Plan umfasst, was bedeutet, dass die Engine einen oder mehrere Werte von einem anderen Ort als dem Index, mit dem die Abfrage durchgeführt wird, abrufen muss. Die Werte müssen über einen Schlüssel oder RID-Wert „nachgeschlagen“ werden. Hierfür dienen die Plan-Vorgänge „Key Lookup“ oder „RID Lookup“. Deadlocks können auftreten, wenn der Nachschlagevorgang in einem Zeilenbereich durchgeführt wird, in dem häufig Aktualisierungs- oder Löschvorgänge vorgenommen werden.
Zum Vergrößern klicken: Ein Schlüsselsuche-Abfrageplanvorgang in Plan Explorer, der Plan ist an einem Lookup-Deadlock beteiligt
Nr. 3: Parallelitäts-Deadlock
Einige Deadlocks treten innerhalb desselben Prozesses auf, der auf verschiedenen Threads ausgeführt wird. Diese Deadlocks werden als Parallelitäts-Deadlocks bezeichnet, da sie auftreten, wenn der Abfrageplan Parallelitätsvorgänge verwendet und die Deadlock-Details parallele Austauschereignisse umfassen.
Diese Art von Deadlock ist schwer zu prognostizieren oder zu vermeiden. Bei einer Aktualisierung auf SQL Server 2017 erkannte Microsoft die Gelegenheit, uns Deadlocks besser verständlich zu machen. Obwohl diese zusätzlichen Deadlock-Informationen sehr hilfreich sind, macht es die Natur dieser Deadlocks immer noch sehr schwer, sie proaktiv zu handhaben.
Die Abbildung auf der rechten Seite ist ein Übersichtsdiagramm eines parallelen Austausch-Deadlocks aus Plan Explorer. Wie Sie sehen können, kann das Auftreten eines dieser Deadlocks sehr unangenehm sein. Wenn Sie das Diagramm verkleinern, um die gesamte Grafik betrachten zu können, erinnert dies an String Art.
Wenn Sie genau hinsehen, können Sie immer noch kleine Knoten erkennen, die Prozesse, Sperren und Anmerkungen darstellen. Dieses komplizierte Diagramm ist in dieser Form nicht sehr hilfreich. SQL Sentry und Plan Explorer bieten hier nützliche Funktionen.
SQL Server-Deadlock-Analyse mit SQL Sentry
Sehen wir uns nun eines der besten Features von SQL Sentry zum Umgang mit Deadlocks an. Es handelt sich um die nachfolgend beschriebene Option „Optimize Layout“ (Layout optimieren), die sich ganz rechts neben den Wiedergabesteuerelementen am unteren Rand der Deadlock-Analyseansicht befindet.
Durch die Optimierung des Layouts werden viele der sich wiederholenden Knoten entfernt, die keine Informationen liefern, die Ihnen bei der Fehlersuche helfen würden. Dadurch wird das Diagramm, mit dem Sie begonnen haben, in seiner Komplexität reduziert.
Das Diagramm ist visuell immer noch schwierig zu verstehen. Aber sobald wir es vergrößern, können wir hilfreiche Details erkennen.
Die Ansicht oben ist stark genug vergrößert, um einzelne Knoten zu erkennen, und ich habe den Layouttyp in „Force Directed“ (Kräftebasiert) geändert. Der Layouttyp bietet verschiedene Möglichkeiten, das Diagramm darzustellen. Dies ist bei komplizierten Deadlocks wie in diesem Fall sehr nützlich. Sie können sehen, wie selbst in diesem kleinen Ausschnitt des Diagramms alles auf das Parallelitäts-Austauschereignis verweist.
Die Layoutänderung hat außerdem den weiteren Vorteil, dass einige Knoten, die nicht direkt am SQL Server-Deadlock beteiligt waren, am Rand platziert wurden. Diese sind nun aus dem Weg geräumt, und Sie können besser ermitteln, welche Auswirkungen dieser monströse Deadlock gehabt haben könnte.
Wie kann ich einen Parallelitäts-Deadlock verhindern?
Das ist eine schwierige Frage, weil es keine eindeutige Antwort gibt. In diesem StackExchange-Thread finden Sie gute Ratschläge, und Sie sollten Folgendes berücksichtigen:
- Widerstehen Sie der Versuchung, MAXDOP auf „1“ zu setzen. Es ist scheinbar eine einfache Lösung, durch Deaktivierung der Parallelität das Deadlock-Risiko zu eliminieren. Allerdings könnten dadurch signifikante, anhaltende Leistungsprobleme entstehen. Der SQL Server-Deadlock erscheint beängstigend, aber in Wirklichkeit ist er weit weniger problematisch als langfristige Leistungsprobleme. Diese Deadlocks haben zudem oft keine wirklichen Auswirkungen .
- Diese Art von SQL Server-Deadlock ist am häufigsten bei ORM-Frameworks zu beobachten. Einige Datenexperten raten, ORM nicht zu verwenden, andere empfehlen, sich die Zeit zu nehmen, um ORM wirklich zu beherrschen. Wenn Sie das Framework gut genug kennen, können Sie Wege finden, einfachere Abfragen zu generieren. Bei diesen ist das Risiko geringer, diesen Deadlock und anderweitige Leistungsprobleme auszulösen.
- Vergessen Sie Modellierung und Design nicht. Eine gut konzipierte Datenbank, die Transaktionen und Abfragen berücksichtigt, ist später der Freund des Entwicklers. Diese Abfragen werden oft so kompliziert, weil das zugrunde liegende Modell nicht auf Ihre Aktionen ausgelegt ist. Obwohl eine Aktualisierung des Schemas nicht immer praktikabel ist, sollten Sie nach Möglichkeit diese Option nutzen.
SQL Server-Deadlocks erkennen
SQL Sentry hilft Ihnen, SQL Server-Deadlocks schnell zu erkennen und zu erfassen. Auf der Registerkarte „Deadlocks“ im SQL Sentry Performance Analysis Dashboard finden Sie einen umfassenden Überblick über SQL Server-Deadlocks. Hier können Sie alle Prozesse und Ressourcen, die an einem Deadlock beteiligt sind, visuell darstellen. Über die Rasteransicht können Sie detaillierte Analysen zu den Anweisungen erstellen, die ausgeführt wurden, als der Deadlock erkannt wurde. Es ist nicht erforderlich, Agents zu installieren oder Ablaufverfolgungsflags zu setzen. Sie müssen auch keine besondere Konfiguration der überwachten Server vornehmen. Es funktioniert einfach problemlos.
Mit SQL Sentry ist es nicht notwendig, SPIDs oder Referenz-IDs zu übersetzen. Die Host-, Anwendungs- und Ressourcennamen werden im Diagramm beschriftet. Sie können das SQL-Deadlock-Opfer (rot dargestellt) sowie die Sperrtypen leicht erkennen, und zwar in der Reihenfolge, in der sie angewendet wurden. Mithilfe dieser Funktionalität können Sie eine erfolgreiche Deadlock-Analyse durchführen.
In SQL Sentry erfasste Deadlocks können als .xdl-Dateien exportiert werden. XDL ist das XML-Format von Microsoft für SQL Server-Deadlocks. Die Dateien können geteilt und in Plan Explorer wie eine Plandatei geöffnet werden. Sie können die Deadlock-Datei in der Deadlock-Rasteransicht anzeigen. Hier ist jede Datei erweiterbar, sodass Sie die Details der Prozesse sehen können, die am SQL Server-Deadlock beteiligt waren. Mithilfe des Deadlock-Diagramms können Sie auch das Opfer und die Prozess- und Ressourcenknoten sowie alle Beziehungen, die zwischen diesen auftreten, anzeigen.
SQL Sentry und Plan Explorer kombinieren
Verwenden Sie Plan Explorer (kostenlos!) und SQL Sentry zusammen, um sich bestmöglich vor SQL Server-Deadlocks zu schützen. Verteilen Sie SQL Sentry an DBAs und Plan Explorer sowie das SQL Sentry Portal an alle. Sie werden bald feststellen, dass sich die Behebung von Deadlocks eher wie eine Einladung zu einer Party anfühlt, als eine lästige, stressige Pflicht.
Plan Explorer
Mit dem kostenlosen Plan Explorer können Sie die Ursache von SQL Server-Abfrageproblemen einfach ermitteln.
