Verstehen und Beheben von SQL Server-Blockierungsproblemen

  • Artikel

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 in vielen DMVs als Sitzungs-ID (SPID) oder session_id angezeigt. Jede dieser SPIDs wird häufig als Prozess bezeichnet, obwohl es sich nicht um einen separaten Prozesskontext im üblichen Sinne handelt. Vielmehr besteht jeder SPID aus den Serverressourcen und Datenstrukturen, die zum Verarbeiten der Anforderungen einer einzelnen Verbindung von einem bestimmten Client erforderlich sind. Eine einzelne Clientanwendung kann eine oder mehrere Verbindungen haben. 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.

Hinweis

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 ist Blockieren?

Das Blockieren ist ein unvermeidbares und entwurfsbedingtes Merkmal jedes Systems zur Verwaltung relationaler Datenbanken (RDBMS) mit sperrenbasierter 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, für den die erste SPID die Ressource sperrt, nur kurz. Wenn die besitzerführende Sitzung die Sperre freigibt, kann die zweite Verbindung ihre eigene Sperre für die Ressource erwerben 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.

Die Dauer und der Transaktionskontext einer Abfrage bestimmen, wie lange ihre Sperren gehalten werden, und damit auch deren 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 werden die Sperren während der Abfrage gehalten, sowohl aus Gründen der Datenkonsistenz als auch um zu ermöglichen, dass die Abfrage bei Bedarf zurückgesetzt wird.

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. Eine Beschreibung der Sperrungs-, Sperrhinweise- und Transaktionsisolationsstufen finden Sie in den folgenden Artikeln:

Wenn das Sperren und Blockieren bis zu dem Punkt fortbesteht, an dem die Systemleistung beeinträchtigt wird, liegt dies an einem der folgenden Gründe:

  • 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 im Laufe der Zeit auf, kann jedoch zu Leistungseinbußen führen.

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

Im ersten Szenario kann die Situation sehr fließend sein, da verschiedene SPIDs im Laufe der Zeit Blockierungen auf verschiedenen Ressourcen verursachen, wodurch sozusagen ein bewegliches Ziel entsteht. Diese Situationen lassen sich nur schwer mithilfe von SQL Server Management Studio beheben, um das Problem auf einzelne Abfragen einzugrenzen. Im Gegensatz dazu führt die zweite Situation zu einem konsistenten Zustand, der einfacher zu diagnostizieren ist.

Anwendungen und Blockieren

Es kann eine Tendenz geben, sich auf serverseitige Optimierungs- und Plattformprobleme zu konzentrieren, wenn ein Blockierungsproblem auftritt. Wenn man sich jedoch nur auf die Datenbank konzentriert, führt dies möglicherweise nicht zu einer Lösung und kann Zeit und Energie verschlingen, die besser für die Untersuchung der Client-Anwendung und der von ihr gestellten Abfragen verwendet werden sollten. Unabhängig davon, welche Sichtbarkeit die Anwendung in Bezug auf die ausgeführten Datenbankaufrufe offenlegt, erfordert ein Blockierungsproblem häufig sowohl die Überprüfung der genauen SQL Anweisungen, die von der Anwendung übermittelt wurden, als auch das genaue Verhalten der Anwendung in Bezug auf Abfrageunterdrückung, Verbindungsverwaltung, Abrufen aller Ergebniszeilen usw. 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 Konstruktionsphase der Datenbank und Anwendung auf die Datenbankleistung. Insbesondere sollten der Ressourcenverbrauch, die Isolationsstufe und die Länge des Transaktionspfads für jede Abfrage ausgewertet werden. Jede Abfrage und Transaktion sollte so einfach wie möglich sein. Eine gute Disziplin bei der Verbindungsverwaltung ist unabdingbar, da die Anwendung sonst bei einer geringen Anzahl von Benutzern eine akzeptable Leistung zu haben scheint, die jedoch mit steigender Benutzerzahl erheblich abnimmt.

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

Unabhängig davon, in welcher Sperrsituation wir uns befinden, ist die Methodik für die Problembehandlung beim Sperren identisch. Diese logischen Trennungen bestimmen den Rest der Zusammensetzung dieses Artikels. Das Konzept besteht darin, den Hauptblockierer zu finden und zu identifizieren, was diese Abfrage tut und warum sie blockiert. 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 wichtigsten blockierenden Sitzung (Hauptblockierer)

  2. Suchen Sie die Abfrage und Transaktion, die die Blockierung verursacht (die Sperrungen für einen längeren Zeitraum hält)

  3. Analysieren/verstehen, warum die verlängerte Blockierung auftritt

  4. Beheben des Blockierungsproblems durch Neugestaltung von Abfrage und Transaktion

Nun wollen wir uns damit befassen, wie sich die Hauptblockierungssitzung durch eine geeignete Datenerfassung ermitteln lässt.

Sammeln von Sperrinformationen

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 im Laufe der Zeit zum Vergleichen zu speichern. Einige Objekte, auf die in diesem Artikel Bezug genommen wird, sind dynamische Verwaltungsansichten (DMVs), und einige sind dynamische Verwaltungsfunktionen (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 der Informationen von DMVs

Das Verweisen auf DMVs zur Problembehandlung beim Blockieren hat das Ziel, die SPID (Sitzungs-ID), die sich am Kopf der Blockierungskette befindet, und die SQL-Anweisung zu identifizieren. Suchen Sie nach SPIDs, die Opfer der Blockierung sind. Wenn eine SPID von einer anderen SPID blockiert wird, untersuchen Sie die SPID, die die Ressource besitzt (die blockierende SPID). Wird auch diese besitzende SPID selbst blockiert? Sie können die Kette durchlaufen, um den Anfangsblockierer zu finden und dann zu untersuchen, warum dieser seine Sperre aufrechterhält.

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. Der DMV sys.dm_exec_sessions gibt mehr Daten in einem Ergebnissatz zurück, der einfacher abzufragen und zu filtern ist. Sie finden sys.dm_exec_sessions im Kern 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 einer Sitzung übermittelt wurde. Ähnliche Ergebnisse können mit der sys.dm_exec_input_buffer dynamischen Verwaltungsfunktion (Dynamic Management Function, DMF) in einem Ergebnissatz zurückgegeben werden, der einfacher abzufragen und zu filtern ist, wobei die session_id und die request_id bereitgestellt werden. So können Sie beispielsweise die letzte von session_id 66 und request_id 0 gesendete Abfrage zurückgeben lassen:

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 ist, wird eine Sitzung nicht blockiert. Während durch sys.dm_exec_requests nur Anfragen aufgelistet werden, die derzeit ausgeführt werden, wird in sys.dm_exec_sessions jede Verbindung (aktiv oder nicht) aufgelistet. Bauen Sie in der nächsten Abfrage auf dieser gemeinsamen Verknüpfung zwischen sys.dm_exec_requests und sys.dm_exec_sessions auf. 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 die aktiv ausgeführten Abfragen und deren aktuellen SQL-Batchtext oder Eingabepuffertext mithilfe der DMVs sys.dm_exec_sql_text oder sys.dm_exec_input_buffer 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. Diese Abfrage kann auch verwendet werden, um Sitzungen zu identifizieren, die andere Sitzungen blockieren, einschließlich einer Liste von session_ids, die pro session_id blockiert werden.

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 ausführlichere Beispielabfrage aus, die vom Microsoft-Support bereitgestellt wird, um den Kopf einer Blockierungskette für mehrere Sitzungen zu identifizieren, einschließlich des Abfragetexts der Sitzungen, die an einer Blockierungskette 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. Wie für sys.dm_exec_requests, werden von sys.dm_os_waiting_tasks nur aktive Anforderungen zurückgegeben.

Hinweis

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

  • Verwenden Sie den DMV sys.dm_tran_locks, um genauere Informationen darüber zu erhalten, welche Sperren 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 der INNER JOIN-Funktion in sys.dm_os_waiting_tasks beschränkt die folgende Abfrage die Ausgabe sys.dm_tran_locks nur auf derzeit blockierte Anforderungen, ihre 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>';

Bei DMVs werden beim Speichern der Abfrageergebnisse im Laufe der Zeit Datenpunkte bereitgestellt, mit denen Sie die Blockierung über ein bestimmtes Zeitintervall überprüfen können, um dauerhafte Blockierungen oder Trends zu identifizieren. 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. Wenn eine Sitzung beispielsweise mehrere Anweisungen innerhalb einer Transaktion ausführt, wird nur die letzte gesendete Anweisung dargestellt. Es kann jedoch sein, dass eine der früheren Anweisungen der Grund dafür ist, dass die Sperren immer noch gehalten werden. Mithilfe einer Ablaufverfolgung können Sie alle Befehle anzeigen, die von einer Sitzung innerhalb der aktuellen Transaktion ausgeführt werden.

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:
    • Achtung
    • 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 keine Berichte über blockierte Prozesse erstellt.

  • Kategoriewarnungen:

    • Hash_warning
    • Missing_column_statistics
    • Missing_join_predicate
    • Sort_warning

  • Kategorieausführung:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Kategoriesperre

    • Lock_deadlock

  • Kategoriesitzung

    • Existing_connection
    • Anmeldung
    • Abmeldung

Identifizieren und Beheben gängiger Blockierungsszenarien

Anhand der oben genannten Informationen können Sie die Ursache für die meisten Blockierungsprobleme ermitteln. Im restlichen Teil dieses Artikels wird erläutert, wie Sie diese Informationen verwenden können, um einige häufige Blockierungsszenarien zu identifizieren und zu beheben. 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 Blockierungsdaten

  • Untersuchen Sie die Ausgabe der DMVs sys.dm_exec_requests und sys.dm_exec_sessions, um die Köpfe der Blockierungsketten mithilfe von blocking_these und session_id zu bestimmen. Dadurch wird am deutlichsten identifiziert, welche Anforderungen blockiert werden und welche ihrerseits blockieren. Sehen Sie sich die blockierten und die blockierenden Sitzungen genauer an. Gibt es etwas Gemeinsames oder einen Stamm der Blockierungskette? Sie teilen sich wahrscheinlich eine gemeinsame Tabelle, und mindestens eine der Sitzungen, die an einer Blockierungskette beteiligt sind, führt einen Schreibvorgang aus.

  • Überprüfen Sie die Ausgabe der DMVs sys.dm_exec_requests und sys.dm_exec_sessions auf Informationen zu den SPIDs am Anfang der Blockierungskette. Suchen Sie nach den folgenden Spalten:

    • sys.dm_exec_requests.status
      In dieser Spalte wird der Status einer bestimmten Anforderung angezeigt. In der Regel gibt ein Ruhezustand an, dass die SPID die Ausführung abgeschlossen hat und auf die Anwendung wartet, um eine weitere Abfrage oder einen Batch zu übermitteln. Ein ausführungsfähiger oder ausgeführter Status gibt an, dass die SPID derzeit eine Abfrage verarbeitet. Die folgende Tabelle enthält kurze Erläuterungen zu den verschiedenen Statuswerten.

      Status Bedeutung
      Hintergrund Die SPID führt eine Hintergrundaufgabe aus, z. B. die Erkennung von Deadlocks, Protokollschreiber oder Checkpoint.
      Ruhezustand Die SPID wird derzeit nicht ausgeführt. Dies bedeutet in der Regel, dass die SPID auf einen Befehl aus der Anwendung wartet.
      Wird ausgeführt Die SPID wird derzeit auf einem Planer ausgeführt.
      Ausführbar Die SPID befindet sich in der durchführbaren Warteschlange eines Planers und wartet auf die Zeit des Planers.
      Gesperrt Die SPID wartet auf eine Ressource, z. B. eine Sperre oder ein Latch.

    • sys.dm_exec_sessions.open_transaction_count
      In dieser Spalte wird die Anzahl der geöffneten Transaktionen in dieser Sitzung angegeben. Wenn dieser Wert größer als 0 ist, befindet sich die SPID innerhalb einer offenen Transaktion und kann Sperren enthalten, die von einer beliebigen Anweisung innerhalb der Transaktion erworben wurden.

    • 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 innerhalb einer offenen Transaktion und kann Sperren enthalten, die von einer beliebigen Anweisung innerhalb der Transaktion erworben wurden.

    • sys.dm_exec_requests.wait_type, wait_time und last_wait_type
      Wenn der Wert von sys.dm_exec_requests.wait_type NULL ist, wartet die Anforderung derzeit nicht auf etwas, und der Wert last_wait_type gibt den letzten wait_type an, auf den die Anforderung gestoßen ist. Weitere Informationen zu sys.dm_os_wait_stats und eine Beschreibung der am häufigsten verwendeten Wartetypen finden Sie unter sys.dm_os_wait_stats. Der Wert von wait_time kann verwendet werden, um festzustellen, ob die Anforderung Fortschritte macht. Wenn eine Abfrage für die Tabelle sys.dm_exec_requests einen Wert in der Spalte wait_time zurückgibt, der kleiner als der wait_time-Wert aus einer vorherigen Abfrage von sys.dm_exec_requests ist, zeigt dies, dass die vorherige Sperre abgerufen und freigegeben wurde und jetzt auf eine neue Sperre wartet (vorausgesetzt, wait_time ist nicht null). Dies kann durch einen Vergleich der Ausgaben zwischen wait_resource und sys.dm_exec_requests überprüft werden, was die Ressource anzeigt, 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 wait_resource-Formate und deren Bedeutung aufgeführt:

      Ressource 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 Datenbank-ID 5 pubs, Datei-ID 1 ist die primäre Datendatei, und Seite 104 ist eine Seite, die zur Titeltabelle gehört. Um die object_id zu identifizieren, zu der die Seite gehört, verwenden Sie die dynamische Verwaltungsfunktion sys.dm_db_page_info, und übergeben Sie die DatabaseID, FileId, PageId aus dem wait_resource.
      Key DatabaseID:Hobt_id (Hashwert für Indexschlüssel) KEY: 5:72057594044284928 (3300a4f361aa) In diesem Fall ist die Datenbank-ID 5 Pubs, und Hobt_ID 72057594044284928 entspricht index_id 2 für 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 zu einem bestimmten Schlüsselwert aufzuheben.
      Zeile DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 In diesem Fall ist Datenbank-ID 5 pubs, 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 Datenbank-ID 5 pubs, 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 von Transaktionen zu erhalten, die auf einen Commit oder Rollback warten. Verwenden Sie die folgende Abfrage, um Informationen zu geöffneten Transaktionen zurückzugeben, die mit anderen DMVs verbunden sind, einschließlich sys.dm_tran_session_transactions. Berücksichtigen Sie den aktuellen Status einer Transaktion, transaction_begin_time und andere Situationsdaten, um auszuwerten, ob es sich 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 Einblicke in die Ursache eines Problems bieten. Ihre Nützlichkeit variiert je nach den Umständen 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 Szenarien für Blockierungen

Die folgende Tabelle ordnet häufige Symptome ihren wahrscheinlichen Ursachen zu.

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 „Resolves?“gibt an, ob die Blockierung sich eigenständig auflösen wird, oder ob die Sitzung über den Befehl KILL gekillt werden soll. Weitere Informationen finden Sie unter KILL (Transact-SQL).

Szenario Wait_type Open_Tran Status Resolves? Andere Symptome
1 NOT NULL >= 0 ausführbar Ja, wenn die Abfrage abgeschlossen ist. In sys.dm_exec_sessions werden die Spalten reads, cpu_time und/oder memory_usage mit der Zeit größer. Die Dauer der Abfrage ist lang, wenn sie abgeschlossen ist.
2 NULL >0 im Ruhezustand Nein, aber die SPID kann gekillt 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 ausführbar Nein. Wird erst aufgelöst, wenn der Client alle Zeilen abruft oder die Verbindung schließt. SPID kann gekillt werden, das 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 Variiert >= 0 ausführbar Nein. Wird erst aufgelöst, wenn der Client Abfragen abbricht oder Verbindungen schließt. SPIDs können gekillt werden, das kann aber bis zu 30 Sekunden dauern. Die Spalte hostname in sys.dm_exec_sessions für die SPID an der Spitze einer Blockierungskette ist dieselbe wie die der SPID, die von ihr 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 im Ruhezustand Schließlich. 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 ist viel früher als die aktuelle Uhrzeit.

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

Auflö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 für eine bestimmte Abfrage mit langer Ausführungszeit finden Sie unter So wird's gemacht: Behandeln von Abfragen mit langer Ausführungszeit auf SQL Server. Weitere Informationen finden Sie unter Überwachen und Abstimmen 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.

Hinweis

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 nicht abgeschlossene explizite Transaktion zu veranschaulichen, führen 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 diese 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, kann eine Kommunikation oder ein anderer Fehler die Sitzung und ihre Transaktion in einem geöffneten Zustand verlassen.

Verwenden Sie das Skript, das weiter oben in diesem Artikel auf der Grundlage von sys.dm_tran_active_transactions beschrieben wurde, um aktuell nicht committete Transaktionen in der gesamten Instanz zu ermitteln.

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 die Verschachtelungsebenen der Transaktionen ordnungsgemäß verwalten, da es sonst nach dem Abbruch der Abfrage zu einem Blockierungsproblem kommen kann. Berücksichtigen Sie dabei Folgendes:

    • Führen Sie im Fehlerhandler der Clientanwendung nach einem Fehler stets IF @@TRANCOUNT > 0 ROLLBACK TRAN aus, auch wenn die Clientanwendung nicht glaubt, dass eine Transaktion geöffnet 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, z. B. das Abbrechen der Abfrage, verhindern, dass die Prozedur über die aktuelle Anweisung hinaus ausgeführt wird. Selbst wenn die Prozedur über eine Logik zum Überprüfen von IF @@ERROR <> 0 und Abbrechen der Transaktion verfügt, wird dieser Rollbackcode in solchen Fällen nicht ausgeführt.

    • 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 Falle eines Laufzeitfehlers bricht diese Einstellung alle geöffneten Transaktionen ab und gibt die Steuerung an den Client zurück. Weitere Informationen finden Sie unter SET XACT_ABORT (Transact-SQL).

Hinweis

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 beabsichtigten Fluss 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 bis zum Abschluss abrufen. Wenn eine Anwendung nicht alle Ergebniszeilen abruft, können Sperren in den Tabellen verbleiben, wodurch andere Benutzer blockiert werden. Wenn Sie eine Anwendung verwenden, die SQL Anweisungen transparent an den Server übermittelt, muss die Anwendung alle 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 das Problem zu vermeiden, können Sie Anwendungen mit schlechtem Verhalten auf eine Berichts- oder Entscheidungsunterstützungsdatenbank, getrennt von der OLTP-Hauptdatenbank, beschränken.

Auflösung:

Die Anwendung muss umgeschrieben werden, um alle Zeilen des Ergebnisses bis zum Abschluss abzurufen. Dies schließt die Verwendung von OFFSET und FETCH in der ORDER BY-Klausel einer Abfrage nicht aus, um serverseitiges Paging durchzufü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.

Auflö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 der Clientnetzwerksitzungstrennung auftreten, oder wenn eine Anforderung als Deadlock-Opfer 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.

Hinweis

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

Auflö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, sollten Sie auf OLTP-Systemen während der Stoßzeiten keine umfangreichen Batchschreibvorgänge oder Indexerstellungs- oder Wartungsvorgänge durchführen. Wenn möglich, führen Sie solche Operationen in Zeiten geringer Aktivität aus.

Szenario 6: Blockierung durch eine verwaiste Transaktion

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 diese 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 verwendet den SPID-Wert als Eingabe. Um z. B. SPID 9 zu killen, führen Sie den folgenden Befehl aus:

KILL 99

Hinweis

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