Freigeben über


Verstehen und Beheben von Problemen durch Blockierungen in SQL Server

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

Ursprüngliche KB-Nummer: 224453

Ziel

Der Artikel beschreibt das Blockieren in SQL Server und veranschaulicht, wie Sie Probleme beim Blockieren 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. Jede 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. Im Hinblick auf SQL Server besteht kein Unterschied zwischen mehreren Verbindungen aus einer einzelnen Clientanwendung auf einem einzelnen Clientcomputer und mehreren Verbindungen aus mehreren Clientanwendungen oder mehreren Clientcomputern, denn sie sind atomar. Eine Verbindung kann eine andere Verbindung blockieren, unabhängig vom Quellclient.

Notiz

Dieser Artikel konzentriert sich auf SQL Server-Instanzen, einschließlich Azure-verwalteten SQL-Instanzen. Informationen zur Problembehandlung beim Blockieren in Azure SQL-Datenbank finden Sie unter Verstehen und Beheben von Blockierungsproblemen bei Azure SQL-Datenbanken.

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 bereits erwähnt, erfolgt in SQL Server das Blockieren, wenn eine Sitzung eine Sperre für eine bestimmte Ressource hält und eine zweite SPID versucht, einen konkurrierenden Sperrtyp für dieselbe Ressource zu erlangen. 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 hier beschriebene Blockieren ist ein normales Phänomen, das im Laufe eines Tages viele Male auftreten kann, ohne dass sich dies auf die Systemleistung auswirkt.

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 auf einer Ressource gehalten, 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 Dauer, für die die Sperren gehalten werden, durch den Abfragetyp, die Transaktionsisolationsebene und die Verwendung 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 eine Gruppe von Ressourcen für einen längeren Zeitraum fest, 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 hält Sperren für eine Gruppe 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 Verbindungsverwaltung, Abfrageabbruch, Abfragetimeout, Ergebnisabruf usw. zulässt, können Blockierungsprobleme möglicherweise nicht gelöst werden. Dieses Potenzial sollte vor der Auswahl eines Anwendungsentwicklungstools für SQL Server, insbesondere für leistungssensitive OLTP-Umgebungen, genau untersucht werden.

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 ist SQL Server in der Lage, viele Tausend gleichzeitige Benutzer auf einem einzigen Server mit nur wenigen Blockierungen zu 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 hier Sperrungen für einen 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 entwerfen.

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 Behandlung von Blockierungsproblemen entgegenzuwirken, kann ein Datenbankadministrator SQL-Skripte verwenden, die den Status des Sperrens und Blockierens für SQL Server ständig überwachen. Um diese Daten zu sammeln, gibt es zwei komplementäre 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 Methode besteht darin, erweiterte Ereignisse (XEvents) oder SQL Profiler-Ablaufverfolgungen zu verwenden, um die ausgeführten Ereignisse zu erfassen. Da die SQL-Ablaufverfolgung und SQL Server-Profiler veraltet sind, konzentriert sich diese Anleitung 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.

Verwenden Sie dazu eine der folgenden Methoden:

  • Klicken Sie im Objekt-Explorer von SQL Server Management Studio (SSMS) 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. Dieser Bericht zeigt aktuelle Transaktionen an der Spitze einer Blockierungskette. Wenn Sie die Transaktion erweitern, werden im Bericht die Transaktionen angezeigt, die von der Haupttransaktion blockiert werden. In diesem Bericht werden auch die blockierende SQL-Anweisung und die blockierte SQL-Anweisung angezeigt.

  • Öffnen Sie den Aktivitätsmonitor in SSMS, und beziehen Sie sich auf die Spalte „Blockiert von“. Hier finden Sie weitere Informationen zum Aktivitätsmonitor.

Ausführlichere abfragebasierte Methoden sind auch mit DMVs verfügbar:

  • Bei den Befehlen sp_who und sp_who2 handelt es sich um ältere Befehle zum Anzeigen aller aktuellen Sitzungen. 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, und referenzieren Sie die Spalte 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. Denken Sie daran, dass die Abfrage aktiv mit SQL Server ausgeführt werden muss, um von sys.dm_exec_requests zurückgegeben zu 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 Daten, die von der Spalte text von sys.dm_exec_sql_text zurückgegeben werden, NULL sind, wird die Abfrage derzeit nicht ausgeführt. In diesem Fall enthält die Spalte event_info von sys.dm_exec_input_buffer die letzte Befehlszeichenfolge, die an die SQL-Engine übergeben wurde. 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 auf sys.dm_os_waiting_tasks auf der Thread-/Aufgabenebene von SQL Server. Dadurch werden Informationen darüber zurückgegeben, welchen SQL wait_type die Anfrage gerade durchläuft. 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 gehalten 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 beste Tool für CSS zur Fehlerbehebung bei solchen Problemen ist der PSSDiag-Datensammler. Dieses Tool verwendet die „SQL Server Perf Stats“, um Ergebnisse von DMVs, auf die oben verwiesen wird, im Laufe der Zeit zu sammeln. 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 notwendig, 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, Spuren in SQL Server zu erfassen: erweiterte Ereignisse (XEvents) und Profiler-Ablaufverfolgungen. SQL Ablaufverfolgungen, die den SQL Server Profiler verwenden, sind jedoch veraltet. XEvents sind die neuere, überlegene Ablaufverfolgungsplattform, die mehr Vielseitigkeit bietet, weniger Auswirkungen auf das beobachtete System hat und deren Schnittstelle in SSMS integriert ist.

Es gibt vorgefertigte erweiterte Ereignissitzungen, die im Object Explorer unter dem Menü für XEvent Profiler aufgeführt sind und die in SSMS gestartet werden können. Weitere Informationen finden Sie unter XEvent Profiler. Sie können auch eigene benutzerdefinierte erweiterte Ereignissitzungen in SSMS erstellen, siehe Assistent für neue erweiterte Ereignisse. Für die 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 der Berichte über blockierte Prozesse erstellt werden, verwenden Sie den Befehl „sp_configure“, um die Option für den Schwellenwert für blockierte Prozesse zu konfigurieren, der in Sekunden angegeben werden kann. 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 für die meisten Blockierungsprobleme 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 Blockierungsskripte (zuvor erwähnt) verwendet haben, um Informationen zu den blockierenden SPIDs zu erfassen, und Anwendungsaktivitäten mithilfe einer XEvent-Sitzung erfasst 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 teilt Ihnen diese Spalte die Anzahl der geöffneten Transaktionen in dieser Anforderung mit. 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, object_id 261575970 die Titeltabelle und 1 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 das hobt_id mit einem bestimmten index_id und object_id zu verknüpfen. 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) oder in bestimmten Netzwerkbibliotheken (client_interface_name) auftritt, wann der letzte von einer SPID übermittelte Batch last_request_start_time in sys.dm_exec_sessions war, wie lange eine Anforderung mithilfe von start_time in sys.dm_exec_requests ausgeführt wurde usw.

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 von 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 -abbrechen 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 ist und die SPID Sperren hält, während die Transaktionsisolationsebene die Standardeinstellung ist (READ COMMITTED), ist dies eine wahrscheinliche 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. In der erweiterten Ereignissitzung kann für diese SPID ein Aufmerksamkeitssignal angezeigt werden, das darauf hinweist, dass eine Zeitüberschreitung oder ein Abbruch der Abfrage stattgefunden hat, oder dass 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

Scenario 1: Blockierung durch eine normal ausgeführte Abfrage mit langer Ausführungszeit

In diesem Szenario wurden für eine aktiv ausgeführte Abfrage Sperren abgerufen, und die Sperren werden nicht freigegeben (dies wird von der Transaktionsisolationsstufe beeinflusst). Daher warten andere Sitzungen auf die Sperren, bis sie freigegeben werden.

Lösung:

Die Lösung für diese Art von Blockierungsproblem besteht darin, nach Möglichkeiten zur Optimierung der Abfrage zu suchen. Es kann sich bei dieser Art von Blockierungsproblem um ein Leistungsproblem handeln, das Sie als solches behandeln sollten. 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 aus dem Abfragespeicher integriert sind (eingeführt in SQL Server 2016), sind ebenfalls ein sehr empfehlenswertes und wertvolles Werkzeug, um die teuersten Abfragen und die suboptimalen Ausführungspläne zu identifizieren.

Wenn Sie eine Abfrage mit langer Ausführungszeit haben, die andere Benutzer blockiert und nicht optimiert werden kann, ziehen Sie in Betracht, sie von einer OLTP-Umgebung in ein dediziertes Berichtssystem zu verschieben. Sie können auch Always-On-Verfügbarkeitsgruppen verwenden, um ein schreibgeschütztes Replikat der Datenbank zu synchronisieren.

Notiz

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

Szenario 2: Blockierung durch eine SPID im Ruhezustand, die eine nicht abgeschlossene Transaktion hat

Diese Art der Blockierung lässt sich häufig an einer SPID erkennen, die sich im Ruhezustand befindet oder auf einen Befehl wartet, deren Transaktionsverschachtelungsebene (@@TRANCOUNT, open_transaction_count von sys.dm_exec_requests) jedoch größer als Null ist. Diese Situation kann auftreten, wenn die Anwendung ein Abfragetimeout aufweist 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 Batch, führt aber nicht automatisch ein Rollback oder einen Commit für die Transaktion durch. Die Anwendung ist dafür verantwortlich, da SQL Server nicht davon ausgehen kann, dass eine gesamte Transaktion zurückgesetzt werden muss, nur weil eine einzelne Abfrage abgebrochen wird. Das Abfragetimeout oder -abbrechen 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 Anzahl der Transaktionen eins beträgt. Alle in der Transaktion erworbenen Sperren bleiben bis zum Commit oder Rollback der Transaktion erhalten. 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:

  • Darüber hinaus kann es sich bei dieser Art von Blockierungsproblem auch um ein Leistungsproblem handeln, so dass Sie es als solches verfolgen müssen. Wenn die Ausführungszeit der Abfrage verringert werden kann, kommt es möglicherweise nicht zu einer Zeitüberschreitung oder einem Abbruch der Abfrage. Es ist wichtig, dass die Anwendung in der Lage ist, die Timeout- oder Abbruchszenarien zu behandeln, falls sie auftreten, aber es kann auch von Vorteil sein, die Leistung der Abfrage zu untersuchen.

  • 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. Das Überprüfen auf geöffnete Transaktionen ist erforderlich, da eine gespeicherte Prozedur, die während des Batches aufgerufen wird, ohne das Wissen der Clientanwendung eine Transaktion 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 an den Pool freigegeben wird, z. B. eine webbasierte Anwendung, kann das vorübergehende Deaktivieren des Verbindungspoolings dazu beitragen, das Problem zu beheben, bis die Clientanwendung geändert wurde, um die Fehler angemessen zu behandeln. Durch Deaktivieren des Verbindungspoolings führt das Freigeben der Verbindung zu einer physischen Trennung der SQL Server-Verbindung, was dazu führt, dass der Server alle geöffneten Transaktionen zurückrollt.

    • Verwenden Sie SET XACT_ABORT ON für die Verbindung oder in allen gespeicherten Prozeduren, 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. Daher kann es sein, dass ein Benutzer eine Transaktion öffnen und dann die Verbindung mit dem Verbindungspool freigeben kann, aber diese möglicherweise mehrere Sekunden lang nicht wiederverwendet wird, während denen die Transaktion geöffnet bleibt. Wenn die Verbindung nicht wiederverwendet wird, wird die Transaktion abgebrochen, sobald die Verbindung ausläuft, und aus dem Verbindungspool entfernt. Daher ist es für die Clientanwendung optimal, Transaktionen in ihrem Fehlerhandler abzubrechen oder SET XACT_ABORT ON zu verwenden, um diese potenzielle Verzögerung zu vermeiden.

Achtung

Nach SET XACT_ABORT ON werden T-SQL-Anweisungen, die einer Anweisung folgen, die einen Fehler verursacht, nicht ausgeführt. Dies kann sich auf den gewünschten Flow des vorhandenen Codes auswirken.

Szenario 3: Blockierung durch eine SPID, deren entsprechende Clientanwendung nicht alle Ergebniszeilen bis zum Abschluss abgerufen hat

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. Wenn dies nicht der Fall ist (und sie auch nicht so konfiguriert werden kann), 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 umgeschrieben werden, um alle Zeilen des Ergebnisses bis zum Abschluss abzurufen. 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: Blockierung durch einen über Client/Server verteilten 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 am Deadlock beteiligten Ressourcen 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 welche Möglichkeiten die Anwendung hat, dies zu vermeiden.

Beispiel A: Über Client/Server verteilter Deadlock mit einem einzigen Client-Thread

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 Client-Verbindungsstruktur 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 auf dbproc1. Dies bedeutet, dass beim Anruf nicht gewartet wird, bis er zurückkehrt, bevor der Vorgang fortgesetzt wird. Die Anwendung sendet dann einen weiteren SQL-Vorgang auf dbproc2 und wartet auf die Ergebnisse, um mit der Verarbeitung der zurückgegebenen Daten zu beginnen. Wenn Daten zurückgegeben werden (je nachdem, welche Dbproc zuerst antwortet – unter der Annahme, dies ist dbproc1), werden alle Daten verarbeitet, die in dieser dbproc zurückgegeben werden. Sie 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). An diesem Punkt wartet dbproc1 unbegrenzt auf weitere Daten. SPID2 ist nicht für eine Sperre blockiert, versucht aber, Daten an den Client dbproc2 zu senden. Dbproc2 wird jedoch auf der Anwendungsebene für dbproc1 effektiv 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: Verteilter Client/Server-Deadlock mit einem Thread pro Verbindung

Selbst wenn für jede Verbindung auf dem Client ein separater Thread vorhanden ist, kann eine Variation dieses verteilten Deadlocks weiterhin auftreten, wie im Folgenden gezeigt.

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, außer dass dbproc2 und SPID2 eine SELECT-Anweisung mit der Absicht ausführen, eine zeilenweise Verarbeitung durchzuführen und jede Zeile über einen Puffer an dbproc1 für eine INSERT-, UPDATE- oder DELETE-Anweisung für dieselbe Tabelle zu übergeben. Schließlich wird SPID1 (Ausführen von INSERT, UPDATE oder DELETE) für eine Sperre blockiert, die von SPID2 gehalten wird (ausführen des 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 wird nämlich blockiert durch das Warten darauf, dass SPID1 den aktuellen INSERT abschließt, der für SPID2 blockiert ist). An diesem Punkt wird dbproc2 auf Anwendungsebene durch dbproc1 blockiert, dessen SPID (SPID1) auf Datenbankebene durch 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.

Beide Beispiele A und B sind grundlegende Probleme, die Anwendungsentwickler beachten müssen. Sie müssen Anwendungen so codieren, dass sie diese Fälle angemessen behandeln.

Lösung:

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

Szenario 5: Blockierung durch eine Sitzung in einem Rollbackstatus

Eine Datenänderungsabfrage, die gekillt wird oder außerhalb einer benutzerdefinierten Transaktion 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 identifiziert werden, indem die Ausgabe von sys.dm_exec_requests beobachtet wird, die auf das ROLLBACK command hindeuten kann, und in der Spalte percent_complete wird möglicherweise der Fortschritt angezeigt.

Eine Datenänderungsabfrage, die gekillt wird oder außerhalb einer benutzerdefinierten Transaktion abgebrochen wird, wird zurückgesetzt. Dies kann auch als Nebeneffekt auftreten, wenn der Clientcomputer neu gestartet und die Netzwerksitzung getrennt wird. Ebenso wird eine Abfrage, die als Deadlock-Opfer ausgewählt wurde, zurückgesetzt. Eine Datenänderungsabfrage kann häufig nicht schneller zurückgesetzt werden, als die Änderungen ursprünglich angewendet wurden. Wenn beispielsweise eine Anweisung DELETE, INSERT oder UPDATE seit einer Stunde ausgeführt wurde, kann das Rollback mindestens eine Stunde dauern. Dies ist ein erwartetes Verhalten, da die vorgenommenen Änderungen zurückgesetzt werden müssen, sonst würde die transaktionale und physische Integrität in der Datenbank kompromittiert. Da dies geschehen muss, markiert SQL Server die SPID in einem goldenen oder Rollbackzustand (was bedeutet, dass sie nicht gekillt werden oder als Deadlock-Opfer ausgewählt werden kann). Dies kann häufig durch Beobachten der Ausgabe von sp_who identifiziert werden, die möglicherweise auf den ROLLBACK-Befehl hinweist. Die Spalte status von sys.dm_exec_sessions gibt einen ROLLBACK-Status an.

Notiz

Lange Rollbacks sind selten, wenn das Feature 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 es kann erst auf sie zugegriffen werden, wenn alle geöffneten Transaktionen verarbeitet wurden. Die Wiederherstellung beim Start benötigt im Wesentlichen die gleiche Zeit pro Transaktion wie die Laufzeitwiederherstellung, und während dieses Zeitraums kann nicht auf die Datenbank zugegriffen werden. Daher ist es oft kontraproduktiv, den Server zwangsweise herunterzufahren, um eine SPID in einem Rollbackzustand zu reparieren. In SQL Server 2019 mit aktivierter beschleunigter Datenbankwiederherstellung sollte dies nicht auftreten.

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: Blockierung durch eine verwaiste Verbindung

Dies ist ein häufiges Problemszenario und überlappt sich teilweise mit Szenario 2. Wenn die Clientanwendung beendet wird, die Clientarbeitsstation neu gestartet wird oder ein Batchabbruchfehler auftritt, bleibt möglicherweise eine Transaktion geöffnet. Diese Situation kann auftreten, wenn die Anwendung nicht das Rollback der Transaktion in den Blöcken CATCH oder FINALLY der Anwendung vornimmt, oder wenn sie diese Situation nicht anderweitig behandelt.

In diesem Szenario lässt die Anwendung die SQL-Transaktion offen, obwohl 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 bleiben erhalten.

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 Anzahl der Transaktionen eins beträgt. Alle in der Transaktion erworbenen Sperren bleiben bis zum Commit oder Rollback der Transaktion erhalten. Da der Batch bereits von der Abfrage abgebrochen wurde, kann die Anwendung, die sie ausführt, andere Abfragen in derselben Sitzung fortsetzen, ohne die noch geöffnete Transaktion zu bereinigen. Die Sperre wird so lange gehalten, bis die Sitzung beendet oder die SQL Server-Instanz neu gestartet wird.

Lösungen:

  • Die beste Möglichkeit, diese Bedingung zu verhindern, besteht darin, die Behandlung von Anwendungsfehlern/-ausnahmen 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 von SET XACT_ABORT ON für die Sitzung oder in allen gespeicherten Prozeduren, die Transaktionen beginnen und nach einem Fehler nicht bereinigt werden. Im Falle eines Laufzeitfehlers, der den Batch abbricht, führt diese Einstellung zu einem automatischen Rollback aller offenen Transaktionen und gibt die Kontrolle 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 mithilfe des Befehls KILL beenden. Weitere Informationen finden Sie unter KILL (Transact-SQL).

Der Befehl KILL akzeptiert den SPID-Wert als Eingabe. Um z. B. SPID 9 zu killen, führen Sie den folgenden Befehl aus:

KILL 99

Notiz

Der Abschluss des Befehls KILL kann aufgrund des Intervalls zwischen den Überprüfungen für den Befehl KILL bis zu 30 Sekunden dauern.

Siehe auch