SQL-Deadlocks

Alles, was Sie über SQL Server-Deadlocks wissen müssen

deadlocks

Aktualisiert: 3. Juni 2020

Inhaltsverzeichnis:

 

Was ist ein SQL Server-Deadlock?

Die SQL Server-Transaktionsverarbeitung ist auf hohe Gleichzeitigkeit ausgelegt. Das bedeutet, dass im Allgemeinen viele Dinge zugleich durchgeführt werden. Es müssen Sperrmechanismen vorhanden sein, um die Integrität Ihrer Daten bei gleichzeitiger Verarbeitung zu schützen. Ein SQL Server-Deadlock tritt auf, wenn exklusive Sperren bei Ressourcen bestehen, die von mehreren Prozessen benötigt werden, und diese Prozesse daher nicht abgeschlossen werden können.

Sind SQL Server-Deadlocks ein Problem?

Ja und nein. Einerseits führt ein Deadlock dazu, dass einer der Prozesse fehlschlägt. Andererseits schützen Deadlocks die Integrität Ihrer Daten bei hochgradig gleichzeitigen Anwendungen. Deadlocks und Blockierungen entsprechen normalem Verhalten in einem sperrenbasierten Gleichzeitigkeitssystem wie SQL Server. Ob sie ein Problem darstellen, hängt davon ab, wie Sie diese einplanen und handhaben, wenn sie auftreten.

Was geschieht nach einem Deadlock?

SQL Server kann einen erkannten Deadlock nur lösen, indem einer der Prozesse als „Victim“ – Opfer – ausgewählt wird. Diese Terminologie klingt zwar etwas bedrohlich, ist aber wohl zielgerichtet. Der „Opfer“-Prozess wird beendet und nicht erfolgreich abgeschlossen. In diesem Fall löst SQL Server einen Fehler für die Aufrufliste des Opfers aus:

"Msg 1205, Level 13, State 51, Line 6

Transaction (Process ID ) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Was als nächstes passiert, hängt davon ab, wie Ihre Anwendung mit dem Fehler umgeht. Im schlimmsten Fall könnte Ihre Anwendung abstürzen oder die Funktion stark beeinträchtigt werden. Im besten Fall kann die Anwendung adäquat mit dem Fehler umgehen und den Vorgang möglicherweise erneut und erfolgreich durchführen.

Fehlerbehebung von SQL Server-Deadlocks mit SentryOne SQL Sentry

SQLsentry

SQL Sentry

Die SQL Sentry-Deadlock-Analyse hilft Ihnen dabei, die Ursache eines Deadlocks schnell zu ermitteln, damit Sie die optimale Leistung der Datenbank wiederherstellen können.

Weitere Informationen

Kann ich SQL Server-Deadlocks verhindern?

Es gibt mehrere Arten oder Klassen von Deadlocks, mit denen wir uns gleich beschäftigen werden. Einige Arten von Deadlocks sind nahezu unmöglich zu prognostizieren oder zu vermeiden, bevor sie eintreten. Vermeidbare Deadlocks treten meist bei Prozessen auf, die nicht unbedingt direkt in Zusammenhang stehen, aber dieselben Datenbankobjekte und -daten verwenden

Daher sollte Ihr Anwendungsdesignprozess potenzielle Deadlocks berücksichtigen. Nicht alle Anwendungen müssen nach einem Deadlock jeden Prozess erneut versuchen. Im Endeffekt möchte man nicht allen Anwendungsprozessen von vornherein dasselbe Deadlock-Verhalten zuweisen.

3 Arten von SQL Server-Deadlocks

Betrachten wir nun Beispiele für 3 Arten von Deadlocks. Die dargestellten Screenshots stammen aus Deadlock-Diagrammen in SentryOne Plan Explorer. Plan Explorer ist ein kostenloses Abfrageanalyse- und Optimierungstool, das mit erweitertem Funktionsumfang Teil von SentryOne 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 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 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.

 

 

2c4d8a51-f67e-d58f-3198-add72a8a8fda

Zum Vergrößern klicken: Ein Vorgangsreihenfolge-Deadlock demonstriert die Wiedergabesteuerelemente in SentryOne Plan Explorer

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.

 

4edf56ab-06c5-bec5-8c9e-cf893204eb70Ansicht der Deadlock-Opfer-Details im SentryOne 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.

b858140d-19e5-1727-19f8-8f70ddba6fbb

Zum Vergrößern klicken: Ein typisches Lookup-Deadlock-Diagramm mit einer Schlüsselsperre für einen gruppierten Index und einer Schlüsselsperre für einen nicht gruppierten Index

 

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.

5f231336-8652-cc09-13e2-e395f58dc8c0

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.

 

6e6acb10-58b7-a786-068e-82607984384f

Plan Explorer und SQL Sentry können auch die kompliziertesten parallelen Austausch-Deadlocks verständlich darstellen

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.

 

2329a8b8-fcdc-fd4c-c6da-c432af5fd12a

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.

04656cdb-d5aa-a6fd-9260-488713d24ea8

 

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 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 herunterladen

 

„Wir haben SQL Sentry auf einem System installiert, bei dem Leistungstests eine schlechte Leistung ermittelt hatten. Am ersten Tag fanden wir drei Deadlocks, die die Ursache der Leistungsprobleme darstellten. Das war ein paar Tage vor einer sehr großen und kritischen Produktionsfreigabe. Die Deadlocks wurden behoben und wir konnten die Anzahl gleichzeitiger Nutzer der Anwendung verdoppeln.“

- Steve Strelzyk
Senior Application Architect, Aon

 

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 SentryOne 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.

PlanExplorer

Plan Explorer

Mit dem kostenlosen Plan Explorer können Sie die Ursache von SQL Server-Abfrageproblemen einfach ermitteln.

Weitere Informationen