Freigeben über

Verstehen und Beheben von Problemen durch Blockierungen in SQL Server

  • Artikel

Gilt für: SQL Server (alle unterstützten Versionen), Azure SQL verwaltete Instanz

Ursprüngliche KB-Nummer: 224453

Ziel

Der Artikel beschreibt die Blockierung in SQL Server und veranschaulicht, wie Sie die Blockierung beheben und beheben können.

In diesem Artikel bezieht sich der Begriff „Verbindung“ auf eine einzelne angemeldete Sitzung der Datenbank. Jede Verbindung wird als Sitzungs-ID (SPID) bzw. in vielen DMVs (Dynamic Management Views, dynamische Verwaltungssichten) als „session_id“ angezeigt. Jeder dieser SPIDs wird häufig als Prozess bezeichnet, obwohl es sich nicht um einen separaten Prozesskontext im üblichen Sinne handelt. Stattdessen besteht jede SPID aus den Serverressourcen und Datenstrukturen, die zum Verarbeiten der Anforderungen einer einzelnen Verbindung von einem bestimmten Client benötigt werden. Ein einzelne Clientanwendung kann über eine oder mehrere Verbindungen verfügen. Aus Sicht von SQL Server gibt es keinen Unterschied zwischen mehreren Verbindungen von einer einzelnen Clientanwendung auf einem einzelnen Clientcomputer und mehreren Verbindungen von mehreren Clientanwendungen oder mehreren Clientcomputern; sie sind atom. Eine Verbindung kann eine andere Verbindung blockieren, unabhängig vom Quellclient.

Notiz

Dieser Artikel konzentriert sich auf SQL Server-Instanzen, einschließlich Azure SQL verwaltete Instanz s. Informationen zur Problembehandlung beim Blockieren in Azure SQL-Datenbank finden Sie unter "Grundlegendes und Beheben von Azure SQL-Datenbank Blockieren von Problemen".

Was blockiert

Blockierungen sind ein unvermeidbares und entwurfsbedingtes Merkmal von Managementsystemen für relationale Datenbanken (Relational Database Management Systems, RDBMSs) mit sperrbasierter Parallelität. Wie an anderer Stelle bereits erwähnt, tritt in SQL Server eine Blockierung auf, wenn eine Sitzung eine bestimmte Ressource gesperrt hat und eine zweite SPID versucht, für dieselbe Ressource einen in Konflikt stehenden Sperrtyp anzufordern. In der Regel ist der Zeitrahmen, in dem die erste SPID die Ressource sperrt, sehr klein. Wenn die Sitzung, die Besitzer der Ressource ist, die Sperre aufhebt, kann die zweite Verbindung die Ressource ihrerseits sperren und die Verarbeitung fortsetzen. Das Blockieren, wie hier beschrieben, ist ein normales Verhalten und kann während eines Tages viele Male auftreten, ohne spürbare Auswirkungen auf die Systemleistung zu haben.

Dauer und Transaktionskontext einer Abfrage bestimmen, wie lange die Sperren aufrechterhalten werden. Daraus ergeben sich die Auswirkungen auf andere Abfragen. Wenn die Abfrage nicht innerhalb einer Transaktion ausgeführt wird (und keine Sperrhinweise verwendet werden), werden die Sperren für SELECT-Anweisungen nur zu dem Zeitpunkt aufbewahrt, zu dem sie tatsächlich gelesen wird, nicht während der Abfrage. Bei INSERT-, UPDATE- und DELETE-Anweisungen bleiben die Sperren während der gesamten Abfrage erhalten. Dies geschieht aus Gründen der Datenkonsistenz und um bei Bedarf einen Rollback der Abfrage zu ermöglichen.

Bei Abfragen, die innerhalb einer Transaktion ausgeführt werden, wird die Sperrdauer durch den Abfragetyp, die Isolationsstufe der Transaktion und das Vorhandensein von Sperrhinweisen in der Abfrage bestimmt. Beschreibungen von Sperren, Sperrhinweisen und Isolationsstufen für Transaktionen finden Sie in den folgenden Artikeln:

Wenn die Anzahl der Sperr- und Blockierungsvorgänge sich dauerhaft auf einem so hohen Niveau bewegt, dass die Systemleistung erheblich beeinträchtigt wird, ist dies auf eine der folgenden Ursachen zurückzuführen:

  • Eine SPID hält Sperren für eine Reihe von Ressourcen für einen längeren Zeitraum, bevor sie freigegeben werden. Diese Art von Blockierung löst sich mit der Zeit von selbst, kann aber zu Leistungsbeeinträchtigungen führen.

  • Eine SPID enthält Sperren für eine Reihe von Ressourcen und gibt sie niemals frei. Diese Art von Blockierung löst sich nicht von selbst und verhindert für unbegrenzte Zeit den Zugriff auf die betroffenen Ressourcen.

Im ersten Szenario kann sich die Situation sehr schnell ändern, weil verschiedene SPIDs im Lauf der Zeit Blockierungen verschiedener Ressourcen verursachen. Dies erschwert die Problemlösung. In solchen Situationen ist die Problembehandlung mit SQL Server Management Studio schwierig, da sich ein Problem kaum auf einzelne Abfragen eingrenzen lässt. Im Gegensatz dazu führt die zweite Situation zu einem konsistenten Zustand, der einfacher zu diagnostizieren ist.

Anwendungen und Blockierungen

Bei Blockierproblemen besteht die Tendenz, sich zur Problemlösung auf serverseitige Optimierungen und Plattformprobleme zu konzentrieren. Eine reine Fokussierung auf die Datenbank führt aber möglicherweise nicht zu einer Lösung und kann Zeit und Energie kosten, die besser in die Untersuchung der Clientanwendung und der von ihr gesendeten Abfragen investiert werden sollte. Unabhängig davon, welches Maß an Transparenz die Anwendung hinsichtlich der ausgeführten Datenbankaufrufe bietet: Bei einem Blockierproblem müssen häufig sowohl die genauen von der Anwendung gesendeten SQL-Anweisungen als auch das genaue Anwendungsverhalten in Bezug auf den Abbruch von Abfragen, die Verbindungsverwaltung, den Abruf aller Ergebniszeilen usw. untersucht werden. Wenn das Entwicklungstool keine explizite Kontrolle über die Verbindungsverwaltung, Abfrageabbruch, Abfragetimeout, Ergebnisabrufe usw. erlaubt, können Blockierungsprobleme möglicherweise nicht aufgelöst werden. Dieses Potenzial sollte genau untersucht werden, bevor ein Anwendungsentwicklungstool für SQL Server ausgewählt wird, insbesondere für leistungsempfindliche OLTP-Umgebungen.

Achten Sie während der Entwurfs- und Erstellungsphase von Datenbank und Anwendung auf die Datenbankleistung. Insbesondere sollten Ressourcenverbrauch, Isolationsstufe und Länge des Transaktionspfads für jede Abfrage ausgewertet werden. Jede Abfrage und jede Transaktion sollte so schlank wie möglich sein. Die Verbindungsverwaltung muss sorgfältig geplant und eingerichtet werden. Andernfalls kann es passieren, dass eine Anwendung bei geringen Benutzerzahlen anscheinend eine akzeptable Leistung zeigt, die Leistung aber bei steigenden Zahlen massiv sinkt.

Mit dem richtigen Anwendungs- und Abfrageentwurf kann SQL Server viele Tausende gleichzeitiger Benutzer auf einem einzelnen Server mit geringem Blockieren unterstützen.

Problembehandlung bei Blockierungen

Unabhängig von der Blockierungssituation ist die Methode für die Problembehandlung dieselbe. Diese logischen Trennungen bestimmen die verbleibenden Abschnitte dieses Artikels. Das Konzept besteht darin, den Anfang der Blockierung zu finden und zu ermitteln, was die Abfrage tut und warum sie blockiert wird. Sobald die problematische Abfrage identifiziert wurde (d. h., was Sperren für den längeren Zeitraum hält), besteht der nächste Schritt darin, zu analysieren und zu bestimmen, warum die Blockierung stattfindet. Nachdem wir verstanden haben, warum, können wir dann Änderungen vornehmen, indem wir die Abfrage und die Transaktion neu gestalten.

Schritte bei der Problembehandlung:

  1. Identifizieren der wesentlichen blockierenden Sitzung (Anfang der Blockierung)

  2. Suchen der Abfrage bzw. Transaktion, die die Blockierung verursacht (also Sperren über einen längeren Zeitraum aufrechterhält)

  3. Analysieren der Ursache für die längere Blockierung

  4. Lösen des Blockierproblems durch erneutes Entwerfen der Abfrage und Transaktion

Im Folgenden wird erläutert, wie Sie die wesentliche blockierende Sitzung mithilfe einer geeigneten Datenerfassung genau lokalisieren.

Sammeln von Informationen zur Blockierung

Um der Schwierigkeit bei der Problembehandlung von Blockierungsproblemen entgegenzuwirken, kann ein Datenbankadministrator SQL-Skripts verwenden, die den Zustand des Sperrens und Blockierens auf SQL Server ständig überwachen. Um diese Daten zu sammeln, gibt es zwei kostenlose Methoden.

Die erste besteht darin, dynamische Verwaltungsobjekte (Dynamic Management Objects, DMOs) abzufragen und die Ergebnisse für einen Vergleich im Zeitverlauf zu speichern. Einige der in diesem Artikel beschriebenen Objekte sind dynamische Verwaltungssichten (Dynamic Management Views, DMVs), einige sind dynamische Verwaltungsfunktionen (Dynamic Management Functions, DMFs).

Die zweite besteht darin, erweiterte Ereignisse(XEvents) oder SQL Profiler-Ablaufverfolgungen zu verwenden, um zu erfassen, was ausgeführt wird. Da SQL Trace und SQL Server Profiler veraltet sind, konzentriert sich dieses Handbuch zur Problembehandlung auf XEvents.

Sammeln von Informationen aus DMVs

Durch Verweisen auf DMVs lassen sich die SPID am Anfang der blockierenden Kette und die SQL-Anweisung identifizieren. Suchen Sie nach SPIDs, die blockiert werden. Wenn eine SPID durch eine andere SPID blockiert wird, untersuchen Sie die SPID, die Besitzer der Ressource ist (die blockierende SPID). Wird diese Besitzer-SPID ebenfalls blockiert? Durchlaufen Sie die Kette, um den Anfang der Blockierung zu finden, und untersuchen Sie, warum die Sperre beibehalten wird.

Dazu können Sie eine der folgenden Methoden verwenden:

  • Klicken Sie in SQL Server Management Studio (SSMS) Objekt-Explorer mit der rechten Maustaste auf das Serverobjekt der obersten Ebene, erweitern Sie Berichte, erweitern Sie Standardberichte, und wählen Sie dann "Aktivität – Alle blockierenden Transaktionen" aus. In diesem Bericht werden aktuelle Transaktionen am Anfang einer Blockierungskette angezeigt. Wenn Sie die Transaktion erweitern, zeigt der Bericht die Transaktionen an, die von der Kopftransaktion blockiert werden. In diesem Bericht werden auch die Blocking SQL-Anweisung und die Blocked SQL-Anweisung angezeigt.

  • Öffnen Sie den Aktivitätsmonitor in SSMS, und verweisen Sie auf die Spalte "Blockiert von". Weitere Informationen zum Aktivitätsmonitor finden Sie hier.

Detailliertere abfragebasierte Methoden stehen auch mithilfe von DMVs zur Verfügung:

  • Die sp_who Befehle und sp_who2 Befehle sind ältere Befehle, um alle aktuellen Sitzungen anzuzeigen. Die DMV sys.dm_exec_sessions gibt mehr Daten in einem Resultset zurück, das sich einfacher abfragen und filtern lässt. sys.dm_exec_sessions ist auch ein wesentlicher Bestandteil anderer Abfragen.

  • Wenn Sie bereits eine bestimmte Sitzung identifiziert haben, können Sie mithilfe von DBCC INPUTBUFFER(<session_id>) die letzte Anweisung suchen, die von dieser Sitzung übermittelt wurde. Ähnliche Ergebnisse können durch die DMF sys.dm_exec_input_buffer in einem Resultset zurückgegeben werden, das sich durch Angabe von „session_id“ und „request_id“ einfacher abfragen und filtern lässt. Mit folgender Anweisung können Sie beispielsweise die letzte von „session_id 66“ und „request_id 0“ übermittelte Abfrage zurückgeben:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

  • Verweisen Sie auf die sys.dm_exec_requests Spalte und verweisen Sie darauf blocking_session_id . Wenn blocking_session_id = 0 wird eine Sitzung nicht blockiert. Während sys.dm_exec_requests nur aktuell ausgeführte Anforderungen auflistet, wird in sys.dm_exec_sessions keine Verbindung (aktiv oder nicht) aufgelistet. In der nächsten Abfrage können wir auf dieser allgemeinen Verknüpfung zwischen sys.dm_exec_requests und sys.dm_exec_sessions aufbauen. Beachten Sie, dass sie von sys.dm_exec_requestsdieser zurückgegeben wird, muss die Abfrage aktiv mit SQL Server ausgeführt werden.

  • Führen Sie diese Beispielabfrage aus, um mithilfe der DMVs sys.dm_exec_sql_text oder sys.dm_exec_input_buffer die aktiv ausgeführten Abfragen und ihren zugehörigen aktuellen SQL-Batchtext oder Eingabepuffertext zu finden. Wenn die von der text Spalte sys.dm_exec_sql_text von NULL zurückgegebenen Daten null sind, wird die Abfrage zurzeit nicht ausgeführt. In diesem Fall enthält die Spalte von sys.dm_exec_input_buffer der event_info letzten Befehlszeichenfolge, die an das SQL-Modul übergeben wird. Mit dieser Abfrage können darüber hinaus Sitzungen identifiziert werden, die andere Sitzungen blockieren. Dabei wird eine nach Sitzungs-ID (session_id) aufgeschlüsselte Liste mit blockierten Sitzungs-IDs (session_ids) zurückgegeben.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Führen Sie diese vom Microsoft-Support bereitgestellte ausführlichere Beispielabfrage aus, um den Kopfteil einer blockierenden Kette mit mehreren Sitzungen zu identifizieren, einschließlich des Abfragetexts der Sitzungen, die an einer blockierenden Kette beteiligt sind.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
  • Verweisen Sie sys.dm_os_waiting_tasks , die sich auf der Thread-/Aufgabenebene von SQL Server befindet. Dadurch werden Informationen darüber zurückgegeben, welche SQL-wait_type die Anforderung derzeit auftritt. Ebenso wie sys.dm_exec_requests gibt sys.dm_os_waiting_tasks nur aktive Anforderungen zurück.

Hinweis

Weitere Informationen zu den Wartetypen, einschließlich aggregierter Wartestatistiken im Zeitverlauf finden Sie in der DMV sys.dm_db_wait_stats.

  • Verwenden Sie die DMV sys.dm_tran_locks, um detaillierte Informationen zu den Sperren zu erhalten, die von Abfragen platziert wurden. Dieser DMV kann große Datenmengen in einer SQL Server-Produktionsinstanz zurückgeben und ist nützlich, um zu diagnostizieren, welche Sperren derzeit aufbewahrt werden.

Aufgrund des INNER JOIN-Vorgangs in sys.dm_os_waiting_tasks, beschränkt die folgende Abfrage die Ausgabe von sys.dm_tran_locks auf aktuell blockierte Anforderungen, ihren Wartestatus und ihre Sperren:

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

Mit DMVs werden durch Speichern der Abfrageergebnisse über einen längeren Zeitraum Datenpunkte bereitgestellt, mit denen Sie Blockierungen in einem bestimmten Zeitintervall überprüfen können, um persistente Blockierungen oder Trends zu ermitteln. Das Go-To-Tool für CSS zur Behebung solcher Probleme verwendet den PSSDiag-Datensammler. Dieses Tool verwendet die "SQL Server Perf Stats", um Resultsets aus DMVs zu sammeln, auf die im Laufe der Zeit verwiesen wird. Da sich dieses Tool ständig weiterentwickelt, überprüfen Sie die neueste öffentliche Version von DiagManager auf GitHub.

Sammeln von Informationen aus erweiterten Ereignissen

Zusätzlich zu den oben genannten Informationen ist es häufig erforderlich, eine Ablaufverfolgung der Aktivitäten auf dem Server zu erfassen, um ein Blockierungsproblem in SQL Server gründlich zu untersuchen. Ein Beispiel: Wenn eine Sitzung innerhalb einer Transaktion mehrere Anweisungen ausführt, wird nur die letzte übermittelte Anweisung dargestellt. Möglicherweise ist aber eine der vorherigen Anweisungen der Grund dafür, dass immer noch Sperren bestehen. Mit einer Ablaufverfolgung können Sie alle durch eine Sitzung ausgeführten Befehle innerhalb der aktuellen Transaktion anzeigen.

Es gibt zwei Möglichkeiten zum Erfassen von Ablaufverfolgungen in SQL Server; Erweiterte Ereignisse (XEvents) und Profiler-Ablaufverfolgungen. SQL-Ablaufverfolgungen mit dem SQL Server Profiler sind jedoch veraltet. XEvents sind die neuere, überlegene Ablaufverfolgungsplattform, die mehr Vielseitigkeit und weniger Auswirkungen auf das beobachtete System ermöglicht und seine Schnittstelle in SSMS integriert ist.

Es sind vorgefertigte erweiterte Ereignissitzungen bereit, um in SSMS zu beginnen, die in Objekt-Explorer unter dem Menü für XEvent Profiler aufgeführt sind. Weitere Informationen finden Sie unter XEvent Profiler. Sie können auch eigene benutzerdefinierte erweiterte Ereignissitzungen in SSMS erstellen, siehe Assistent für erweiterte Ereignisse für neue Sitzungen. Zur Problembehandlung bei Blockierungsproblemen erfassen wir in der Regel Folgendes:

  • Kategoriefehler:
    • Aufmerksamkeit
    • Blocked_process_report**
    • Error_reported (Kanaladministrator)
    • Exchange_spill
    • Execution_warning

**Um den Schwellenwert und die Häufigkeit zu konfigurieren, mit dem blockierte Prozessberichte generiert werden, verwenden Sie den Befehl sp_configure, um die Option für blockierte Prozessschwellenwerte zu konfigurieren, die in Sekunden festgelegt werden können. Standardmäßig werden für blockierte Prozesse keine Berichte erstellt.

  • Kategorie „Warnungen“:

    • Hash_warning
    • Missing_column_statistics
    • Missing_join_predicate
    • Sort_warning

  • Kategorie „Ausführung“:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Kategoriesperre

    • Lock_deadlock

  • Kategoriesitzung

    • Existing_connection
    • Anmelden
    • Logout

Identifizieren und Beheben von häufigen Blockierungsszenarien

Anhand der oben genannten Informationen können Sie die Ursache der meisten Blockierprobleme ermitteln. Im Rest dieses Artikels wird erläutert, wie Sie diese Informationen zum Identifizieren und Beheben einiger häufiger Blockierungsszenarien verwenden. In dieser Diskussion wird davon ausgegangen, dass Sie die Blockierungsskripts (referenziert früher) verwendet haben, um Informationen zu den blockierten SPIDs zu erfassen und Anwendungsaktivitäten mithilfe einer XEvent-Sitzung erfasst zu haben.

Analysieren von Daten zur Blockierung

  • Untersuchen Sie die Ausgabe der DMVs sys.dm_exec_requests und sys.dm_exec_sessions, und verwenden Sie dabei blocking_these und session_id, um den Anfang der blockierenden Ketten zu finden. So lässt sich sehr deutlich bestimmen, welche Anforderungen blockieren und welche blockiert werden. Untersuchen Sie die blockierten und blockierenden Sitzungen genauer. Gibt es gemeinsame oder Stammelemente in der blockierenden Kette? Wahrscheinlich verwenden diese gemeinsam eine Tabelle, und mindestens eine der an einer blockierenden Kette beteiligten Sitzungen führt einen Schreibvorgang aus.

  • Suchen Sie in der Ausgabe der DMVs sys.dm_exec_requests und sys.dm_exec_sessions nach Informationen zu den SPIDs am Anfang der blockierenden Kette. Suchen Sie nach den folgenden Spalten:

    • sys.dm_exec_requests.status

      Diese Spalte zeigt den Status einer bestimmten Anforderung an. In der Regel weist der Ruhestatus darauf hin, dass die SPID die Ausführung beendet hat und darauf wartet, dass die Anwendung eine weitere Abfrage oder einen weiteren Batch sendet. Der Status „Ausführbar“ oder „Wird ausgeführt“ weist darauf hin, dass die SPID zurzeit eine Abfrage ausführt. In der folgenden Tabelle finden Sie kurze Erläuterungen der verschiedenen Statuswerte.

      Status Bedeutung
      Hintergrund Die SPID führt einen Hintergrundtask aus, z. B. eine Deadlockerkennung, einen Protokollwriter oder einen Prüfpunkt.
      Ruhezustand Die SPID wird zurzeit nicht ausgeführt. Dies weist in der Regel darauf hin, dass die SPID auf einen Befehl der Anwendung wartet.
      Wird ausgeführt Die SPID wird derzeit in einem Planer ausgeführt.
      Ausführbar Die SPID befindet sich in der ausführbaren Warteschlange eines Planers und wartet auf die Zuteilung von Ausführungszeit.
      Ausgesetzt Die SPID wartet auf eine Ressource, z. B. eine Sperre oder einen Latch.

    • sys.dm_exec_sessions.open_transaction_count

      In dieser Spalte wird die Anzahl der geöffneten Transaktionen in dieser Sitzung angegeben. Wenn der Wert größer als 0 ist, befindet sich die SPID in einer offenen Transaktion und hält möglicherweise Sperren aufrecht, die von einer beliebigen Anweisung innerhalb der Transaktion abgerufen wurden. Die geöffnete Transaktion könnte entweder durch eine aktuell aktive Anweisung oder durch eine Anweisungsanforderung erstellt worden sein, die in der Vergangenheit ausgeführt wurde und nicht mehr aktiv ist.

    • sys.dm_exec_requests.open_transaction_count

      In ähnlicher Weise informiert Sie in dieser Spalte über die Anzahl der geöffneten Transaktionen in dieser Anforderung. Wenn dieser Wert größer als 0 ist, befindet sich die SPID in einer offenen Transaktion und kann Sperren halten, die von einer aktiven Anweisung innerhalb der Transaktion erworben wurden. Im Gegensatz dazu sys.dm_exec_sessions.open_transaction_countzeigt diese Spalte 0 an, wenn keine aktive Anforderung vorhanden ist.

    • sys.dm_exec_requests.wait_type, wait_time und last_wait_type

      Wenn sys.dm_exec_requests.wait_type NULL lautet, wartet die Anforderung derzeit auf nichts, und der Wert last_wait_type gibt den letzten von der Anforderung gefundenen wait_type an. Weitere Informationen zu sys.dm_os_wait_stats und eine Beschreibung der gängigsten Wartetypen finden Sie unter sys.dm_os_wait_stats. Mit dem wait_time-Wert kann ermittelt werden, ob die Anforderung fortgesetzt wird. Wenn eine Abfrage der sys.dm_exec_requests-Tabelle einen Wert in der Spalte wait_time zurückgibt, der niedriger ist als der wait_time-Wert aus einer vorherigen Abfrage von sys.dm_exec_requests, weist dies darauf hin, dass die vorherige Sperre abgerufen und freigegeben wurde und jetzt auf eine neue Sperre gewartet wird (vorausgesetzt, dass wait_time nicht 0 ist). Dies lässt sich durch einen Vergleich der wait_resource zwischen sys.dm_exec_requests-Ausgaben ermitteln: Dies zeigt die Ressource an, auf die die Anforderung wartet.

    • sys.dm_exec_requests.wait_resource

      Diese Spalte gibt die Ressource an, auf die eine blockierte Anforderung wartet. In der folgenden Tabelle sind gängige Formate für wait_resource sowie ihre Bedeutung aufgeführt:

      Resource Format Beispiel Erklärung
      Tabelle DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 In diesem Fall ist die Datenbank-ID 5 die Pubs-Beispieldatenbank und object_id 261575970 die Titeltabelle und 1 ist der gruppierte Index.
      Seite DatabaseID:FileID:PageID PAGE: 5:1:104 In diesem Fall ist die Datenbank-ID 5 „pubs“, die Datei-ID 1 ist die primäre Datendatei, und Seite 104 ist eine Seite, die zur Titeltabelle gehört. Zum Identifizieren der „object_id“, zu der die Seite gehört, verwenden Sie die DMF sys.dm_db_page_info, und übergeben Sie die DatabaseID, FileId und PageId aus wait_resource.
      Schlüssel DatabaseID:Hobt_id (Hashwert für Indexschlüssel) KEY: 5:72057594044284928 (3300a4f361aa) In diesem Fall ist die Datenbank-ID 5 „pubs“, und die Hobt_ID 72057594044284928 entspricht der „index_id“ 2 für die „object_id“ 261575970 (Titeltabelle). Verwenden Sie die sys.partitions Katalogansicht, um eine hobt_id bestimmte und eine bestimmte index_id Ansicht object_idzuzuordnen. Es gibt keine Möglichkeit, den Indexschlüsselhash in einen bestimmten Schlüsselwert aufzulösen.
      Zeile DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 In diesem Fall ist die Datenbank-ID 5 „pubs“, die Datei-ID 1 ist die primäre Datendatei, Seite 104 ist eine Seite, die zur Titeltabelle gehört, und Slot 3 gibt die Position der Zeile auf der Seite an.
      Compile DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 In diesem Fall ist die Datenbank-ID 5 „pubs“, die Datei-ID 1 ist die primäre Datendatei, Seite 104 ist eine Seite, die zur Titeltabelle gehört, und Slot 3 gibt die Position der Zeile auf der Seite an.

    • sys.dm_tran_active_transactions: Die DMV sys.dm_tran_active_transactions enthält Daten zu offenen Transaktionen, die mit anderen DMVs verknüpft werden können, um ein vollständiges Bild der Transaktionen zu erhalten, die auf einen Commit oder Rollback warten. Verwenden Sie die folgende Abfrage, um Informationen zu offenen Transaktionen zurückzugeben, die mit anderen DMVs einschließlich sys.dm_tran_session_transactions verknüpft sind. Beachten Sie den aktuellen Zustand einer Transaktion, den Wert für transaction_begin_time sowie weitere situationsbezogene Daten, um zu bewerten, ob es sich möglicherweise um eine Blockierungsquelle handelt.

      SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

    • Andere Spalten

      Die verbleibenden Spalten in sys.dm_exec_sessions und sys.dm_exec_request können ebenfalls Erkenntnisse zur Ursache des Problems bieten. Ihr Nutzen variiert je nach Art des Problems. Sie können z. B. feststellen, ob das Problem nur von bestimmten Clients (hostname), in bestimmten Netzwerkbibliotheken (client_interface_name), bei dem der letzte von einem SPID übermittelte Batch enthalten sys.dm_exec_sessionswarlast_request_start_time, wie lange eine Anforderung in usw. ausgeführt start_time sys.dm_exec_requestswurde.

Häufige Blockierungsszenarien

In der folgenden Tabelle werden häufige Symptome ihren möglichen Ursachen zugeordnet.

Die Spalten wait_type, open_transaction_count und status beziehen sich auf Informationen, die von sys.dm_exec_request zurückgegeben werden. Andere Spalten können durch sys.dm_exec_sessions zurückgegeben werden. Die Spalte „Wird aufgelöst?“ gibt an, ob die Blockierung von selbst aufgelöst wird oder ob die Sitzung über den Befehl KILL beendet werden muss. Weitere Informationen finden Sie unter KILL (Transact-SQL).

Szenario Wait_type Open_Tran Status Löst das Problem? Andere Symptome
1 NOT NULL >= 0 runnable Ja, wenn die Abfrage beendet wird. In sys.dm_exec_sessions, reads, cpu_time und/oder memory_usage wachsen die Spalten im Lauf der Zeit an. Bei Abschluss ist die Dauer der Abfrage hoch.
2 NULL >0 sleeping Nein, aber die SPID kann beendet werden. In der erweiterten Ereignissitzung für diese SPID kann ein Aufmerksamkeitssignal angezeigt werden, das angibt, dass ein Abfragetimeout oder ein Abbruch aufgetreten ist.
3 NULL >= 0 runnable Nein Wird erst aufgelöst, wenn der Client alle Zeilen abgerufen oder die Verbindung geschlossen hat. Die SPID kann beendet werden, dies kann aber bis zu 30 Sekunden dauern. Wenn open_transaction_count = 0, und die SPID sperrt, während die Transaktionsisolationsstufe standard (READ COMMIT) ist, ist dies wahrscheinlich eine Ursache.
4 Varies >= 0 runnable Nein Wird erst aufgelöst, wenn der Client Abfragen abgebrochen oder Verbindungen geschlossen hat. Die SPIDs können beendet werden, dies kann aber bis zu 30 Sekunden dauern. Die Spalte hostname in sys.dm_exec_sessions für die SPID am Anfang einer blockierenden Kette ist die gleiche wie eine der SPIDs, die blockiert wird.
5 NULL >0 rollback Ja. Ein Aufmerksamkeitssignal kann in der Erweiterten Ereignissitzung für diese SPID angezeigt werden, die angibt, dass ein Abfragetimeout oder ein Abbruch aufgetreten ist, oder einfach eine Rollback-Anweisung ausgegeben wurde.
6 NULL >0 sleeping Irgendwann. Wenn Windows NT feststellt, dass die Sitzung nicht mehr aktiv ist, wird die Verbindung unterbrochen. Der last_request_start_time-Wert in sys.dm_exec_sessions liegt viel früher als der aktuelle Zeitpunkt.

Ausführliche Blockierungsszenarien

Szenario 1: Blockieren durch eine normalerweise ausgeführte Abfrage mit langer Ausführungszeit

In diesem Szenario wurde eine aktiv ausgeführte Abfrage gesperrt, und die Sperren werden nicht freigegeben (dies ist von der Transaktionsisolationsstufe betroffen). Daher warten andere Sitzungen auf die Sperren, bis sie losgelassen werden.

Lösung:

Die Lösung für dieses Blockierungsproblem besteht darin, nach Möglichkeiten zur Optimierung der Abfrage zu suchen. Diese Blockierungsklasse kann ein Leistungsproblem sein und erfordert, dass Sie es als solche behandeln. Informationen zur Problembehandlung einer bestimmten langsam ausgeführten Abfrage finden Sie unter Problembehandlung bei langsam ausgeführten Abfragen in SQL Server. Weitere Informationen finden Sie unter Überwachen und Optimieren der Leistung.

Berichte, die in SSMS von der Abfragespeicher (eingeführt in SQL Server 2016) integriert sind, sind auch ein sehr empfohlenes und wertvolles Tool zum Identifizieren der kostspieligsten Abfragen und suboptimalen Ausführungspläne.

Wenn Sie eine lange Ausgeführte Abfrage blockieren, die andere Benutzer blockiert und sie nicht optimiert werden kann, ziehen Sie in Betracht, sie von einer OLTP-Umgebung in ein dediziertes Berichtssystem zu verschieben. Sie können auch AlwaysOn-Verfügbarkeitsgruppen verwenden, um ein schreibgeschütztes Replikat der Datenbank zu synchronisieren.

Notiz

Das Blockieren während der Abfrageausführung kann durch die Abfrageeskalation verursacht werden, ein Szenario, wenn Zeilen- oder Seitensperren an Tabellensperren eskaliert werden. Microsoft SQL Server bestimmt dynamisch, wann eine Sperreskalation durchgeführt werden soll. Die einfachste und sicherste Möglichkeit, die Sperreskalation zu verhindern, besteht darin, Transaktionen kurz zu halten und den Sperrbedarf von teuren Abfragen zu verringern, damit die Schwellenwerte für die Sperreskalation nicht überschritten werden. Weitere Informationen zum Erkennen und Verhindern einer übermäßigen Sperreskalation finden Sie unter Beheben des Blockierungsproblems, das durch die Sperreskalation verursacht wird.

Szenario 2: Blockieren durch eine schlafende SPID mit einer nicht abgeschlossenen Transaktion

Diese Art von Blockierung kann häufig durch eine SPID identifiziert werden, die schlafen oder auf einen Befehl mit einer Transaktionsschachtelungsebene (@@TRANCOUNTopen_transaction_countvon sys.dm_exec_requests) größer als Null wartet. Diese Situation kann auftreten, wenn die Anwendung ein Abfragetimeout erlebt oder einen Abbruch ausgibt, ohne die erforderliche Anzahl von ROLLBACK- und/oder COMMIT-Anweisungen auszugeben. Wenn eine SPID ein Abfragetimeout oder einen Abbruch empfängt, beendet sie die aktuelle Abfrage und den Aktuellen Batch, führt aber kein automatisches Rollback oder Commit für die Transaktion durch. Die Anwendung ist dafür verantwortlich, da SQL Server nicht davon ausgehen kann, dass eine gesamte Transaktion aufgrund einer einzelnen Abfrage abgebrochen werden muss. Das Abfragetimeout oder -abbruch wird als ATTENTION-Signalereignis für die SPID in der erweiterten Ereignissitzung angezeigt.

Um eine explizite Transaktion ohne Commit zu veranschaulichen, geben Sie die folgende Abfrage aus:

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

Führen Sie dann die folgende Abfrage im selben Fenster aus:

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

Die Ausgabe der zweiten Abfrage gibt an, dass die Transaktionsanzahl eins ist. Alle in der Transaktion erworbenen Sperren bleiben erhalten, bis die Transaktion zugesichert oder zurückgesetzt wurde. Wenn Anwendungen Transaktionen explizit öffnen und committen, könnte ein Kommunikations- oder sonstiger Fehler dazu führen, dass die Sitzung und die zugehörige Transaktion in einem offenen Zustand verbleiben.

Verwenden Sie das auf sys.dm_tran_active_transactions basierende Skript weiter oben in diesem Artikel, um Transaktionen in der Instanz zu identifizieren, für die derzeit noch kein Commit ausgeführt wurde.

Lösungen:

  • Diese Klasse des Blockierungsproblems kann auch ein Leistungsproblem sein und erfordert, dass Sie es als solche behandeln. Wenn die Abfrageausführungszeit verringert werden kann, tritt das Abfragetimeout oder -abbruch möglicherweise nicht auf. Es ist wichtig, dass die Anwendung die Timeout- oder Abbruchszenarien verarbeiten kann, wenn sie auftreten, aber Sie können auch von der Untersuchung der Leistung der Abfrage profitieren.

  • Anwendungen müssen Transaktionsschachtelungsebenen ordnungsgemäß verwalten. Andernfalls können sie ein Blockierproblem verursachen, nachdem eine Abfrage auf diese Weise abgebrochen wurde. Beachten Sie Folgendes:

    • Führen Sie im Fehlerhandler der Clientanwendung nach jedem Fehler IF @@TRANCOUNT > 0 ROLLBACK TRAN aus, auch wenn die Clientanwendung nicht anzeigt, dass eine Transaktion offen ist. Die Überprüfung auf geöffnete Transaktionen ist erforderlich, da eine gespeicherte Prozedur, die während des Batches aufgerufen wird, eine Transaktion ohne Wissen der Clientanwendung gestartet haben könnte. Bestimmte Bedingungen – beispielsweise das Abbrechen einer Abfrage – verhindern die weitere Ausführung der Prozedur nach der aktuellen Anweisung. Daher wird dieser Rollbackcode in solchen Fällen auch dann nicht ausgeführt, wenn die Prozedur über eine Logik zum Überprüfen von IF @@ERROR <> 0 und Abbrechen der Transaktion verfügt.

    • Wenn verbindungspooling in einer Anwendung verwendet wird, die die Verbindung öffnet und einige Abfragen ausführt, bevor die Verbindung wieder mit dem Pool freigegeben wird, z. B. eine webbasierte Anwendung, kann das vorübergehende Deaktivieren des Verbindungspools dazu beitragen, das Problem zu verringern, bis die Clientanwendung geändert wird, um die Fehler entsprechend zu behandeln. Durch Deaktivieren des Verbindungspools führt die Freigabe der Verbindung zu einer physischen Verbindung der SQL Server-Verbindung, was dazu führt, dass der Server alle geöffneten Transaktionen zurücksetzt.

    • Wird für die Verbindung oder in gespeicherten Prozeduren verwendet SET XACT_ABORT ON , die Transaktionen beginnen und nach einem Fehler nicht bereinigen. Im Fall eines Laufzeitfehlers bricht diese Einstellung alle offenen Transaktionen ab und gibt die Steuerung an den Client zurück. Weitere Informationen finden Sie unter SET XACT_ABORT (Transact-SQL).

Notiz

Die Verbindung wird erst zurückgesetzt, wenn sie aus dem Verbindungspool wiederverwendet wird, sodass es möglich ist, dass ein Benutzer eine Transaktion öffnen und dann die Verbindung mit dem Verbindungspool freigeben kann, aber möglicherweise nicht für mehrere Sekunden wiederverwendet wird, während derer die Transaktion geöffnet bleibt. Wenn die Verbindung nicht wiederverwendet wird, wird die Transaktion abgebrochen, wenn die Verbindung ausläuft und aus dem Verbindungspool entfernt wird. Daher ist es optimal, dass die Clientanwendung Transaktionen in ihrem Fehlerhandler abbricht oder verwendet wird SET XACT_ABORT ON , um diese potenzielle Verzögerung zu vermeiden.

Achtung

Im Folgenden SET XACT_ABORT ONwerden T-SQL-Anweisungen nach einer Anweisung, die einen Fehler verursacht, nicht ausgeführt. Dies kann sich auf den gewünschten Flow des vorhandenen Codes auswirken.

Szenario 3: Blockieren durch eine SPID, deren entsprechende Clientanwendung nicht alle Ergebniszeilen zum Abschluss abrufte

Nach dem Senden einer Abfrage an den Server müssen alle Anwendungen sofort alle Ergebniszeilen vollständig abrufen. Wenn eine Anwendung nicht alle Ergebniszeilen abruft, können Sperren für die Tabellen verbleiben, wodurch andere Benutzer blockiert werden. Sollten Sie eine Anwendung nutzen, die SQL-Anweisungen transparent an den Server übermittelt, muss die Anwendung sämtliche Ergebniszeilen abrufen. Andernfalls (und wenn dies nicht möglich ist), können Sie das Blockierungsproblem möglicherweise nicht beheben. Um dieses Problem zu vermeiden, können Sie entsprechende Anwendungen auf eine Berichtsdatenbank oder eine Datenbank zur Entscheidungsfindung, getrennt von der Haupt-OLTP-Datenbank, beschränken.

Lösung:

Die Anwendung muss so umgeschrieben werden, dass sie alle Zeilen des Ergebnisses vollständig abruft. Dies schließt das Verwenden von OFFSET und FETCH in der ORDER BY-Klausel einer Abfrage nicht aus, um ein serverseitiges Paging auszuführen.

Szenario 4: Blockieren durch einen verteilten Client-/Server-Deadlock

Im Gegensatz zu einem herkömmlichen Deadlock kann ein verteilter Deadlock nicht mithilfe des RDBMS-Sperr-Managers erkannt werden. Dies liegt daran, dass nur eine der Ressourcen, die am Deadlock beteiligt sind, eine SQL Server-Sperre ist. Die andere Seite des Deadlocks befindet sich auf Clientanwendungsebene, über die SQL Server keine Kontrolle hat. Die folgenden beiden Abschnitte zeigen Beispiele dafür, wie dies geschehen kann und wie die Anwendung dies vermeiden kann.

Beispiel A: Verteilter Client-/Server-Deadlock mit einem einzelnen Clientthread

Wenn der Client über mehrere offene Verbindungen und einen einzelnen Ausführungsthread verfügt, kann der folgende verteilte Deadlock auftreten. Beachten Sie, dass sich der hier verwendete Begriff dbproc auf die Clientverbindungsstruktur bezieht.

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

In dem oben gezeigten Fall verfügt ein einzelner Clientanwendungsthread über zwei offene Verbindungen. Er sendet asynchron einen SQL-Vorgang für dbproc1. Dies bedeutet, dass der Anruf nicht auf die Rückgabe wartet, bevor er fortgefahren wird. Die Anwendung sendet dann einen weiteren SQL-Vorgang für dbproc2 und wartet auf die Ergebnisse, um mit der Verarbeitung der zurückgegebenen Daten zu beginnen. Wenn Daten wieder zurückkommen (je nachdem, welche dbproc zuerst antwortet– gehen Sie davon aus, dass es sich um dbproc1 handelt), verarbeitet sie, um alle daten zu schließen, die für diesen dbproc zurückgegeben werden. Es ruft Ergebnisse von dbproc1 ab, bis SPID1 für eine von SPID2 gehaltene Sperre blockiert wird (da die beiden Abfragen asynchron auf dem Server ausgeführt werden). Zu diesem Zeitpunkt wartet dbproc1 auf unbestimmte Zeit auf weitere Daten. SPID2 ist für eine Sperre nicht blockiert, versucht jedoch, Daten an den Client dbproc2 zu senden. Dbproc2 wird jedoch effektiv auf dbproc1 auf der Anwendungsebene blockiert, da der einzelne Ausführungsthread für die Anwendung von dbproc1 verwendet wird. Dies führt zu einem Deadlock, den SQL Server nicht erkennen oder auflösen kann, da nur eine der beteiligten Ressourcen eine SQL Server-Ressource ist.

Beispiel B: Client/Server verteilter Deadlock mit einem Thread pro Verbindung

Selbst wenn für jede Verbindung auf dem Client ein separater Thread vorhanden ist, kann eine Variante dieses verteilten Deadlocks wie folgt auftreten.

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

Dieser Fall ähnelt Beispiel A, mit Ausnahme von dbproc2 und SPID2, dass eine SELECT Anweisung mit der Absicht ausgeführt wird, zeilenweise Verarbeitung durchzuführen und jede Zeile über einen Puffer an dbproc1 für eine INSERT, UPDATEoder DELETE Anweisung in derselben Tabelle zu übergeben. Schließlich wird SPID1 (ausführen des INSERTUPDATE, oder DELETE) auf einer von SPID2 gehaltenen Sperre blockiert (ausführend die SELECT). SPID2 schreibt eine Ergebniszeile in den Client dbproc2. Dbproc2 versucht dann, die Zeile in einem Puffer an dbproc1 zu übergeben, findet jedoch, dass dbproc1 ausgelastet ist (es ist blockiert, dass auf SPID1 gewartet wird, um den aktuellen INSERT, auf SPID2 blockierten Wert abzuschließen). An dieser Stelle wird dbproc2 auf der Anwendungsebene durch dbproc1 blockiert, deren SPID1 (SPID1) auf Datenbankebene von SPID2 blockiert wird. Dies führt wiederum zu einem Deadlock, den SQL Server nicht erkennen oder auflösen kann, da nur eine der beteiligten Ressourcen eine SQL Server-Ressource ist.

Sowohl beispiele A als auch B sind grundlegende Probleme, die Anwendungsentwickler kennen müssen. Sie müssen Anwendungen codieren, um diese Fälle entsprechend zu behandeln.

Lösung:

Wenn ein Abfragetimeout bereitgestellt wurde, wenn der verteilte Deadlock auftritt, wird es beim Timeout unterbrochen. Weitere Informationen zur Verwendung eines Abfragetimeouts erfahren Sie in der Dokumentation ihres Verbindungsanbieters.

Szenario 5: Blockieren durch eine Sitzung in einem Rollbackstatus

Eine Datenänderungsabfrage, die außerhalb einer benutzerdefinierten Transaktion abgebrochen oder abgebrochen wird, wird zurückgesetzt. Dies kann auch als Nebeneffekt auftreten, wenn eine Clientnetzwerksitzung getrennt oder eine Anforderung als Deadlockopfer ausgewählt wird. Dies kann häufig durch Beobachten der Ausgabe identifiziert sys.dm_exec_requestswerden, die auf den ROLLBACK commandhinweisen kann, und die Spalte kann den percent_complete Fortschritt anzeigen.

Eine Datenänderungsabfrage, die außerhalb einer benutzerdefinierten Transaktion abgebrochen oder abgebrochen wird, wird zurückgesetzt. Dies kann auch als Nebeneffekt des Neustarts des Clientcomputers und dessen Netzwerksitzungsverbindung auftreten. Ebenso wird eine Abfrage, die als Deadlock-Opfer ausgewählt ist, zurückgesetzt. Eine Datenänderungsabfrage kann häufig nicht schneller zurückgesetzt werden, als die Änderungen ursprünglich angewendet wurden. Wenn beispielsweise eine DELETE, INSERToder UPDATE eine Anweisung eine Stunde lang ausgeführt wurde, kann es mindestens eine Stunde dauern, bis ein Rollback ausgeführt wurde. Dies wird erwartet, da die vorgenommenen Änderungen zurückgesetzt werden müssen, oder transaktionsale und physische Integrität in der Datenbank würde kompromittiert. Da dies geschehen muss, markiert SQL Server den SPID in einem goldenen oder rollback-Zustand (was bedeutet, dass er nicht als Deadlock-Opfer getötet oder ausgewählt werden kann). Dies kann häufig durch Beobachten der Ausgabe identifiziert sp_whowerden, die den ROLLBACK-Befehl angeben kann. In der status sys.dm_exec_sessions Spalte wird ein ROLLBACK-Status angegeben.

Notiz

Lange Rollbacks sind selten, wenn das Feature für die beschleunigte Datenbankwiederherstellung aktiviert ist. Dieses Feature wurde in SQL Server 2019 eingeführt.

Lösung:

Sie müssen warten, bis die Sitzung das Rollback der vorgenommenen Änderungen abgeschlossen hat.

Wenn die Instanz in der Mitte dieses Vorgangs heruntergefahren wird, befindet sich die Datenbank beim Neustart im Wiederherstellungsmodus und kann erst wieder auf sie zugreifen, wenn alle geöffneten Transaktionen verarbeitet werden. Die Startwiederherstellung benötigt im Wesentlichen die gleiche Zeit pro Transaktion wie die Laufzeitwiederherstellung, und die Datenbank kann während dieses Zeitraums nicht darauf zugreifen. Daher ist es oft kontraproduktiv, den Server zu zwingen, eine SPID in einem Rollbackzustand zu reparieren. In SQL Server 2019 mit aktivierter beschleunigter Datenbankwiederherstellung sollte dies nicht erfolgen.

Um diese Situation zu vermeiden, führen Sie in OLTP-Systemen in Zeiträumen mit sehr viel Aktivität keine umfangreichen Batchschreibvorgänge oder Indexerstellungs- oder Wartungsvorgänge aus. Führen Sie solche Vorgänge nach Möglichkeit in Zeiträumen mit geringer Aktivität aus.

Szenario 6: Blockieren durch eine verwaiste Transaktion

Dies ist ein häufiges Problemszenario und überlappt teilweise mit Szenario 2. Wenn die Clientanwendung beendet wird, die Clientarbeitsstation neu gestartet wird oder ein Batchabbruchfehler auftritt, kann eine Transaktion geöffnet bleiben. Diese Situation kann auftreten, wenn die Anwendung die Transaktion nicht in den Anwendungs CATCH - oder FINALLY Blöcken zurückrollt oder wenn dies andernfalls nicht behandelt wird.

In diesem Szenario wird die SQL-Transaktion geöffnet, während die Ausführung eines SQL-Batches abgebrochen wurde. Aus Sicht der SQL Server-Instanz scheint der Client weiterhin vorhanden zu sein, und alle erworbenen Sperren werden beibehalten.

Um eine verwaiste Transaktion zu veranschaulichen, führen Sie die folgende Abfrage aus, die einen Batchabbruchfehler simuliert, indem Sie Daten in eine nicht vorhandene Tabelle einfügen:

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

Führen Sie dann die folgende Abfrage im selben Fenster aus:

SELECT @@TRANCOUNT;

Die Ausgabe der zweiten Abfrage gibt an, dass die Transaktionsanzahl eins ist. Alle in der Transaktion erworbenen Sperren bleiben erhalten, bis die Transaktion zugesichert oder zurückgesetzt wird. Da der Batch bereits von der Abfrage abgebrochen wurde, kann die Anwendung, die sie ausführt, andere Abfragen in derselben Sitzung weiterhin ausführen, ohne die noch geöffnete Transaktion zu bereinigen. Die Sperre wird gehalten, bis die Sitzung beendet wird oder die SQL Server-Instanz neu gestartet wird.

Lösungen:

  • Die beste Möglichkeit, diese Bedingung zu verhindern, besteht darin, die Behandlung von Anwendungsfehlern/Ausnahmebehandlungen zu verbessern, insbesondere bei unerwarteten Beendigungen. Stellen Sie sicher, dass Sie einen Try-Catch-Finally Block im Anwendungscode verwenden und die Transaktion im Falle einer Ausnahme zurücksetzen.
  • Erwägen Sie die Verwendung SET XACT_ABORT ON für die Sitzung oder in gespeicherten Prozeduren, die Transaktionen beginnen und nach einem Fehler nicht bereinigen. Bei einem Laufzeitfehler, der den Batch abbricht, führt diese Einstellung automatisch einen Rollback aller geöffneten Transaktionen durch und gibt die Steuerung an den Client zurück. Weitere Informationen finden Sie unter SET XACT_ABORT (Transact-SQL).
  • Um eine verwaiste Verbindung einer Clientanwendung aufzulösen, die getrennt wurde, ohne die Ressourcen entsprechend zu bereinigen, können Sie die SPID mit dem KILL Befehl beenden. Referenz finden Sie unter KILL (Transact-SQL).For reference, see KILL (Transact-SQL).

Der Befehl KILL akzeptiert den SPID-Wert als Eingabe. Führen Sie beispielsweise den folgenden Befehl aus, um SPID 9 zu beenden:

KILL 99

Notiz

Der KILL Befehl kann bis zu 30 Sekunden dauern, da das Intervall zwischen den Überprüfungen für den KILL Befehl liegt.

Siehe auch