Leitfaden zu Deadlocks

Gilt für:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric

In diesem Artikel werden Deadlocks im Datenbankmodul ausführlich erläutert. Deadlocks werden durch konkurrierende, gleichzeitige Sperrn der Datenbank verursacht, oft in mehrstufigen Transaktionen. Weitere Informationen zu Transaktionen und Sperren finden Sie im Leitfaden zur Transaktionssperre und Zeilenversionsverwaltung.

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

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.

Transaktion A kann nicht abgeschlossen werden, bevor Transaktion B abgeschlossen ist, aber Transaktion B ist durch Transaktion A blockiert. Dieser Zustand wird auch als zyklische Abhängigkeit bezeichnet: Transaktion A hat eine Abhängigkeit von Transaktion B, und Transaktion B schließt den Kreis, indem sie eine Abhängigkeit von Transaktion A hat.

Beide Transaktionen in einem Deadlock warten ewig, es sei denn, der Deadlock wird durch einen externen Prozess aufgelöst. Der Deadlock-Monitor des Datenbankmoduls 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 abgebrochen wurde, kann die Transaktion erneut versuchen, die normalerweise abgeschlossen wird, nachdem die andere blockierte Transaktion beendet wurde.

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 werden Transaktionen im Datenbankmodul nicht zeitlich begrenzt, es sei denn LOCK_TIMEOUT ist festgelegt. Die anfordernde Transaktion ist blockiert, nicht festgefahren, da die anfordernde Transaktion nichts getan hat, um die Transaktion, die die Sperre besitzt, zu blockieren. Schließlich schließt die besitzende Transaktion ab und gibt die Sperre frei, woraufhin die anfordernde Transaktion die Sperre erhält und fortfährt. Deadlocks werden fast sofort aufgelöst, während Sperren theoretisch unbegrenzt bestehen bleiben können. Deadlocks werden manchmal auch "deadly embrace" (tödliche Umarmung) genannt.

Deadlocks werden fast sofort aufgelöst, während Sperren theoretisch unbegrenzt bestehen bleiben können. Ein Thread in einem Multithread-Betriebssystem kann beispielsweise eine Ressource oder mehrere Ressourcen, wie z. B. Speicherblöcke, reservieren. Ein Deadlock kann auf jedem System mit mehreren Threads auftreten, nicht nur auf einem relationalen Datenbankmanagementsystem, und kann auch für andere Ressourcen als Sperren von Datenbankobjekten auftreten. Der wartende Thread ist für diese bestimmte Ressource abhängig vom Besitzerthread. In einer Instanz des Datenbankmoduls können Sitzungen beim Abrufen von Ressourcen ohne Datenbank, z. B. Arbeitsspeicher oder Threads, deadlockt werden.

In der Abbildung ist die Transaktion T1 von der Transaktion T2 für die Ressource Part Tabellensperre abhängig. Ebenso ist die Transaktion T2 von der Transaktion T1 abhängig, was die Ressource Supplier Tabellensperre betrifft. Da diese Abhängigkeiten einen Zyklus bilden, gibt es ein Deadlock zwischen den Transaktionen T1 und T2.

Hier ist eine allgemeinere Abbildung eines Deadlocks:

• Aufgabe T1 hat eine Sperre auf Ressource R1 (gekennzeichnet durch den Pfeil von R1 zu T1) und hat eine Sperre auf Ressource R2 angefordert (gekennzeichnet durch den Pfeil von T1 zu R2).

• Der Vorgang T2 verfügt über eine Sperre der Ressource R2 (durch den Pfeil von R2 bis T2 angegeben) und hat eine Sperre für Ressource R1 angefordert (durch den Pfeil von T2 bis R1 angegeben).

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

Note

Das Datenbankmodul erkennt automatisch Deadlock-Zyklen. Es wählt eine der Transaktionen als Deadlock-Opfer und beendet sie mit einem Fehler, um den Deadlock zu brechen.

Ressourcen, die an einem Deadlock beteiligt sein können

Jede Benutzersitzung kann eine oder mehrere Aufgaben haben, die in ihrem Namen ausgeführt werden, wobei jede Aufgabe Ressourcen erwerben oder auf den Erwerb warten kann. Die folgenden Typen von Ressourcen können eine Blockierung bewirken, die zu einem Deadlock führt.

• Locks. Das Warten auf den Erwerb von Sperren für Ressourcen wie Objekte, Seiten, Zeilen, Metadaten und Anwendungen kann zu einem Deadlock führen. Beispielsweise verfügt die Transaktion T1 über eine freigegebene (S) Sperrung für Zeile r1 und wartet darauf, eine exklusive (X) Sperrung auf r2 zu erhalten. Transaktion T2 verfügt über eine freigegebene (S) Sperre auf 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.

• Worker-Threads. Eine Aufgabe in der Warteschlange, die auf einen verfügbaren Worker-Thread wartet, kann einen Deadlock verursachen. Wenn die Aufgabe in der Warteschlange Ressourcen besitzt, die alle Worker-Threads blockieren, kommt es zu einem Deadlock. Beispielsweise startet Sitzung S1 eine Transaktion und erwirbt eine geteilte (S) Sperre für Zeile r1 und geht dann in den Ruhezustand. Aktive Sitzungen, die in allen verfügbaren Arbeitsthreads ausgeführt werden, versuchen, exklusive (X) Sperren auf Zeile r1 zu erwerben. Da Sitzung S1 keinen Worker-Thread erhalten kann, kann sie die Transaktion nicht bestätigen und die Sperre für Zeile r1 nicht freigeben. Das Ergebnis ist ein Deadlock.

• Memory. Wenn gleichzeitige Anforderungen auf Speicherzuweisungen warten, die mit dem verfügbaren Speicher nicht erfüllt werden können, kann es zu einem Deadlock kommen. Zwei gleichzeitige Abfragen, Q1 und Q2, werden z. B. als benutzerdefinierte Funktionen ausgeführt, die 10 MB bzw. 20 MB Arbeitsspeicher abrufen. Wenn jede Abfrage 30 MB benötigt und der gesamte verfügbare Speicher 20 MB beträgt, müssen Q1 und Q2 darauf warten, dass der jeweils andere Speicher freigegeben wird, was zu einer Blockierung führt.

• Ressourcen für die Ausführung von Abfragen Coordinator-, Producer- oder Consumer-Threads, die mit einem Exchange-Port verbunden sind, können sich gegenseitig blockieren, was in der Regel zu einem Deadlock führt, wenn mindestens ein weiterer Prozess beteiligt ist, der nicht Teil der parallelen Abfrage ist. Wenn eine parallele Abfrage die Ausführung startet, bestimmt das Datenbankmodul außerdem den Grad der Parallelität und die Anzahl der erforderlichen 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.

• aktive Ergebnismengen (MARS) Ressourcen Diese Ressourcen werden zum Steuern des Interleavings mehrerer aktiver Anforderungen unter MARS verwendet. Weitere Informationen finden Sie unter Verwenden mehrerer aktiver Ergebnissätze (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. Wenn die Aufgabe beendet ist oder mitten in der Anforderung aufgibt, gibt sie zuerst die Transaktions-Mutex frei, gefolgt von der Sitzungs-Mutex, in umgekehrter Reihenfolge des Erwerbs. 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 vom Datenbankmodul 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:

    

Deadlocks können auch auftreten, wenn eine Tabelle partitioniert ist und die LOCK_ESCALATION-Einstellung von ALTER TABLE auf AUTO aktiviert ist. Wenn LOCK_ESCALATION auf AUTO festgelegt wird, erhöht sich die Parallelität, indem das Datenbankmodul Tabellenpartitionen auf HoBT-Ebene sperren kann, anstatt auf Tabellenebene. 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 Gleichzeitigkeit, da große Aktualisierungen einer Partition gezwungen werden, auf eine Tabellensperre zu warten.

Deadlock-Erkennung

Alle im Abschnitt Ressourcen, die zu einer Blockierung führen können aufgeführten Ressourcen nehmen am Deadlock-Erkennungsschema des Datenbankmoduls teil. Die Deadlock-Erkennung wird von einem Sperrüberwachungsthread ausgeführt, der regelmäßig eine Suche durch alle Aufgaben in einer Instanz der Datenbank-Engine initiiert. Die folgenden Schritte beschreiben den Suchvorgang:

• Das Standardintervall beträgt 5 Sekunden.

• Wenn der Lock-Monitor-Thread Deadlocks findet, sinkt das Deadlock-Erkennungsintervall von 5 Sekunden auf bis zu 100 Millisekunden, je nach Häufigkeit der Deadlocks.

• Wenn der Sperrmonitorthread die Suche nach Deadlocks beendet, erhöht das Datenbankmodul die Intervalle zwischen Suchvorgängen auf 5 Sekunden.

• Wenn ein Deadlock erkannt wird, wird davon ausgegangen, dass die neuen Threads, die auf eine Sperre warten müssen, in den Deadlock-Zyklus gelangen. Die ersten paar Sperren, die nach der Erkennung eines Deadlocks warten, lösen sofort eine Deadlock-Suche aus, anstatt auf das nächste Deadlock-Erkennungsintervall zu warten. Beträgt das aktuelle Intervall z. B. 5 Sekunden und wurde gerade ein Deadlock entdeckt, löst das nächste Warten auf eine Sperre sofort den Deadlock-Detektor aus. Wenn das Warten auf eine Sperre Teil eines Deadlocks ist, wird es sofort erkannt und nicht erst bei der nächsten Deadlock-Suche.

Das Datenbankmodul führt in der Regel nur eine regelmäßige Deadlock-Erkennung durch. Da die Anzahl der im System aufgetretenen Deadlocks in der Regel klein ist, trägt die regelmäßige Deadlock-Erkennung dazu bei, den Aufwand der Deadlockerkennung im System zu verringern.

Wenn die Sperrenüberwachung die Suche nach Deadlocks für einen bestimmten Thread initiiert, wird die Ressource identifiziert, auf die der Thread wartet. Der Sperrmonitor findet dann die Eigentümer der jeweiligen Ressource und setzt die Deadlock-Suche für diese Threads rekursiv fort, bis er einen Zyklus findet. Ein auf diese Art identifizierter Zyklus bildet einen Deadlock.

Nachdem ein Deadlock erkannt wurde, beendet das Datenbankmodul einen Deadlock, indem er einen der Threads als Deadlock-Opfer auswählt. Das Datenbankmodul beendet den aktuellen Batch, der für den Thread ausgeführt wird, rollt die Transaktion des Deadlock-Opfers zurück und gibt 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 (Deadlock-Opfer) zeichnet Informationen über den Typ der Ressourcen auf, die an einem Deadlock beteiligt sind.

Standardmäßig wählt das Datenbankmodul die Transaktion als Deadlock-Opfer aus, deren Rollback am wenigsten teuer ist. 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. In bestimmten Fällen kann das Datenbankmodul die Deadlock-Priorität für eine kurze Dauer anpassen, um eine bessere Gleichzeitigkeit zu erzielen.

Die Deadlock-Priorität ist standardmäßig auf NORMALoder 0 festgelegt. Wenn zwei Sitzungen unterschiedliche Deadlock-Prioritäten haben, wird die Transaktion auf der Sitzung mit der niedrigeren Priorität als Deadlock-Opfer ausgewählt. Wenn beide Sitzungen dieselbe Deadlockpriorität haben, wird die Transaktion ausgewählt, die für den Rollback am wenigsten teuer ist. Wenn die am Deadlockzyklus beteiligten Sitzungen die gleiche Deadlockpriorität und die gleichen Kosten besitzen, wird das Opfer zufällig ausgewählt. Eine Task, die zurückgerollt wird, kann nicht als Deadlock-Opfer ausgewählt werden.

Beim Arbeiten mit der Common Language Runtime (CLR) erkennt der Deadlock-Monitor automatisch Deadlocks für Synchronisierungsressourcen (Monitore, Leser/Schreiber-Sperre und Thread-Beitritt), auf die innerhalb von verwalteten Prozeduren zugegriffen wird. Der Deadlock wird jedoch behoben, indem eine Ausnahme in der Prozedur ausgelöst wird, die als Deadlockopfer ausgewählt wurde. Es ist wichtig zu verstehen, dass die Ausnahme nicht automatisch Ressourcen freigibt, die derzeit im Besitz des Opfers sind; 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

Um Deadlock-Informationen anzuzeigen, stellt das Datenbankmodul Überwachungstools in Form des xml_deadlock_report erweiterten Ereignisses, zwei Ablaufverfolgungskennzeichnungen und das Deadlock-Diagrammereignis in SQL Profiler bereit.

Das xml_deadlock_report erweiterte Ereignis ist die empfohlene Methode zum Erfassen von Deadlock-Daten.

Erweitertes Deadlock-Ereignis

In SQL Server 2012 (11.x) und höheren Versionen sollte das xml_deadlock_report erweiterte Ereignis anstelle der Deadlock graph-Ereignisklasse in SQL Trace oder SQL Profiler verwendet werden.

Die system_health Ereignissitzung erfasst xml_deadlock_report standardmäßig Ereignisse. Diese Ereignisse enthalten das Deadlock-Diagramm. Da die system_health Sitzung standardmäßig aktiviert ist, müssen Sie keine separate Ereignissitzung konfigurieren, um Deadlock-Informationen zu erfassen.

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.

Sie können die event_file Zieldaten der system_health Sitzung in Management Studio anzeigen. Wenn Ereignisse xml_deadlock_report aufgetreten sind, zeigt Management Studio eine grafische Darstellung der Vorgänge und Ressourcen an, die an einem Deadlock beteiligt sind, wie im folgenden Beispiel gezeigt:

Die folgende Abfrage kann alle Deadlock-Ereignisse anzeigen, die vom ring_buffer-Ziel der system_health-Sitzung erfasst werden.

SELECT xdr.value('@timestamp', 'datetime') AS deadlock_time,
       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 deadlock_time DESC;

Das Resultset sieht wie folgt aus.

Das folgende Beispiel zeigt ein Beispiel für die Ausgabe aus der event_data Spalte:

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

Trace Flag 1204 und Trace Flag 1222

Wenn Deadlocks auftreten und Ablaufverfolgungskennzeichnung 1204 oder Ablaufverfolgungskennzeichnung 1222 aktiviert ist, werden Deadlock-Details im SQL Server-Fehlerprotokoll gemeldet. Das Traceflag 1204 meldet Informationen über Deadlocks, die von jedem Knoten formatiert werden, der an dem Deadlock beteiligt ist. Die Ablaufverfolgungskennzeichnung 1222 formatiert Deadlock-Informationen zuerst nach Prozessen und dann nach Ressourcen. Es ist möglich, beide Trace-Flags zu aktivieren, um zwei Darstellungen desselben Deadlock-Ereignisses zu erhalten.

Important

Vermeiden Sie die Verwendung von Traceflags 1204 und 1222 bei workload-intensiven Systemen, bei denen Deadlocks auftreten. Die Verwendung dieser Trace-Flags kann zu Leistungsproblemen führen. Verwenden Sie stattdessen das erweiterte Deadlock-Ereignis , um die erforderlichen Informationen zu erfassen.

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

Property	Trace Flag 1204 und Trace Flag 1222	Nur Trace-Flag 1204	Nur Ablaufverfolgungskennzeichnung 1222
Ausgabeformat	Die Ausgabe wird im SQL Server-Fehlerprotokoll aufgezeichnet.	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 nicht dem XML Schema Definition (XSD)-Schema 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 mit Knoten im Ablaufverfolgungs-Flag 1204 gleichzusetzen sind.
Identifizieren von Attributen	SPID:<x> ECID:<x>. Identifiziert den Sitzungs-ID-Thread in Fällen paralleler Prozesse. 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>, wobei <x> durch den SPID-Wert ersetzt wird und <y> größer als 0 ist, stellt den Ausführungskontext für denselben SPID dar. BatchID (sbid für 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 Sperrtyp für eine bestimmte Ressource an, die von einem Thread angefordert, gewährt oder darauf gewartet wird. Der Modus kann "Intent Shared" (IS), "Shared" (S), "Update" (U), "Intent Exclusive" (IX), "Shared with Intent Exclusive" (SIX) und "Exklusiv" (X) sein. Line # (line für Trace-Flag 1222). Listet die Zeilennummer des aktuellen Batches von Anweisungen auf, die beim Auftreten des Deadlocks ausgeführt wurden. Input Buf (inputbuf für Trace-Flag 1222). Listet alle Anweisungen im aktuellen Batch auf.	Node Stellt die Eintragsnummer in der Deadlock-Kette dar. Lists Der Eigentümer des Schlosses kann Teil dieser Listen sein: Grant List Listet die aktuellen Eigentümer der Ressource auf. Convert List Listet die aktuellen Eigentümer auf, die versuchen, ihre Sperren auf eine höhere Stufe zu konvertieren. Wait List Listet aktuelle neue Sperranforderungen für die Ressource auf. Statement Type Beschreibt den Typ der Anweisung (SELECT, INSERT, UPDATEoder DELETE), für die die Threads Berechtigungen besitzen. Victim Resource Owner Gibt den teilnehmenden Thread an, den das Datenbankmodul als Opfer auswähelt, um den Deadlock-Zyklus zu unterbrechen. Der ausgewählte Thread und alle Ausführungskontexte werden beendet. Next Branch Stellt die zwei oder mehr Ausführungskontexte aus demselben SPID dar, die am Deadlock-Zyklus beteiligt sind.	deadlock victim Stellt die physische Speicheradresse der Aufgabe dar (siehe sys.dm_os_tasks) dar Der Wert kann bei einem nicht aufgelösten Deadlock null sein. executionstack Stellt den Transact-SQL Aufrufstapel dar, der zum Zeitpunkt der Ausführung des Deadlocks ausgeführt wird. priority Stellt die Deadlock-Priorität dar. logused Von der Aufgabe belegter Speicherplatz. owner id Die ID der Transaktion, die die Kontrolle über die Anforderung hat. status Status des Vorgangs. Weitere Informationen finden Sie unter sys.dm_os_tasks. waitresource Die Ressource, die vom Vorgang benötigt wird. waittime Zeit in Millisekunden, die beim Warten auf die Ressource vergeht. schedulerid Der diesem Vorgang zugeordnete Zeitplan. Siehe sys.dm_os_schedulers. hostname Der Name der Arbeitsstation. isolationlevel Die aktuelle Transaktionsisolationsstufe. Xactid Die ID der Transaktion, die die Kontrolle über die Anforderung hat. currentdb Die ID der Datenbank. lastbatchstarted Das letzte Mal, wenn ein Clientprozess die Batchausführung gestartet hat. lastbatchcompleted Das letzte Mal, wenn ein Clientprozess die Batchausführung abgeschlossen hat. clientoption1 und clientoption2 die konfigurierten Optionen für diese Sitzung. Diese Werte sind Bitmasken, die Optionen darstellen, die normalerweise durch SET-Anweisungen wie SET NOCOUNT und SET XACTABORT gesteuert werden. Weitere Informationen finden Sie unter @@OPTIONS. associatedObjectId Repräsentiert die HoBT (Heap- oder B-Baum-) ID.
Ressourcenattribute	RID identifiziert die einzelne Zeile in einer Tabelle, in der eine Sperre gehalten oder angefordert wird. RID wird als RID dargestellt: db_id:file_id:page_no:row_no. Beispiel: RID: 6:1:20789:0. OBJECT gibt die Tabelle an, in der eine Sperre gehalten oder angefordert wird. OBJECT dargestellt als OBJECT: db_id:object_id. Beispiel: TAB: 6:2009058193. KEY Bezeichnet den Schlüsselbereich innerhalb eines Index, für den eine Sperre gehalten oder angefordert wird. KEY wird als KEY: db_id:hobt_id (Indexschlüssel-Hashwert) dargestellt. Beispiel: KEY: 6:72057594057457664 (350007a4d329). PAG Bezeichnet die Seitenressource, für die eine Sperre gehalten oder angefordert wird. PAG dargestellt als PAG: db_id:file_id:page_no. Beispiel: PAG: 6:1:20789. EXT Identifiziert die Ausdehnungsstruktur. EXT dargestellt als EXT: db_id:file_id:extent_no. Beispiel: EXT: 6:1:9. DB Identifiziert die Ausdehnungsstruktur. DB wird auf eine der folgenden Arten dargestellt: DB: db_id DB: db_id[BULK-OP-DB], die die Datenbanksperre identifiziert, die von der Datenbanksicherung übernommen wird. DB: db_id[BULK-OP-LOG], die die durch die Protokollsicherung gesetzte Sperre identifiziert. APP Identifiziert eine Anwendungssperre. APP dargestellt als APP: lock_resource. Beispiel: APP: Formf370f478. METADATA Stellt Metadaten-Ressourcen dar, die an einem Deadlock beteiligt sind. Da METADATA viele Subressourcen hat, hängt der zurückgegebene Wert von der Subressource ab, die gesperrt wurde. METADATA.USER_TYPE gibt beispielsweise user_type_id = *integer_value* zurück. Für weitere Informationen über METADATA Ressourcen und Subressourcen siehe sys.dm_tran_locks. HOBT Stellt einen Heap oder B-Baum dar, der in ein Deadlock verwickelt ist.	Gilt nicht ausschließlich für dieses Ablaufverfolgungsflag.	Gilt nicht ausschließlich für dieses Ablaufverfolgungsflag.

Trace-Flag 1204 Beispiel

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 Trace-Flag 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

SQL Profiler verfügt über ein Ereignis, das eine grafische Darstellung der Aufgaben und Ressourcen darstellt, die an einem Deadlock beteiligt sind. Im folgenden Beispiel wird die Ausgabe von SQL Profiler gezeigt, wenn das Deadlock Graph-Ereignis aktiviert ist.

Die SQL Profiler- und SQL-Ablaufverfolgungsfeatures sind veraltet und werden durch erweiterte Ereignisse ersetzt. Erweiterte Ereignisse haben eine geringere Leistungsbelastung und sind besser anpassbar als SQL Trace. Erwägen Sie, das Deadlock-Ereignis von Extended Events anstelle der Ablaufverfolgung von Deadlocks in SQL Profiler zu verwenden.

Weitere Informationen zum Deadlockereignis finden Sie unter Lock:Deadlock (Ereignisklasse). Weitere Informationen zu SQL Profiler-Deadlockdiagrammen finden Sie unter Speichern von Deadlockdiagrammen (SQL Server Profiler).

Erweiterte Ereignisse stellen Äquivalente von SQL Trace-Ereignisklassen bereit. Weitere Informationen finden Sie unter Anzeigen der Äquivalente für erweiterte Ereignisse zu SQL-Ablaufverfolgungsereignisklassen. Erweiterte Ereignisse werden über die SQL-Ablaufverfolgung empfohlen.

Deadlocks beheben

Wenn eine Instanz des Datenbankmoduls eine Transaktion als Deadlock-Opfer auswäht, wird der aktuelle Batch beendet, die Transaktion zurückgesetzt und fehler 1205 an die Anwendung zurückgegeben. Die zurückgegebene Nachricht ist wie folgt strukturiert:

Your transaction (process ID #...) 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 Fehler 1205 behandeln kann. Wenn eine Anwendung den Fehler nicht behandelt, kann die Anwendung nicht wissen, dass die Transaktion zurückgesetzt wurde.

Das Implementieren eines Fehlerhandlers, der Fehler 1205 abfangen kann, ermöglicht einer Anwendung das Behandeln von Deadlocks und die Durchführung von Abhilfemaßnahmen (z. B. das automatische erneute Übermitteln der Abfrage, die an dem Deadlock beteiligt war).

Die Anwendung sollte kurzzeitig angehalten werden, bevor die Abfrage erneut abgesendet wird. Dies gibt der anderen Transaktion, die am Deadlock beteiligt ist, die Möglichkeit, ihre Sperren zu lösen und freizugeben. Die Zufälligkeit der Dauer der Pause minimiert die Wahrscheinlichkeit, dass der Deadlock erneut auftritt, wenn die erneut übermittelte Abfrage die Sperre anfordert. Der Fehlerhandler kann beispielsweise so codiert werden, dass er für eine zufällige Dauer zwischen 1 und drei Sekunden angehalten wird.

Mit TRY...CATCH behandeln

Sie können TRY...CATCH verwenden, um Deadlocks zu behandeln. Fehler 1205 kann vom CATCH Block abgefangen werden.

Weitere Informationen finden Sie unter Handling von Deadlocks.

Deadlocks minimieren

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.
• Vermeiden Sie höhere Isolationsstufen wie REPEATABLE READ z. B. und SERIALIZABLE wenn sie nicht erforderlich sind.
• Verwenden Sie eine auf der Zeilenversionsverwaltung basierende Isolationsstufe.
  • Aktivieren Sie die READ_COMMITTED_SNAPSHOT Datenbankoption, um die Zeilenversionsverwaltung für Transaktionen mithilfe der READ COMMITTED Isolationsebene zu verwenden.
  • Verwenden Sie Snapshot-Isolationstransaktionen.
• 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 auf die Tabelle Supplier und dann auf die Tabelle Part erhalten, ist eine Transaktion auf der Tabelle Supplier blockiert, bis die andere Transaktion abgeschlossen ist. Nach dem Commit oder Rollback der ersten Transaktion wird die zweite fortgesetzt, und es kommt nicht zu einem Deadlock. 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 Transaktionen, die Benutzerinteraktionen enthalten, da die Geschwindigkeit von Batches, die ohne Benutzereingriff ausgeführt werden, viel schneller ist als die Geschwindigkeit, mit der ein Benutzer manuell auf Abfragen reagieren muss, z. B. das Beantworten einer Aufforderung zu einem von einer Anwendung angeforderten Parameter. 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. Auch wenn kein Deadlock auftritt, werden andere Transaktionen, die auf dieselben Ressourcen zugreifen, blockiert, während sie darauf warten, dass die Transaktion abgeschlossen ist.

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.

Die Beibehaltung von Transaktionen in einem Batch minimiert Netzwerk-Roundtrips während einer Transaktion, wodurch mögliche Verzögerungen beim Abschließen der Transaktion aufgrund der Clientverarbeitung reduziert werden.

Vermeiden höherer Isolationsstufen

Ermitteln Sie, ob eine Transaktion auf einer niedrigeren Isolationsstufe ausgeführt werden kann. Die Verwendung READ COMMITTED ermöglicht es einer Transaktion, Zuvor gelesene (aber nicht geänderte) Daten von einer anderen Transaktion zu lesen, ohne auf den Abschluss der Transaktion zu warten. READ COMMITTED hält freigegebene Sperren für eine kürzere Dauer als eine höhere Isolationsstufe wie z. B. SERIALIZABLE. Dadurch wird die Sperrkonkurrenz reduziert.

Verwenden einer auf der Zeilenversionsverwaltung basierenden Isolationsstufe

Wenn die READ_COMMITTED_SNAPSHOT Datenbankoption festgelegt ONist, verwendet eine Transaktion, die unter der READ COMMITTED Isolationsebene ausgeführt wird, Zeilenversionsverwaltung anstelle freigegebener Sperren während lesevorgängen.

Tipp

Microsoft empfiehlt die Zeilenversionsverwaltungs-basierte READ COMMITTED Isolationsstufe für alle Anwendungen, es sei denn, eine Anwendung basiert auf dem Sperrverhalten der sperrbasierten READ COMMITTED Isolationsstufe.

Die Snapshot-Isolation verwendet auch Zeilenversionierung, bei der während Lesevorgängen keine gemeinsamen Sperren verwendet werden. Bevor eine Transaktion unter Snapshot-Isolation ausgeführt werden kann, muss die Datenbankoption ALLOW_SNAPSHOT_ISOLATION aktiviert werden ON.

Verwenden Sie Zeilenversionsbasierte Isolationsebenen, um 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. Daher blockieren sie sich nicht.

Einen Deadlock verursachen

Möglicherweise müssen Sie zu Lern- oder Demonstrationszwecken einen Deadlock verursachen.

Im folgenden Beispiel wird in der AdventureWorksLT2019 Beispieldatenbank das Standardschema und die Standarddaten verwendet, wenn READ_COMMITTED_SNAPSHOT aktiviert wurde. Um dieses Beispiel herunterzuladen, besuchen Sie AdventureWorks-Beispieldatenbanken.

Ein Beispiel, das zu einem Deadlock führt, wenn die optimierte Sperrung aktiviert ist, finden Sie unter Optimierte Sperrung und Deadlocks.

Um einen Deadlock zu verursachen, müssen Sie zwei Sessions mit der AdventureWorksLT2019 Datenbank verbinden. Wir bezeichnen diese Sitzungen als Session A und Session B. Sie können diese beiden Sitzungen erstellen, indem Sie zwei Abfragefenster in SQL Server Management Studio (SSMS) erstellen.

Führen Sie in Sitzung A den folgenden Batch aus. Dieser Code beginnt eine explizite Transaktion und führt eine Anweisung aus, die die SalesLT.Product Tabelle aktualisiert. Dazu erwirbt die Transaktion eine Aktualisierungssperre (U) für die qualifizierenden Zeilen in der Tabelle SalesLT.Product , die dann in exklusive (X) Sperren konvertiert werden. 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 Batch 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. Das Update übernimmt eine Aktualisierungssperre (U) für die qualifizierenden Zeilen in der SalesLT.ProductDescription Tabelle. 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 dieses Update abzuschließen, benötigt Session B freigegebene (S) Sperren für Zeilen in der Tabelle SalesLT.Product, einschließlich der Zeilen, die durch Session A gesperrt sind. Session B ist blockiertSalesLT.Product.

Zurück zu Sitzung A. Führen Sie die folgende UPDATE Anweisung aus. Diese Aussage wird als Teil der zuvor 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';

Eine 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 stellt der Deadlock-Monitor fest, dass sich die Transaktionen in Session A und Session B gegenseitig blockieren und dass keine der beiden Transaktionen Fortschritte machen kann. Sie sehen, dass ein Deadlock auftritt, wobei Session A als Deadlock-Opfer ausgewählt wurde. Sitzung B erfolgreich abgeschlossen. Eine Fehlermeldung wird im Abfragefenster von Sitzung A mit Text angezeigt, der dem folgenden Beispiel ähnelt:

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, überprüfen Sie, ob READ_COMMITTED_SNAPSHOT in Ihrer Beispieldatenbank aktiviert ist. Deadlocks können in jeder Datenbankkonfiguration auftreten, aber dieses Beispiel erfordert, dass READ_COMMITTED_SNAPSHOT aktiviert ist.

Sie können die Details des Deadlocks im ring_buffer Ziel der system_health Ereignissitzung anzeigen, die standardmäßig in SQL Server und Azure SQL Managed Instance 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 die XML-Datei in der Deadlock_XML Spalte in SSMS anzeigen, indem Sie die Zelle auswählen, die als Hyperlink erscheint. Speichern Sie diese Ausgabe als .xdl Datei, schließen Sie sie und öffnen Sie dann erneut die .xdl Datei in SSMS, um das Deadlock-Diagramm anzuzeigen. Das Deadlock-Diagramm sollte ungefähr wie die folgende Abbildung aussehen.

Optimierte Verriegelung und Deadlocks

Bei optimierter Sperre werden Seiten- und Zeilensperren erst am Ende der Transaktion gehalten. Sie werden veröffentlicht, sobald eine Zeile aktualisiert wird. Zusätzlich wird, wenn READ_COMMITTED_SNAPSHOT aktiviert ist, keine Updatesperren (U) verwendet. Daher wird die Wahrscheinlichkeit von Deadlocks reduziert.

Im vorherigen Beispiel wird kein Deadlock verursacht, wenn die optimierte Sperre aktiviert ist, da sie auf den Updatesperren (U) basiert.

Das folgende Beispiel kann verwendet werden, um einen Deadlock in einer Datenbank zu verursachen, bei der die optimierte Sperrung aktiviert ist.

Erstellen Sie zunächst eine Beispieltabelle, und fügen Sie Daten hinzu.

CREATE TABLE t2
(
    a INT PRIMARY KEY NOT NULL,
    b INT NULL
);

INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);

Die folgenden T-SQL-Batches, die in zwei separaten Sitzungen ausgeführt werden, erstellen einen Deadlock.

In Sitzung 1:

BEGIN TRANSACTION xactA;

UPDATE t2
    SET b = b + 10
WHERE a = 1;

In Sitzung 2:

BEGIN TRANSACTION xactB;

UPDATE t2
    SET b = b + 10
WHERE a = 2;

In Sitzung 1:

UPDATE t2
    SET b = b + 100
WHERE a = 2;

In Sitzung 2:

UPDATE t2
    SET b = b + 20
WHERE a = 1;

In diesem Fall hält jede Sitzung eine exklusive (X) Sperre für ihre eigene Transaktions-ID (TID)-Ressource und wartet auf die gemeinsame (S) Sperre der anderen TID, was zu einem Deadlock führt.

Der folgende abgekürzte Deadlock-Bericht enthält Elemente und Attribute, die für eine optimierte Sperrung spezifisch sind. Unter jeder Ressource im Deadlock-Bericht <resource-list> meldet jedes <xactlock>-Element die zugrunde liegenden Ressourcen und TID-Sperrungsinformationen jedes Deadlock-Mitglieds.

<deadlock>
 <victim-list>
  <victimProcess id="process12994344c58" />
 </victim-list>
 <process-list>
  <process id="process12994344c58" taskpriority="0" logused="272" waitresource="XACT: 23:2476:0 KEY: 23:72057594049593344 (8194443284a0)" waittime="447" ownerId="3234906" transactionname="xactA" lasttranstarted="2025-10-08T21:36:34.063" XDES="0x12984ba0480" lockMode="S" schedulerid="2" kpid="204928" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2025-10-08T21:36:40.857" lastbatchcompleted="2025-10-08T21:36:34.063" lastattention="2025-10-08T21:36:11.340" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WS1" hostpid="23380" loginname="user1" isolationlevel="read committed (2)" xactid="3234906" currentdb="23" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <inputbuf>
UPDATE t2
    SET b = b + 20
WHERE a = 1;
   </inputbuf>
  </process>
  <process id="process1299c969828" taskpriority="0" logused="272" waitresource="XACT: 23:2477:0 KEY: 23:72057594049593344 (61a06abd401c)" waittime="3083" ownerId="3234886" transactionname="xactB" lasttranstarted="2025-10-08T21:36:30.303" XDES="0x12995c84480" lockMode="S" schedulerid="2" kpid="63348" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2025-10-08T21:36:38.223" lastbatchcompleted="2025-10-08T21:36:30.303" lastattention="1900-01-01T00:00:00.303" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WS1" hostpid="23380" loginname="user1" isolationlevel="read committed (2)" xactid="3234886" currentdb="23" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <inputbuf>
UPDATE t2
    SET b = b + 100
WHERE a = 2;
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <xactlock xdesIdLow="2476" xdesIdHigh="0" dbid="23" id="lock1299fa06c00" mode="X">
   <UnderlyingResource>
    <keylock hobtid="72057594049593344" dbid="23" objectname="e6fc405e-1ee8-49df-a2b3-54ee0151d851.dbo.t2" indexname="PK__t2__3BD0198ED3CBA65E" />
   </UnderlyingResource>
   <owner-list>
    <owner id="process1299c969828" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process12994344c58" mode="S" requestType="wait" />
   </waiter-list>
  </xactlock>
  <xactlock xdesIdLow="2477" xdesIdHigh="0" dbid="23" id="lock129940b2380" mode="X">
   <UnderlyingResource>
    <keylock hobtid="72057594049593344" dbid="23" objectname="e6fc405e-1ee8-49df-a2b3-54ee0151d851.dbo.t2" indexname="PK__t2__3BD0198ED3CBA65E" />
   </UnderlyingResource>
   <owner-list>
    <owner id="process12994344c58" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process1299c969828" mode="S" requestType="wait" />
   </waiter-list>
  </xactlock>
 </resource-list>
</deadlock>

Verwandte Inhalte

• Übersicht über erweiterte Ereignisse
• sys.dm_tran_locks (Transact-SQL)
• Deadlock Graph (Ereignisklasse)
• Deadlocks mit wiederholbarer Isolationsstufe beim Lesen
• Sperre: Deadlock-Kettenereignis-Klasse
• Sperre: Deadlock-Ereignisklasse
• SET DEADLOCK_PRIORITY (Transact-SQL)
• Analysieren und Verhindern von Deadlocks in Azure SQL-Datenbank und SQL-Datenbank in Fabric
• Öffnen, Darstellen und Drucken einer Deadlock-Datei in SQL Server Management Studio (SSMS)