Leitfaden zu Deadlocks

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

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.

Spezifischere Informationen zur Identifizierung und Verhinderung von Deadlocks in Azure SQL-Datenbank finden Sie unter Analysieren und Verhindern von Deadlocks in Azure SQL-Datenbank.

Verstehen 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. 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, die sich im Deadlock befinden, werden auf unbegrenzte Zeit aufeinander warten, es sei denn, der Deadlock wird von einem externen Prozess unterbrochen. Der SQL Server-Datenbank-Engine Deadlock-Monitor sucht regelmäßig nach Vorgängen, die sich 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 bereits abgeschlossen ist.

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. Sql Server-Transaktionen werden standardmäßig nicht timeout ausgeführt, es sei denn, LOCK_TIMEOUT festgelegt ist. 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 ist eine Bedingung, die in jedem System mit mehreren Threads auftreten kann, nicht nur bei Managementsystemen für relationale Datenbanken, sowie in 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 die erworbene Ressource derzeit einem anderen Thread gehört, muss der erste Thread möglicherweise warten, bis der besitzereigene Thread die Zielressource freigibt. Der wartende Thread ist für diese bestimmte Ressource abhängig vom Besitzerthread. In einer Instanz des SQL Server-Datenbank-Engine können Sitzungen beim Abrufen von Ressourcen ohne Datenbank, z. B. Arbeitsspeicher oder Threads, deadlockt werden.

In der Abbildung weist die Transaktion T1 eine Abhängigkeit von Transaktion T2 für die Part Tabellensperrressource auf. Ebenso hat Transaktion T2 eine Abhängigkeit von Transaktion T1 für die Supplier Tabellensperrressource. 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 für die LOCK_ESCALATION-Einstellung von ALTER TABLE die Option AUTO festgelegt ist. Wenn LOCK_ESCALATION sie auf AUTO festgelegt ist, erhöht sich die Parallelität, indem die SQL Server-Datenbank-Engine tabellenpartitionen auf HoBT-Ebene statt auf Tabellenebene sperren kann. 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 Deadlock kann durch Festlegen von TABLE für LOCK_ESCALATION vermieden werden, auch wenn mit dieser Einstellung die Parallelität verringert wird, indem große Updates 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 T1 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. sql Server Datenbank-Engine wählt eine der Sitzungen als Deadlock-Opfer aus, und die aktuelle Transaktion wird mit einem Fehler beendet, um den Deadlock zu unterbrechen.

Ressourcen, die inaktiv werden können

Für jede Benutzersitzung werden möglicherweise ein oder mehrere Tasks ausgeführt, von denen jeder Task eine Vielzahl von 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 das Abrufen von Sperren für Ressourcen, z. B. Objekte, Seiten, Zeilen, Metadaten und Anwendungen, kann zu einem Deadlock führen. 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: Eine in die Warteschlange eingereihte Aufgabe, die auf einen verfügbaren Arbeitsthread wartet, kann zu einem Deadlock führen. Wenn der Task in der Warteschlange Ressourcen besitzt, die alle Arbeitsthreads blockieren, führt dies 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 befriedigt 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; dies führt zu einem Deadlock.

• Ressourcen in Verbindung mit einer parallelen Abfrageausführung: Koordinator-, Produzenten- oder Consumerthreads, die mit einem Exchange-Port verbunden sind, können einander blockieren, was normalerweise zu einem Deadlock führt, wenn mindestens ein anderer Prozess eingeschlossen wird, der nicht Teil der parallelen Abfrage ist. Wenn eine parallele Abfrage die Ausführung startet, bestimmt SQL Server außerdem den Grad der Parallelität oder die Anzahl der Arbeitsthreads basierend auf 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).

  • 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 Aufgaben, Benutzeranforderung U1 und Benutzeranforderung U2, in derselben 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 lange dauert, wird sie vom SQL Server-Datenbank-Engine davon ausgegangen, dass die gespeicherte Prozedur auf die Eingabe des Benutzers 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:

  

Deadlock-Erkennung

Alle im abschnitt oben aufgeführten Ressourcen nehmen am SQL Server-Datenbank-Engine Deadlock-Erkennungsschema teil. Die Deadlock-Erkennung wird von einem Sperrüberwachungsthread ausgeführt, der regelmäßig eine Suche durch alle Aufgaben in einer Instanz der SQL Server-Datenbank-Engine initiiert. 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 Sperrmonitorthread die Suche nach Deadlocks beendet, erhöht der SQL Server-Datenbank-Engine die Intervalle zwischen Suchvorgängen auf 5 Sekunden.
• Wenn ein Deadlock gerade erkannt wurde, wird davon ausgegangen, dass die nächsten Threads, die auf eine Sperre warten müssen, in den Deadlockzyklus eingehen. Die ersten 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.

Die SQL Server-Datenbank-Engine führt in der Regel nur eine regelmäßige Deadlock-Erkennung durch. 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 den (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 der SQL Server-Datenbank-Engine einen Deadlock, indem er einen der Threads als Deadlock-Opfer auswählt. Die SQL Server-Datenbank-Engine beendet den aktuellen Batch, der für den Thread ausgeführt wird, rollt die Transaktion des Deadlock-Opfers zurück und gibt einen 1205-Fehler 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 die SQL Server-Datenbank-Engine als Deadlock-Opfer die Sitzung aus, die die Transaktion ausführt, die am wenigsten teuer ist, um ein Rollback durchzuführen. 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 beliebigen 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.

Wenn CLR verwendet wird, erkennt der Deadlockmonitor automatisch Deadlocks für Synchronisierungsressourcen (Überwachungsprogramme, Leser/Schreibersperre und Threadjoin), 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.

Deadlock-Informationstools

Um Deadlock-Informationen anzuzeigen, stellt die SQL Server-Datenbank-Engine Überwachungstools in Form der system_health xEvent-Sitzung, zwei Ablaufverfolgungskennzeichnungen und 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

Ab SQL Server 2012 (11.x) sollte das xml_deadlock_report erweiterte Ereignis (xEvent) anstelle der Deadlock Graph-Ereignisklasse in SQL Trace oder SQL Profiler verwendet werden.

Ab SQL Server 2012 (11.x) erfasst die system_health Sitzung beim Auftreten von Deadlocks bereits alle xml_deadlock_report xEvents, die das Deadlock-Diagramm enthalten. Da die system_health Sitzung standardmäßig aktiviert ist, ist es nicht erforderlich, dass eine separate xEvent-Sitzung für die Erfassung von Deadlock-Informationen konfiguriert ist. Es ist keine zusätzliche Aktion zur Erfassung von Deadlock-Informationen mit xml_deadlock_report xEvent 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.

Beim Öffnen der Sitzungsdatei oder des system_health Ringpuffers, wenn das xml_deadlock_report xEvent aufgezeichnet wird, stellt Management Studio eine grafische Darstellung der Aufgaben und Ressourcen dar, die an einem Deadlock beteiligt sind, wie im folgenden Beispiel gezeigt:

Die folgende Abfrage kann alle Deadlock-Ereignisse anzeigen, die system_health vom Sitzungsringpuffer erfasst werden:

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 ist das Resultset.

Das folgende Beispiel zeigt die Ausgabe, nachdem Sie den ersten Link des obigen 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 Trace Flag 1204 und Trace Flag 1222 Informationen zurück, die im SQL Server-Fehlerprotokoll erfasst werden. Trace Flag 1204 meldet Deadlock-Informationen, die von jedem Knoten formatiert sind, der am Deadlock beteiligt ist. Trace Flag 1222 formatiert Deadlock-Informationen zuerst nach Prozessen und dann nach Ressourcen. Es ist möglich, beide Ablaufverfolgungsflags zu aktivieren, um zwei Darstellungen desselben Deadlockereignisses zu erhalten.

Wichtig

Vermeiden Sie die Verwendung von Trace Flag 1204 und 1222 für workloadintensive Systeme, die Deadlocks erleben. Die Verwendung dieser Ablaufverfolgungskennzeichnungen kann Zu Leistungsprobleme führen. Verwenden Sie stattdessen das Deadlock Extended-Ereignis , um die erforderlichen Informationen zu erfassen.

Neben der Definition der Eigenschaften von Trace Flag 1204 und 1222 zeigt die folgende Tabelle auch die Ähnlichkeiten und Unterschiede.

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 im XML-ähnlichen Format zurück, das einer XSD-Sprache (XML Schema Definition) nicht 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. Im dritten Abschnitt werden die Ressourcen beschrieben, die synonym mit Knoten in Trace Flag 1204 sind.
Identifizieren von Attributen	SPID:x> ECID:<<x>. Identifiziert den Thread der Systemprozess-ID bei parallelen Prozessen. Der Eintrag SPID:<x> ECID:0, wobei <x> durch den SPID-Wert ersetzt wird, stellt den Standard Thread dar. Der Eintrag SPID:<x> ECID:<y>, wobei <x> durch den SPID-Wert ersetzt wird und <y> größer als 0 ist, stellt die Unterthreads für denselben SPID dar. BatchID (sbid for Trace Flag 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. Zeile # (Zeile für Spurkennzeichnung 1222). Listet die Zeilennummer des aktuellen Batches von Anweisungen auf, die beim Auftreten des Deadlocks ausgeführt wurden. Input Buf (Inputbuf for Trace Flag 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 als Opfer auswähelt, um den Deadlock-Zyklus zu unterbrechen. 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 der Aufgabe dar (siehe sys.dm_os_tasks (Transact-SQL)), die als Deadlock-Opfer ausgewählt wurde. Bei einem ungelösten Deadlock kann es 0 (null) sein. Ein Task, für den ein Rollback ausgeführt wird, kann nicht als Deadlockopfer ausgewählt werden. executionstack: Stellt Transact-SQL-Code dar, der zum Zeitpunkt der Ausführung des Deadlocks ausgeführt wird. priority: Stellt die Deadlockpriorität dar. In bestimmten Fällen kann die SQL Server-Datenbank-Engine die Deadlock-Priorität für eine kurze Dauer ändern, um eine bessere Parallelität zu erzielen. logused: Vom Task verwendeter Protokollspeicherplatz. Besitzer-ID. Die ID der Transaktion, die die Kontrolle über die Anforderung hat. status: Der 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 (Transact-SQL). 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: SET-Optionen für diese Clientverbindung. 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 innerhalb einer Tabelle, in der 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, in der eine Sperre aufrechterhalten oder angefragt wird. OBJECT wird als OBJECT dargestellt: db_id:object_id. 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üsselhashwert). 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_id DB: db_id[BULK-OP-DB], die die Datenbanksperre identifiziert, die von der Sicherungsdatenbank übernommen wird. DB: db_id[BULK-OP-LOG], die die Sperre identifiziert, die vom Sicherungsprotokoll für diese bestimmte Datenbank übernommen wurde. 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 METADATENressourcen und Unterressourcen finden Sie unter sys.dm_tran_locks (Transact-SQL).For more information about METADATA resources and subresources, see sys.dm_tran_locks (Transact-SQL). 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 Ablaufverfolgungskennzeichnung 1204

Das folgende Beispiel zeigt die Ausgabe, wenn die Ablaufverfolgungskennzeichnung 1204 aktiviert ist. 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 Ablaufverfolgungskennzeichnung 1222

Das folgende Beispiel zeigt die Ausgabe, wenn die Ablaufverfolgungskennzeichnung 1222 aktiviert ist. 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 SQL Profiler-Deadlock-Diagramms finden Sie unter Speichern von Deadlock Graphs (SQL Server Profiler).

Es gibt Entsprechungen für SQL-Ablaufverfolgungsereignisklassen in erweiterten Ereignissen, siehe Extended Events Equivalents to SQL Trace Event Classes. Erweiterte Ereignisse werden über SQL-Ablaufverfolgungen empfohlen.

Behandeln von Deadlocks

Wenn eine Instanz der SQL Server-Datenbank-Engine eine Transaktion als Deadlock-Opfer auswäht, beendet sie den aktuellen Batch, setzt die Transaktion zurück und gibt fehlermeldung 1205 an die Anwendung zurück.

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 übermittelt, als Deadlock-Opfer ausgewählt werden kann, sollten Anwendungen über einen Fehlerhandler verfügen, der Fehlermeldung 1205 abfangen kann. Wenn eine Anwendung den Fehler nicht auffängt, 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 Absendung der Abfrage ist es nicht notwendig, dass der Benutzer von dem Deadlock erfährt.

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.

Minimieren von Deadlocks

Obwohl 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_SNAPSHOT auf „on“ fest, um die Verwendung der Zeilenversionsverwaltung für READ COMMITTED-Transaktionen zu aktivieren.
  • Verwenden Sie die Momentaufnahmeisolation.
• 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 eine Sperre für die Supplier Tabelle und dann in der Part Tabelle erhalten, wird eine Transaktion auf der Supplier Tabelle blockiert, bis die andere Transaktion 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 dieselben Ressourcen zugreifen, da sie 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 zum Aktualisieren von Sperren finden Sie im Leitfaden zur Transaktionssperre 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. Wenn eine niedrigere Isolationsstufe verwendet wird, 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 freigegebenen Sperren verwenden. Bevor eine Transaktion unter Momentaufnahme Isolation ausgeführt werden kann, muss die ALLOW_SNAPSHOT_ISOLATION Datenbankoption festgelegt ONwerden.

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.

Ursache eines Deadlocks

Hinweis

Dieses Beispiel funktioniert in der AdventureWorksLT2019 Beispieldatenbank mit dem Standardschema und den Daten, 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 AdventureWorksLT2019-Datenbank verbinden. Diese Sitzungen werden als Sitzung A und Sitzung B bezeichnet. Sie können diese beiden Sitzungen einfach erstellen, indem Sie zwei Abfragefenster 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 TRAN

    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 SalesLT.ProductDescription-Tabelle 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
    JOIN SalesLT.ProductModelProductDescription as pmpd on
        pd.ProductDescriptionID = pmpd.ProductDescriptionID
    JOIN SalesLT.ProductModel as pm on
        pmpd.ProductModelID = pm.ProductModelID
    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 Aktualisierungssperre als Teil der geöffneten Transaktion ausgeführt.

    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        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. Eine Fehlermeldung wird in Sitzung A mit Text ähnlich dem folgenden 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, dass READ_COMMITTED_SNAPSHOT in Ihrer Beispieldatenbank aktiviert wurde. Deadlocks können in jeder Datenbankkonfiguration auftreten, dieses Beispiel erfordert jedoch, dass READ_COMMITTED_SNAPSHOT aktiviert werden.

Anschließend können Sie Details des Deadlocks im ring_buffer Ziel der system_health Erweiterten Ereignissitzung anzeigen, die standardmäßig in SQL Server aktiviert und aktiv ist. Betrachten Sie die folgende Abfrage:

WITH cteDeadLocks ([Deadlock_XML]) AS (
  SELECT [Deadlock_XML] = CAST(target_data AS 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 
  Deadlock_XML = x.Graph.query('(event/data/value/deadlock)[1]')  
, when_occurred = x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') 
, DB = DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) --Current database of the first listed process 
FROM (
 SELECT Graph.query('.') AS Graph 
 FROM cteDeadLocks 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 Deadlock_XML Spalte in SSMS anzeigen, indem Sie die Zelle auswählen, die als Link angezeigt wird. Speichern Sie diese Ausgabe als .xdl Datei, schließen Sie sie, und öffnen Sie die .xdl Datei dann erneut in SSMS für visuelle Deadlock-Diagramme. Ihr Deadlock-Diagramm sollte ungefähr wie die folgende Abbildung 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 Deadlock-Bericht <resource-list>meldet jedes <xactlock> Element die zugrunde liegenden Ressourcen und spezifischen Informationen für Sperren jedes Elements 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 TSQL-Befehle in zwei Sitzungen erstellen einen Deadlock in der Tabelle t2:

In Sitzung 1:

--session 1
BEGIN TRAN foo;
UPDATE t2 SET b = b+ 10 WHERE a = 1; 

In Sitzung 2:

--session 2:
BEGIN TRAN 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 Aussagen führt zu einem Deadlock. In diesem Fall eine Keylock-Ressource, bei der jede Sitzung eine X-Sperre auf der eigenen TID enthä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 Ereignisse
• sys.dm_tran_locks (Transact-SQL)
• Deadlock Graph (Ereignisklasse)
• ADO-Remotedatendienst: Deadlocks mit wiederholbarer Isolationsstufe lesen
• 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 Deadlock-Datei in SQL Server Management Studio (SSMS)