Analysieren und Verhindern von Deadlocks in Azure SQL-Datenbank

Gilt für: Azure SQL-Datenbank

In diesem Artikel erfahren Sie, wie Sie Deadlocks in Azure SQL-Datenbank identifizieren, Deadlockdiagramme und den Abfragedatenspeicher verwenden, um die Abfragen im Deadlock zu identifizieren, und wie Sie Änderungen planen und testen, um zu verhindern, dass Deadlocks erneut auftreten.

Dieser Artikel konzentriert sich auf die Identifizierung und Analyse von Deadlocks aufgrund von Sperrkonflikten. Erfahren Sie mehr über andere Arten von Deadlocks unter Ressourcen, für die ein Deadlock auftreten kann.

Auftreten von Deadlocks in Azure SQL-Datenbank

Jede neue Datenbank in Azure SQL-Datenbank verfügt standardmäßig über die aktivierte Datenbankeinstellung READ COMMITTED-Momentaufnahme (RCSI). Blockierung zwischen Sitzungen, die Daten lesen, und Sitzungen, die Daten schreiben, wird unter RCSI minimiert, weil Zeilenversionsverwaltung zur Erhöhung der Parallelität verwendet wird. Blockierungen und Deadlocks können jedoch in Datenbanken in Azure SQL-Datenbank trotzdem auftreten, weil:

• Abfragen, die Daten ändern, können einander blockieren.
• Abfragen können unter Isolationsstufen ausgeführt werden, die Blockierungen erhöhen. Isolationsstufen können über Clientbibliotheksmethoden, Abfragehinweise oder SET-Anweisungen in Transact-SQL angegeben werden.
• RCSI kann deaktiviert sein, wodurch die Datenbank gemeinsame Sperren (S) verwendet, um SELECT-Anweisungen zu schützen, die unter der READ COMMITTED-Isolationsstufe ausgeführt werden. Dies kann Blockierungen und Deadlocks erhöhen.

Ein Beispieldeadlock

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. Ein Deadlock wird auch zyklische Abhängigkeit genannt: Im Fall eines Deadlocks mit zwei Tasks ist Transaktion A von Transaktion B abhängig, und Transaktion B schließt den Kreis, da sie von Transaktion A abhängig ist.

Beispiel:

1. Sitzung A beginnt eine explizite Transaktion und führt eine Aktualisierungsanweisung aus, die eine Aktualisierungssperre (U) für eine Zeile der Tabelle SalesLT.Product abruft, die in eine exklusive Sperre (X) umgewandelt wird.
2. Sitzung B führt eine Aktualisierungsanweisung aus, die die Tabelle SalesLT.ProductDescription ändert. Die Aktualisierungsanweisung wird mit der Tabelle SalesLT.Product verknüpft, um die richtigen Zeilen zu finden, die aktualisiert werden sollen.
   • Sitzung B ruft eine Aktualisierungssperre (U) für 72 Zeilen der Tabelle SalesLT.ProductDescription ab.
   • Sitzung B benötigt 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.
3. Sitzung A setzt ihre Transaktion fort und führt nun eine Aktualisierung der Tabelle SalesLT.ProductDescription durch. Sitzung A wird von Sitzung B für SalesLT.ProductDescription blockiert.

Alle Transaktionen in einem Deadlock warten auf unbestimmte Zeit, es sei denn, eine der beteiligten Transaktionen wird zurückgesetzt, z. B. weil ihre Sitzung beendet wurde.

Der Deadlockmonitor der Datenbank-Engine überprüft regelmäßig, ob sich Tasks in einem Deadlock befinden. Wenn der Deadlockmonitor eine zyklische Abhängigkeit feststellt, wählt er einen der Tasks als Deadlockopfer aus und beendet seine Transaktion mit der Fehlermeldung 1205: „Transaction (Process ID N) was deadlocked on lock resources with another process and has been chosen as the deadlock victim (Transaktion (Prozess-ID „N“) befand sich auf Sperrenressourcen aufgrund eines anderen Prozesses in einer Deadlocksituation und wurde als Deadlockopfer ausgewählt). Rerun the transaction“. Durch das Unterbrechen des Deadlocks auf diese Weise können die anderen Tasks oder Tasks im Deadlock ihre Transaktionen abschließen.

Die Anwendung mit der Transaktion, die als Deadlockopfer ausgewählt wurde, sollte die Transaktion erneut versuchen, die normalerweise abgeschlossen wird, nachdem die anderen Transaktionen, die in den Deadlock verwickelt sind, beendet wurden.

Es ist eine bewährte Methode, eine kurze, zufällige Verzögerung vor der Wiederholung einzuführen, um ein erneutes Auftreten der gleichen Sperre zu vermeiden. Erfahren Sie mehr darüber, wie Wiederholungslogik für vorübergehende Fehler entworfen werden kann.

Standardisolationsstufe in Azure SQL-Datenbank

Neue Datenbanken in Azure SQL-Datenbank aktivieren standardmäßig READ COMMITTED-Momentaufnahmen (RCSI). RCSI ändert das Verhalten der READ COMMITTED-Isolationsstufe, um Zeilenversionsverwaltung zu verwenden, um Konsistenz auf Anweisungsebene ohne Verwendung gemeinsamer Sperren (S) für SELECT-Anweisungen bereitzustellen.

Wenn RCSI aktiviert ist:

• Anweisungen zum Lesen von Daten blockieren keine Anweisungen zum Ändern von Daten.
• Anweisungen zum Ändern von Daten blockieren keine Anweisungen zum Lesen von Daten.

Die Momentaufnahmeisolationsstufe ist standardmäßig für neue Datenbanken in Azure SQL-Datenbank aktiviert. Die Momentaufnahmeisolation ist eine zusätzliche zeilenbasierte Isolationsstufe, die Konsistenz auf Transaktionsebene für Daten bereitstellt und Zeilenversionsverwaltung verwendet, um Zeilen auszuwählen, die aktualisiert werden sollen. Zum Verwenden der Momentaufnahmeisolation müssen Abfragen oder Verbindungen ihre Transaktionsisolationsstufe explizit auf SNAPSHOT festlegen. Dies kann nur erfolgen, wenn Momentaufnahmeisolation für die Datenbank aktiviert ist.

Sie können mit Transact-SQL ermitteln, ob RCSI und/oder Momentaufnahmeisolation aktiviert ist. Stellen Sie in Azure SQL-Datenbank eine Verbindung mit Ihrer Datenbank her, und führen Sie die folgende Abfrage aus:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Wenn RCSI aktiviert ist, gibt die is_read_committed_snapshot_on-Spalte den Wert 1 zurück. Wenn Momentaufnahmeisolation aktiviert ist, gibt die snapshot_isolation_state_desc-Spalte den Wert ON (EIN) zurück.

Wenn RCSI für eine Datenbank in Azure SQL-Datenbank deaktiviert wurde, untersuchen Sie, warum RCSI deaktiviert wurde, bevor Sie die Funktion erneut aktivieren. Anwendungscode kann in der Erwartung geschrieben worden sein, dass Abfragen, die Daten lesen, durch Abfragen, die Daten schreiben, blockiert werden, was zu falschen Ergebnissen aufgrund von Rennbedingungen führt, wenn RCSI aktiviert ist.

Interpretieren von Deadlockereignissen

Ein Deadlockereignis wird ausgelöst, wenn der Deadlock-Manager in Azure SQL-Datenbank einen Deadlock feststellt und eine Transaktion als Opfer auswählt. Mit anderen Worten: Wenn Sie Warnungen für Deadlocks einrichten, wird die Benachrichtigung ausgelöst, nachdem ein einzelner Deadlock aufgelöst wurde. Für diesen Deadlock ist keine Benutzeraktion erforderlich. Anwendungen sollten so geschrieben werden, dass sie Wiederholungslogik enthalten, sodass sie automatisch fortgesetzt werden, wenn sie Fehler 1205 erhalten: „Transaction (Process ID N) was deadlocked on lock resources with another process and has been chosen as the deadlock victim (Transaktion (Prozess-ID „N“) befand sich auf Sperrenressourcen aufgrund eines anderen Prozesses in einer Deadlocksituation und wurde als Deadlockopfer ausgewählt). Führen Sie die Transaktion erneut aus."

Es ist jedoch sinnvoll, Warnungen einzurichten, da Deadlocks erneut auftreten können. Mit Deadlockwarnungen können Sie untersuchen, ob in Ihrer Datenbank ein Muster von wiederholten Deadlocks auftritt. In diesem Fall können Sie Maßnahmen ergreifen, um das erneute Auftreten von Deadlocks zu verhindern. Erfahren Sie mehr über Warnungen im Abschnitt Überwachung und Warnungen für Deadlocks dieses Artikels.

Wichtigste Methoden zur Vermeidung von Deadlocks

Der risikoärmste Ansatz zur Vermeidung von Deadlocks besteht im Allgemeinen darin, nicht gruppierte Indizes so zu optimieren, dass die am Deadlock beteiligten Abfragen optimiert werden.

• Das Risiko ist bei diesem Ansatz gering, da die Optimierung von nicht gruppierten Indizes keine Änderungen am Abfragecode selbst erfordert, wodurch das Risiko eines Benutzerfehlers beim Umschreiben von Transact-SQL, der dazu führt, dass falsche Daten an den Benutzer zurückgegeben werden, verringert wird.
• Eine effektive Optimierung nicht gruppierter Indizes hilft Abfragen, Daten effizienter zu lesen und zu ändern. Durch die Verringerung der Datenmenge, auf die eine Abfrage zugreifen muss, wird die Wahrscheinlichkeit des Blockierens verringert, und Deadlocks können oft verhindert werden.

In einigen Fällen kann das Erstellen oder Optimieren eines gruppierten Index Blockierungen und Deadlocks verringern. Da der gruppierte Index in allen nicht gruppierten Indexdefinitionen enthalten ist, kann das Erstellen oder Ändern eines gruppierten Indexes ein E/A-intensiver und zeitaufwändiger Vorgang bei größeren Tabellen mit vorhandenen nicht gruppierten Indizes sein. Mehr dazu erfahren Sie unter Entwurfsrichtlinien für gruppierte Indizes.

Wenn Indexoptimierung bei der Verhinderung von Deadlocks nicht erfolgreich ist, stehen andere Methoden zur Verfügung:

• Wenn der Deadlock nur dann auftritt, wenn ein bestimmter Plan für eine der am Deadlock beteiligten Abfragen ausgewählt wird, kann das Erzwingen eines Abfrageplans mit Query Store das erneute Auftreten von Deadlocks verhindern.
• Das Umschreiben von Transact-SQL für mindestens eine Transaktion, die an der Blockierung beteiligt ist, kann ebenfalls zur Vermeidung von Deadlocks beitragen. Die Aufteilung expliziter Transaktionen in kleinere Transaktionen erfordert eine sorgfältige Codierung und Tests, um die Datengültigkeit bei gleichzeitigen Änderungen zu gewährleisten.

Erfahren Sie mehr über jeden dieser Ansätze im Abschnitt Verhindern des erneuten Auftretens eines Deadlocks dieses Artikels.

Überwachen von und Warnungen bei Deadlocks

In diesem Artikel verwenden wir die Beispieldatenbank AdventureWorksLT, um Warnungen für Deadlocks einzurichten, einen Beispielsdeadlock auszulösen, das Deadlockdiagramm für den Beispielsdeadlock zu analysieren und Änderungen zu testen, um ein erneutes Auftreten des Deadlocks zu verhindern.

In diesem Artikel wird der SQL Server Management Studio-Client (SSMS) verwendet, da er Funktionen zur Anzeige von Deadlockdiagrammen in einem interaktiven visuellen Modus enthält. Sie können andere Clients wie Azure Data Studio verwenden, um die Beispiele nachzuvollziehen, aber möglicherweise können Sie Deadlockdiagramme nur als XML anzeigen.

Erstellen der AdventureWorksLT-Datenbank

Um die Beispiele nachzuvollziehen, erstellen Sie eine neue Datenbank in Azure SQL-Datenbank, und wählen Sie die Beispieldaten („Sample“) als Datenquelle aus.

Um ausführliche Anweisungen zum Erstellen von AdventureWorksLT mit dem Azure-Portal, der Azure CLI oder mit PowerShell zu erhalten, wählen Sie den Ansatz Ihrer Wahl unter Schnellstart: Erstellen einer einzelnen Azure SQL-Datenbank-Instanz.

Einrichten von Deadlockwarnungen im Azure-Portal

Um Warnungen für Deadlockereignisse einzurichten, führen Sie die Schritte im Artikel Erstellen von Warnungen für Azure SQL-Datenbank und Azure Synapse Analytics mit dem Azure-Portal aus.

Wählen Sie Deadlocks als Signalname für die Warnung aus. Konfigurieren Sie die Aktionsgruppe, damit Sie mithilfe der Methode Ihrer Wahl benachrichtigt werden, z. B. mit dem Aktionstyp E-Mail/SMS/Push/Voice.

Erfassen von Deadlockdiagrammen in Azure SQL-Datenbank mit erweiterten Ereignissen

Deadlockdiagramme sind eine reichhaltige Quelle für Informationen zu den an einem Deadlock beteiligten Prozessen und Sperren. Um Deadlockdiagramme mit erweiterten Ereignissen (XEvents) in Azure SQL-Datenbank zu sammeln, erfassen Sie das sqlserver.database_xml_deadlock_report-Ereignis.

Sie können Deadlockdiagramme mit XEvents mithilfe des Ringpufferziels oder eines Ereignisdateiziels sammeln. Überlegungen zum Auswählen des entsprechenden Zieltyps werden in der folgenden Tabelle zusammengefasst:

Wählen Sie den Zieltyp aus, den Sie verwenden möchten:

CREATE EVENT SESSION [deadlocks] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer 
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
    STATE = START;
GO

IF 0 = (SELECT COUNT(*)
    FROM sys.symmetric_keys
    WHERE symmetric_key_id = 101 and name=N'##MS_DatabaseMasterKey##')
BEGIN
    PRINT N'Creating master key';
    CREATE MASTER KEY;
END
ELSE 
BEGIN
    PRINT N'Master key already exists, no action taken';
END
GO

CREATE DATABASE SCOPED CREDENTIAL
    [https://yourstorageaccountname.blob.core.windows.net/yourcontainername]
    WITH
        IDENTITY = 'SHARED ACCESS SIGNATURE', 
        SECRET = 'sp=r&st=2022-04-08T14:34:21Z&se=2032-04-08T22:34:21Z&sv=2020-08-04&sr=c&sig=pUNbbsmDiMzXr1vuNGZh84zyOMBFaBjgWv53IhOzYWQ%3D'
    ;
GO

CREATE EVENT SESSION [deadlocks_eventfile] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.event_file
    (SET filename =
    'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/deadlocks.xel'
    )
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = START;
GO

Verursachen eines Deadlocks in AdventureWorksLT

Um einen Deadlock zu verursachen, müssen Sie zwei Sitzungen mit der AdventureWorksLT-Datenbank verbinden. Wir verweisen auf diese Sitzungen als Sitzung A und Sitzung B.

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. Eine Fehlermeldung wird in Sitzung A mit Text ähnlich dem folgenden angezeigt:

Msg 1205, Level 13, State 51, Line 7 Transaction (Process ID 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim (Msg 1205, Ebene 13, Status 51, Zeile 7 Transaktion (Prozess-ID 91) befand sich auf Sperrenressourcen aufgrund eines anderen Prozesses in einer Deadlocksituation und wurde als Deadlockopfer ausgewählt). Führen Sie die Transaktion erneut aus.

Sitzung B wird erfolgreich abgeschlossen.

Wenn Sie Deadlockwarnungen im Azure-Portal einrichten, sollten Sie kurz nach Auftreten des Deadlocks eine Benachrichtigung erhalten.

Anzeigen von Deadlockdiagrammen aus einer XEvents-Sitzung

Wenn Sie eine XEvents-Sitzung zum Erfassen von Deadlocks eingerichtet haben und nach dem Start der Sitzung ein Deadlock aufgetreten ist, können Sie eine interaktive grafische Anzeige des Deadlockdiagramms sowie das XML für das Deadlockdiagramm anzeigen.

Es sind verschiedene Methoden verfügbar, um Deadlock-Informationen für das Ringpufferziel und die Ereignisdateiziele zu erhalten. Wählen Sie das Ziel aus, das Sie für Ihre XEvents-Sitzung verwendet haben:

DECLARE @tracename sysname = N'deadlocks';

WITH ring_buffer AS (
    SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    JOIN sys.dm_xe_database_session_targets AS t 
        ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
    WHERE s.name = @tracename and
    t.target_name = N'ring_buffer'
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM ring_buffer
    CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = STOP;
GO
ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = START;
GO

DECLARE @tracename sysname = N'deadlocks_eventfile',
    @filename nvarchar(2000);

WITH eft as (SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    JOIN sys.dm_xe_database_session_targets AS t 
        ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
    WHERE s.name = @tracename and
    t.target_name = N'event_file'
)
SELECT @filename = ft.evtdata.value('(@name)[1]','nvarchar(2000)') 
FROM eft
CROSS APPLY rb.nodes('EventFileTarget/File') as ft(evtdata);

WITH xevents AS (
    SELECT cast(event_data as XML) as ed
    FROM sys.fn_xe_file_target_read_file(@filename, null, null, null )
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM xevents
    CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

declare @filename nvarchar(2000) = N'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/yourfilename.xel';

with xevents AS (
    SELECT cast(event_data as XML) as ed
    FROM sys.fn_xe_file_target_read_file(@filename, null, null, null )
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM xevents
    CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Anzeigen und Speichern eines Deadlockdiagramms in XML

Das Anzeigen eines Deadlockdiagramms im XML-Format ermöglicht es Ihnen, den inputbuffer der Transact-SQL-Anweisungen zu kopieren, die am Deadlock beteiligt sind. Möglicherweise möchten Sie auch Deadlocks in einem textbasierten Format analysieren.

Wenn Sie eine Transact-SQL-Abfrage zum Zurückgeben von Deadlockdiagramminformationen verwendet haben, wählen Sie den Wert in der deadlock_xml-Spalte aus einer beliebigen Zeile aus, um das XML des Deadlockdiagramms in einem neuen Fenster in SSMS zu öffnen.

Dies ist das XML für dieses Deadlockbeispieldiagramm:

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    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'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    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';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

So speichern Sie das Deadlockdiagramm als XML-Datei:

1. Wählen Sie Datei und Speichern unter... aus.
2. Übernehmen Sie für den Wert Speichern unter die Standardeinstellung XML-Dateien (*.xml).
3. Legen Sie den Dateinamen auf den Namen Ihrer Wahl fest.
4. Wählen Sie Speichern aus.

Speichern eines Deadlockdiagramms als XDL-Datei, die interaktiv in SSMS angezeigt werden kann

Eine interaktive Darstellung eines Deadlockdiagramms kann nützlich sein, um einen schnellen Überblick über die an einem Deadlock beteiligten Prozesse und Ressourcen zu erhalten und das Deadlockopfer schnell zu identifizieren.

So speichern Sie ein Deadlockdiagramm als Datei, die von SSMS grafisch angezeigt werden kann:

1. Wählen Sie den Wert in der deadlock_xml-Spalte aus einer beliebigen Zeile aus, um das XML des Deadlockdiagramms in einem neuen Fenster in SSMS zu öffnen.

2. Wählen Sie Datei und Speichern unter... aus.

3. Legen Sie Dateityp auf Alle Dateien fest.

4. Legen Sie den Dateinamen auf den Namen Ihrer Wahl mit der Erweiterung XDL fest.

5. Wählen Sie Speichern aus.

   

6. Schließen Sie die Datei, indem Sie am oberen Rand des Fensters das X auf der Registerkarte auswählen, oder indem Sie Datei und dann Schließen auswählen.

7. Öffnen Sie die Datei in SSMS erneut, indem Sie Datei, Öffnen und dann Datei auswählen. Wählen Sie die Datei aus, die Sie mit der Erweiterung .xdl gespeichert haben.

   Das Deadlockdiagramm wird nun in SSMS mit einer visuellen Darstellung der Prozesse und Ressourcen angezeigt, die am Deadlock beteiligt sind.

   

Analysieren eines Deadlocks für Azure SQL-Datenbank

Ein Deadlockdiagramm weist in der Regel drei Knoten auf:

• victim-list. Prozessbezeichner des Deadlockopfers.
• Process-list. Informationen zu allen am Deadlock beteiligten Prozessen. Deadlockdiagramme verwenden den Begriff „Prozess“, um eine Sitzung darzustellen, die eine Transaktion ausführt.
• Resource-list. Informationen zu den am Deadlock beteiligten Ressourcen.

Bei der Analyse eines Deadlocks ist es nützlich, diese Knoten zu durchlaufen.

Opferliste des Deadlocks

Die Deadlockopferliste zeigt den Prozess an, der als Deadlockopfer ausgewählt wurde. In der visuellen Darstellung eines Deadlockdiagramms werden Prozesse durch Ovale dargestellt. Für den Deadlockopferprozess wird ein „X“ über dem Oval angezeigt.

In der XML-Ansicht eines Deadlockdiagramms gibt der victim-list-Knoten eine ID für den Prozess an, der das Opfer des Deadlocks war.

In unserem Beispieldeadlock lautet die Opferprozess-ID prozess24756e75088. Wir können diese ID beim Untersuchen der process-list- und resource-list-Knoten verwenden, um mehr über den Opferprozess und die Ressourcen zu erfahren, die gesperrt wurden oder gesperrt werden sollten.

Deadlockprozessliste

Die Deadlockprozessliste ist eine reichhaltige Quelle für Informationen zu den am Deadlock beteiligten Transaktionen.

Die Grafikdarstellung des Deadlockdiagramms zeigt nur eine Teilmenge von Informationen, die im Deadlockdiagramm-XML enthalten sind. Die Ovale im Deadlockdiagramm stellen den Prozess dar und zeigen Informationen an, darunter:

• Serverprozess-ID, auch als Sitzungs-ID oder SPID bezeichnet.

• Deadlockpriorität der Sitzung. Wenn die Sitzungen verschiedene Deadlockprioritäten besitzen, wird die Sitzung mit der niedrigeren Deadlockpriorität als Deadlockopfer ausgewählt. In diesem Beispiel weisen beide Sitzungen die gleiche Deadlockpriorität auf.

• Umfang des von der Sitzung verwendeten Transaktionsprotokolls in Bytes. Wenn für beide Sitzungen die gIeiche Deadlockprioriät festgelegt wurde, wählt der Deadlockmonitor diejenige Sitzung als Deadlockopfer aus, für die das Rollback weniger aufwändig ist. Die Kosten werden anhand der Anzahl der bis zu dem jeweiligen Zeitpunkt in jeder Transaktion geschriebenen Protokollbytes ermittelt.

  In unserem Deadlockbeispiel hatte session_id 89 eine geringere Menge des Transaktionsprotokolls verwendet und wurde als Deadlockopfer ausgewählt.

Darüber hinaus können Sie den Eingabepuffer für die letzte Anweisung anzeigen, die in jeder Sitzung ausgeführt wurde, bevor der Deadlock aufgetreten ist, indem Sie mit der Maus auf den jeweiligen Prozess zeigen. Der Eingabepuffer wird in einer QuickInfo angezeigt.

Zusätzliche Informationen stehen für Prozesse in der XML-Ansicht des Deadlockdiagramms zur Verfügung, darunter:

• Identifizierende Informationen für die Sitzung, z. B. der Clientname, der Hostname und der Anmeldename.
• Der Abfrageplanhash für die letzte Anweisung, die von jeder Sitzung vor dem Auftreten des Deadlocks ausgeführt wurde. Der Abfrageplanhash ist nützlich, um weitere Informationen zur Abfrage aus dem Abfragespeicher abzurufen.

In unserem Beispieldeadlock:

• Wir können erkennen, dass beide Sitzungen mit dem SSMS-Client unter der Anmeldung chrisqpublic ausgeführt wurden.
• Der Abfrageplanhash der letzten Anweisung, die vor dem Deadlock von unserem Deadlockopfer ausgeführt wurde, lautet 0x02b0f58d7730f798. Wir können den Text dieser Anweisung im Eingabepuffer erkennen.
• Der Abfrageplanhash der letzten Anweisung, die von der anderen Sitzung in unserem Deadlock ausgeführt wurde, lautet ebenfalls 0x02b0f58d7730f798. Wir können den Text dieser Anweisung im Eingabepuffer erkennen. In diesem Fall weisen beide Abfragen denselben Abfrageplanhash auf, da die Abfragen bis auf einen Literalwert, der als Gleichheitsprädikat verwendet wird, identisch sind.

Wir verwenden diese Werte später in diesem Artikel, um weitere Informationen im Abfragespeicher zu erhalten.

Einschränkungen des Eingabepuffers in der Deadlockprozessliste

In Bezug auf die Eingabepufferinformationen in der Deadlockprozessliste müssen einige Einschränkungen beachtet werden.

Abfragetext kann im Eingabepuffer abgeschnitten sein. Der Eingabepuffer ist auf die ersten 4.000 Zeichen der Anweisung begrenzt, die ausgeführt wird.

Darüber hinaus werden einige Anweisungen, die am Deadlock beteiligt sind, möglicherweise nicht in das Deadlockdiagramm einbezogen. In unserem Beispiel hat Sitzung A zwei UPDATE-Anweisungen innerhalb einer einzelnen Transaktion ausgeführt. Nur die zweite UPDATE-Anweisung, die den Deadlock verursacht hat, wird im Deadlockdiagramm berücksichtigt. Die erste UPDATE-Anweisung, die von Sitzung A ausgeführt wurde, hat zum Deadlock beigetragen, indem sie Sitzung B blockiert hat. Der Eingabepuffer, query_hash und die zugehörigen Informationen für die erste von Sitzung A ausgeführte Anweisung sind nicht im Deadlockdiagramm enthalten.

Um die vollständige Transact-SQL-Ausführung in einer Transaktion mit mehreren Anweisungen zu identifizieren, die an einem Deadlock beteiligt ist, müssen Sie entweder die relevanten Informationen in der gespeicherten Prozedur oder im Anwendungscode finden, die bzw. der die Abfrage ausgeführt hat, oder eine Ablaufverfolgung mit erweiterten Ereignissen ausführen, um die vollständigen Anweisungen zu erfassen, die von den an einem Deadlock beteiligten Sitzungen ausgeführt werden, während dieser auftritt. Wenn eine Anweisung, die am Deadlock beteiligt ist, abgeschnitten wurde und nur partielles Transact-SQL im Eingabepuffer angezeigt wird, können Sie das Transact-SQL für die Anweisung im Abfragespeicher mit dem Ausführungsplan finden.

Deadlockressourcenliste

Die Deadlockressourcenliste zeigt, welche Sperrressourcen, auf die gewartet wird, im Besitz der Prozesse im Deadlock sind.

Ressourcen werden durch Rechtecks in der visuellen Darstellung des Deadlocks angezeigt:

In diesem Beispieldeadlock:

• Für das Deadlockopfer, das wir als Sitzung A bezeichnet haben, gilt Folgendes:

  • Es besitzt eine exklusive Sperre (X) für einen Schlüssel für den PK_Product_ProductID-Index für die SalesLT.Product-Tabelle.
  • Es fordert eine Aktualisierungssperre (U) für einen Schlüssel für den PK_ProductDescription_ProductDescriptionID-Index für die SalesLT.ProductDescription-Tabelle an.

• Für den anderen Prozess, den wir als Sitzung B bezeichnet haben, gilt Folgendes:

  • Er besitzt eine Aktualisierungssperre (U) für einen Schlüssel für den PK_ProductDescription_ProductDescriptionID-Index für die SalesLT.ProductDescription-Tabelle.
  • Er besitzt eine geteilte Sperre (S) für einen Schlüssel für den PK_ProductDescription_ProductDescriptionID-Index für die SalesLT.ProductDescription-Tabelle.

Wir können die gleichen Informationen im XML des Deadlockdiagramms im resource-list-Knoten anzeigen.

Suchen nach Abrageausführungsplänen im Abfragespeicher

Oft ist es sinnvoll, die Abfrageausführungspläne für die am Deadlock beteiligten Anweisungen zu untersuchen. Diese Ausführungspläne können oft im Abfragespeicher unter Verwendung des Abfrageplanhashs aus der XML-Ansicht der Prozessliste des Deadlockdiagramms gefunden werden.

Diese Transact-SQL-Abfrage sucht nach Abfrageplänen, die mit dem Abfrageplanhash übereinstimmen, den wir für unser Deadlockbeispiel gefunden haben. Stellen Sie eine Verbindung mit der Benutzerdatenbank in Azure SQL-Datenbank her, um die Abfrage auszuführen.

DECLARE @query_plan_hash binary(8) = 0x02b0f58d7730f798

SELECT 
    qrsi.end_time as interval_end_time,
    qs.query_id,
    qp.plan_id,
    qt.query_sql_text, 
    TRY_CAST(qp.query_plan as XML) as query_plan,
    qrs.count_executions
FROM sys.query_store_query as qs
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qrsi on qrs.runtime_stats_interval_id=qrsi.runtime_stats_interval_id
WHERE query_plan_hash =  @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

Abhängig von Ihren Einstellungen für CLEANUP_POLICY oder QUERY_CAPTURE_MODE im Abfragespeicher können Sie möglicherweise keinen Abfrageausführungsplan aus dem Abfragespeicher abrufen. In diesem Fall können Sie oft die benötigten Informationen erhalten, indem Sie den geschätzten Ausführungsplan für die Abfrage anzeigen.

Suchen nach Mustern, die Blockierungen erhöhen

Wenn Sie Abfrageausführungspläne untersuchen, die Deadlocks beinhalten, suchen Sie nach Mustern, die zu Blockierungen und Deadlocks beitragen können.

• Tabellen- oder Indexscans. Wenn Abfragen, die Daten ändern, unter RCSI ausgeführt werden, erfolgt die Auswahl der zu aktualisierenden Zeilen mit einem blockierenden Scan, bei dem eine Aktualisierungssperre (U) auf die Datenzeile angewendet wird, wenn Datenwerte gelesen werden. Wenn die Datenzeile nicht dem Aktualisierungskriterium entspricht, wird die Aktualisierungssperre aufgehoben, und die nächste Zeile wird gesperrt und gescannt.

  Die Optimierung von Indizes, damit Änderungsabfragen Zeilen effizienter finden können, verringert die Anzahl der ausgegebenen Aktualisierungssperren. Dadurch wird das Risiko von Blockierungen und Deadlocks verringert.

• Indizierte Sichten, die auf mehrere Tabellen verweisen. Wenn Sie eine Tabelle ändern, auf die in einer indizierten Sicht verwiesen wird, muss die Datenbank-Engine auch die indizierte Sicht verwalten. Dies erfordert mehr Sperren und kann zu erhöhten Blockierungen und Deadlocks führen. Indizierte Sichten können auch Aktualisierungsvorgänge verursachen, die intern unter der READ COMMITTED-Isolationsstufe ausgeführt werden.

• Änderungen an Spalten, auf die in FOREIGN KEY-Einschränkungen verwiesen wird. Wenn Sie Spalten in einer Tabelle ändern, auf die in einer FOREIGN KEY-Einschränkung verwiesen wird, muss die Datenbank-Engine nach in Beziehung stehenden Zeilen in der verweisenden Tabelle suchen. Zeilenversionen können für diese Lesevorgänge nicht verwendet werden. In Fällen, in denen kaskadierende Aktualisierungen oder Löschungen aktiviert sind, kann die Isolationsebene zum Schutz vor Phantomeinfügungen für die Dauer der Anweisung auf serialisierbar hochgestuft werden.

• Sperrhinweise. Suchen Sie nach Tabellenhinweisen, die Isolationsstufen angeben, die mehr Sperren erfordern. Diese Hinweise umfassen HOLDLOCK (entspricht serialisierbar), SERIALIZABLE, READCOMMITTEDLOCK (deaktiviert RCSI) und REPEATABLEREAD. Darüber hinaus können Hinweise wie PAGLOCK, TABLOCK, UPDLOCK und XLOCK die Risiken von Blockierungen und Deadlocks erhöhen.

  Wenn diese Hinweise vorhanden sind, untersuchen Sie, warum sie implementiert wurden. Diese Hinweise können Racebedingungen verhindern und die Gültigkeit der Daten gewährleisten. Es kann möglich sein, diese Hinweise beizubehalten und zukünftige Deadlocks mit einer alternativen Methode zu verhindern, falls erforderlich, wie im Abschnitt Verhindern, dass ein Deadlock erneut auftritt dieses Artikels beschrieben.

  Hinweis

  Erfahren Sie mehr über das Verhalten beim Ändern von Daten mithilfe von Zeilenversionsverwaltung im Leitfaden zu Transaktionssperren und Zeilenversionsverwaltung.

Bei der Untersuchung des vollständigen Codes für eine Transaktion (in einem Ausführungsplan oder im Abfragecode der Anwendung) sollten Sie nach weiteren problematischen Mustern suchen:

• Benutzerinteraktionen in Transaktionen. Eine Benutzerinteraktion innerhalb einer expliziten Transaktion mit mehreren Anweisungen verlängert die Dauer von Transaktionen erheblich. Dadurch wird es wahrscheinlicher, dass sich diese Transaktionen überschneiden und dass es zu Blockierungen und Deadlocks kommt.

  Ebenso erhöht das Halten einer offenen Transaktion und die Abfrage einer nicht verwandten Datenbank oder eines Systems mitten in der Transaktion die Wahrscheinlichkeit von Blockierungen und Deadlocks erheblich.

• Transaktionen, die auf Objekte in verschiedener Reihenfolge zugreifen. Das Auftreten von Deadlocks ist weniger wahrscheinlich, wenn gleichzeitige explizite Transaktionen mit mehreren Anweisungen denselben Mustern folgen und in derselben Reihenfolge auf Objekte zugreifen.

Verhindern, dass ein Deadlock erneut auftritt

Es gibt mehrere Techniken, um das erneute Auftreten von Deadlocks zu verhindern, darunter Indexoptimierung, Erzwingen von Plänen mit dem Abfragespeicher und Ändern von Transact-SQL-Abfragen.

• Überprüfen des gruppierten Index der Tabelle. Die meisten Tabellen profitieren von gruppierten Indizes, aber oftmals werden Tabellen unabsichtlich als Heaps implementiert.

  Eine Möglichkeit zur Prüfung auf einen gruppierten Index ist die Verwendung der gespeicherten Systemprozedur sp_helpindex. Sie können beispielsweise eine Zusammenfassung der Indizes für die Tabelle SalesLT.Product anzeigen, indem Sie die folgende Anweisung ausführen:

  exec sp_helpindex 'SalesLT.Product';
  GO
  

  Überprüfen Sie die index_description-Spalte. Eine Tabelle kann nur über einen gruppierten Index verfügen. Wenn ein gruppierter Index für die Tabelle implementiert wurde, enthält index_description das Wort „clustered“.

  Wenn kein gruppierter Index vorhanden ist, ist die Tabelle ein Heap. Überprüfen Sie in diesem Fall, ob die Tabelle absichtlich als Heap erstellt wurde, um ein bestimmtes Leistungsproblem zu lösen. Erwägen Sie die Implementierung eines gruppierten Indexes, ausgehend von den Entwurfsrichtlinien für gruppierte Indizes.

  In einigen Fällen kann das Erstellen oder Optimieren eines gruppierten Indexes Blockierungen in Deadlocks verringern oder beseitigen. In anderen Fällen müssen Sie möglicherweise eine zusätzliche Technik anwenden, etwa die anderen in dieser Liste genannten Vorgehensweisen.

• Erstellen oder Ändern Sie nicht gruppierte Indizes. Durch das Optimieren von nicht gruppierten Indizes können Ihre Änderungsabfragen die Daten schneller finden, die aktualisiert werden sollen, wodurch die Anzahl der erforderlichen Aktualisierungssperren reduziert wird.

  In unserem Beispieldeadlock enthält der Abfrageausführungsplan, der im Abfragespeicher gefunden wurde, einen gruppierten Indexscan für den PK_Product_ProductID-Index. Das Deadlockdiagramm gibt an, dass eine gemeinsame (S) Wartesperre für diesen Index eine Komponente im Deadlock ist.

  

  Dieser Indexscan wird ausgeführt, weil unsere Aktualisierungsabfrage eine indizierte Sicht namens vProductAndDescription ändern muss. Wie im Abschnitt Suchen nach Mustern, die Blockierungen erhöhen dieses Artikels erwähnt, können indizierte Sichten, die auf mehrere Tabellen verweisen, Blockierungen und die Wahrscheinlichkeit von Deadlocks erhöhen.

  Wenn wir den folgenden nicht gruppierten Index in der Datenbank AdventureWorksLT erstellen, der die Spalten von SalesLT.Product „abdeckt“, auf die von der indizierten Sicht verwiesen wird, hilft dies der Abfrage, Zeilen viel effizienter zu finden:

  CREATE INDEX ix_Product_ProductID_Name_ProductModelID on SalesLT.Product (ProductID, Name, ProductModelID);
  GO
  

  Nachdem dieser Index erstellt wurde, tritt der Deadlock nicht mehr auf.

  Wenn Deadlocks Änderungen an Spalten beinhalten, auf die in FOREIGN KEY-Einschränkungen verwiesen wird, stellen Sie sicher, dass Indizes für die referenzierende Tabelle des FOREIGN KEY die effiziente Suche nach zugehörigen Zeilen unterstützen.

  Zwar können Indizes in einigen Fällen die Abfrageleistung erheblich verbessern, Indizes verursachen jedoch auch Mehraufwand und Verwaltungskosten. Lesen Sie die allgemeinen Richtlinien für den Entwurf von Indizes, um den Nutzen von Indizes zu beurteilen, bevor Sie Indizes erstellen, vor allem von breit angelegten Indizes und Indizes für große Tabellen.

• Bewerten Sie den Wert von indizierten Sichten. Eine weitere Option, um zu verhindern, dass unser Beispieldeadlock erneut ausgeführt wird, besteht darin, die indizierte SalesLT.vProductAndDescription-Sicht abzulegen. Wenn diese indizierte Sicht nicht verwendet wird, verringert dies den Mehraufwand für die Verwaltung der indizierten Sicht im Laufe der Zeit.

• Verwenden Sie Momentaufnahmeisolation. In einigen Fällen kann das Festlegen der Transaktionsisolationsebene auf „Momentaufnahme“ für mindestens eine der an einem Deadlock beteiligten Transaktionen das erneute Auftreten von Blockierungen und Deadlocks verhindern.

  Diese Technik ist am ehesten erfolgreich, wenn sie auf SELECT-Anweisungen angewendet wird, wenn READ COMMITTED-Momentaufnahmen in einer Datenbank deaktiviert sind. Wenn die READ COMMITTED-Momentaufnahme deaktiviert ist, erfordern SELECT-Abfragen mit der READ COMMITTED-Isolationsebene gemeinsame (S) Sperren. Durch die Verwendung von Momentaufnahmeisolation für diese Transaktionen entfällt die Notwendigkeit von gemeinsamen Sperren, was Blockierungen und Deadlocks verhindern kann.

  In Datenbanken, in denen READ COMMITTED-Momentaufnahmeisolation aktiviert wurde, erfordert SELECT-Abfragen keine gemeinsamen (S) Sperren, sodass Deadlocks wahrscheinlicher zwischen Transaktionen auftreten, die Daten ändern. In Fällen, in denen Deadlocks zwischen mehreren datenverändernden Transaktionen auftreten, kann Momentaufnahmeisolierung zu einem Aktualisierungskonflikt anstatt zu einem Deadlock führen. Dies führt ebenfalls dazu, dass eine der Transaktionen ihren Vorgang wiederholen muss.

• Erzwingen Sie einen Plan mit dem Abfragespeicher. Es kann vorkommen, dass eine der Abfragen im Deadlock mehrere Ausführungspläne hat und der Deadlock nur auftritt, wenn ein bestimmter Plan verwendet wird. Sie können verhindern, dass der Deadlock erneut auftritt, indem Sie einen Plan in Abfragespeicher erzwingen.

• Ändern Sie die Transact-SQL-Anweisungen. Möglicherweise müssen Sie die Transact-SQL-Anweisungen ändern, um zu verhindern, dass der Deadlock erneut auftritt. Das Ändern von Transact-SQL-Anweisungen sollte sorgfältig erfolgen, und die Änderungen sollten sorgfältig getestet werden, um sicherzustellen, dass die Daten richtig sind, wenn die Änderungen parallel ausgeführt werden. Wenn Sie Transact-SQL-Anweisungen neu schreiben, sollten Sie Folgendes berücksichtigen:

  • Anordnen von Anweisungen in Transaktionen, damit sie auf Objekte in derselben Reihenfolge zugreifen.
  • Aufteilen von Transaktionen in kleinere Transaktionen, wenn möglich.
  • Verwenden von Abfragehinweisen, falls erforderlich, um die Leistung zu optimieren. Sie können Hinweise mithilfe des Abfragespeichers anwenden, ohne den Anwendungscode zu ändern.

Weitere Möglichkeiten zum Minimieren von Deadlocks finden Sie im Leitfaden zu Deadlocks.

Ablegen einer XEvents-Sitzung

Möglicherweise möchten Sie eine XEvents-Sitzung verlassen, die DeadlockiInformationen für wichtige Datenbanken für lange Zeiträume erfasst. Beachten Sie, dass dies bei Verwendung eines Ereignisdateiziels zu großen Dateien führen kann, wenn mehrere Deadlocks auftreten. Sie können Blobdateien aus Azure Storage für eine aktive Ablaufverfolgung löschen, mit Ausnahme der Datei, in die aktuell geschrieben wird.

Wenn Sie eine XEvents-Sitzung entfernen möchten, sind die Transact-SQL-Anweisungen, mit denen die Sitzung gelöscht wird, dieselben, und zwar unabhängig vom ausgewählten Zieltyp.

Führen Sie zum Entfernen einer XEvents-Sitzung die folgenden Transact-SQL-Anweisungen aus. Ersetzen Sie vor dem Ausführen des Codes den Namen der xEvents-Sitzung durch den entsprechenden Wert.

ALTER EVENT SESSION [deadlocks] ON DATABASE
    STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Verwenden von Azure Storage-Explorer

Azure Storage-Explorer ist eine eigenständige Anwendung, die das Arbeiten mit Ereignisdateizielen vereinfacht, die in Blobs in Azure Storage gespeichert sind. Sie können Storage-Explorer für die folgenden Aufgaben verwenden:

• Erstellen eines Blobcontainers, um XEvent-Sitzungsdaten zu speichern.
• Abrufen des SAS-Tokens für einen Blobcontainer.
  • Wie in Erfassen von Deadlockdiagrammen in Azure SQL-Datenbank mit erweiterten Ereignissen erläutert wurde, sind Berechtigungen zum Lesen, Schreiben und Auflisten erforderlich.
  • Entfernen Sie alle führenden ?-Zeichen aus dem Query string-Element, um den Wert als Geheimnis zu verwenden, wenn Sie datenbankbezogene Anmeldeinformationen erstellen.
• Anzeigen und Herunterladen erweiterter Ereignisdateien aus einem Blobcontainer.

Herunterladen von Azure Storage-Explorer.

Nächste Schritte

Weitere Informationen zur Leistung in Azure SQL-Datenbank:

• Verstehen und Beheben von Problemen durch Blockierungen in Azure SQL-Datenbank
• Handbuch zu Transaktionssperren und Zeilenversionsverwaltung
• Leitfaden zu Deadlocks
• SET TRANSACTION ISOLATION LEVEL
• Azure SQL-Datenbank: Verbessern der Leistungsoptimierung mithilfe der automatischen Optimierung
• Bereitstellen einer konsistenten Leistung mit Azure SQL
• Wiederholungslogik für vorübergehende Fehler.