Omówienie i rozwiązywanie problemów z blokowaniem programu SQL Server.

Dotyczy: SQL Server (wszystkie obsługiwane wersje), Azure SQL Managed Instance

Oryginalny numer KB: 224453

Cel cząstkowy

W tym artykule opisano blokowanie w programie SQL Server i pokazano, jak rozwiązywać problemy z blokowaniem i rozwiązywać problemy z blokowaniem.

W tym artykule termin połączenie odnosi się do jednej zalogowanej sesji bazy danych. Każde połączenie jest wyświetlane jako identyfikator sesji (SPID) lub session_id w wielu widokach DMV. Każdy z tych identyfikatorów SPID jest często określany jako proces, chociaż nie jest to oddzielny kontekst procesu w zwykłym sensie. Zamiast tego każdy SPID składa się z zasobów serwera i struktur danych niezbędnych do obsługi żądań pojedynczego połączenia od danego klienta. Jedna aplikacja kliencka może mieć co najmniej jedno połączenie. Z punktu widzenia programu SQL Server nie ma różnicy między wieloma połączeniami z jednej aplikacji klienckiej na jednym komputerze klienckim i wieloma połączeniami z wielu aplikacji klienckich lub wielu komputerów klienckich; są niepodzielne. Jedno połączenie może zablokować inne połączenie, niezależnie od klienta źródłowego.

Co powoduje blokadę

Blokowanie jest niemożliwą do uniknięcia i celową cechą każdego systemu zarządzania relacyjnymi bazami danych (RDBMS) ze współbieżnością opartą na blokadach. Jak wspomniano wcześniej, w programie SQL Server blokowanie występuje, gdy jedna sesja przechowuje blokadę określonego zasobu, a drugi spiD próbuje uzyskać typ blokady powodującej konflikt w tym samym zasobie. Zazwyczaj przedział czasu, dla którego pierwszy SPID blokuje zasób jest mały. Gdy sesja będąca właścicielem zwalnia blokadę, drugie połączenie jest następnie zwalniane, aby uzyskać własną blokadę na zasobie i może kontynuować przetwarzanie. Blokowanie zgodnie z opisem w tym miejscu jest normalnym zachowaniem i może wystąpić wiele razy w ciągu dnia bez zauważalnego wpływu na wydajność systemu.

Czas trwania i kontekst transakcji zapytania określają, jak długo są przechowywane jego blokady, a tym samym ich wpływ na inne zapytania. Jeśli zapytanie nie jest wykonywane w ramach transakcji (i nie są używane żadne wskazówki dotyczące blokady), blokady instrukcji SELECT będą przechowywane tylko na zasobie w momencie jego odczytu, a nie podczas wykonywania zapytania. W przypadku instrukcji INSERT, UPDATE i DELETE blokady są przechowywane w zapytaniu, zarówno dla spójności danych, jak i w celu umożliwienia wycofania zapytania w razie potrzeby.

W przypadku zapytań wykonywanych w ramach transakcji czas trwania blokad zależy od typu zapytania, poziomu izolacji transakcji i tego, czy w zapytaniu są używane wskazówki dotyczące blokady. Opis blokowania, wskazówek dotyczących blokady i poziomów izolacji transakcji można znaleźć w następujących artykułach:

• Blokowanie w aparacie bazy danych
• Dostosowywanie blokowania i przechowywanie wersji wierszy
• Tryby blokady
• Zgodność blokady
• Poziomy izolacji oparte na przechowywaniu wersji wierszy w aparacie bazy danych
• Transakcje

W przypadku blokowania i blokowania występuje w punkcie, w którym występuje szkodliwy wpływ na wydajność systemu, wynika to z jednego z następujących powodów:

• SpiD przechowuje blokady na zestawie zasobów przez dłuższy czas przed ich zwolnieniem. Ten typ blokowania rozwiązuje się w czasie, ale może spowodować obniżenie wydajności.

• SpiD przechowuje blokady na zestawie zasobów i nigdy ich nie zwalnia. Ten typ blokowania nie rozwiązuje problemu i uniemożliwia dostęp do zasobów, których dotyczy problem, na czas nieokreślony.

W pierwszym scenariuszu sytuacja może być bardzo płynna, ponieważ różne SPID powodują zablokowanie różnych zasobów w czasie, tworząc ruch docelowy. Te sytuacje są trudne do rozwiązania problemów przy użyciu programu SQL Server Management Studio , aby zawęzić problem do poszczególnych zapytań. Natomiast druga sytuacja powoduje spójny stan, który może być łatwiejszy do zdiagnozowania.

Aplikacje i blokowanie

Może wystąpić tendencja do skupienia się na dostrajaniu po stronie serwera i problemach z platformą w przypadku wystąpienia problemu blokującego. Jednak uwaga zwracana tylko do bazy danych może nie prowadzić do rozwiązania i może lepiej absorbować czas i energię skierowaną do badania aplikacji klienckiej i przesyłanych zapytań. Niezależnie od tego, jaki poziom widoczności aplikacja uwidacznia wywołania bazy danych, problem blokujący jednak często wymaga zarówno inspekcji dokładnych instrukcji SQL przesłanych przez aplikację, jak i dokładnego zachowania aplikacji w zakresie anulowania zapytań, zarządzania połączeniami, pobierania wszystkich wierszy wyników itd. Jeśli narzędzie programistyczne nie zezwala na jawną kontrolę nad zarządzaniem połączeniami, anulowaniem zapytań, limitem czasu zapytania, pobieraniem wyników itd., blokowanie problemów może nie być możliwe do rozwiązania. Ten potencjał należy dokładnie zbadać przed wybraniem narzędzia programistycznego aplikacji dla programu SQL Server, szczególnie w przypadku środowisk OLTP z uwzględnieniem wydajności.

Zwróć uwagę na wydajność bazy danych podczas fazy projektowania i budowy bazy danych i aplikacji. W szczególności należy ocenić użycie zasobów, poziom izolacji i długość ścieżki transakcji dla każdego zapytania. Każde zapytanie i transakcja powinny być tak lekkie, jak to możliwe. Należy wykonać dobrą dyscyplinę zarządzania połączeniami, bez niej, aplikacja może wydawać się mieć akceptowalną wydajność przy niskiej liczbie użytkowników, ale wydajność może znacznie obniżyć się w miarę skalowania liczby użytkowników w górę.

Dzięki właściwemu projektowi aplikacji i zapytań program SQL Server może obsługiwać wiele tysięcy równoczesnych użytkowników na jednym serwerze z niewielkim blokowaniem.

Rozwiązywanie problemów z blokowaniem

Niezależnie od tego, w jakiej sytuacji blokujemy, metodologia rozwiązywania problemów z blokowaniem jest taka sama. Te separacje logiczne będą dyktować resztę kompozycji tego artykułu. Koncepcja polega na znalezieniu bloku głównego i zidentyfikowaniu tego, co robi to zapytanie i dlaczego blokuje. Po zidentyfikowaniu problematycznego zapytania (czyli przechowywania blokad przez dłuższy czas), następnym krokiem jest przeanalizowanie i określenie przyczyny blokowania. Po zrozumieniu, dlaczego możemy wprowadzić zmiany, przeprojektując zapytanie i transakcję.

Kroki rozwiązywania problemów:

1. Identyfikowanie głównej sesji blokującej (blokada głowy)

2. Znajdź zapytanie i transakcję powodującą blokowanie (co przechowuje blokady przez dłuższy czas)

3. Analizowanie/zrozumienie, dlaczego występuje długotrwałe blokowanie

4. Rozwiązywanie problemu z blokowaniem przez przeprojektowanie zapytania i transakcji

Teraz przyjrzyjmy się, jak wskazać główną sesję blokującą przy użyciu odpowiedniego przechwytywania danych.

Zbieranie informacji blokujących

Aby przeciwdziałać trudnościom z rozwiązywaniem problemów z blokowaniem, administrator bazy danych może używać skryptów SQL, które stale monitorują stan blokowania i blokowania w programie SQL Server. Aby zebrać te dane, istnieją dwie bezpłatne metody.

Pierwszą z nich jest wykonywanie zapytań dotyczących dynamicznych obiektów zarządzania (DMO) i przechowywanie wyników w celu porównania w czasie. Niektóre obiekty, do których odwołuje się ten artykuł, to dynamiczne widoki zarządzania (DMV), a niektóre to dynamiczne funkcje zarządzania (DMFS).

Drugim jest użycie zdarzeń rozszerzonych (XEvents) lub śladów profilera SQL w celu przechwycenia wykonywanych operacji. Ponieważ narzędzie SQL Trace i SQL Server Profiler są przestarzałe, ten przewodnik rozwiązywania problemów koncentruje się na elementach XEvents.

Zbieranie informacji z widoków DMV

Odwoływanie się do widoków DMV w celu rozwiązywania problemów z blokowaniem ma na celu zidentyfikowanie identyfikatora SPID (identyfikatora sesji) na czele łańcucha blokowania i instrukcji SQL. Poszukaj identyfikatorów SPID ofiary, które są blokowane. Jeśli jakikolwiek SPID jest blokowany przez inny SPID, zbadaj SPID będący właścicielem zasobu (blokujący SPID). Czy ten właściciel SPID jest również blokowany? Możesz przejść przez łańcuch, aby znaleźć blokadę głowy, a następnie zbadać, dlaczego utrzymuje blokadę.

W tym celu można użyć jednej z następujących metod:

• W programie SQL Server Management Studio (SSMS) Eksplorator obiektów kliknij prawym przyciskiem myszy obiekt serwera najwyższego poziomu, rozwiń węzeł Raporty, rozwiń węzeł Raporty standardowe, a następnie wybierz pozycję Działanie — wszystkie transakcje blokujące. Ten raport przedstawia bieżące transakcje na czele łańcucha blokującego. Po rozwinięciu transakcji raport pokaże transakcje, które są blokowane przez transakcję head. Ten raport będzie również zawierać instrukcję Blocking SQL i zablokowaną instrukcję SQL.

• Otwórz monitor aktywności w programie SSMS i odwołaj się do kolumny Zablokowane przez. Więcej informacji na temat monitora aktywności można znaleźć tutaj.

Bardziej szczegółowe metody oparte na zapytaniach są również dostępne przy użyciu widoków DMV:

• Polecenia sp_who i sp_who2 są starszymi poleceniami, aby wyświetlić wszystkie bieżące sesje. Widok DMV sys.dm_exec_sessions zwraca więcej danych w zestawie wyników, który jest łatwiejszy do wykonywania zapytań i filtrowania. Znajdziesz sys.dm_exec_sessions ją w rdzeniu innych zapytań.

• Jeśli masz już zidentyfikowaną określoną sesję, możesz użyć DBCC INPUTBUFFER(<session_id>) polecenia , aby znaleźć ostatnią instrukcję przesłaną przez sesję. Podobne wyniki można zwrócić za pomocą funkcji dynamicznego sys.dm_exec_input_buffer zarządzania (DMF) w zestawie wyników, który jest łatwiejszy do wykonywania zapytań i filtrowania, zapewniając session_id i request_id. Aby na przykład zwrócić najnowsze zapytanie przesłane przez session_id 66 i request_id 0:

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

• Zapoznaj się z kolumną sys.dm_exec_requests i odwołaj się do tej kolumny blocking_session_id . Gdy blocking_session_id = 0, sesja nie jest blokowana. Podczas gdy sys.dm_exec_requests lista zawiera tylko żądania aktualnie wykonywane, wszystkie połączenia (aktywne lub nie) będą wyświetlane na liście .sys.dm_exec_sessions Na podstawie tego wspólnego sprzężenia między sys.dm_exec_requests i sys.dm_exec_sessions w następnym zapytaniu. Należy pamiętać, że zapytanie musi być aktywnie wykonywane za pomocą sys.dm_exec_requestsprogramu SQL Server.

• Uruchom to przykładowe zapytanie, aby znaleźć aktywnie wykonywane zapytania i ich bieżący tekst wsadowy SQL lub tekst buforu wejściowego przy użyciu sys.dm_exec_sql_text lub sys.dm_exec_input_buffer widoków DMV. Jeśli dane zwrócone przez kolumnę text sys.dm_exec_sql_text mają wartość NULL, zapytanie nie jest obecnie wykonywane. W takim przypadku kolumna event_info elementu sys.dm_exec_input_buffer będzie zawierać ostatni ciąg polecenia przekazany do aparatu SQL. To zapytanie może również służyć do identyfikowania sesji blokujących inne sesje, w tym listę zablokowanych session_ids na session_id.

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;

• Uruchom bardziej rozbudowane przykładowe zapytanie dostarczone przez pomoc techniczna firmy Microsoft, aby zidentyfikować nagłówek łańcucha blokowania wielu sesji, w tym tekst zapytania sesji zaangażowanych w łańcuch blokowania.

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;

• Aby przechwycić długotrwałe lub niezatwierdzone transakcje, użyj innego zestawu widoków DMV do wyświetlania bieżących otwartych transakcji, w tym sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections i sys.dm_exec_sql_text. Istnieje kilka widoków DMV skojarzonych ze śledzeniem transakcji. Zobacz więcej widoków DMV dotyczących transakcji tutaj.

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];

• Odwołanie sys.dm_os_waiting_tasks , które znajduje się w warstwie wątku/zadania programu SQL Server. Spowoduje to zwrócenie informacji o tym, co usługa SQL wait_type aktualnie występuje żądanie. Podobnie jak sys.dm_exec_requests, tylko aktywne żądania są zwracane przez .sys.dm_os_waiting_tasks

• Użyj sys.dm_tran_locks widoku DMV, aby uzyskać bardziej szczegółowe informacje na temat blokad, które zostały umieszczone przez zapytania. Ten dynamiczny widok zarządzania może zwracać duże ilości danych w produkcyjnym wystąpieniu programu SQL Server i jest przydatny do diagnozowania, jakie blokady są obecnie przechowywane.

Ze względu na sprzężenie WEWNĘTRZNE w systemie sys.dm_os_waiting_tasksnastępujące zapytanie ogranicza dane wyjściowe tylko sys.dm_tran_locks do aktualnie zablokowanych żądań, ich stanu oczekiwania i blokad:

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>';

W przypadku widoków DMV przechowywanie wyników zapytania w czasie zapewni punkty danych, które umożliwią przeglądanie blokowania w określonym przedziale czasu w celu zidentyfikowania utrwalonego blokowania lub trendów. Narzędzie go-to css do rozwiązywania takich problemów polega na użyciu modułu zbierającego dane PSSDiag. To narzędzie używa statystyk wydajności programu SQL Server do zbierania zestawów wyników z widoków DMV wymienionych powyżej w czasie. Ponieważ to narzędzie stale ewoluuje, przejrzyj najnowszą publiczną wersję narzędzia DiagManager w witrynie GitHub.

Zbieranie informacji z zdarzeń rozszerzonych

Oprócz powyższych informacji często konieczne jest przechwycenie śladu działań na serwerze w celu dokładnego zbadania problemu blokującego w programie SQL Server. Jeśli na przykład sesja wykonuje wiele instrukcji w ramach transakcji, będzie reprezentowana tylko ostatnia instrukcja, która została przesłana. Jednak jedno z wcześniejszych stwierdzeń może być powodem, dla którego blokady są nadal przetrzymywane. Śledzenie umożliwi wyświetlanie wszystkich poleceń wykonywanych przez sesję w ramach bieżącej transakcji.

Istnieją dwa sposoby przechwytywania śladów w programie SQL Server; Zdarzenia rozszerzone (XEvents) i ślady profilera. Jednak ślady SQL korzystające z programu SQL Server Profiler są przestarzałe. XEvents to nowsza, lepsza platforma śledzenia, która umożliwia bardziej wszechstronność i mniejszy wpływ na obserwowany system, a jego interfejs jest zintegrowany z programem SSMS.

Wstępnie utworzone sesje zdarzeń rozszerzonych są gotowe do uruchomienia w programie SSMS wymienione w Eksplorator obiektów w menu programu XEvent Profiler. Aby uzyskać więcej informacji, zobacz XEvent Profiler. Możesz również utworzyć własne niestandardowe sesje zdarzeń rozszerzonych w programie SSMS. Zobacz Kreator nowych sesji zdarzeń rozszerzonych. W przypadku rozwiązywania problemów z blokowaniem zazwyczaj przechwycimy:

• Błędy kategorii:
  • Uwaga
  • Blocked_process_report**
  • Error_reported (administrator kanału)
  • Exchange_spill
  • Execution_warning

**Aby skonfigurować próg i częstotliwość generowania zablokowanych raportów procesów, użyj polecenia sp_configure, aby skonfigurować opcję progu zablokowanego procesu, którą można ustawić w sekundach. Domyślnie nie są tworzone żadne zablokowane raporty procesów.

• Ostrzeżenia kategorii:

  • Hash_warning
  • Missing_column_statistics
  • Missing_join_predicate
  • Sort_warning

• Wykonywanie kategorii:

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• Blokada kategorii

  • Lock_deadlock

• Sesja kategorii

  • Existing_connection
  • Zaloguj się
  • Wyloguj

Identyfikowanie i rozwiązywanie typowych scenariuszy blokowania

Sprawdzając powyższe informacje, możesz określić przyczynę większości problemów blokujących. Pozostała część tego artykułu to omówienie sposobu używania tych informacji do identyfikowania i rozwiązywania typowych scenariuszy blokowania. W tej dyskusji założono, że użyto skryptów blokujących (przywoływanych wcześniej) do przechwytywania informacji na temat blokujących identyfikatorów SPID i przechwycenia aktywności aplikacji przy użyciu sesji XEvent.

Analizowanie danych blokujących

• Zbadaj dane wyjściowe widoków DMV sys.dm_exec_requests i sys.dm_exec_sessions określ głowice łańcuchów blokujących, używając elementów blocking_these i session_id. Najjjaśniej określi, które żądania są blokowane i które blokują. Przyjrzyj się kolejnym sesjom, które są blokowane i blokowane. Czy istnieje wspólny lub główny łańcuch blokowania? Prawdopodobnie współużytkują wspólną tabelę, a co najmniej jedna sesja zaangażowana w łańcuch blokowania wykonuje operację zapisu.

• Sprawdź dane wyjściowe dynamicznych widoków sys.dm_exec_requests zarządzania i sys.dm_exec_sessions , aby uzyskać informacje na temat identyfikatorów SPID na czele łańcucha blokującego. Wyszukaj następujące kolumny:

  • sys.dm_exec_requests.status

    Ta kolumna zawiera stan określonego żądania. Zazwyczaj stan uśpienia wskazuje, że SPID zakończył wykonywanie i oczekuje na przesłanie przez aplikację innego zapytania lub partii. Stan uruchamiania lub uruchamiania wskazuje, że identyfikator SPID obecnie przetwarza zapytanie. W poniższej tabeli przedstawiono krótkie wyjaśnienia różnych wartości stanu.

    Stan	Znaczenie
    Tło	SpiD uruchamia zadanie w tle, takie jak wykrywanie zakleszczenia, zapis dzienników lub punkt kontrolny.
    Uśpienie	SpiD nie jest obecnie wykonywany. Zwykle oznacza to, że spiD oczekuje na polecenie z aplikacji.
    Uruchomiono	SpiD jest obecnie uruchomiony w harmonogramie.
    Możliwość uruchomienia	SpiD znajduje się w możliwej do uruchomienia kolejce harmonogramu i czeka na uzyskanie czasu harmonogramu.
    Suspended	SPID czeka na zasób, taki jak blokada lub zatrzask.

  • sys.dm_exec_sessions.open_transaction_count

    Ta kolumna informuje o liczbie otwartych transakcji w tej sesji. Jeśli ta wartość jest większa niż 0, SPID znajduje się w otwartej transakcji i może przechowywać blokady uzyskane przez dowolną instrukcję w ramach transakcji. Otwarta transakcja mogła zostać utworzona przez aktualnie aktywną instrukcję lub przez żądanie instrukcji, które zostało uruchomione w przeszłości i nie jest już aktywne.

  • sys.dm_exec_requests.open_transaction_count

    Podobnie ta kolumna informuje o liczbie otwartych transakcji w tym żądaniu. Jeśli ta wartość jest większa niż 0, SPID znajduje się w otwartej transakcji i może przechowywać blokady uzyskane przez dowolną aktywną instrukcję w ramach transakcji. W przeciwieństwie do sys.dm_exec_sessions.open_transaction_countmetody , jeśli nie ma aktywnego żądania, ta kolumna będzie zawierać wartość 0.

  • sys.dm_exec_requests.wait_type, wait_timei last_wait_type

    Jeśli wartość sys.dm_exec_requests.wait_type ma wartość NULL, żądanie nie oczekuje obecnie na żadne elementy, a last_wait_type wartość wskazuje ostatni wait_type , że napotkano żądanie. Aby uzyskać więcej informacji o sys.dm_os_wait_stats typach oczekiwania i opis najbardziej typowych typów oczekiwania, zobacz sys.dm_os_wait_stats. Wartość wait_time może służyć do określenia, czy żądanie postępuje. Gdy zapytanie względem sys.dm_exec_requests tabeli zwraca wartość w wait_time kolumnie, która jest mniejsza niż wait_time wartość z poprzedniego zapytania , oznacza to, że poprzednia blokada została pobrana i zwolniona, a teraz czeka na nową blokadę (przy założeniu sys.dm_exec_requests, że niezerowa wait_time). Można to zweryfikować, porównując wait_resource między danymi wyjściowymi sys.dm_exec_requests , który wyświetla zasób, dla którego żądanie oczekuje.

  • sys.dm_exec_requests.wait_resource

    Ta kolumna wskazuje zasób, na który oczekuje zablokowane żądanie. W poniższej tabeli wymieniono typowe wait_resource formaty i ich znaczenie:

    Zasób	Format	Przykład	Wyjaśnienie
    Table	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	W takim przypadku identyfikator bazy danych 5 to przykładowa baza danych pubów, a object_id 261575970 to tabela tytułów, a 1 to indeks klastrowany.
    Strona	DatabaseID:FileID:PageID	STRONA: 5:1:104	W takim przypadku identyfikator bazy danych 5 to puby, identyfikator pliku 1 jest podstawowym plikiem danych, a strona 104 jest stroną należącą do tabeli tytułów. Aby zidentyfikować object_id, do którego należy strona, użyj funkcji dynamicznego zarządzania sys.dm_db_page_info, przekazując identyfikator DatabaseID, FileId, PageId z .wait_resource
    Klucz	DatabaseID:Hobt_id (wartość skrótu klucza indeksu)	KLUCZ: 5:72057594044284928 (3300a4f361aa)	W takim przypadku identyfikator bazy danych 5 to Pubs, Hobt_ID 72057594044284928 odpowiada index_id 2 dla object_id 261575970 (tabela tytułów). sys.partitions Użyj widoku wykazu, aby skojarzyć element hobt_id z określonym index_id elementem i object_id. Nie można usunąć skrótu klucza indeksu z określoną wartością klucza.
    Wiersz	DatabaseID:FileID:PageID:Slot(wiersz)	RID: 5:1:104:3	W takim przypadku identyfikator bazy danych 5 to puby, identyfikator pliku 1 jest podstawowym plikiem danych, strona 104 jest stroną należącą do tabeli tytułów, a miejsce 3 wskazuje położenie wiersza na stronie.
    Kompiluj	DatabaseID:FileID:PageID:Slot(wiersz)	RID: 5:1:104:3	W takim przypadku identyfikator bazy danych 5 to puby, identyfikator pliku 1 jest podstawowym plikiem danych, strona 104 jest stroną należącą do tabeli tytułów, a miejsce 3 wskazuje położenie wiersza na stronie.

  • sys.dm_tran_active_transactions Widok DMV sys.dm_tran_active_transactions zawiera dane dotyczące otwartych transakcji, które można połączyć z innymi widokami DMV w celu uzyskania pełnego obrazu transakcji oczekujących na zatwierdzenie lub wycofanie. Użyj następującego zapytania, aby zwrócić informacje dotyczące otwartych transakcji, dołączone do innych widoków ZARZĄDZANIA, w tym sys.dm_tran_session_transactions. Rozważ bieżący stan transakcji, i inne dane sytuacyjne, aby ocenić, transaction_begin_timeczy może to być źródło blokowania.

    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;
    

  • Inne kolumny

    Pozostałe kolumny w sys.dm_exec_sessions i sys.dm_exec_request mogą również zapewnić wgląd w główny problem. Ich użyteczność różni się w zależności od okoliczności problemu. Na przykład można określić, czy problem występuje tylko od niektórych klientów (hostname), w niektórych bibliotekach sieciowych (client_interface_name), kiedy ostatnia partia przesłana przez SPID znajdowała last_request_start_time się w sys.dm_exec_sessionsobiekcie , jak długo żądanie było uruchomione start_time w programie sys.dm_exec_requestsitd.

Typowe scenariusze blokowania

Poniższa tabela przedstawia typowe objawy ich prawdopodobnych przyczyn.

Kolumny wait_type, open_transaction_counti status odwołują się do informacji zwracanych przez sys.dm_exec_request, inne kolumny mogą być zwracane przez sys.dm_exec_sessions. Kolumna "Resolves?" wskazuje, czy blokowanie zostanie rozpoznane samodzielnie, czy sesja powinna zostać zabita za pomocą KILL polecenia . Aby uzyskać więcej informacji, zobacz KILL (Transact-SQL).

Szczegółowe scenariusze blokowania

Scenariusz 1. Blokowanie spowodowane przez zwykle uruchomione zapytanie z długim czasem wykonywania

W tym scenariuszu aktywne uruchomione zapytanie uzyskało blokady, a blokady nie są zwalniane (ma to wpływ na poziom izolacji transakcji). Więc inne sesje będą czekać na blokady, dopóki nie zostaną zwolnione.

Rozwiązanie:

Rozwiązaniem tego problemu blokującego jest wyszukanie sposobów optymalizacji zapytania. Ta klasa problemu z blokowaniem może być problemem z wydajnością i wymaga traktowania go jako takiego. Aby uzyskać informacje na temat rozwiązywania problemów z konkretnym wolno działającym zapytaniem, zobacz Jak rozwiązywać problemy z wolno działającymi zapytaniami w programie SQL Server. Aby uzyskać więcej informacji, zobacz Monitorowanie i dostrajanie wydajności.

Raporty wbudowane w program SSMS z magazynu zapytań (wprowadzonego w programie SQL Server 2016) są również wysoce zalecanym i cennym narzędziem do identyfikowania najbardziej kosztownych zapytań i nieoptymalnych planów wykonywania.

Jeśli masz długotrwałe zapytanie blokujące innych użytkowników i nie można go zoptymalizować, rozważ przeniesienie go ze środowiska OLTP do dedykowanego systemu raportowania. Możesz również użyć zawsze włączonych grup dostępności, aby zsynchronizować replikę bazy danych tylko do odczytu.

Scenariusz 2. Blokowanie spowodowane przez spany identyfikator SPID, który ma niezatwierdzonej transakcji

Ten typ blokowania może być często identyfikowany przez SPID, który śpi lub oczekuje na polecenie z poziomem zagnieżdżania transakcji (@@TRANCOUNT, open_transaction_count z sys.dm_exec_requests) większym niż zero. Taka sytuacja może wystąpić, jeśli aplikacja napotyka przekroczenie limitu czasu zapytania lub wystawia anulowanie bez wystawiania wymaganej liczby instrukcji ROLLBACK i/lub COMMIT. Gdy identyfikator SPID odbiera limit czasu zapytania lub anulowanie, spowoduje to zakończenie bieżącego zapytania i partii, ale nie powoduje automatycznego wycofania ani zatwierdzenia transakcji. Aplikacja jest odpowiedzialna za to, ponieważ program SQL Server nie może zakładać, że cała transakcja musi zostać wycofana z powodu anulowania pojedynczego zapytania. Limit czasu zapytania lub anulowanie będą wyświetlane jako zdarzenie sygnału UWAGI dla SPID w sesji zdarzenia rozszerzonego.

Aby zademonstrować niezatwierdzonej jawnej transakcji, wykonaj następujące zapytanie:

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

Następnie wykonaj to zapytanie w tym samym oknie:

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

Dane wyjściowe drugiego zapytania wskazują, że liczba transakcji jest jedna. Wszystkie blokady uzyskane w transakcji są nadal przechowywane do momentu zatwierdzenia lub wycofania transakcji. Jeśli aplikacje jawnie otwierają i zatwierdzają transakcje, komunikacja lub inny błąd może pozostawić sesję i jej transakcję w stanie otwartym.

Użyj skryptu wcześniej w tym artykule na podstawie, sys.dm_tran_active_transactions aby zidentyfikować aktualnie niezatwierdzone transakcje w wystąpieniu.

Rozwiązania:

• Ta klasa problemu z blokowaniem może być również problemem z wydajnością i wymaga traktowania go jako takiego. Jeśli czas wykonywania zapytania może zostać zmniejszony, limit czasu zapytania lub anulowanie może nie wystąpić. Ważne jest, aby aplikacja mogła obsłużyć limit czasu lub anulować scenariusze, jeśli wystąpią, ale możesz również skorzystać z badania wydajności zapytania.

• Aplikacje muszą prawidłowo zarządzać poziomami zagnieżdżania transakcji lub mogą powodować problem z blokowaniem po anulowaniu zapytania w ten sposób. Rozważ następujące źródła:

  • W procedurze obsługi błędów aplikacji klienckiej wykonaj następujące IF @@TRANCOUNT > 0 ROLLBACK TRAN czynności, nawet jeśli aplikacja kliencka nie uważa, że transakcja jest otwarta. Sprawdzanie otwartych transakcji jest wymagane, ponieważ procedura składowana wywoływana podczas partii mogła rozpocząć transakcję bez wiedzy aplikacji klienckiej. Niektóre warunki, takie jak anulowanie zapytania, uniemożliwiają wykonanie procedury w przeszłości bieżącej instrukcji, więc nawet jeśli procedura ma logikę sprawdzania IF @@ERROR <> 0 i przerwania transakcji, ten kod wycofywania nie zostanie wykonany w takich przypadkach.

  • Jeśli buforowanie połączeń jest używane w aplikacji, która otwiera połączenie i uruchamia kilka zapytań przed zwolnieniem połączenia z powrotem do puli, takich jak aplikacja internetowa, tymczasowe wyłączenie puli połączeń może pomóc złagodzić problem, dopóki aplikacja kliencka nie zostanie odpowiednio zmodyfikowana w celu obsługi błędów. Wyłączenie buforowania połączeń spowoduje fizyczne rozłączenie połączenia z programem SQL Server, co spowoduje wycofywanie wszystkich otwartych transakcji przez serwer.

  • Służy SET XACT_ABORT ON do nawiązywania połączenia lub w dowolnych procedurach składowanych, które rozpoczynają transakcje i nie są czyszczące po błędzie. W przypadku błędu czasu wykonywania to ustawienie spowoduje przerwanie wszystkich otwartych transakcji i zwrócenie kontroli do klienta. Aby uzyskać więcej informacji, zobacz SET XACT_ABORT (Transact-SQL).

Scenariusz 3. Blokowanie spowodowane przez SPID, którego odpowiednia aplikacja kliencka nie pobrała wszystkich wierszy wyników do ukończenia

Po wysłaniu zapytania do serwera wszystkie aplikacje muszą natychmiast pobrać wszystkie wiersze wyników w celu ukończenia. Jeśli aplikacja nie pobierze wszystkich wierszy wyników, blokady mogą pozostać w tabelach, blokując innych użytkowników. Jeśli używasz aplikacji, która w sposób niewidoczny przesyła instrukcje SQL na serwer, aplikacja musi pobrać wszystkie wiersze wyników. Jeśli tak nie jest (i jeśli nie można go skonfigurować), być może nie możesz rozwiązać problemu blokującego. Aby uniknąć tego problemu, możesz ograniczyć nieprawidłowo zachowujące się aplikacje do bazy danych raportowania lub wspierania decyzji, oddzielnej od głównej bazy danych OLTP.

Rozwiązanie:

Aplikacja musi zostać przepisana, aby pobrać wszystkie wiersze wyniku do ukończenia. Nie wyklucza to użycia funkcji OFFSET i FETCH w klauzuli ORDER BY zapytania do wykonywania stronicowania po stronie serwera.

Scenariusz 4. Blokowanie spowodowane zakleszczeniem rozproszonego klienta/serwera

W przeciwieństwie do konwencjonalnego zakleszczenia rozproszony zakleszczenie nie jest wykrywalne przy użyciu menedżera blokady RDBMS. Dzieje się tak, ponieważ tylko jeden z zasobów zaangażowanych w zakleszczenie jest blokadą programu SQL Server. Druga strona zakleszczenia znajduje się na poziomie aplikacji klienckiej, nad którą program SQL Server nie ma kontroli. W poniższych dwóch sekcjach przedstawiono przykłady tego, jak może się to zdarzyć, i możliwe sposoby, w jaki aplikacja może jej uniknąć.

Przykład: Zakleszczenie rozproszone klienta/serwera z jednym wątkiem klienta

Jeśli klient ma wiele otwartych połączeń i jeden wątek wykonywania, może wystąpić następujące rozproszone zakleszczenie. Należy pamiętać, że termin dbproc użyty tutaj odnosi się do struktury połączenia klienta.

 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)

W powyższym przypadku jeden wątek aplikacji klienckiej ma dwa otwarte połączenia. Asynchronicznie przesyła operację SQL na dbproc1. Oznacza to, że nie czeka na wywołanie, aby powrócić przed kontynuowaniem. Następnie aplikacja przesyła kolejną operację SQL na dbproc2 i oczekuje na wyniki, aby rozpocząć przetwarzanie zwróconych danych. Po rozpoczęciu powrotu danych (w zależności od tego, która funkcja dbproc najpierw odpowiada — załóżmy, że jest to dbproc1), przetwarza je w celu ukończenia wszystkich danych zwracanych na tym dbproc. Pobiera wyniki z dbproc1, dopóki spiD1 nie zostanie zablokowany blokada blokady przechowywanej przez SPID2 (ponieważ dwa zapytania są uruchomione asynchronicznie na serwerze). W tym momencie dbproc1 będzie czekać na czas nieokreślony na więcej danych. SpiD2 nie jest zablokowany w blokadzie, ale próbuje wysłać dane do klienta dbproc2. Jednak dbproc2 jest skutecznie blokowany na dbproc1 w warstwie aplikacji, ponieważ pojedynczy wątek wykonywania aplikacji jest używany przez dbproc1. Powoduje to zakleszczenie, którego program SQL Server nie może wykryć lub rozwiązać, ponieważ tylko jeden z zaangażowanych zasobów jest zasobem programu SQL Server.

Przykład B: zakleszczenie rozproszone klienta/serwera z wątkiem na połączenie

Nawet jeśli istnieje oddzielny wątek dla każdego połączenia na kliencie, odmiana tego rozproszonego zakleszczenia może nadal występować, jak pokazano poniżej.

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)

Ten przypadek jest podobny do przykładu A, z wyjątkiem dbproc2 i SPID2 uruchamia instrukcję SELECT z zamiarem wykonywania przetwarzania wierszy w czasie i przekazywania każdego wiersza przez bufor do dbproc1 dla INSERTinstrukcji , UPDATElub DELETE w tej samej tabeli. Ostatecznie SPID1 (wykonanie INSERTmetody , UPDATElub DELETE) zostanie zablokowane na blokadzie przechowywanej przez SPID2 (wykonując funkcję SELECT). SPID2 zapisuje wiersz wyników do klienta dbproc2. Dbproc2 następnie próbuje przekazać wiersz w buforze do dbproc1, ale znajduje dbproc1 jest zajęty (zablokowano oczekiwanie na SPID1, aby zakończyć bieżącą wartość INSERT, która jest zablokowana na SPID2). W tym momencie dbproc2 jest blokowany w warstwie aplikacji przez dbproc1, którego SPID1 (SPID1) jest blokowany na poziomie bazy danych przez SPID2. Ponownie powoduje to zakleszczenie, którego program SQL Server nie może wykryć lub rozwiązać, ponieważ tylko jeden z zaangażowanych zasobów jest zasobem programu SQL Server.

Zarówno przykłady A, jak i B są podstawowymi problemami, o których muszą wiedzieć deweloperzy aplikacji. Muszą kodować aplikacje, aby odpowiednio obsługiwać te przypadki.

Rozwiązanie:

Po podaniu limitu czasu zapytania, jeśli wystąpi rozproszony impas, zostanie przerwany po przekroczeniu limitu czasu. Aby uzyskać więcej informacji na temat używania limitu czasu zapytania, zobacz dokumentację dostawcy połączeń.

Scenariusz 5. Blokowanie spowodowane przez sesję w stanie wycofywania

Zapytanie modyfikacji danych, które zostało zabite lub anulowane poza transakcją zdefiniowaną przez użytkownika, zostanie wycofane. Może to również wystąpić jako efekt uboczny rozłączenia sesji sieci klienta lub gdy żądanie zostanie wybrane jako ofiara zakleszczenia. Często można to zidentyfikować, obserwując dane wyjściowe sys.dm_exec_requestselementu , co może wskazywać na wycofywanie command, a kolumna percent_complete może pokazywać postęp.

Zapytanie modyfikacji danych, które zostało zabite lub anulowane poza transakcją zdefiniowaną przez użytkownika, zostanie wycofane. Może to również wystąpić jako efekt uboczny ponownego uruchomienia komputera klienckiego i rozłączenia sesji sieciowej. Podobnie zapytanie wybrane jako ofiara zakleszczenia zostanie wycofane. Kwerenda modyfikacji danych często nie może zostać wycofana szybciej niż początkowo zastosowane zmiany. Jeśli na przykład instrukcja DELETE, INSERTlub UPDATE była uruchomiona przez godzinę, wycofanie może potrwać co najmniej godzinę. Jest to oczekiwane zachowanie, ponieważ wprowadzone zmiany muszą zostać wycofane, a integralność transakcyjna i fizyczna w bazie danych zostanie naruszona. Ponieważ musi się tak zdarzyć, program SQL Server oznacza SPID w stanie złotym lub wycofywania (co oznacza, że nie można go zabić lub wybrać jako ofiarę zakleszczenia). Często można to zidentyfikować, obserwując dane wyjściowe sp_whopolecenia , co może wskazywać na polecenie ROLLBACK. Kolumna status sys.dm_exec_sessions elementu będzie wskazywać stan WYCOFYWANIa.

Rozwiązanie:

Musisz poczekać, aż sesja zakończy wycofywanie wprowadzonych zmian.

Jeśli wystąpienie zostanie zamknięte w trakcie tej operacji, baza danych będzie w trybie odzyskiwania po ponownym uruchomieniu i będzie niedostępna do momentu przetworzenia wszystkich otwartych transakcji. Odzyskiwanie uruchamiania zajmuje zasadniczo tyle samo czasu na transakcję co odzyskiwanie w czasie wykonywania, a baza danych jest niedostępna w tym okresie. W związku z tym wymuszenie usunięcia identyfikatora SPID w stanie wycofywania często będzie sprzeczne z produktem. W programie SQL Server 2019 z włączonym przyspieszonym odzyskiwaniem bazy danych nie powinno to wystąpić.

Aby uniknąć takiej sytuacji, nie wykonuj dużych operacji zapisu wsadowego ani tworzenia indeksu ani operacji konserwacji w godzinach pracy w systemach OLTP. Jeśli to możliwe, wykonaj takie operacje w okresach niskiej aktywności.

Scenariusz 6. Blokowanie spowodowane przez transakcję oddzieloną

Jest to typowy scenariusz problemu i nakłada się częściowo na scenariusz 2. Jeśli aplikacja kliencka zostanie zatrzymana, stacja robocza klienta zostanie uruchomiona ponownie lub wystąpi błąd przerwania wsadowego, wszystkie te elementy mogą pozostawić otwartą transakcję. Taka sytuacja może wystąpić, jeśli aplikacja nie wycofa transakcji w blokach lub FINALLY blokach aplikacji CATCH lub jeśli nie obsługuje takiej sytuacji.

W tym scenariuszu, podczas gdy wykonywanie partii SQL zostało anulowane, aplikacja pozostawia otwartą transakcję SQL. Z perspektywy wystąpienia programu SQL Server klient nadal wydaje się być obecny, a wszystkie uzyskane blokady są zachowywane.

Aby zademonstrować transakcję oddzieloną, wykonaj następujące zapytanie, które symuluje błąd przerywania wsadowego, wstawiając dane do tabeli, która nie istnieje:

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)

Następnie wykonaj to zapytanie w tym samym oknie:

SELECT @@TRANCOUNT;

Dane wyjściowe drugiego zapytania wskazują, że liczba transakcji jest jedna. Wszystkie blokady uzyskane w transakcji są nadal przechowywane do momentu zatwierdzenia lub wycofania transakcji. Ponieważ partia jest już przerwana przez zapytanie, aplikacja, która ją wykonuje, może kontynuować wykonywanie innych zapytań w tej samej sesji bez czyszczenia transakcji, która jest nadal otwarta. Blokada będzie przechowywana do momentu, gdy sesja zostanie przerwana lub wystąpienie programu SQL Server zostanie ponownie uruchomione.

Rozwiązania:

• Najlepszym sposobem zapobiegania temu warunekowi jest poprawienie obsługi błędów/wyjątków aplikacji, szczególnie w przypadku nieoczekiwanych zakończeń. Upewnij się, że używasz Try-Catch-Finally bloku w kodzie aplikacji i wycofaj transakcję w przypadku wyjątku.
• Rozważ użycie SET XACT_ABORT ON sesji lub procedur składowanych, które rozpoczynają transakcje i nie są czyszczące po błędzie. W przypadku błędu czasu wykonywania, który przerywa partię, to ustawienie automatycznie wycofa wszystkie otwarte transakcje i zwróci kontrolę do klienta. Aby uzyskać więcej informacji, zobacz SET XACT_ABORT (Transact-SQL).
• Aby rozwiązać problem z oddzielonym połączeniem aplikacji klienckiej, która została rozłączona bez odpowiedniego czyszczenia zasobów, możesz zakończyć spid przy użyciu KILL polecenia . Aby uzyskać informacje, zobacz KILL (Transact-SQL).

Polecenie KILL przyjmuje wartość SPID jako dane wejściowe. Na przykład, aby zabić SPID 9, uruchom następujące polecenie:

KILL 99

Zobacz też

• Monitorowanie wydajności za pomocą Magazynu zapytań
• Rozwiązywanie problemów z blokowaniem spowodowanych eskalacją blokady w programie SQL Server
• Przewodnik dotyczący blokowania transakcji i przechowywania wersji wierszy
• USTAWIANIE POZIOMU IZOLACJI TRANSAKCJI
• Szybki start: zdarzenia rozszerzone w programie SQL Server