Identifizieren und Beheben von Blockierungen und Deadlocks

Abgeschlossen

Das Blockieren ist in einer Datenbank, die sperrung verwendet, normal. Eine Transaktion enthält eine Sperre. Eine andere Transaktion wartet. Eine kurze Blockierung, die einige Millisekunden dauert, wird erwartet. Es wird zu einem Problem, wenn es lange genug dauert, um benutzer zu beeinflussen. Deadlocks sind die schwerere Form: Zwei Transaktionen blockieren sich dauerhaft, und das Datenbankmodul muss einen beenden, um den Zyklus zu unterbrechen.

Blockierung

Blockierung tritt auf, wenn eine Sitzung eine Sperre für eine Ressource enthält und eine andere Sitzung eine widersprüchliche Sperre für dieselbe Ressource anfordert. Die anfordernde Sitzung wartet, bis die erste Sitzung die Sperre freigibt.

Ressourcen können Zeilen, Seiten oder sogar ganze Tabellen sein. Sperren können gemeinsame Zugriffe erlauben (für Lesevorgänge) oder exklusiv gelten (für Schreibvorgänge). Wenn eine Sitzung eine Sperre anfordert, die mit einer vorhandenen Sperre in Konflikt steht, wird sie blockiert, bis die erste Sitzung ihre Transaktion abschließt oder zurücksetzt und die Sperren freigibt.

Identifizieren von Blockierungsketten

In der Azure SQL-Datenbank ist RCSI (Read Committed Snapshot Isolation) standardmäßig aktiviert, sodass Lesevorgänge die Zeilenversionsverwaltung anstelle geteilter Sperren verwenden. Durch diese Einstellung wird die Blockierung zwischen Lesern und Autoren erheblich reduziert. Blockierungen zwischen zwei Autoren und Blockierungen aufgrund expliziter Transaktionen mit höheren Isolationsstufen werden weiterhin gesetzt.

Die Session am Anfang einer Blockierungskette wird als Kopfblocker bezeichnet. Alle anderen blockierten Sitzungen warten direkt oder indirekt darauf, dass der Verursacher der Blockierung auf höchster Ebene seine Sperren aufhebt. Sie können den Verursacher der Blockierung höchster Ebene mit der Abfrage sys.dm_exec_requests ermitteln, indem Sie Sitzungen suchen, für die blocking_session_id ungleich null ist.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS query_text,
    r.status,
    r.command
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Den Verursacher der Blockierung höchster Ebene in diesen Ergebnissen ermitteln Sie, indem Sie nach der Sitzungs-ID suchen, die andere Vorgänge blockiert, aber selbst nicht blockiert ist. Angenommen, die Abfrage gibt diese Zeilen zurück:

Sitzungs-ID	blocking_session_id
55	52
60	52
52	0

Die Sitzungen 55 und 60 werden von Sitzung 52 blockiert, und Sitzung 52 weist für blocking_session_id den Wert 0 auf, wird also selbst nicht blockiert. Sitzung 52 ist der Verursacher der Blockierung höchster Ebene. Nachdem Sie den Verursacher der Blockierung höchster Ebene identifiziert haben, fragen Sie sys.dm_exec_sessions und sys.dm_exec_requests nach dieser Sitzungs-ID ab, um zu ermitteln, was ausgeführt wird und warum die Sperren nicht freigegeben werden.

Denken Sie daran, dass RCSI die Blockierung zwischen Lesern und Autoren, aber nicht zwischen zwei Autoren verhindert. Betrachten Sie ein Szenario, in dem Sitzung 52 innerhalb einer expliziten Transaktion eine Batchaktualisierung ausführt:

-- Session 52
BEGIN TRANSACTION;
UPDATE Orders SET Status = 'Processing' WHERE Region = 'West';
-- Transaction stays open while application does other work

Für diese Aktualisierung werden exklusive Sperren für jede übereinstimmende Zeile benötigt. Jetzt versucht Sitzung 55, eine der gleichen Zeilen zu aktualisieren:

-- Session 55
UPDATE Orders SET Priority = 1 WHERE OrderId = 4820;

Sitzung 55 wartet, weil Sitzung 52 bereits eine exklusive Sperre für diese Zeile besitzt und noch kein Commit ausgeführt wurde. Eine SELECT-Abfrage über diese Zeilen könnte unter RCSI weiterhin erfolgreich ausgeführt werden, weil dann eine Zeilenversion gelesen und keine geteilte Sperre angefordert wird. Da RCSI standardmäßig die Leser-Schreiber-Blockierung entfernt, tritt die Blockierung, die Sie in der Azure SQL-Datenbank erleben, normalerweise auf, wenn zwei Sitzungen beide auf dieselben Zeilen zugreifen müssen.

Erkennen allgemeiner Blockierungsszenarien

Verstehen , warum das Blockieren geschieht, hilft Ihnen, dies zu verhindern. Das Azure SQL-Datenbankmodul verwaltet automatisch Sperren, aber bestimmte Muster führen zu einer längeren Blockierung:

Eine zeitintensive Abfrage, die Sperren für einen langen Zeitraum hält. Die Abfrage wird aktiv ausgeführt, hält jedoch die Sperren über die gesamte Zeit. Andere Sitzungen, die konfliktierende Sperren für dieselben Ressourcen benötigen, warten, bis die Abfrage abgeschlossen ist. Um dieses Problem zu beheben, suchen Sie nach Möglichkeiten, um die Abfrage zu optimieren, z. B. das Hinzufügen von Indizes oder das Umschreiben der Abfrage, um weniger Zeilen zu berühren.

Eine Sitzung im Ruhezustand mit einer Transaktion ohne Commit. Eine Sitzung führt eine Anweisung in einer expliziten Transaktion aus, beendet dann die Ausführung, veranlasst aber weder Commits noch Rollbacks. Die Sperren der Transaktion bleiben auf unbestimmte Zeit bestehen. Dieses Problem tritt häufig auf, wenn in einer Anwendung ein Abfragetimeout oder -abbruch auftritt, dies aber kein entsprechendes ROLLBACK auslöst. Verwenden Sie diese Funktion SET XACT_ABORT ON , damit Laufzeitfehler die Transaktion automatisch zurücksetzen.

Eine Sitzung, die nicht alle Ergebniszeilen abgerufen hat. Eine Anwendung sendet eine Abfrage, ruft aber nicht alle Zeilen aus dem Resultset ab. Sperren von Zeilen, die noch nicht abgerufen wurden, bleiben erhalten. Stellen Sie sicher, dass Ihre Anwendung alle Ergebniszeilen vollständig abruft.

Eine Sitzung befindet sich im Rollback. Eine Abfrage, die (mit KILL oder durch einen Deadlock) beendet wurde, führt ein Rollback der Änderungen durch. Ein Rollback kann bei großen Änderungen viel Zeit in Anspruch nehmen, und die Sitzung hält die Sperren während des gesamten Prozesses. Warten Sie, bis der Rollback abgeschlossen ist, und vermeiden Sie große Batch-Änderungen während der Stoßzeiten.

Eine verwaiste Verbindung. Eine Clientanwendung stürzt ab oder eine Arbeitsstation wird neu gestartet, ohne die Datenbankverbindung sauber zu schließen. Der Server erkennt die Trennung nicht sofort, sodass die Sitzung weiterhin Sperren hält. Beenden Sie die verwaiste Sitzung mit KILL <session_id>;.

Hinweis

Zwei dieser Szenarien werden standardmäßig in der Azure SQL-Datenbank abgemildert. RCSI reduziert die Auswirkungen von nicht abgerufenen Ergebniszeilen, weil SELECT-Abfragen unter Zeilenversionsverwaltung keine geteilten Sperren abrufen und nicht abgerufene Zeilen deshalb Schreibvorgänge nicht blockieren. Die Beschleunigte Datenbankwiederherstellung (ADR) macht langwierige Rollbacks selten, da Änderungen unabhängig von der Transaktionsgröße nahezu sofort rückgängig gemacht werden können. Die verbleibenden drei Szenarien (zeitintensive Abfragen, Sitzungen im Ruhezustand mit Transaktionen ohne Commit und verwaiste Verbindungen) bleiben relevant, weil sie exklusive Schreibsperren umfassen, die RCSI und ADR nicht vorzeitig freigeben können.

Auflösen der aktiven Blockierung

Wenn eine aktive Blockierung erfolgt:

1. Identifizieren Sie den Kopfblocker mithilfe der zuvor gezeigten DMV-Abfrage.
2. Bestimmen Sie, ob die Transaktion der Blockierungssitzung eigenständig abgeschlossen werden kann oder ob sie auf externe Eingaben wartet.
3. Wenn es sich bei der Blockierungssitzung um eine verwaiste oder abgebrochene Verbindung handelt, beenden Sie sie mit KILL <session_id>;
4. Überprüfen Sie den Ausführungsplan der Blockierungsabfrage auf Optimierungsmöglichkeiten wie fehlende Indizes.

Um zu verhindern, dass eine Blockierung erneut auftritt, halten Sie die Transaktionen kurz. Führen Sie nur die unbedingt erforderlichen Anweisungen innerhalb einer Transaktion aus, und schließen Sie diese unmittelbar mit einem Commit ab. Verwenden Sie SET XACT_ABORT ON im Anwendungscode, damit bei Laufzeitfehlern automatisch ein Rollback der gesamten Transaktion erfolgt. Dies verhindert, dass nur halb abgeschlossene Transaktionen ihre Sperren aufrechterhalten. Verschieben aller benutzerorientierten Logik außerhalb von Transaktionsgrenzen.

Deadlocks

Ein Deadlock tritt auf, wenn zwei oder mehr Transaktionen eine Zirkelabhängigkeit bilden. Jede Transaktion setzt eine Sperre, die von der anderen Transaktion benötigt wird, sodass keine Transaktion die Ausführung fortsetzen kann. Hier ist ein konkretes Beispiel:

1. Transaktion A aktualisiert Zeile 1 und fordert eine exklusive Sperre an.
2. Transaktion B aktualisiert Zeile 2 und erwirbt eine exklusive Sperre.
3. Transaktion A versucht, Zeile 2 zu aktualisieren und wird durch Transaktion B blockiert.
4. Transaktion B versucht, Zeile 1 zu aktualisieren und wird durch Transaktion A blockiert.

Keine Transaktion kann abgeschlossen werden. Der Deadlock-Monitor des Datenbankmoduls sucht regelmäßig nach diesen Zyklen mit einem Standardintervall von fünf Sekunden, das bei häufigen Deadlocks auf so niedrig wie 100 Millisekunden fällt. Wenn ein Zyklus erkannt wird, wählt er die Transaktion aus, deren Rollback als Opfer am kostengünstigsten ist, setzt sie zurück und gibt Fehler 1205 an die Anwendung zurück. Mit diesem Rollback kann die andere Transaktion abgeschlossen werden.

Erfassen von Deadlock-Informationen

In SQL Server und azure SQL Managed Instance erfasst die system_health Sitzung für erweiterte Ereignisse standardmäßig Deadlock-Ereignisse. Sie können den Deadlock-Bericht aus dem Ringpuffer mithilfe von sys.dm_xe_session_targets und sys.dm_xe_sessions abfragen.

In der Azure SQL-Datenbank unterscheidet sich der Ansatz. Sie erstellen eine benutzerdefinierte „Erweiterte Ereignisse“-Sitzung, die das Ereignis sqlserver.database_xml_deadlock_report erfasst, und fragen sie mit den datenbankweiten DMVs sys.dm_xe_database_sessions und sys.dm_xe_database_session_targets ab. Im folgenden Beispiel wird eine Deadlock-Erfassungssitzung erstellt und deren Ringpuffer abgefragt:

-- Create and start the session
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

-- Query deadlock events from the ring buffer
DECLARE @tracename sysname = N'deadlocks';

SELECT
    d.value('(/event/@timestamp)[1]', 'datetime2') AS deadlock_time,
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml
FROM (
    SELECT CAST(target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
    INNER 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'
) AS ring_buffer
CROSS APPLY rb.nodes(
    '/RingBufferTarget/event[@name=''database_xml_deadlock_report'']'
) AS xevent(d)
ORDER BY deadlock_time DESC;

Das Deadlock-Diagramm enthält drei Abschnitte. Die Opferliste gibt an, welche Transaktion beendet wurde. Die Prozessliste zeigt jeden beteiligten Prozess, einschließlich Abfragetext, Isolationsstufe und Sperrmodus. Die Ressourcenliste zeigt die gesperrten Ressourcen und welcher Prozess jede einzelne besitzt und auf jede wartet.

In der Azure SQL-Datenbank können Sie auch Deadlock-Warnungen über das Azure-Portal konfigurieren, um Benachrichtigungen zu empfangen, wenn Deadlocks auftreten.

Vermeidung von Deadlocks

Sie können nicht alle Deadlocks beseitigen, aber Sie können die Häufigkeit der Sperre erheblich reduzieren:

• Greifen Sie auf Objekte in einer konsistenten Reihenfolge zu: Wenn alle Transaktionen Tabelle A vor Tabelle B ändern, können keine kreisförmigen Abhängigkeiten gebildet werden. Standardisieren Sie Zugriffsmuster über gespeicherte Prozeduren.
• Transaktionen kurz gestalten: Kürzere Transaktionen halten Sperren kürzere Zeit. Das verkleinert das Fenster, in dem es zu zirkulären Abhängigkeiten kommen kann.
• Isolationsebenen für Zeilenversionierung verwenden: RCSI eliminiert freigegebene Sperren für Lesevorgänge und beseitigt dadurch eine typische Ursache von Deadlockzyklen. Durch die optimierte Sperrung in der Azure SQL-Datenbank wird die Deadlock-Wahrscheinlichkeit weiter reduziert.
• Geeignete Indizes hinzufügen: Wenn Abfragen viele Zeilen scannen, setzen sie für eine große Datenmenge Sperren. Durch das Hinzufügen von Indizes, die den Scan auf weniger Zeilen einschränken, verringert sich die Anzahl der Sperrkonflikte.
• Planerzwingung mit dem Abfragespeicher verwenden: Wenn eine Planänderung dazu führte, dass eine Abfrage mehr Zeilen scannen und mehr Sperren setzen musste, kann das Erzwingen des vorherigen Plans Deadlocks reduzieren, während das Problem untersucht wird.

Deadlocks im Anwendungscode beheben

Anwendungen sollten immer Wiederholungslogik für Deadlock-Fehler enthalten. Wenn eine Transaktion als Deadlock-Opfer ausgewählt wird, setzt das Datenbankmodul sie zurück und gibt Fehler 1205 zurück. Ihre Anwendung sollte diesen Fehler abfangen, kurz anhalten und die Transaktion erneut übermitteln.

BEGIN TRY
    BEGIN TRANSACTION;
    -- Your data modification statements
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1205
    BEGIN
        ROLLBACK TRANSACTION;
        WAITFOR DELAY '00:00:01';  -- Brief pause before retry
        -- Retry logic here
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        THROW;
    END
END CATCH;

Tipp

Mit einem zufälligen Wert für das Intervall zwischen wiederholten Versuchen können Sie verhindern, dass sich zwei Transaktionen ununterbrochen gegenseitig blockieren. Ein gängiges Muster besteht darin, zwischen einer und drei Sekunden mit einer zufälligen Komponente zu warten.

Wichtige Erkenntnisse

Das Blockieren ist normal. Lange Blockierungen beeinträchtigen aber die Benutzer. Ermitteln Sie deshalb mit sys.dm_exec_requests den Verursacher der Blockierung höchster Ebene, um zu analysieren, was geschieht. Typische Szenarien sind zeitintensive Abfragen, Sitzungen im Ruhezustand mit Transaktionen ohne Commit und verwaiste Verbindungen. Sie können sämtlich durch kurze Transaktionen, die Verwendung von SET XACT_ABORT ON und ordnungsgemäße Verwaltung von Verbindungen und Resultsets deutlich reduziert werden. Deadlocks bilden sich, wenn Transaktionen Zirkelsperrabhängigkeiten erstellen, und das Datenbankmodul löst sie automatisch auf, indem die am wenigsten teure Transaktion beendet und Fehler 1205 zurückgegeben wird. Sie reduzieren die Häufigkeit von Deadlocks, indem Sie auf Objekte in einer konsistenten Reihenfolge zugreifen, Transaktionen kurz halten, Zeilenversionsisolationsebenen verwenden und entsprechende Indexe hinzufügen. Der Anwendungscode sollte immer wiederholungslogik für Fehler 1205 enthalten, damit er automatisch wiederhergestellt werden kann, wenn er als Deadlock-Opfer ausgewählt wird.