SQL Server Technical bulletin - wie ein Deadlock zu beheben

Merkblatt für SQL Server

Thema behandelt in dieser Ausgabe: wie ein Deadlock zu beheben
Aktualisierte Dokumentation und Vorschläge für dieses Thema in späteren Versionen von SQL Server finden Sie auf folgenden Websites:

Problembehandlung bei Deadlocks
Erkennen und Beenden von Deadlocks

Wenn Sie allgemeine Leistungsprobleme beheben, finden Sie in der folgenden Dokumentation:

Verbesserung der Leistung von SQL-Abfragen

Ziel

Zu beheben und eine Lösung für die Auflösung eines Deadlocks.

Einführung

In diesem Artikel eine Deadlocksituation untersucht und beschreibt die Schritte zum Beheben des Deadlocks. Jeder Deadlock kann unterschiedlich sein und kann mehrere Umgebungsvariablen durch. Die Informationen in diesem Artikel helfen Sie identifizieren und lösen einen Deadlock.

Fallstudie

In einer Fallstudie untersucht 911 Systemen mit sechs Operatoren. Die Front-End-Microsoft Visual Basic-Anwendung verwendeten treten während Spitzenzeiten unterbrochene Verbindung. Aufgrund der fehlerhaften Verbindungen müssen die Operatoren Daten erneut eingeben. Ein 911 System, das 24 Stunden pro Tag arbeitet, sieben Tage die Woche, dieses Verhalten ist nicht akzeptabel.

Was ist ein Deadlock?

Ein Deadlock tritt auf, wenn zwei Server Systemprozess IDs (SPIDs) auf eine Ressource warten und keiner der Prozesse kann, da der Prozess verhindert die Ressource abgerufen.

Der Sperren-Manager Thread überprüft Deadlocks. Erkennt ein Sperren Deadlockerkennungsalgorithmus einen Deadlock, wählt der Sperren-Manager eines der SPIDs als Opfer. Der Sperren-Manager initiiert eine Fehlermeldung 1205, die an den Client gesendet wird und der Sperren-Manager beendet die SPID. Tötung der SPID Gibt Ressourcen frei und kann die SPID fortgesetzt. Tötung als Deadlockopfer SPID ist unterbrochene Verbindung, die die Visual Basic-Front-End-Anwendung auftritt.

In einer gut entworfenen Anwendung sollte die Front-End-Anwendung die Fehlermeldung 1205 auffangen, die Verbindung mit dem SQL Server erneut herstellen und die Transaktion erneut ausführen.

Obwohl Deadlocks minimiert werden können, können sie nicht vollständig vermieden werden. Deshalb die Front-End-Anwendung Deadlocks behandeln sollen.

So identifizieren Sie einen deadlock

Schritt 1

Um einen Deadlock zu identifizieren, müssen Sie zuerst Informationen abrufen. Wenn einen Deadlock vermuten, müssen Sie sammeln Informationen (SPIDs) und die Ressourcen, die am Deadlock beteiligt sind. Zu diesem Zweck hinzufügen-T1204 und -T3605-Startparameter an SQL Server. Gehen Sie folgendermaßen vor, um diese zwei Startparameter hinzuzufügen:
  • Starten Sie SQL Server Enterprise Manager.
  • Auswählen und anschließend mit der Maustaste des Servers.
  • Klicken Sie auf Eigenschaften.
  • Klicken Sie auf Startparameter.
  • Geben Sie im Dialogfeld Startparameter -T1204 Parameter Text Feld, und klicken Sie dann auf Hinzufügen.
  • Geben Sie in das Textfeld Parameter
    -T3605, und klicken Sie dann auf Hinzufügen.
  • Klicken Sie auf OK.

Die Startparameter werden wirksam, wenn SQL Server beendet und neu gestartet.

-T1204 Start-Parameter sammelt Informationen über den Prozess und die Ressourcen der Deadlockerkennungsalgorithmus einen Deadlock stößt. -T3605 Startparameter schreibt diese Informationen in der SQL Server-Fehlerprotokolle.

-T1205 Start-Parameter sammelt Informationen jedes Mal ein Deadlock nicht, wenn ein Deadlock festgestellt wird der Deadlock-Algorithmus überprüft. Verwenden Sie unbedingt den T1205 Start-Parameter.


Verwenden Sie-Startparameter T1205 folgende ist ein Beispiel für die Ausgabe im SQL Server-Fehlerprotokoll werden:

2003-05-14 11:46:26.76 spid4     Starting deadlock search 12003-05-14 11:46:26.76 spid4     Target Resource Owner:
2003-05-14 11:46:26.76 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4
2003-05-14 11:46:26.76 spid4 End deadlock search 1 ... a deadlock was not found.
2003-05-14 11:46:26.76 spid4 ----------------------------------
2003-05-14 11:46:31.76 spid4 ----------------------------------
2003-05-14 11:46:31.76 spid4 Starting deadlock search 2



Manchmal nicht möglicherweise beenden und starten Sie SQL Server neu. Query Analyzer können Sie in diesem Fall führen Sie den folgenden Befehl zum Deadlock Ablaufverfolgungsflags zu aktivieren.

Hinweis Auf diese Weise können Sie sofort Informationen über Deadlocks sammeln. "-1" bedeutet alle SPIDs.

dbcc traceon (1204, 3605, -1)go
dbcc tracestatus(-1)
go


Schritt 2

Als Nächstes müssen Sie SQL Profiler Trace sammeln. Wenn Sie das Ablaufverfolgungsflag Deadlock aktivieren, erhalten Sie die meisten benötigten Informationen jedoch nicht immer. Z. B. in einer Fallstudie die Ablaufverfolgungsausgabe Flag angegeben, dass eine Systemprozedur Sp_cursoropen gespeicherte und "UPDATE TblQueuedEvents festlegen Notifyid = 3 ResynchDate"-Anweisung ein Deadlock beteiligt waren. Leider wissen Sie nicht die Definition der gespeicherten Systemprozedur Sp_cursoropen . Sie müssen auch vollständige UPDATE-Anweisung keinen da abgeschnitten wurde.

SQL Profiler erhalten vollständigen Anweisungen zusätzlich die Ausführungspläne der Aussagen. SQL Profiler Trace verfügt auch über ein Ereignis sperren "Deadlock" und "Deadlockkette." "Deadlock"-Flag T1204 entspricht, und "Deadlockkette"-Flag T1205 entspricht. Deadlock Ablaufverfolgungsflags aktivieren und Ausführen von SQL Profiler Trace während das Auftreten von Deadlocks sollten Sie Daten bereitstellen, die für einen Deadlock zu beheben. In diesem Fall und in anderen Zeitverhalten SQL Profiler ausführen Ausführung genug zu einem Deadlock. Daher erfasst in der Regel die Deadlock-Informationen mit Ablaufverfolgungsflags und Sie SQL Profiler ausführen.

Problembehandlung bei Stillstand

Nachdem ein Deadlock auftritt, sammeln Sie Informationen über den Deadlock mithilfe der
Sqldiag Utility und mithilfe von SQL Profiler. Suchen Sie in der Ausgabe der Datei SQLDiag.txt "Wartezeit für Grafik"-Eintrag. Ein "Wait-Diagramm" Eintrag gibt an, dass ein Deadlock aufgetreten ist.

Im folgenden ist ein Beispiel für die Ausgabe, die im SQL Server-Fehlerprotokoll angezeigt werden bei Verwendung der - T1205-Startparameter.

2003-05-05 15:11:50.80 spid4    Wait-for graph2003-05-05 15:11:50.80 spid4    Node:1
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4 Victim Resource Owner:
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:60 ECID:0 Ec:(0x1F1BB5B0) Value:0x193
2003-05-05 15:11:50.80 spid4 Requested By:
2003-05-05 15:11:50.80 spid4 Input Buf: RPC Event: sp_cursoropen;1
2003-05-05 15:11:50.80 spid4 SPID: 55 ECID: 0 Statement Type: EXECUTE Line #: 1
2003-05-05 15:11:50.80 spid4 Owner:0x1937f2a0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:55 ECID:0
2003-05-05 15:11:50.80 spid4 Grant List 0::
2003-05-05 15:11:50.80 spid4 KEY: 8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode: U Fl ags: 0x0

2003-05-05 15:11:50.80 spid4 Node:2
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4 Requested By:
2003-05-05 15:11:50.80 spid4 Input Buf: Language Event: Update tblQueuedEvents Set NotifyID = 2, ResynchDate
2003-05-05 15:11:50.80 spid4 SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 1
2003-05-05 15:11:50.80 spid4 Owner:0x1936e420 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0
2003-05-05 15:11:50.80 spid4 Grant List 0::
2003-05-05 15:11:50.80 spid4 KEY: 8:1653632984:1 (2d018af70d80) CleanCnt:1 Mode: X Flags: 0x0



Im Eintrag "Wartezeit für Grafik" haben Sie Knoten 1 und Knoten 2. In den einzelnen Knoten müssen Sie gewähren und eine Anforderung. Abschnitt gewähren "Grant List" und Abschnitt Anforderung "Request durch."
In den einzelnen Knoten können Sie Folgendes angeben:
  • Die SPID.
  • Der Befehl, den der SPID ausgeführt wurden.
  • Die Ressource.
  • Der Sperrmodus für die Ressource.

Beispielsweise in Knoten 1 Liste erteilen SPID 55 gewährt wurde eine Aktualisierungssperre Modus: U Ressource Schlüssel: 8:1653632984:2. 8 = DBID 1653632984 = ObjectID und 2 Indid =. Datenbank-ID zu erhalten, führen Sie Sp_helpdb gespeicherten Prozedur. Um die Tabelle zu erhalten, führen Sie den folgenden Code:

select * from sysobjects where id = 1653632984


Um den Index zu erhalten, führen Sie den folgenden Code:
select * from sysindexes where indid = 2 and id = 1653632984

Wenn IndexId gleich 2 ist, wissen Sie, dass der Index ein nicht gruppierter Index ist. Der Befehl, den SPID 55 ausgeführt wurde wurde Sp_cursoropen gespeicherten Prozedur.

In der Liste erteilen Knoten 2 SPID 60 erteilt eine exklusive Sperre Modus: X Ressource Schlüssel: 8:1653632984:1. 8 = DBID 1653632984 ObjectID, 1 = Indid =. Dies ist für die gleiche Tabelle Index 1 ist der gruppierte Index. Befehls 60 SPID ausgeführt wurde:
Update tblQueuedEvents Set NotifyID = 2, ResynchDate

Eine IndexId gleich 1 ist ein gruppierter Index.

Eine IndexId gleich 2 ist ein nicht gruppierter Index.

Hinweis Deadlocks sind sehr empfindlich.

Anschließend in Knoten 1 Anfordern von SPID 55 angefordert eine gemeinsame Sperre Modus: S, IndexId = 1. Knoten 2 Anfordern von SPID 60 eine exklusive Sperre Modus angefordert: X IndexId = 2. Da diese Sperre Anfragen gleichzeitig auftreten, wird der Deadlock. Jede SPID Sperren verhindert das Fortsetzen die angeforderten Sperren des gewährt.

Die folgende Tabelle zeigt die Tabelle sperren. Weitere Informationen zur Kompatibilität von Sperren finden Sie "Lock Compatibility" in der Onlinedokumentation zu SQL Server 2000.

Tabelle sperren
Angeforderte ModusISTSUIXSECHSX
Beabsichtigte gemeinsame Sperre (IS)JaJaJaJaJaNein
Shared (S)JaJaJaNeinNeinNein
Update (U)
JaJaNeinNeinNeinNein
Beabsichtigte exklusive (IX)JaNeinNeinJaNeinNein
Gemeinsam genutzt mit beabsichtigter exklusiver Sperre (SIX)
JaNeinNeinNeinNeinNein
Exklusive (X)NeinNeinNeinNeinNeinNein


Anschließend anhand der Ausgabe ermitteln ObjectId 1653632984 wie die TblQueuedEvents -Tabelle und Sie eine Sp_help gespeicherte Prozedur für die Tabelle ausgegeben. Es wurden zwei Indizes für die Tabelle. Die zwei Indizes wurden Ix_tblQueuedEvents und PK_tblQueuedEvent. Ix_tblQueuedEvents ist ein gruppierter Index für ResynchDate und PK_tblQueuedEvent ist ein Primärschlüssel, eindeutigen nicht gruppierten Index für EventSID.


SQL Profiler Trace konnte nicht erfassen Deadlock auftreten. Beachten Sie, dass Deadlocks sehr abhängig sind. Aufwand von SQL Profiler hinzugefügt wahrscheinlich einige Zeit für die Ausführung einer der Prozesse und SQL Profiler, die verhindert, dass in einer Deadlocksituation abrufen. Allerdings sie Informationen, mit denen Sie das Problem zu beheben. Sie finden die vollständige Aktualisierung TblQueuedEvents Anweisung ähnlich der folgenden:

Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16' where eventSID = 73023
Sie finden außerdem den Ausführungsplan. Sie noch keinen vollständigen Sp_cursoropen gespeicherten Procedure-Anweisung, aber haben Sie genügend Informationen, um eine Lösung, die den Deadlock aufzulösen.

Hier ist der Ausführungsplan.

Hinweis Dieser bestimmten Ausführungsplan lesen links und von unten nach oben.

StmtText                                                                                                                                 


--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16'
where eventSID = 73023


|--Clustered Index
Update(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[ix_tblQueuedEvents ]),
SET:([tblQueuedEvents].[NotifyID]=[@1],
[tblQueuedEvents].[ResynchDate]=[Expr1004]))
|--Top(1)



|--Compute Scalar(DEFINE:([Expr1004]=Convert([@2])))



|--Index
Seek(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[PK_tblQueuedEvents]),
SEEK:([tblQueuedEvents].[EventSID]=[@3])

Empfehlen Sie eine Lösung, den Deadlock aufzulösen

Beachten Sie, dass die UPDATE-Anweisung für den gruppierten Index "clustered Index Update" ausführt. Daher müssen der nicht gruppierte Index und der gruppierte Index beide aktualisiert werden. Der gruppierte Index ist Ix_tblQueuedEvents und der nicht gruppierte Index ist PK_tblQueuedEvents. Zum Durchführen der Updates benötigen die UPDATE-Anweisung exklusive Sperren für beide Indizes. Diese beiden Indizes werden Indizes, die am Deadlock beteiligt sind. Von SQL Profiler Spuren Überprüfung nicht Abfragen angezeigt, die die ResynchDate in der WHERE-Klausel verwendet. Die Aussagen wurden sehr spezifische und der EventSID in der WHERE-Klausel verwendet. Bessere Wahl eines gruppierten Indexes wäre EventSID. Gespräch mit dem Kunden diese Informationen und fanden wir, dass der Index ResynchDate alte und war nicht erforderlich. Wir empfehlen, dass Kunden auf ResynchDate Ix_tblQueuedEvents Index löschen und sie PK_tblQueuedEvent einen gruppierten Index erstellen. Dieses Problem gelöst Deadlock-Situation.

Dies ist nur ein Beispiel Deadlock, bei dem Sperren. Deadlocks können beinhalten Parallelität und Threads beinhalten. Sie können eine, zwei, drei oder mehr SPIDs und Ressourcen umfassen. Mit jeder Deadlock benötigen – T1204 Start-Parameterausgabe und SQL Profiler Trace zu beheben und um den Deadlock aufzulösen. Die Deadlock-Situation beinhaltet verschiedene Prozesse und Ressourcen. Deshalb werden Lösungen von Fall zu Fall variieren. Normale Methoden, Deadlocks zu enthalten:
  • Hinzufügen und Löschen von Indizes.
  • Index-Hints hinzufügen
  • Ändern der Anwendung Zugriff auf Ressourcen in einem ähnlichen Muster.
  • Aktivität entfernen aus der Buchung wie Trigger Standardmäßig sind Trigger transaktional.
  • Halten Transaktionen so kurz wie möglich.
Eigenschaften

Artikelnummer: 832524 – Letzte Überarbeitung: 23.01.2017 – Revision: 2

Feedback