Leitfaden zu Deadlocks
Gilt für: 
SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL-Datenbank in Microsoft Fabric
In diesem Artikel werden Deadlocks im SQL Server-Datenbank-Engine ausführlicher erläutert. Deadlocks werden durch konkurrierende, gleichzeitige Sperren in der Datenbank verursacht, häufig in mehrstufigen Transaktionen. Weitere Informationen zum Sperren von Transaktionen finden Sie im Leitfaden zur Transaktionssperre und Zeilenversionsverwaltung.
Genauere Informationen zur Identifizierung und Vermeidung von Deadlocks in Azure SQL-Datenbank finden Sie unter Analysieren und Verhindern von Deadlocks in Azure SQL-Datenbank.
Grundlegendes zu Deadlocks
Ein Deadlock tritt auf, wenn zwei Tasks einander dauerhaft gegenseitig blockieren, weil jeder der Tasks eine Sperre für eine Ressource aufrecht erhält, die die anderen Tasks zu sperren versuchen. Zum Beispiel:
Die Transaktion A richtet eine freigegebene Sperre für Zeile 1 ein.
Die Transaktion B richtet eine freigegebene Sperre für Zeile 2 ein.
Transaktion A fordert nun eine exklusive Sperre für Zeile 2 an und ist blockiert, bis Transaktion B beendet ist und die freigegebene Sperre für Zeile 2 aufhebt.
Transaktion B fordert nun eine exklusive Sperre für Zeile 1 an und ist blockiert, bis die Transaktion A beendet ist und die freigegebene Sperre für Zeile 1 aufhebt.
Folglich kann Transaktion A nicht abgeschlossen werden, bis die Transaktion B abgeschlossen ist. Die Transaktion B ist aber durch Transaktion A blockiert. Diese Bedingung wird auch zyklische Abhängigkeit genannt: Die Transaktion A ist von der Transaktion B abhängig, und die Transaktion B schließt den Kreis wieder, da sie von der Transaktion A abhängig ist.
Die beiden Transaktionen im Deadlock warten auf unbegrenzte Zeit aufeinander, es sei denn, der Deadlock wird von einem externen Prozess unterbrochen. Der Deadlockmonitor der SQL Server-Datenbank-Engine überprüft regelmäßig, ob sich Tasks in einem Deadlock befinden. Wenn der Monitor eine solche zyklische Abhängigkeit erkennt, wählt er einen der Tasks als Opfer aus und beendet dessen Transaktion mit einem Fehler. Dies ermöglicht dem anderen Task, seine Transaktion abzuschließen. Die Anwendung mit der Transaktion, die mit einem Fehler beendet wurde, kann nun erneut versuchen, die Transaktion auszuführen. Dies gelingt nun normalerweise, nachdem die andere, an dem Deadlock beteiligte Transaktion abgeschlossen wird.
Deadlocks werden oft mit normalen Blockierungen verwechselt. Wenn eine Transaktion eine Sperre für eine Ressource anfordert, die bereits von einer anderen Transaktion gesperrt ist, wartet die anfordernde Transaktion, bis die Sperre aufgehoben wird. Standardmäßig treten bei SQL Server-Transaktionen keine Timeouts auf, es sei denn, LOCK_TIMEOUT ist festgelegt. Die anfordernde Transaktion ist also blockiert, befindet sich aber nicht in einem Deadlock, da sie ihrerseits die andere Transaktion, die im Besitz der Sperre ist, nicht blockiert. Die Transaktion, die die Sperre besitzt, wird zu gegebener Zeit abgeschlossen und die Sperre aufgehoben, woraufhin die anfordernde Transaktion die Sperre erhält und den Transaktionsvorgang ausführt. Deadlocks werden fast sofort aufgelöst, während die Blockierung theoretisch unbegrenzt bestehen kann. Deadlocks werden manchmal auch "deadly embrace" (tödliche Umarmung) genannt.
Ein Deadlock kann in jedem System mit mehreren Threads auftreten, nicht nur bei Managementsystemen für relationale Datenbanken, sowie bei anderen Ressourcen als Sperren für Datenbankobjekte. Ein Thread in einem Multithread-Betriebssystem kann beispielsweise eine Ressource oder mehrere Ressourcen, wie z. B. Speicherblöcke, reservieren. Wenn sich die zu reservierende Ressource derzeit im Besitz eines anderen Threads befindet, muss der erste Thread eventuell warten, bis der Besitzerthread die Zielressource freigegeben hat. Der wartende Thread ist für diese bestimmte Ressource abhängig vom Besitzerthread. In einer Instanz von SQL Server-Datenbank-Engine können Sitzungen beim Reservieren von anderen als Datenbankressourcen, wie z. B. Speicher oder Threads, in eine Deadlocksituation geraten.
In der Abbildung weist Transaktion T1 eine Abhängigkeit von Transaktion T2 für die Sperrressource der Part-Tabelle auf. Entsprechend weist Transaktion T2 eine Abhängigkeit von Transaktion T1 für die Sperrressource der Supplier-Tabelle auf. Da diese Abhängigkeiten einen Kreis bilden, besteht ein Deadlock zwischen den Transaktionen T1 und T2.
Deadlocks können auch auftreten, wenn eine Tabelle partitioniert wird und die Einstellung LOCK_ESCALATION von ALTER TABLE auf AUTO festgelegt ist. Wenn LOCK_ESCALATION auf AUTO festgelegt ist, nimmt die Nebenläufigkeit durch Unterstützung der Sperre von Tabellenpartitionen auf HoBT-Ebene anstatt auf TABLE-Ebene durch die SQL Server-Datenbank-Engine zu. Wenn jedoch separate Transaktionen Partitionssperren in eine Tabelle aufnehmen und in der anderen Partitionstransaktion eine Sperre hinzugefügt werden soll, wird hiermit ein Deadlock verursacht. Diese Art von Deadlock lässt sich durch Festlegen von LOCK_ESCALATION auf TABLE vermeiden. Diese Einstellung verringert jedoch die Nebenläufigkeit, da große Updates einer Partition gezwungen werden, auf eine Tabellensperre zu warten.
Erkennen und Beenden von Deadlocks
Ein Deadlock tritt auf, wenn zwei Tasks einander dauerhaft gegenseitig blockieren, weil jeder der Tasks eine Sperre für eine Ressource aufrecht erhält, die die anderen Tasks zu sperren versuchen. Die folgende Abbildung zeigt den Deadlockstatus auf hoher Ebene, wobei Folgendes gilt.
Task T1 erhält eine Sperre für Ressource R1 aufrecht (wird durch den Pfeil von R1 zu T1 angezeigt) und hat eine Sperre für Ressource R2 angefordert (wird durch den Pfeil von T1 zu R2 angezeigt).
Task T2 erhält eine Sperre für Ressource R2 aufrecht (wird durch den Pfeil von R2 zu T2 angezeigt) und hat eine Sperre für Ressource R1 angefordert (wird durch den Pfeil von T2 zu R1 angezeigt).
Da keiner der Tasks fortgesetzt werden kann, bevor eine Ressource verfügbar ist, und keine der Ressourcen freigegeben werden kann, bevor ein Task fortgesetzt wird, ist ein Deadlock vorhanden.
Die SQL Server-Datenbank-Engine erkennt automatisch Deadlockzyklen in SQL Server. Die SQL Server-Datenbank-Engine wählt eine der Sitzungen als Deadlockopfer aus, und die aktuelle Transaktion wird mit einem Fehler beendet, um den Deadlock zu durchbrechen.
Ressourcen, die an einem Deadlock beteiligt sein können
Für jede Benutzersitzung werden möglicherweise ein oder mehrere Tasks ausgeführt, von denen jeder Task Ressourcen abruft oder auf den Abruf wartet. Die folgenden Typen von Ressourcen können eine Blockierung bewirken, die zu einem Deadlock führt.
Sperren: Das Warten auf den Abruf von Sperren für Ressourcen, z. B. Objekte, Seiten, Zeilen, Metadaten und Anwendungen, kann einen Deadlock verursachen. Transaktion T1 besitzt z. B. eine freigegebene (S) Sperre für Zeile r1 und wartet darauf, eine exklusive (X) Sperre für r2 zu erhalten. Transaktion T2 besitzt eine freigegebene (S) Sperre für Zeile r2 und wartet darauf, eine exklusive (X) Sperre für Zeile r1 zu erhalten. Dies führt zu einem Sperrenzyklus, in dem T1 und T2 darauf warten, dass die jeweils andere Transaktion die gesperrten Ressourcen freigibt.
Arbeitsthreads: Ein Task in der Warteschlange, der auf einen verfügbaren Arbeitsthread wartet, kann einen Deadlock verursachen. Wenn der Task in der Warteschlange Ressourcen besitzt, die alle Arbeitsthreads blockieren, führt das zu einem Deadlock. Sitzung S1 startet z. B. eine Transaktion, ruft eine freigegebene (S) Sperre für Zeile r1 ab und wird dann in den Ruhezustand versetzt. Aktive Sitzungen, die für alle verfügbaren Arbeitsthreads ausgeführt werden, versuchen, exklusive (X) Sperren für Zeile r1 abzurufen. Da Sitzung S1 keinen Arbeitsthread abrufen kann, kann kein Commit für die Transaktion ausgeführt und die Sperre für Zeile r1 nicht freigegeben werden. Das Ergebnis ist ein Deadlock.
Arbeitsspeicher: Wenn gleichzeitige Anforderungen auf Arbeitsspeicherzuweisungen warten, die mit dem verfügbaren Arbeitsspeicher nicht erfüllt werden können, kann ein Deadlock auftreten. Zwei gleichzeitige Abfragen, Q1 und Q2, werden z. B. als benutzerdefinierte Funktionen ausgeführt, die 10 MB bzw. 20 MB Arbeitsspeicher abrufen. Wenn jede der Abfragen 30 MB benötigt und der gesamte verfügbare Arbeitsspeicher 20 MB beträgt, müssen Q1 und Q2 warten, bis die jeweils andere Transaktion Arbeitsspeicher freigibt, was zu einem Deadlock führt.
Ressourcen in Verbindung mit einer parallelen Abfrageausführung: Coordinator-, Producer- oder Consumer-Threads, die mit einem Austauschport verknüpft sind, können einander blockieren und einen Deadlock verursachen, wenn mindestens ein weiterer Prozess einbezogen ist, der nicht Teil der parallelen Abfrage ist. Wenn also eine parallele Abfrageausführung gestartet wird, bestimmt SQL Server den Grad des Parallelismus oder die Anzahl der Arbeitsthreads auf Basis der aktuellen Arbeitsauslastung. Ein Deadlock kann auftreten, wenn sich die Arbeitsauslastung des Systems unerwartet ändert. Das ist beispielsweise der Fall, wenn neue Abfragen auf dem Server gestartet werden oder im System nicht mehr genügend Arbeitsthreads vorhanden sind.
MARS-Ressourcen (Multiple Active Result Sets): Diese Ressourcen werden zum Steuern des Interleavings mehrerer aktiver Anforderungen unter MARS verwendet. Weitere Informationen finden Sie unter Verwenden von Multiple Active Result Sets (MARS) in SQL Server Native Client.
Benutzerressource: Wenn ein Thread auf eine Ressource wartet, die potenziell von einer Benutzeranwendung gesteuert wird, wird die Ressource als externe oder Benutzerressource betrachtet und wie eine Sperre behandelt.
Sitzungsmutex: Die Tasks, die in einer Sitzung ausgeführt werden, sind verzahnt. Dies bedeutet, dass nur jeweils ein Task unter der Sitzung zu einem bestimmten Zeitpunkt ausgeführt werden kann. Bevor der Task ausgeführt werden kann, muss er exklusiven Zugriff auf den Sitzungsmutex besitzen.
Transaktionsmutex: Alle Tasks, die in einer Transaktion ausgeführt werden, sind verzahnt. Dies bedeutet, dass nur jeweils ein Task unter der Transaktion zu einem bestimmten Zeitpunkt ausgeführt werden kann. Bevor der Task ausgeführt werden kann, muss er exklusiven Zugriff auf den Transaktionsmutex besitzen.
Damit ein Task unter MARS ausgeführt werden kann, muss er den Sitzungsmutex abrufen. Wenn der Task unter einer Transaktion ausgeführt wird, muss er den Transaktionsmutex abrufen. Auf diese Weise wird garantiert, dass nur jeweils ein Task gleichzeitig in einer bestimmten Sitzung und einer bestimmten Transaktion aktiviert ist. Nachdem die erforderlichen Mutexe abgerufen wurden, kann der Task ausgeführt werden. Nachdem der Task beendet ist oder in der Mitte der Anforderung ein Ergebnis liefert, gibt er zuerst den Transaktionsmutex frei und dann den Sitzungsmutex (in umgekehrter Reihenfolge des Abrufs). Mit diesen Ressourcen können jedoch Deadlocks auftreten. Im folgenden Pseudocode werden zwei Tasks, Benutzeranforderung U1 und Benutzeranforderung U2, in der gleichen Sitzung ausgeführt.
U1: Rs1=Command1.Execute("insert sometable EXEC usp_someproc"); U2: Rs2=Command2.Execute("select colA from sometable");Die gespeicherte Prozedur, die durch Benutzeranforderung U1 ausgeführt wird, hat den Sitzungsmutex abgerufen. Wenn die gespeicherte Prozedur viel Zeit für die Ausführung benötigt, geht die SQL Server-Datenbank-Engine davon aus, dass die gespeicherte Prozedur auf Benutzereingaben wartet. Benutzeranforderung U2 wartet auf den Sitzungsmutex, während der Benutzer auf das Resultset aus U2 wartet, und U1 wartet auf eine Benutzerressource. Logisch stellt sich der Deadlockstatus wie folgt dar:
Deadlockerkennung
Alle Ressourcen, die im Abschnitt Ressourcen, die einen Deadlock verursachen können aufgeführt sind, nehmen am SQL Server Database Engine Deadlock-Erkennungsschema teil. Die Deadlockerkennung wird von einem Sperrenüberwachungsthread ausgeführt, der periodisch alle Tasks in einer Instanz von SQL Server-Datenbank-Engine durchsucht. Die folgenden Schritte beschreiben den Suchvorgang:
Das Standardintervall beträgt 5 Sekunden.
Wenn der Sperrenüberwachungsthread Deadlocks findet, sinkt das Deadlockerkennungsintervall abhängig von der Häufigkeit von Deadlocks von 5 Sekunden auf bis zu 100 Millisekunden.
Wenn der Sperrenüberwachungsthread keine weiteren Deadlocks mehr findet, verlängert SQL Server-Datenbank-Engine die Intervalle zwischen den Suchvorgängen auf 5 Sekunden.
Wird ein Deadlock erkannt, wird davon ausgegangen, dass die nächsten Threads, die auf eine Sperre warten müssen, in den Deadlockzyklus eintreten. Die ersten paar Wartevorgänge auf Sperren nach der Erkennung eines Deadlocks lösen sofort eine Deadlocksuche aus; es wird nicht auf das nächste Deadlockerkennungsintervall gewartet. Wenn das aktuelle Intervall z. B. 5 Sekunden beträgt und soeben ein Deadlock erkannt wurde, löst der nächste Wartevorgang auf eine Sperre die Deadlockerkennung sofort aus. Wenn dieser Wartevorgang auf eine Sperre Teil eines Deadlocks ist, wird er sofort und nicht erst während der nächsten Deadlocksuche erkannt.
SQL Server Datenbank-Engine führt normalerweise nur regelmäßige Deadlockerkennung aus. Da die Anzahl der vorgefundenen Deadlocks im System in der Regel gering ist, kann mithilfe der regelmäßigen Erkennung von Deadlocks der Aufwand der Deadlockerkennung im System gesenkt werden.
Wenn die Sperrenüberwachung die Suche nach Deadlocks für einen bestimmten Thread initiiert, wird die Ressource identifiziert, auf die der Thread wartet. Die Sperrenüberwachung findet dann die Besitzer dieser Ressource und führt rekursiv die Deadlocksuche für diese Threads fort, bis ein Zyklus gefunden wird. Ein auf diese Art identifizierter Zyklus bildet einen Deadlock.
Nachdem ein Deadlock erkannt wurde, beendet SQL Server Datenbank-Engine den Deadlock, indem einer der Threads als Deadlockopfer ausgewählt wird. SQL Server Datenbank-Engine beendet den aktuellen Batch, der für den Thread ausgeführt wird, führt ein Rollback der Transaktion des Deadlockopfers aus und gibt den Fehler 1205 an die Anwendung zurück. Durch den Rollback der Transaktion für das Deadlockopfer werden alle von der Transaktion aufrecht erhaltenen Sperren freigegeben. Auf diese Weise kann die Sperre der Transaktionen der anderen Threads aufgehoben werden, und diese können fortgesetzt werden. Der Fehler 1205 (Deadlockopfer) zeichnet Informationen zu den an einem Deadlock beteiligten Threads und Ressourcen im Fehlerprotokoll auf.
Standardmäßig wählt SQL Server Datenbank-Engine die Sitzung als Deadlockopfer aus, die die Transaktion ausführt, für die mit dem geringsten Aufwand ein Rollback ausgeführt werden kann. Alternativ kann ein Benutzer mithilfe der SET DEADLOCK_PRIORITY-Anweisung die Priorität der Sitzungen im Falle eines Deadlocks angeben. DEADLOCK_PRIORITY kann auf LOW, NORMAL oder HIGH oder alternativ auf einen ganzzahligen Wert im Bereich zwischen -10 und 10 festgelegt werden. Die Deadlockpriorität ist standardmäßig NORMAL. Wenn die Sitzungen verschiedene Deadlockprioritäten besitzen, wird die Sitzung mit der niedrigeren Deadlockpriorität als Deadlockopfer ausgewählt. Wurde für beide Sitzungen die gleiche Deadlockprioriät festgelegt, wird diejenige Sitzung als Deadlockopfer ausgewählt, für die der Rollback weniger aufwändig ist. Wenn die am Deadlockzyklus beteiligten Sitzungen die gleiche Deadlockpriorität und die gleichen Kosten besitzen, wird das Opfer zufällig ausgewählt.
Bei Nutzung der Common Language Runtime (CLR) erkennt der Deadlockmonitor automatisch Deadlocks für Synchronisierungsressourcen (Überwachungsprogramme, Leser-/Schreibersperre und Thread-Join), auf die in verwalteten Prozeduren zugegriffen wird. Der Deadlock wird jedoch behoben, indem eine Ausnahme in der Prozedur ausgelöst wird, die als Deadlockopfer ausgewählt wurde. Beachten Sie unbedingt, dass die Ausnahme nicht automatisch Ressourcen freigibt, die sich zurzeit im Besitz des Opfers befinden; die Ressourcen müssen explizit freigegeben werden. Die zum Identifizieren eines Deadlockopfers verwendete Ausnahme kann konsistent mit dem Verhalten der Ausnahme abgefangen und behandelt werden.
Tools zum Anzeigen von Deadlockinformationen
Zum Anzeigen von Deadlockinformationen stellt die SQL Server Datenbank-Engine Überwachungstools in Form der XEvent-Sitzung system_health, zwei Ablaufverfolgungsflags sowie das Deadlock-Diagrammereignis in SQL Profiler bereit.
Hinweis
Dieser Abschnitt enthält Informationen zu erweiterten Ereignissen, Ablaufverfolgungskennzeichnungen und Ablaufverfolgungen, aber das erweiterte Deadlock-Ereignis ist die empfohlene Methode zum Erfassen von Deadlock-Informationen.
Erweitertes Deadlock-Ereignis
In SQL Server 2012 (11.x) und höheren Versionen sollte das erweiterte Ereignis (XEvent) xml_deadlock_report anstelle der Ereignisklasse des Deadlock-Diagramms in der SQL-Ablaufverfolgung oder in SQL Server Profiler verwendet werden.
Bei Auftreten eines Deadlocks erfasst die Sitzung system_health bereits alle xml_deadlock_report-XEvents, die das Deadlock-Diagramm enthalten. Da die Sitzung system_health aktiviert ist, müssen Sie keine separate XEvent-Sitzung konfigurieren, um Deadlockinformationen zu erfassen. Es ist keine zusätzliche Aktion zur Erfassung von Deadlockinformationen mit dem XEvent xml_deadlock_report erforderlich.
Der in der Regel erfasste Deadlock Graph verfügt über drei unterschiedliche Knoten:
victim-list. Prozessbezeichner des Deadlockopfers.process-list. Informationen zu allen am Deadlock beteiligten Prozessen.resource-list. Informationen zu den am Deadlock beteiligten Ressourcen.
Das folgende Beispiel zeigt, wie Management Studio beim Öffnen der Sitzungsdatei system_health oder des Ringpuffers (sofern das XEvent xml_deadlock_report aufgezeichnet wird) eine grafische Darstellung der an einem Deadlock beteiligten Tasks und Ressourcen bereitstellt:
Die folgende Abfrage kann alle Deadlock-Ereignisse anzeigen, die vom Ringpuffer der system_health-Sitzung erfasst wurden:
SELECT xdr.value('@timestamp', 'datetime') AS [Date],
xdr.query('.') AS [Event_Data]
FROM (SELECT CAST ([target_data] AS XML) AS Target_Data
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs
ON xs.address = xt.event_session_address
WHERE xs.name = N'system_health'
AND xt.target_name = N'ring_buffer') AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;
Hier sehen Sie das Ergebnis.
Das folgende Beispiel zeigt die Ausgabe, nachdem Sie den Link in Event_Data in der ersten Zeile des Ergebnisses ausgewählt haben:
<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
<data name="xml_report">
<type name="xml" package="package0" />
<value>
<deadlock>
<victim-list>
<victimProcess id="process27b9b0b9848" />
</victim-list>
<process-list>
<process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+ </frame>
<frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
EXEC p1 4
END
</inputbuf>
</process>
<process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p </frame>
<frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
EXEC p2 4
END
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
<owner-list>
<owner id="process27b9ee33c28" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process27b9b0b9848" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
<owner-list>
<owner id="process27b9b0b9848" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process27b9ee33c28" mode="X" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</value>
</data>
</event>
Weitere Informationen finden Sie unter Verwenden der system_health-Sitzung.
Ablaufverfolgungsflag 1204 und Ablaufverfolgungsflag 1222
Wenn Deadlocks auftreten, geben die Ablaufverfolgungsflags 1204 und 1222 Informationen zurück, die im SQL Server-Fehlerprotokoll erfasst werden. Ablaufverfolgungsflag 1204 meldet von jedem im Deadlock beteiligten Knoten formatierte Deadlockinformationen. Ablaufverfolgungsflag 1222 formatiert Deadlockinformationen, zunächst prozessweise, anschließend Ressource für Ressource. Es ist möglich, beide Ablaufverfolgungsflags zu aktivieren, um zwei Darstellungen desselben Deadlockereignisses zu erhalten.
Wichtig
Vermeiden Sie die Verwendung der Ablaufverfolgungsflags 1204 und 1222 für Systeme mit hoher Arbeitsauslastung, bei denen es zu Deadlocks kommt. Die Verwendung dieser Ablaufverfolgungsflags kann zu Leistungsproblemen führen. Verwenden Sie stattdessen das Deadlock Extended-Ereignis, um die erforderlichen Informationen zu erfassen.
Zur weiteren Definition der Eigenschaften der Ablaufverfolgungsflags 1204 und 1222 werden in der folgenden Tabelle die Ähnlichkeiten und Unterschiede aufgeführt.
| Eigenschaft | Ablaufverfolgungsflag 1204 und Ablaufverfolgungsflag 1222 | Nur Ablaufverfolgungsflag 1204 | Nur Ablaufverfolgungsflag 1222 |
|---|---|---|---|
| Ausgabeformat | Die Ausgabe wird im SQL Server-Fehlerprotokoll erfasst. | Ist auf die im Deadlock beteiligten Knoten ausgerichtet. Jeder Knoten verfügt über einen dedizierten Abschnitt, wobei der letzte Abschnitt das Deadlockopfer beschreibt. | Gibt Informationen in einem XML-ähnlichen Format zurück, das keinem XSD-Schema (XML Schema Definition) entspricht. Das Format verfügt über drei große Abschnitte. Der erste Abschnitt deklariert das Deadlockopfer. Der zweite Abschnitt beschreibt die jeweiligen im Deadlock beteiligten Prozesse. Der dritte Abschnitt beschreibt die Ressourcen, die den Knoten des Ablaufverfolgungsflags 1204 entsprechen. |
| Identifizieren von Attributen | SPID:<x> ECID:<x>. identifiziert den Thread der Systemprozess-ID bei parallelen Prozessen. Der Eintrag SPID:<x> ECID:0, in dem <x> durch den SPID-Wert ersetzt wird, stellt den Hauptthread dar. Der Eintrag SPID:<x> ECID:<y>, in dem <x> durch den SPID-Wert ersetzt wird und <y> größer als 0 ist, stellt die Unterthreads desselben SPID-Werts dar.BatchID (sbid für Ablaufverfolgungsflag 1222). Identifiziert den Batch, von dem die Codeausführung angefragt oder eine Sperre aufrechterhalten wird. Wenn MARS (Multiple Active Result Sets) deaktiviert sind, ist der BatchID-Wert gleich 0. Wenn MARS aktiviert sind, kann der Wert für aktive Batches von 1 bis n reichen. Sind in der Sitzung keine aktiven Batches vorhanden, ist der BatchID-Wert gleich 0.Mode gibt den Typ der Sperre für eine bestimmte Ressource an, die angefragt, erteilt oder von einem Thread erwartet wird. Dies kann eine beabsichtigte freigegebene Sperre (Intent Shared, IS), eine freigegebene Sperre (Shared), eine Updatesperre (Update, U), eine beabsichtigte exklusive Sperre (Intent Exclusive, IX), eine freigegebene mit beabsichtigten exklusiven Sperren (Shared with Intent Exclusive, SIX) und eine exklusive Sperre (Exclusive, X) sein.Line # (line für Ablaufverfolgungsflag 1222). Listet die Zeilennummer des aktuellen Batches von Anweisungen auf, die beim Auftreten des Deadlocks ausgeführt wurden.Input Buf (inputbuf für Ablaufverfolgungsflag 1222). Listet alle Anweisungen im aktuellen Batch auf. |
Node stellt die Eintragsnummer in der Deadlockkette dar.Lists Der Sperrenbesitzer kann Bestandteil dieser Listen sein:Grant List zählt die aktuellen Besitzer der Ressource auf.Convert List zählt die aktuellen Besitzer auf, die versuchen, ihre Sperren in eine höhere Ebene zu konvertieren.Wait List zählt die neuesten Sperrenanforderungen für die Ressource auf.Statement Type beschreibt den Typ der DML-Anweisung (SELECT, INSERT, UPDATE oder DELETE), für die Threads über Berechtigungen verfügen.Victim Resource Owner gibt den teilnehmenden Thread an, den SQL Server zum Durchbrechen des Deadlockzyklus als Opfer auswählt. Der ausgewählte Thread und alle vorhandenen Unterthreads werden beendet.Next Branch stellt die beiden oder mehreren im Deadlockzyklus beteiligten Unterthreads desselben SPID-Werts dar. |
deadlock victim stellt die physische Speicheradresse des Tasks dar (siehe sys.dm_os_tasks), der als Deadlockopfer ausgewählt wurde. Im Fall eines nicht aufgelösten Deadlocks kann diese Angabe 0 (Null) sein. Ein Task, für den ein Rollback ausgeführt wird, kann nicht als Deadlockopfer ausgewählt werden.executionstack stellt den Transact-SQL-Code dar, der zum Zeitpunkt des Auftretens des Deadlocks ausgeführt wird.priority stellt die Deadlockpriorität dar. Unter bestimmten Umständen kann SQL Server Datenbank-Engine die Deadlockpriorität für eine kurze Zeitspanne ändern, um eine bessere Parallelität zu erzielen.logused Vom Task verwendeter Protokollspeicherplatz.owner id Die ID der Transaktion, die die Steuerung der Anforderung durchführt.status Status des Tasks. Ist einer der folgenden Werte:- pending Warten auf einen Arbeitsthread.- runnable Bereit zum Ausführen, jedoch wird auf das Eintreffen eines Quantums gewartet.- running Wird derzeit auf dem Zeitplanungsmodul ausgeführt.- suspended Die Ausführung wird angehalten.- done Der Task ist abgeschlossen.- spinloop Es wird auf die Verfügbarkeit eines Spinlocks gewartet.waitresource Die vom Task benötigte Ressource.waittime Zeitspanne in Millisekunden, die auf die Ressource gewartet wurde.schedulerid Diesem Task zugeordnetes Zeitplanungsmodul. Siehe sys.dm_os_schedulers.hostname Der Name der Arbeitsstation.isolationlevel Die aktuelle Isolationsstufe für Transaktionen.Xactid Die ID der Transaktion, die die Steuerung der Anforderung durchführt.currentdb Die ID der Datenbank.lastbatchstarted Uhrzeit des letzten Starts der Batchausführung durch einen Clientprozess.lastbatchcompleted Uhrzeit des letzten Abschlusses der Batchausführung durch einen Clientprozess.clientoption1 und clientoption2 legen Optionen für diese Clientverbindung fest. Es handelt sich um ein Bitmuster, das Informationen zu Optionen enthält, die normalerweise durch SET-Anweisungen, z. B. SET NOCOUNT und SET XACTABORT, gesteuert werden.associatedObjectId stellt die HoBT-ID (Heap- oder B-Struktur) dar. |
| Ressourcenattribute | RID identifiziert die einzelne Zeile in einer Tabelle, für die eine Sperre aufrechterhalten oder angefragt wird. RID wird als RID dargestellt: db_id:file_id:page_no:row_no. Beispiel: RID: 6:1:20789:0.OBJECT identifiziert die Tabelle, für die eine Sperre aufrechterhalten oder angefragt wird. OBJECT wird als OBJECT: db_id:object_id dargestellt. Beispiel: TAB: 6:2009058193.KEY identifiziert den Schlüsselbereich innerhalb eines Indexes, in dem eine Sperre aufrechterhalten oder angefragt wird. KEY wird als KEY dargestellt: db_id:hobt_id (Indexschlüssel-Hashwert). Beispiel: KEY: 6:72057594057457664 (350007a4d329).PAG identifiziert die Seitenressource, in der eine Sperre aufrechterhalten oder angefragt wird. PAG wird als PAG dargestellt: db_id:file_id:page_no. Beispiel: PAG: 6:1:20789.EXT identifiziert die Blockstruktur. EXT wird als EXT dargestellt: db_id:file_id:extent_no. Beispiel: EXT: 6:1:9.DB identifiziert die Datenbanksperre. DB wird auf eine der folgenden Arten dargestellt:DB: db_idDB: db_id[BULK-OP-DB], identifiziert die von der Sicherungsdatenbank erstellte Datenbanksperre.DB: db_id[BULK-OP-LOG], identifiziert die vom Sicherungsprotokoll für diese bestimmte Datenbank erstellte Sperre.APP identifiziert die von einer Anwendungsressource erstellte Sperre. APP wird als APP dargestellt: lock_resource. Beispiel: APP: Formf370f478.METADATA stellt die in einem Deadlock beteiligten Metadatenressourcen dar. Da METADATA über viele Unterressourcen verfügt, hängt der zurückgegebene Wert von der Unterressource ab, für die ein Deadlock vorliegt. METADATA.USER_TYPE gibt beispielsweise user_type_id = *integer_value* zurück. Weitere Informationen zu METADATA-Ressourcen und -Unterressourcen finden Sie unter sys.dm_tran_locks.HOBT stellt eine in einem Deadlock beteiligte Heap- oder B-Struktur dar. |
Gilt nicht ausschließlich für dieses Ablaufverfolgungsflag. | Gilt nicht ausschließlich für dieses Ablaufverfolgungsflag. |
Beispiel für Ablaufverfolgungsflag 1204
Im folgenden Beispiel wird die Ausgabe beim Aktivieren des Ablaufverfolgungsflags 1204 gezeigt. Hierbei wird die Tabelle auf Knoten 1 als Heap ohne Indizes und die Tabelle auf Knoten 2 als Heap mit einem nicht gruppierten Index verwendet. Der Indexschlüssel auf Knoten 2 wird beim Auftreten des Deadlocks aktualisiert.
Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
RID: 6:1:20789:0 CleanCnt:3 Mode:X Flags: 0x2
Grant List 0:
Owner:0x0315D6A0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
Input Buf: Language Event:
BEGIN TRANSACTION
EXEC usp_p2
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0
Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)
Node:2
KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
Grant List 0:
Owner:0x0315D140 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
Input Buf: Language Event:
BEGIN TRANSACTION
EXEC usp_p1
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)
Beispiel für Ablaufverfolgungsflag 1222
Im folgenden Beispiel wird die Ausgabe beim Aktivieren des Ablaufverfolgungsflags 1222 gezeigt. Hierbei wird eine Tabelle als Heap ohne Indizes und die andere Tabelle als Heap mit einem nicht gruppierten Index verwendet. In der zweiten Tabelle wird der Indexschlüssel beim Auftreten des Deadlocks aktualisiert.
deadlock-list
deadlock victim=process689978
process-list
process id=process6891f8 taskpriority=0 logused=868
waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444
transactionname=user_transaction
lasttranstarted=2022-02-05T11:22:42.733 XDES=0x3a3dad0
lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54
sbid=0 ecid=0 priority=0 transcount=2
lastbatchstarted=2022-02-05T11:22:42.733
lastbatchcompleted=2022-02-05T11:22:42.733
clientapp=Microsoft SQL Server Management Studio - Query
hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
isolationlevel=read committed (2) xactid=310444 currentdb=6
lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
executionStack
frame procname=AdventureWorks2022.dbo.usp_p1 line=6 stmtstart=202
sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;
frame procname=adhoc line=3 stmtstart=44
sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
EXEC usp_p1
inputbuf
BEGIN TRANSACTION
EXEC usp_p1
process id=process689978 taskpriority=0 logused=380
waitresource=KEY: 6:72057594057457664 (350007a4d329)
waittime=5015 ownerId=310462 transactionname=user_transaction
lasttranstarted=2022-02-05T11:22:44.077 XDES=0x4d9e258 lockMode=U
schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0
priority=0 transcount=2 lastbatchstarted=2022-02-05T11:22:44.077
lastbatchcompleted=2022-02-05T11:22:44.077
clientapp=Microsoft SQL Server Management Studio - Query
hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
isolationlevel=read committed (2) xactid=310462 currentdb=6
lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
executionStack
frame procname=AdventureWorks2022.dbo.usp_p2 line=6 stmtstart=200
sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;
frame procname=adhoc line=3 stmtstart=44
sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
EXEC usp_p2
inputbuf
BEGIN TRANSACTION
EXEC usp_p2
resource-list
ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2022.dbo.T2
id=lock3136940 mode=X associatedObjectId=72057594057392128
owner-list
owner id=process689978 mode=X
waiter-list
waiter id=process6891f8 mode=U requestType=wait
keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2022.dbo.T1
indexname=nci_T1_COL1 id=lock3136fc0 mode=X
associatedObjectId=72057594057457664
owner-list
owner id=process6891f8 mode=X
waiter-list
waiter id=process689978 mode=U requestType=wait
Profiler Deadlock Graph-Ereignis
Dies ist ein Ereignis in SQL Profiler, das eine grafische Darstellung der an einem Deadlock beteiligten Tasks und Ressourcen bereitstellt. Im folgenden Beispiel wird die Ausgabe von SQL Profiler gezeigt, wenn das Deadlock Graph-Ereignis aktiviert ist.
Wichtig
Der SQL Profiler erstellt Ablaufverfolgungen, die 2016 veraltet und durch erweiterte Ereignisse ersetzt wurden. Erweiterte Ereignisse haben viel weniger Leistungsaufwand und sind wesentlich konfigurierbarer als Ablaufverfolgungen. Erwägen Sie die Verwendung des Deadlock-Ereignisse-Ereignisses anstelle von Ablaufverfolgungen.
Weitere Informationen zum Deadlockereignis finden Sie unter Lock:Deadlock (Ereignisklasse). Weitere Informationen zum Ausführen des Deadlock-Diagramms von SQL Profiler finden Sie unter Speichern von Deadlock-Diagrammen (SQL Server Profiler).
Es gibt Entsprechungen für Ereignisklassen von SQL-Ablaufverfolgungen in erweiterten Ereignissen, siehe Anzeigen der Entsprechungen von erweiterten Ereignissen für SQL-Ablaufverfolgungsklassen. Erweiterte Ereignisse werden über SQL-Ablaufverfolgungen empfohlen.
Behandeln von Deadlocks
Wenn eine Instanz der SQL Server-Datenbank-Engine eine Transaktion als Deadlockopfer bestimmt, wird der aktuelle Batch beendet, ein Rollback der Transaktion durchgeführt und die Fehlermeldung 1205 an die Anwendung zurückgegeben.
Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.
Da jede Anwendung, die Transact-SQL-Abfragen sendet, als Deadlockopfer ausgewählt werden kann, sollten die Anwendungen über einen Fehlerhandler verfügen, der Fehlermeldung 1205 auffangen kann. Wenn eine Anwendung den Fehler nicht erfasst, wird sie möglicherweise weiter ausgeführt, da nicht erkannt wird, dass ein Rollback für die zugehörige Transaktion ausgeführt wurde, und es können Fehler auftreten.
Durch Implementieren eines Fehlerhandlers, der die Fehlermeldung 1205 abfängt, kann eine Anwendung den Deadlock verarbeiten und Abhilfemaßnahmen ergreifen, wie etwa die Abfrage, die am Deadlock beteiligt war, automatisch erneut abzusenden. Durch die automatische erneute Übermittlung der Abfrage muss der Benutzer nicht erfahren, das ein Deadlock aufgetreten ist.
Die Anwendung sollte kurzzeitig angehalten werden, bevor die Abfrage erneut abgesendet wird. Auf diese Weise kann die andere Transaktion, die an einem Deadlock beteiligt ist, abgeschlossen werden und die Sperren freigeben, die einen Anteil am Deadlockzyklus hatten. Die Wahrscheinlichkeit, dass ein Deadlock erneut auftritt, wenn die erneut abgesendete Abfrage ihre Sperren anfordert, wird so verringert.
Behandeln mit TRY...CATCH
Deadlocks können mit TRY...CATCH behandelt werden. Der Fehler 1205 (Deadlockopfer) kann vom Block CATCH erfasst werden, und die Transaktion kann zurückgesetzt werden, bis die Threads entsperrt werden.
Weitere Informationen finden Sie unter Behandeln von Deadlocks.
Minimieren von Deadlocks
Auch wenn Deadlocks nicht vollständig vermieden werden können, kann das Risiko eines Deadlocks durch das Befolgen bestimmter Codierungskonventionen minimiert werden. Wenn die Anzahl der Deadlocks minimiert wird, können der Transaktionsdurchsatz erhöht und der Aufwand des Systems reduziert werden, und zwar aus folgenden Gründen:
- Die Anzahl der Transaktionen, für die ein Rollback ausgeführt wird, durch den die von einer Transaktion ausgeführte Arbeit rückgängig gemacht wird, ist geringer.
- Die Anzahl der Transaktionen, die von den Anwendungen erneut abgesendet werden, da für sie aufgrund des Deadlocks ein Rollback ausgeführt wurde, ist geringer.
So kann das Risiko von Deadlocks minimiert werden:
- Greifen Sie in derselben Reihenfolge auf Objekte zu.
- Vermeiden Sie Benutzerinteraktionen in Transaktionen.
- Verwenden Sie kurze Transaktionen in einem einzigen Batch.
- Verwenden Sie eine niedrigere Isolationsstufe.
- Verwenden Sie eine auf der Zeilenversionsverwaltung basierende Isolationsstufe.
- Legen Sie die Datenbankoption
READ_COMMITTED_SNAPSHOTauf „on“ fest, um die Verwendung der Zeilenversionsverwaltung für READ COMMITTED-Transaktionen zu aktivieren. - Verwenden Sie die Momentaufnahmeisolation.
- Legen Sie die Datenbankoption
- Verwenden Sie gebundene Verbindungen.
Greifen Sie in derselben Reihenfolge auf Objekte zu.
Wenn alle gleichzeitigen Transaktionen in derselben Reihenfolge auf Objekte zugreifen, treten Deadlocks seltener auf. Wenn beispielsweise zwei gleichzeitige Transaktionen jeweils zuerst die Supplier-Tabelle und anschließend die Part-Tabelle mit einer Sperre belegen, wird eine Transaktion für die Supplier-Tabelle blockiert, bis die andere abgeschlossen ist. Nachdem für die erste Transaktion ein Commit- oder Rollback-Vorgang ausgeführt wurde, wird die Ausführung der zweiten Transaktion fortgesetzt, und es tritt kein Deadlock auf. Durch das Verwenden von gespeicherten Prozeduren für alle Datenänderungen kann die Reihenfolge, in der auf Objekte zugegriffen wird, standardisiert werden.
Vermeiden von Benutzerinteraktionen in Transaktionen
Vermeiden Sie es, Transaktionen zu schreiben, die Benutzerinteraktionen enthalten, da die Geschwindigkeit von Batches, die ohne Benutzereingriffe ausgeführt werden, bedeutend höher ist als die Geschwindigkeit, mit der ein Benutzer manuell auf Abfragen reagieren muss (z. B. beim Antworten auf eine Eingabeaufforderung, wenn eine Anwendung einen Parameter anfordert). Wenn eine Transaktion z. B. auf eine Benutzereingabe wartet, der jeweilige Benutzer jedoch zum Essen oder sogar für das Wochenende nach Hause geht, verzögert der Benutzer die Fertigstellung der Transaktion. Dadurch wird der Durchsatz des Systems beeinträchtigt, da Sperren, die von der Transaktion aufrechterhalten werden, erst dann aufgehoben werden, wenn ein Commit oder Rollback für die Transaktion ausgeführt wird. Selbst wenn es nicht zu einem Deadlock kommt, werden andere Transaktionen blockiert, die auf die gleichen Ressourcen zugreifen, da diese Transaktionen darauf warten, dass die Transaktion beendet wird.
Verwenden von kurzen Transaktionen in einem einzigen Batch
Ein Deadlock tritt in der Regel dann auf, wenn mehrere Transaktionen mit langer Ausführungszeit gleichzeitig in derselben Datenbank ausgeführt werden. Je länger die Transaktion dauert, desto länger werden die exklusiven Sperren oder Updatesperren aufrechterhalten, wodurch andere Aktivitäten blockiert werden und es möglicherweise zu Deadlocks kommt.
Wenn die Transaktionen in einem einzigen Batch enthalten sind, wird die Anzahl der Netzwerkroundtrips während einer Transaktion minimiert, wodurch mögliche Verzögerungen beim Beenden der Transaktion und Aufheben der Sperren reduziert werden.
Weitere Informationen über Aktualisierungssperren finden Sie im Handbuch zu Transaktionssperren und Zeilenversionsverwaltung.
Verwenden einer niedrigeren Isolationsstufe
Ermitteln Sie, ob eine Transaktion auf einer niedrigeren Isolationsstufe ausgeführt werden kann. Durch die READ COMMITTED-Implementierung kann eine Transaktion Daten, die zuvor von einer anderen Transaktion gelesen (nicht geändert) wurden, lesen, ohne warten zu müssen, bis die erste Transaktion abgeschlossen ist. Bei einer niedrigeren Isolationsstufe, beispielsweise READ COMMITTED, werden freigegebene Sperren kürzer aufrechterhalten als bei einer höheren Isolationsstufe, beispielsweise der serialisierbaren. Hierdurch werden Sperrkonflikte reduziert.
Verwenden einer auf der Zeilenversionsverwaltung basierenden Isolationsstufe
Wenn die Datenbankoption READ_COMMITTED_SNAPSHOT auf ON festgelegt ist, verwendet eine Transaktion, die gemäß der READ COMMITTED-Isolationsstufe ausgeführt wird, bei Lesevorgängen die Zeilenversionsverwaltung anstelle freigegebener Sperren.
Hinweis
Einige Anwendungen sind auf das Sperr- und Blockierverhalten der READ COMMITTED-Isolation angewiesen. Für diese Anwendungen sind Änderungen erforderlich, bevor diese Option aktiviert werden kann.
Die Momentaufnahmeisolation verwendet auch die Zeilenversionsverwaltung, die bei Lesevorgängen keine gemeinsamen Sperren nutzt. Bevor eine Transaktion gemäß der Momentaufnahmeisolation ausgeführt werden kann, muss die Datenbankoption ALLOW_SNAPSHOT_ISOLATION auf ON festgelegt werden.
Implementieren Sie diese Isolationsstufen, um die Wahrscheinlichkeit von Deadlocks zu minimieren, die zwischen Lese- und Schreibvorgängen auftreten können.
Verwenden gebundener Verbindungen
Beim Verwenden gebundener Verbindungen können zwei oder mehr Verbindungen, die von derselben Anwendung geöffnet wurden, zusammenarbeiten. Sperren, die von den sekundären Verbindungen eingerichtet wurden, werden so aufrechterhalten, als ob sie von der primären Verbindung eingerichtet wurden, und umgekehrt. Folglich blockieren sie sich nicht gegenseitig.
Beenden einer Transaktion
In einem Deadlock-Szenario wird die Opfertransaktion automatisch beendet und zurückgesetzt. Es ist nicht erforderlich, eine Transaktion in einem Deadlock-Szenario zu beenden.
Ein Deadlock verursachen
Hinweis
In diesem Beispiel wird die AdventureWorksLT2019-Beispieldatenbank mit dem Standardschema und Daten verwendet, wenn READ_COMMITTED_SNAPSHOT aktiviert wurde. Um dieses Beispiel herunterzuladen, besuchen Sie AdventureWorks-Beispieldatenbanken.
Um einen Deadlock zu verursachen, müssen Sie zwei Sitzungen mit der Datenbank AdventureWorksLT2019 verbinden. Diese Sitzungen werden als Sitzung A und Sitzung B bezeichnet. Sie können diese beiden Sitzungen einfach erstellen, indem Sie zwei Abfragezeitfenster in SQL Server Management Studio (SSMS) erstellen.
Führen Sie in Sitzung A den folgenden Transact-SQL-Befehl aus. Dieser Code leitet eine explizite Transaktion ein und führt eine einzelne Anweisung aus, die die SalesLT.Product-Tabelle aktualisiert. Dazu ruft die Transaktion eine Aktualisierungssperre (U) für eine Zeile für die SalesLT.Product-Tabelle ab, die in eine exklusive Sperre (X) konvertiert wird. Wir lassen die Transaktion geöffnet.
BEGIN TRANSACTION;
UPDATE SalesLT.Product
SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';
Führen Sie nun in Sitzung B den folgenden Transact-SQL-Befehl aus. Mit diesem Code wird nicht explizit eine Transaktion eingeleitet. Stattdessen wird er im Autocommit-Transaktionsmodus ausgeführt. Diese Anweisung aktualisiert die SalesLT.ProductDescription-Tabelle. Die Aktualisierung ruft eine Aktualisierungssperre (U) für 72 Zeilen in der Tabelle SalesLT.ProductDescription ab. Die Abfrage wird mit anderen Tabellen verknüpft, darunter die SalesLT.Product-Tabelle.
UPDATE SalesLT.ProductDescription
SET Description = Description
FROM SalesLT.ProductDescription AS pd
INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
INNER JOIN SalesLT.ProductModel AS pm
ON pmpd.ProductModelID = pm.ProductModelID
INNER JOIN SalesLT.Product AS p
ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';
Um diese Aktualisierung abzuschließen, benötigt Sitzung B eine gemeinsame Sperre für Zeilen der Tabelle SalesLT.Product, einschließlich der Zeile, die von Sitzung A gesperrt ist. Sitzung B ist für SalesLT.Product gesperrt.
Kehren Sie zu Sitzung A zurück. Führen Sie die folgende Transact-SQL-Anweisung aus. Dadurch wird eine zweite UPDATE-Anweisung als Teil der geöffneten Transaktion ausgeführt.
UPDATE SalesLT.ProductDescription
SET Description = Description
FROM SalesLT.ProductDescription AS pd
INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
INNER JOIN SalesLT.ProductModel AS pm
ON pmpd.ProductModelID = pm.ProductModelID
INNER JOIN SalesLT.Product AS p
ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';
Die zweite UPDATE-Anweisung in Sitzung A wird von Sitzung B für SalesLT.ProductDescription blockiert.
Sitzung A und Sitzung B blockieren sich jetzt gegenseitig. Keine der Transaktionen kann fortgesetzt werden, da sie jeweils eine Ressource benötigt, die von der anderen Transaktion gesperrt wird.
Nach einigen Sekunden erkennt der Deadlockmonitor, dass die Transaktionen in Sitzung A und Sitzung B sich gegenseitig blockieren und dass keine der Transaktionen fortgesetzt werden kann. Sie sollten sehen, dass ein Deadlock auftritt, wobei Sitzung A als Deadlockopfer ausgewählt wurde. Sitzung B wird erfolgreich abgeschlossen. In Sitzung A wird eine Fehlermeldung mit einem ähnlichen Wortlaut wie im folgenden Beispiel angezeigt:
Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Wenn kein Deadlock ausgelöst wird, vergewissern Sie sich, ob READ_COMMITTED_SNAPSHOT in Ihrer Beispieldatenbank aktiviert ist. Deadlocks können in jeder Datenbankkonfiguration auftreten, für dieses Beispiel jedoch muss READ_COMMITTED_SNAPSHOT aktiviert sein.
Sie können dann Details des Deadlocks im Ziel ring_buffer der Sitzung Extended Events system_health anzeigen, die in SQL Server standardmäßig aktiviert und aktiv ist. Betrachten Sie die folgende Abfrage:
WITH cteDeadLocks ([Deadlock_XML])
AS (SELECT CAST (target_data AS XML) AS [Deadlock_XML]
FROM sys.dm_xe_sessions AS xs
INNER JOIN sys.dm_xe_session_targets AS xst
ON xs.[address] = xst.event_session_address
WHERE xs.[name] = 'system_health'
AND xst.target_name = 'ring_buffer')
SELECT x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
FROM cteDeadLocks AS c
CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)) AS x
ORDER BY when_occurred DESC;
Sie können den XML-Code in der Spalte Deadlock_XML in SSMS anzeigen, indem Sie die Zelle auswählen, die als Link angezeigt wird. Speichern Sie diese Ausgabe als Datei .xdl, schließen Sie sie und öffnen Sie die Datei .xdl dann erneut in SSMS, um ein visuelles Deadlock-Diagramm zu erhalten. Ihr Deadlock-Diagramm sollte nun wie in der Abbildung unten aussehen.
Optimierte Sperren und Deadlocks
Gilt für: Azure SQL-Datenbank
Optimierte Sperrung führte eine andere Methode für die Sperrmechanik ein, die ändert, wie Deadlocks mit exklusiven TID-Sperren gemeldet werden können. Unter jeder Ressource im <resource-list> des Deadlock-Berichts meldet jedes <xactlock>-Element die zugrunde liegenden Ressourcen und spezifische Informationen für Sperren jedes Mitglieds eines Deadlocks.
Betrachten Sie das folgende Beispiel, in dem die optimierte Sperrung aktiviert ist:
CREATE TABLE t2
(
a INT PRIMARY KEY NOT NULL,
b INT NULL
);
INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);
GO
Die folgenden Transact-SQL-Befehle in zwei Sitzungen erstellen einen Deadlock für die Tabelle t2:
In Sitzung 1:
--session 1
BEGIN TRANSACTION foo;
UPDATE t2
SET b = b + 10
WHERE a = 1;
In Sitzung 2:
--session 2:
BEGIN TRANSACTION bar;
UPDATE t2
SET b = b + 10
WHERE a = 2;
In Sitzung 1:
--session 1:
UPDATE t2
SET b = b + 100
WHERE a = 2;
In Sitzung 2:
--session 2:
UPDATE t2
SET b = b + 20
WHERE a = 1;
Dieses Szenario konkurrierender UPDATE-Anweisungen führt zu einem Deadlock. In diesem Fall handelt es sich um eine Keylock-Ressource, bei der jede Sitzung eine X-Sperre auf ihrer eigenen TID hält und auf die S-Sperre auf der anderen TID wartet, was zu einem Deadlock führt. Der folgende XML-Code, der als Deadlock-Bericht erfasst wird, enthält Elemente und Attribute, die für eine optimierte Sperrung spezifisch sind:
Zugehöriger Inhalt
- Erweiterte Ereignisübersicht
- sys.dm_tran_locks (Transact-SQL)
- Deadlock Graph (Ereignisklasse)
- Deadlocks mit Read Repeatable-Isolationsstufe
- Lock:Deadlock Chain (Ereignisklasse)
- Lock:Deadlock (Ereignisklasse)
- SET DEADLOCK_PRIORITY (Transact-SQL)
- Analysieren und Verhindern von Deadlocks in Azure SQL-Datenbank
- Öffnen, Anzeigen und Drucken einer Deadlockdatei in SQL Server Management Studio (SSMS)