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

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

Oryginalny numer KB: 224453

Cel

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

W tym artykule termin połączenie odnosi się do pojedynczej sesji logowania bazy danych. Każde połączenie jest wyświetlane jako identyfikator sesji (SPID) lub session_id w wielu 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 znaczeniu. Zamiast tego każdy identyfikator 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 perspektywy SQL Server nie ma różnicy między wieloma połączeniami z pojedynczej aplikacji klienckiej na jednym komputerze klienckim a 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.

Czym jest blokowanie

Blokowanie jest nieuniknioną i projektową cechą każdego systemu zarządzania relacyjnymi bazami danych (RDBMS) ze współbieżnością opartą na blokadach. Jak wspomniano wcześniej, w SQL Server blokowanie występuje, gdy jedna sesja przechowuje blokadę określonego zasobu, a drugi identyfikator SPID próbuje uzyskać powodujący konflikt typ blokady dla tego samego zasobu. Zazwyczaj przedział czasu, dla którego pierwszy identyfikator SPID blokuje zasób, jest mały. Gdy sesja będąca właścicielem zwalnia blokadę, drugie połączenie może uzyskać własną blokadę zasobu i kontynuować przetwarzanie. Blokowanie, jak opisano tutaj, jest normalnym zachowaniem i może wystąpić wiele razy w ciągu dnia bez zauważalnego wpływu na wydajność systemu.

Kontekst czasu trwania i transakcji zapytania określa, jak długo są przechowywane 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 dla zasobu w czasie, gdy jest ono faktycznie odczytywane, a nie podczas zapytania. W przypadku instrukcji INSERT, UPDATE i DELETE blokady są przechowywane podczas zapytania, zarówno w celu zapewnienia spójności danych, jak i umożliwienia wycofania zapytania w razie potrzeby.

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

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

Gdy blokowanie utrzymuje się do punktu, w którym występuje szkodliwy wpływ na wydajność systemu, jest to spowodowane jedną z następujących przyczyn:

• Identyfikator SPID przechowuje blokady zestawu zasobów przez dłuższy czas przed ich wydaniem. Ten typ blokowania rozwiązuje się w czasie, ale może powodować obniżenie wydajności.

• Identyfikator SPID przechowuje blokady zestawu zasobów i nigdy ich nie zwalnia. Ten typ blokowania nie rozwiązuje się samodzielnie 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 identyfikatory SPID powodują blokowanie różnych zasobów w czasie, tworząc ruchomy cel. Te sytuacje są trudne do rozwiązania przy użyciu 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

W przypadku problemów z blokowaniem może występować tendencja do koncentracji na dostrajaniu po stronie serwera i problemach z platformą. Jednak zwracanie uwagi tylko na bazę danych może nie prowadzić do rozwiązania oraz może pochłaniać czas i energię, które lepiej ukierunkować na badanie aplikacji klienckiej i przesłanych przez nią zapytań. Niezależnie od tego, jaki poziom widoczności aplikacja uwidacznia w odniesieniu do wykonywanych wywołań bazy danych, problem z blokowaniem 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 deweloperskie nie zezwala na jawną kontrolę nad zarządzaniem połączeniami, anulowaniem zapytań, przekroczeniem limitu czasu zapytania, pobieraniem wyników itd., problemy z blokowaniem mogą nie być możliwe do rozwiązania. Ten potencjał należy dokładnie zbadać przed wybraniem narzędzia do tworzenia aplikacji dla SQL Server, szczególnie w przypadku środowisk OLTP wrażliwych na wydajność.

Zwróć uwagę na wydajność bazy danych na etapie projektowania i budowy bazy danych i aplikacji. W szczególności powinno się oceniać zużycie zasobów, poziom izolacji i długość ścieżki transakcji dla każdego zapytania. Każde zapytanie i transakcja powinny być jak najbardziej uproszczone. Należy zachować dobrą dyscyplinę zarządzania połączeniami, bo bez niej może się wydawać, że aplikacja ma akceptowalną wydajność przy niskiej liczbie użytkowników, jednak wydajność może ulec znacznemu pogorszeniu w miarę zwiększania się liczby użytkowników.

Dzięki prawidłowej konstrukcji aplikacji i zapytań SQL Server może obsługiwać wiele tysięcy równoczesnych użytkowników na jednym serwerze przy niewielkim blokowaniu.

Rozwiązywanie problemów z blokowaniem

Niezależnie od sytuacji blokowania, w której się znajdujemy, metodyka rozwiązywania problemów z blokowaniem jest taka sama. Te logiczne separacje są tym, co uszereguje pozostałą część tego artykułu. Koncepcja polega na znalezieniu blokady głównej i określeniu, co to zapytanie robi i dlaczego blokuje. Po zidentyfikowaniu problematycznego zapytania (tj. blokowanie trwa przez dłuższy czas) następnym krokiem jest przeanalizowanie i ustalenie, dlaczego występuje blokowanie. Gdy zrozumiemy powody, możemy wprowadzić zmiany, przeprojektowując zapytanie i transakcję.

Kroki do podjęcia w celu rozwiązywania problemów:

1. Identyfikowanie głównej sesji blokowania (bloker główny)

2. Znalezienie zapytania i transakcji powodującej blokowanie (co powoduje blokowanie przez dłuższy czas)

3. Analizowanie/zrozumienie, dlaczego występuje blokowanie przez dłuższy czas

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

Teraz przyjrzyjmy się omówieniu sposobu określenia głównej sesji blokującej przy użyciu odpowiedniego przechwytywania danych.

Zbieranie informacji o blokowaniu

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

Pierwszą z nich jest wykonywanie zapytań dotyczących obiektów zarządzania dynamicznego (DMO) i przechowywanie wyników do 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 (DMF).

Druga to użycie zdarzeń rozszerzonych (XEvents) lub Śledzenia profilera SQL w celu przechwycenia tego, co jest wykonywane. Ponieważ SQL Trace i SQL Server Profiler są przestarzałe, ten przewodnik rozwiązywania problemów koncentruje się na zdarzeniach 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 określenie identyfikatora SPID (identyfikatora sesji) na czele łańcucha blokowania i instrukcji SQL. Poszukaj zablokowanych identyfikatorów SPID ofiary. Jeśli jakikolwiek identyfikator SPID jest blokowany przez inny identyfikator SPID, zbadaj identyfikator SPID będący właścicielem zasobu (blokujący identyfikator SPID). Czy ten identyfikator SPID będący właścicielem jest również blokowany? Możesz przejść przez łańcuch, aby znaleźć bloker główny, a następnie zbadać, dlaczego utrzymuje blokadę.

W tym celu zastosuj jedną z następujących metod:

• W Eksploratorze obiektów SQL Server Management Studio (SSMS) kliknij prawym przyciskiem myszy obiekt serwera najwyższego poziomu, rozwiń Raporty, rozwiń Raporty standardowe, a następnie wybierz pozycję Działanie — wszystkie transakcje blokujące. Ten raport przedstawia bieżące transakcje na czele łańcucha blokowania. Jeśli rozwiniesz transakcję, w raporcie zostaną wyświetlone transakcje zablokowane przez transakcję główną. W tym raporcie zostaną również wyświetlone instrukcje Instrukcja blokująca SQL i Instrukcja zablokowana SQL.

• Otwórz monitor aktywności w SSMS i zapoznaj się z kolumną 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. Funkcja 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 u podstaw innych zapytań.

• Jeśli masz już określoną sesję, możesz użyć DBCC INPUTBUFFER(<session_id>), aby znaleźć ostatnią instrukcję przesłaną przez sesję. Podobne wyniki można zwrócić za pomocą funkcji zarządzania dynamicznego (DMF) sys.dm_exec_input_buffer w zestawie wyników, który jest łatwiejszy do odpytywania 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 sys.dm_exec_requests i odwołaj się do kolumny blocking_session_id. Gdy blocking_session_id = 0, sesja nie jest blokowana. Chociaż sys.dm_exec_requests wymienia tylko obecnie wykonywane żądania, dowolne połączenie (aktywne lub nie) zostanie wyświetlone w sys.dm_exec_sessions. Skompiluj na tym typowe sprzężenia między sys.dm_exec_requests i sys.dm_exec_sessions w następnym zapytaniu. Należy pamiętać, że aby zapytanie było zwracane przez sys.dm_exec_requests, musi być aktywnie wykonywane przy użyciu SQL Server.

• Uruchom to przykładowe zapytanie, aby znaleźć aktywne wykonywanie zapytań i bieżącego SQL tekstu wsadowego lub wejściowego tekstu buforu przy użyciu DMV sys.dm_exec_sql_text lub sys.dm_exec_input_buffer. Jeśli dane zwracane przez kolumnę textsys.dm_exec_sql_text mają wartość NULL, zapytanie nie jest obecnie wykonywane. W takim przypadku kolumna event_infosys.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ę session_ids zablokowanych według 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 to bardziej zaawansowane przykładowe zapytanie dostarczone przez pomoc techniczną Microsoft, aby zidentyfikować główny łańcuch blokowania wielu sesji, w tym tekst zapytania sesji biorących udział w łańcuchu 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 przechwytywać 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 SQL Server. Spowoduje to zwrócenie informacji o tym, co dzieje się obecnie z żądaniem SQL wait_type. Podobnie jak sys.dm_exec_requests, tylko aktywne żądania są zwracane przez sys.dm_os_waiting_tasks.

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

Ze względu na INNER JOIN w sys.dm_os_waiting_tasks, następujące zapytanie ogranicza dane wyjściowe z sys.dm_tran_locks tylko do obecnie 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 trwałego blokowania lub trendów. Narzędzie przejścia do CSS w celu rozwiązywania takich problemów używa modułu zbierającego dane PSSDiag. To narzędzie używa „Statystyk wydajności SQL Server” do zbierania wyników z DMV, do których odwołuje się powyżej, w czasie. Ponieważ to narzędzie stale ewoluuje, zapoznaj się z najnowszą publiczną wersją DiagManager on GitHub.

Zbieranie informacji ze zdarzeń rozszerzonych

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

Istnieją dwa sposoby przechwytywania śledzenia w SQL Server; Zdarzenia rozszerzone (XEvents) i Śledzenie profilera. Jednak śledzenie SQL przy użyciu SQL Server Profiler jest przestarzałe. XEvents to nowsza, doskonała platforma do śledzenia, która umożliwia większą wszechstronność i mniejszy wpływ na obserwowany system, a jej interfejs jest zintegrowany z SSMS.

Wstępnie wykonane rozszerzone sesje zdarzeń są gotowe do uruchomienia w SSMS oraz są wymienione w Eksploratorze obiektów w menu XEvent Profiler. Aby uzyskać więcej informacji, zobacz XEvent Profiler: Możesz również utworzyć własne niestandardowe sesje zdarzeń rozszerzonych w SSMS, zobacz Kreator nowej sesji zdarzeń rozszerzonych. W przypadku rozwiązywania problemów z blokowaniem zwykle przechwytujemy:

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

**Aby skonfigurować próg i częstotliwość generowania raportów zablokowanych 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 raporty zablokowanych procesów.

• Ostrzeżenia kategorii:

  • Hash_warning
  • Missing_column_statistics
  • Missing_join_predicate
  • Sort_warning

• Wykonanie kategorii:

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• Blokada kategorii

  • Lock_deadlock

• Sesja kategorii

  • Existing_connection
  • Logowanie
  • Wylogowanie

Identyfikowanie i rozwiązywanie typowych scenariuszy blokowania

Badając powyższe informacje, można określić przyczynę większości problemów z blokowaniem. W dalszej części tego artykułu omówiono sposób używania tych informacji do identyfikowania i rozwiązywania niektórych typowych scenariuszy blokowania. W tej dyskusji założono, że użyto skryptów blokujących (o których mowa wcześniej) do przechwytywania informacji o blokujących identyfikatorach SPID i przechwycono działanie aplikacji przy użyciu sesji XEvent.

Analizowanie danych o blokowaniu

• Sprawdź dane wyjściowe widoków DMV sys.dm_exec_requests i sys.dm_exec_sessions, aby określić główne łańcuchy blokujące, używając blocking_these i session_id. Pozwoli to w najdokładniejszy sposób określić, które żądania są blokowane i które są blokujące. Przyjrzyj się potem zablokowanym i blokującym sesjom. Czy istnieje wspólny lub główny element łańcucha blokującego? Prawdopodobnie mają wspólną tabelę, a co najmniej jedna sesja biorąca udział w łańcuchu blokowania wykonuje operację zapisu.

• Sprawdź dane wyjściowe widoków DMV sys.dm_exec_requests i sys.dm_exec_sessions pod kątem informacji o identyfikatorach SPID na czele łańcucha blokowania. Poszukaj następujących kolumn:

  • sys.dm_exec_requests.status
    Ta kolumna przedstawia stan określonego żądania. Zazwyczaj stan uśpienia wskazuje, że identyfikator SPID zakończył wykonywanie i czeka, aż aplikacja prześle kolejne zapytanie lub partię. Stan uruchamiania lub uruchomienia wskazuje, że identyfikator SPID aktualnie przetwarza zapytanie. Poniższa tabela zawiera krótkie wyjaśnienia różnych wartości stanu.

    Stan	Znaczenie
    Tło	Funkcja SPID uruchamia zadanie w tle, takie jak wykrywanie blokad, zapisywanie dzienników lub punkt kontrolny.
    Uśpienie	Identyfikator SPID nie jest obecnie wykonywany. Zwykle oznacza to, że identyfikator SPID oczekuje na polecenie z aplikacji.
    Uruchomienie	Identyfikator SPID jest obecnie uruchomiony w harmonogramie.
    Uruchamianie	Identyfikator SPID znajduje się w kolejki do uruchomienia harmonogramu i czeka, aby uzyskać czas harmonogramu.
    Zawieszenie	Identyfikator 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, identyfikator SPID znajduje się w ramach otwartej transakcji i może zawierać blokady nabyte przez dowolną instrukcję w ramach transakcji.

  • 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, identyfikator SPID znajduje się w ramach otwartej transakcji i może zawierać blokady nabyte przez dowolną instrukcję w ramach transakcji.

  • sys.dm_exec_requests.wait_type, wait_time i last_wait_type
    Jeśli sys.dm_exec_requests.wait_type ma wartość NULL, żądanie nie czeka obecnie na nic, a wartość last_wait_type wskazuje ostatnie wait_type napotkane przez żądanie. Aby uzyskać więcej informacji o sys.dm_os_wait_stats i opis najbardziej typowych przykładów oczekiwania, zobacz sys.dm_os_wait_stats. Wartość wait_time może służyć do określenia, czy żądanie czyni postęp. Gdy zapytanie względem tabeli sys.dm_exec_requests zwraca wartość w kolumnie wait_time mniejszą niż wartość wait_time z poprzedniego zapytania sys.dm_exec_requests, oznacza to, że poprzednia blokada została uzyskana i wydana, a teraz czeka na nową blokadę (przy założeniu, że nie ma wartości zero wait_time). Można to sprawdzić, porównując dane wyjściowe wait_resource między sys.dm_exec_requests, które wyświetlają zasób, na który oczekuje żądanie.

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

    Zasób	Formatowanie	Przykład	Objaśnienie
    Tabela	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	W tym przypadku identyfikator bazy danych 5 jest przykładową bazą danych pubów, 261575970 object_id jest tabelą tytułów, a 1 jest indeksem klastrowanym.
    Strona	DatabaseID:FileID:PageID	PAGE: 5:1:104	W tym 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 zarządzania dynamicznego sys.dm_db_page_info, przekazując identyfikator DatabaseID, FileId, PageId z wait_resource.
    Klucz	DatabaseID:Hobt_id (wartość skrótu dla klucza indeksu)	KEY: 5:72057594044284928 (3300a4f361aa)	W tym przypadku identyfikator bazy danych 5 to puby, Hobt_ID 72057594044284928 odpowiada index_id 2 dla object_id 261575970 (tabela tytułów). Użyj widoku katalogu sys.partitions, aby skojarzyć hobt_id z określonymi index_id i object_id. Nie ma sposobu na usunięcie skrótu klucza indeksu z określoną wartością klucza.
    Wiersz	DatabaseID:FileID:PageID:Slot(row)	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 pozycję wiersza na stronie.
    Kompilowanie	DatabaseID:FileID:PageID:Slot(row)	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 pozycję wiersza na stronie.

  • sys.dm_tran_active_transactionsDMV sys.dm_tran_active_transactions zawiera dane dotyczące otwartych transakcji, które mogą być przyłączone do innych widoków DMV, aby uzyskać pełny obraz transakcji oczekujących na zatwierdzenie lub wycofanie. Użyj następującego zapytania, aby zwrócić informacje o otwartych transakcjach przyłączonych do innych widoków DMV, w tym sys.dm_tran_session_transactions. Rozważ bieżący stan transakcji, transaction_begin_time i inne dane sytuacyjne, aby ocenić, czy 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 źródło problemu. Ich przydatność różni się w zależności od okoliczności problemu. Na przykład można określić, czy problem występuje tylko z niektórych klientów (hostname), w niektórych bibliotekach sieciowych (client_interface_name), gdy ostatnia partia przesłana przez identyfikator SPID była last_request_start_time w sys.dm_exec_sessions, jak długo żądanie było uruchomione za pomocą start_time w sys.dm_exec_requests i tak dalej.

Typowe scenariusze blokowania

Poniższa tabela mapuje typowe objawy oraz ich prawdopodobne przyczyny.

Kolumny wait_type, open_transaction_count i 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 rozwiązane samodzielnie, czy też sesja powinna zostać zabita za pośrednictwem KILL polecenia . Aby uzyskać więcej informacji, zobacz KILL (Transact-SQL).

Szczegółowe scenariusze blokowania

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

W tym scenariuszu aktywnie uruchomione zapytanie uzyskało blokady i blokady te nie są zwalniane (ma to wpływ na poziom izolacji transakcji). Dlatego inne sesje będą czekać na blokady do momentu ich wydania.

Rozwiązanie:

Rozwiązaniem tego typu problemu z blokowaniem jest wyszukanie sposobów optymalizacji zapytania. Ta klasa problemu z blokowaniem może być problemem z wydajnością i wymagać jego realizacji jako takiej. Aby uzyskać informacje na temat rozwiązywania problemów z określonym wolno działającym zapytaniem, zobacz Jak rozwiązywać problemy z wolno działającymi zapytaniami w systemie SQL Server. Aby uzyskać więcej informacji, zobacz Monitorowanie i dostrajanie pod kątem wydajności.

Raporty wbudowane w program SSMS z magazynu zapytań (wprowadzone w systemie SQL Server 2016 r.) są również zalecanym i cennym narzędziem do identyfikowania najbardziej obciążających zapytań i nieoptymalnych planów wykonywania.

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

Scenariusz 2. Blokowanie spowodowane uśpionym identyfikatorem SPID, który ma niezatwierdzone transakcje

Ten typ blokowania może być często identyfikowany przez identyfikator SPID, który znajduje się w stanie uśpienia lub oczekuje na polecenie, ale którego poziom zagnieżdżania transakcji (@@TRANCOUNTopen_transaction_count z sys.dm_exec_requests) jest większy niż zero. Taka sytuacja może wystąpić, jeśli w aplikacji wystąpi przekroczenie limitu czasu zapytania lub anulowanie bez wystawiania wymaganej liczby instrukcji ROLLBACK i/lub COMMIT. Gdy identyfikator SPID otrzyma limit czasu zapytania lub dokona anulowania, zakończy bieżące zapytanie i partię, ale nie wycofa automatycznie ani nie zatwierdzi transakcji. Odpowiedzialna jest za to aplikacja, ponieważ system SQL Server nie może zakładać, że cała transakcja musi zostać wycofana z powodu anulowania pojedynczego zapytania. Przekroczenie limitu czasu zapytania lub anulowanie zostanie wyświetlone jako zdarzenie sygnału UWAGA dla identyfikatora SPID w sesji zdarzeń rozszerzonych.

Aby zademonstrować niezatwierdzone jawne transakcje, 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 wynosi jeden. Wszystkie blokady nabyte w transakcji są przechowywane do momentu zatwierdzenia lub wycofania transakcji. Jeśli aplikacje jawnie otwierają i zatwierdzają transakcje, błąd komunikacji lub inny może spowodować, że sesja i jej transakcja pozostaną w stanie otwartym.

Użyj skryptu ze wcześniejszej części tego artykułu opartego na sys.dm_tran_active_transactions, aby zidentyfikować obecnie niezatwierdzone transakcje w całym wystąpieniu.

Rozwiązania:

• Ponadto ten rodzaj problemu z blokowaniem może być problemem z wydajnością i jako taki powinien być rozpatrywany. Jeśli czas wykonywania zapytania może zostać zmniejszony, nie nastąpi przekroczenie limitu czasu zapytania lub anulowanie. Ważne jest, aby aplikacja mogła obsługiwać scenariusze przekroczenia limitu czasu lub anulowania, jeśli takie wystąpią, ale można również skorzystać z badania wydajności zapytania.

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

  • W programie obsługi błędów aplikacji klienckiej wykonaj polecenie IF @@TRANCOUNT > 0 ROLLBACK TRAN po każdym błędzie, nawet jeśli aplikacja kliencka nie wierzy, ż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 poza bieżącą instrukcją, więc nawet jeśli procedura posiada logikę pozwalającą na sprawdzenie IF @@ERROR <> 0 i przerwanie transakcji, to w takich przypadkach kod wycofujący nie zostanie wykonany.

  • Jeśli buforowanie jest używane w aplikacji, która otwiera połączenie i uruchamia kilka zapytań przed zwolnieniem połączenia z powrotem do puli, np. w aplikacji internetowej, tymczasowe wyłączenie łączenia może pomóc złagodzić problem do czasu zmodyfikowania aplikacji klienckiej w celu odpowiedniej obsługi błędów. Wyłączenie puli połączeń spowoduje fizyczne rozłączenie połączenia z systemem SQL Server, co spowoduje wycofanie przez serwer wszelkich otwartych transakcji.

  • Użyj SET XACT_ABORT ON dla połączenia lub w procedurach składowanych, które rozpoczynają transakcje i nie są czyszczone po wystąpieniu błędu. W przypadku błędu w czasie wykonywania ustawienie to spowoduje przerwanie wszelkich otwartych transakcji i zwrócenie kontroli do klienta. Aby uzyskać więcej informacji, zobacz SET XACT_ABORT (Transact-SQL).

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

Po wysłaniu zapytania na serwer wszystkie aplikacje muszą natychmiast pobrać wszystkie wiersze wyników do ukończenia. Jeśli aplikacja nie pobierze wszystkich wierszy wyniku, na tabelach mogą zostać pozostawione blokady, blokujące innych użytkowników. Jeśli używasz aplikacji, która w sposób jawny wysyła polecenia SQL do serwera, musi ona pobierać wszystkie wiersze wyniku. Jeśli tak nie jest (i jeśli nie można jej tak skonfigurować), rozwiązanie problemu blokowania może okazać się niemożliwe. Aby uniknąć tego problemu, można ograniczyć działanie niewłaściwie zachowujących się aplikacji do bazy danych raportowania lub wspomagania decyzji, oddzielonej od głównej bazy danych OLTP.

Rozwiązanie:

Aby pobrać wszystkie wiersze wyniku do ukończenia, należy ponownie napisać aplikację. Nie wyklucza to użycia funkcji OFFSET i FETCH w klauzuli ORDER BY zapytania do wykonywania stronicowania po stronie serwera.

Scenariusz 4. Blokowanie spowodowane rozproszonym zakleszczeniem klienta/serwera

W przeciwieństwie do konwencjonalnego zakleszczenia rozproszone 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ą systemu SQL Server. Druga strona zakleszczenia znajduje się na poziomie aplikacji klienckiej, nad którą system SQL Server nie ma kontroli. Poniżej przedstawiono dwie sekcje, w jaki sposób może dojść do takiej sytuacji, oraz możliwe sposoby uniknięcia jej ze strony aplikacji.

Przykład A. Zakleszczenie rozproszone klienta/serwera z pojedynczym 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. Uwaga, użyty tutaj termin dbproc odnosi się do struktury połączeń 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 przypadku pokazanym powyżej wątek pojedynczej aplikacji klienckiej ma dwa otwarte połączenia. Asynchronicznie przesyła operację SQL w dbproc1. Oznacza to, że nie czeka na wywołanie, aby powrócić przed kontynuowaniem. Następnie aplikacja przesyła kolejną operację SQL w dbproc2 i oczekuje na wyniki rozpoczęcia przetwarzania zwróconych danych. Gdy dane zaczynają wracać (niezależnie od tego, który dbproc odpowie jako pierwszy — przyjmijmy, że jest to dbproc1), aplikacja przetwarza do końca wszystkie dane zwrócone przez ten dbproc. Pobiera wyniki z dbproc1 do momentu zablokowania identyfikatora SPID1 na blokadzie przechowywanej przez spid2 (ponieważ dwa zapytania są uruchomione asynchronicznie na serwerze). W tym momencie dbproc1 będzie czekać przez czas nieokreślony na więcej danych. Funkcja SPID2 nie jest zablokowana na blokadzie, ale próbuje wysłać dane do swojego klienta, dbproc2. Jednak dbproc2 jest skutecznie blokowany w dbproc1 w warstwie aplikacji, ponieważ pojedynczy wątek wykonywania dla aplikacji jest używany przez dbproc1. Powoduje to zakleszczenie, którego system SQL Server nie może wykryć ani rozwiązać, ponieważ tylko jeden z zaangażowanych zasobów jest zasobem systemu 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 występować nadal, 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 tym wyjątkiem, że dbproc2 i SPID2 uruchamiają instrukcję SELECT z zamiarem wykonywania przetwarzania wiersza w czasie i przekazywania każdego wiersza za pośrednictwem buforu do dbproc1 dla instrukcji INSERT, UPDATE lub DELETE w tej samej tabeli. Ostatecznie SPID1 (wykonujący INSERT, UPDATE lub DELETE) staje się zablokowany na blokadzie przechowywanej przez SPID2 (wykonującego SELECT). SPID2 zapisuje wiersz wyniku do klienta dbproc2. Następnie dbproc2 próbuje przekazać wiersz w buforze do dbproc1, ale stwierdza, że dbproc1 jest zajęty (jest zablokowany, oczekując na zakończenie bieżącego INSERT przez SPID1, które jest zablokowany na SPID2). W tym momencie dbproc2 jest blokowany w warstwie aplikacji przez dbproc1, którego identyfikator SPID (SPID1) jest blokowany na poziomie bazy danych przez SPID2. Powoduje to zakleszczenie, którego system SQL Server nie może wykryć ani rozwiązać, ponieważ tylko jeden z zaangażowanych zasobów jest zasobem systemu SQL Server.

Oba przykłady A i B to podstawowe problemy, o których muszą pamiętać deweloperzy aplikacji. Deweloperzy muszą kodować aplikacje tak, aby odpowiednio radziły sobie z tymi przypadkami.

Rozwiązanie:

Jeśli podano limit czasu zapytania, to w przypadku wystąpienia zakleszczenia rozproszonego zostanie on przerwany po przekroczeniu limitu czasu. Zapoznaj się z dokumentacją dostawcy połączeń, aby uzyskać więcej informacji na temat korzystania z limitu czasu zapytania.

Scenariusz 5. Blokowanie spowodowane przez sesję w stanie wycofania

Zapytanie o modyfikację danych, które zostało zlikwidowane lub anulowane poza transakcją zdefiniowaną przez użytkownika, zostanie cofnięte. Może to również wystąpić jako efekt uboczny rozłączenia sesji sieci klienta lub wybrania żądania jako ofiary zakleszczenia. Można to często zidentyfikować, obserwując dane wyjściowe elementu sys.dm_exec_requests, co może wskazywać na wartość ROLLBACK command, a kolumna percent_complete może pokazywać postęp.

Zapytanie o modyfikację danych, które zostało zlikwidowane lub anulowane poza transakcją zdefiniowaną przez użytkownika, zostanie cofnięte. Sytuacja taka może również wystąpić jako efekt uboczny ponownego uruchomienia komputera klienckiego i rozłączenia jego sesji sieciowej. Podobnie zapytanie wybrane jako ofiara zakleszczenia zostanie wycofane. Zapytania modyfikacji danych często nie można cofnąć szybciej niż początkowo zastosowano zmiany. Jeśli na przykład instrukcja DELETE, INSERT lub UPDATE była uruchomiona przez godzinę, wycofanie może potrwać co najmniej godzinę. Jest to oczekiwane zachowanie, ponieważ dokonane zmiany muszą zostać wycofane, w przeciwnym razie naruszona zostałaby integralność transakcyjna i fizyczna bazy danych. Ponieważ musi to nastąpić, system SQL Server oznacza identyfikator SPID w stanie złotym lub wycofanym (co oznacza, że nie można go zlikwidować ani wybrać jako ofiary zakleszczenia). Często można to zidentyfikować, obserwując dane wyjściowe elementu sp_who, co może wskazywać polecenie ROLLBACK. Kolumna statussys.dm_exec_sessions będzie wskazywać stan ROLLBACK.

Rozwiązanie:

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

Jeśli instancja zostanie zamknięta w trakcie wykonywania tej operacji, po ponownym uruchomieniu baza danych znajdzie się w trybie odzyskiwania i będzie niedostępna do czasu przetworzenia wszystkich otwartych transakcji. Odzyskiwanie po uruchomieniu trwa zasadniczo tyle samo czasu na każdą transakcję, co odzyskiwanie w czasie pracy. Baza danych jest w tym czasie niedostępna. W związku z tym wymuszanie na serwerze naprawy identyfikatora SPID w stanie wycofywania często przynosi efekt przeciwny do zamierzonego. W systemie SQL Server 2019 z włączonym przyspieszonym odzyskiwaniem bazy danych nie powinno to nastąpić.

Aby uniknąć tej sytuacji, nie należy wykonywać dużych operacji zapisu wsadowego ani operacji tworzenia indeksu lub 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 oddzieloną transakcję

Jest to typowy scenariusz problemu, który pokrywa się częściowo ze scenariuszem 2. Jeśli aplikacja kliencka zostanie zatrzymana, stacja robocza klienta zostanie ponownie uruchomiona lub wystąpi błąd przerywania wsadowego, wszystkie te elementy mogą pozostawić otwartą transakcję. Taka sytuacja może wystąpić, jeśli aplikacja nie wycofa transakcji w blokach aplikacji CATCH lub FINALLY lub jeśli w przeciwnym razie nie poradzi sobie z tą sytuacją.

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

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

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 wynosi jeden. Wszystkie blokady nabyte w transakcji są przechowywane do momentu zatwierdzenia lub wycofania transakcji. Ponieważ partia została już przerwana przez zapytanie, aplikacja, która ją wykonuje, może kontynuować uruchamianie innych zapytań w tej samej sesji bez czyszczenia transakcji, która jest nadal otwarta. Blokada będzie przechowywana do momentu zlikwidowania sesji lub ponownego uruchomienia wystąpienia SQL Server.

Rozwiązania:

• Najlepszym sposobem zapobiegania temu warunkowi jest poprawa obsługi błędów/wyjątków aplikacji, szczególnie w przypadku nieoczekiwanych zakończeń. Upewnij się, że używany jest blok Try-Catch-Finally w kodzie aplikacji, transakcja wycofywana jest w przypadku wyjątku.
• Użyj również SET XACT_ABORT ON dla sesji lub w procedurach składowanych, które rozpoczynają transakcje i nie są czyszczone po wystąpieniu błędu. W przypadku błędu w czasie wykonywania, który spowoduje przerwanie partii, ustawienie to spowoduje automatyczne cofnięcie wszystkich otwartych transakcji i przywrócenie kontroli klientowi. Aby uzyskać więcej informacji, zobacz SET XACT_ABORT (Transact-SQL).
• Aby rozwiązać problem z oddzielonym połączeniem aplikacji klienckiej, która rozłączyła się bez odpowiedniego czyszczenia jej zasobów, można zakończyć pracę SPID za pomocą polecenia KILL. Aby uzyskać informacje, zobacz KILL (Transact-SQL).

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

KILL 99

Zobacz też

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