Rozwiązywanie problemów blokujących występujących w wyniku eskalacji blokady w systemie SQL Server

Artykuł
01/10/2025

Podsumowanie

Eskalacja blokady to proces konwertowania wielu precyzyjnych blokad (takich jak blokady wierszy lub stron) na blokady tabeli. Program Microsoft SQL Server dynamicznie określa, kiedy należy eskalować blokadę. Po podjęciu tej decyzji program SQL Server uwzględnia liczbę blokad przechowywanych w określonym skanowaniu, liczbę blokad przechowywanych przez całą transakcję oraz pamięć używaną do blokad w systemie jako całości. Zazwyczaj domyślne zachowanie programu SQL Server powoduje, że eskalacja blokady występuje tylko wtedy, gdy poprawi wydajność lub gdy trzeba zmniejszyć nadmierną ilość pamięci blokady systemu na bardziej rozsądny poziom. Jednak niektóre projekty aplikacji lub zapytań mogą wyzwalać eskalację blokady w czasie, gdy ta akcja nie jest pożądana, a eskalowana blokada tabeli może blokować innych użytkowników. W tym artykule omówiono sposób określania, czy eskalacja blokady powoduje blokowanie i jak radzić sobie z niepożądanym eskalacją blokady.

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 323630

Określanie, czy eskalacja blokady powoduje blokowanie

Eskalacja blokady nie powoduje większości problemów z blokowaniem. Aby określić, czy eskalacja blokady występuje w czasie, w którym występują problemy z blokowaniem, uruchom sesję zdarzeń rozszerzonych, która obejmuje lock_escalation zdarzenie. Jeśli nie widzisz żadnych lock_escalation zdarzeń, eskalacja blokady nie występuje na serwerze, a informacje zawarte w tym artykule nie dotyczą Twojej sytuacji.

Jeśli występuje eskalacja blokady, sprawdź, czy eskalacja blokady tabeli blokuje innych użytkowników.

Aby uzyskać więcej informacji na temat identyfikowania blokady nagłówka i zasobu blokady przechowywanego przez blokowanie nagłówka i blokującego inne identyfikatory procesów serwera (SPID), zobacz INF: Understanding and rozwiązywanie problemów z blokowaniem programu SQL Server.

Jeśli blokada blokująca innych użytkowników jest niczym innym niż blokada TAB (na poziomie tabeli), która ma tryb blokady S (udostępniony) lub X (wyłączny), eskalacja blokady nie jest problemem. W szczególności, jeśli blokada TAB jest blokadą intencji (taką jak tryb blokady IS, IU lub IX), nie jest to spowodowane eskalacją blokady. Jeśli problemy z blokowaniem nie są spowodowane eskalacją blokady, zobacz INF: Understanding and troubleshooting SQL Server blocking problems troubleshooting steps (Omówienie i rozwiązywanie problemów z blokowaniem programu SQL Server).

Zapobieganie eskalacji blokady

Najprostszą i najbezpieczniejszą metodą zapobiegania eskalacji blokady jest utrzymywanie krótkich transakcji i zmniejszenie śladu blokady kosztownych zapytań, dzięki czemu progi eskalacji blokady nie zostaną przekroczone. Istnieje kilka metod osiągnięcia tego celu, w tym następujące strategie:

Podziel duże operacje wsadowe na kilka mniejszych operacji. Na przykład uruchomisz następujące zapytanie, aby usunąć 100 000 starych rekordów z tabeli inspekcji, a następnie określić, że zapytanie spowodowało eskalację blokady, która zablokowała innych użytkowników:
```
DELETE FROM LogMessages WHERE LogDate < '20020102';
```
Usuwając te rekordy kilkaset naraz, można znacznie zmniejszyć liczbę blokad, które gromadzą się na transakcję. Zapobiegnie to eskalacji blokady. Na przykład uruchamiasz następujące zapytanie:
```
DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;
```
Zmniejsz ślad blokady zapytania, aby zapytanie było tak wydajne, jak to możliwe. Duże skanowania lub wiele odnośników zakładek może zwiększyć prawdopodobieństwo eskalacji blokady. Ponadto zwiększają one prawdopodobieństwo zakleszczenia i niekorzystnie wpływają na współbieżność i wydajność. Po zidentyfikowaniu zapytania, które powoduje eskalację blokady, poszukaj możliwości utworzenia nowych indeksów lub dodania kolumn do istniejącego indeksu w celu usunięcia skanów indeksów lub tabel oraz zmaksymalizowania wydajności wyszukiwania indeksu. Przejrzyj plan wykonywania i potencjalnie utwórz nowe indeksy nieklastrowane, aby zwiększyć wydajność zapytań. Aby uzyskać więcej informacji, zobacz Sql Server Index Architecture and Design Guide (Architektura indeksu i projektowanie programu SQL Server).

Jednym z celów tej optymalizacji jest zapewnienie, że indeks będzie zwracać jak najmniej wierszy, aby zminimalizować koszt wyszukiwania zakładek (maksymalizuj wybór indeksu dla zapytania). Jeśli program SQL Server szacuje, że operator logiczny Odnośnik zakładki zwróci wiele wierszy, może użyć PREFETCH klauzuli , aby wyszukać zakładkę. Jeśli program SQL Server używa PREFETCH wyszukiwania zakładek, musi zwiększyć poziom izolacji transakcji części zapytania w celu "powtarzalnego odczytu" dla części zapytania. Oznacza to, że to, co może wyglądać jak SELECT instrukcja na poziomie izolacji "zatwierdzone do odczytu", może uzyskać wiele tysięcy blokad kluczy (zarówno w indeksie klastrowanym, jak i w jednym indeksie nieklastrowanym). Może to spowodować przekroczenie progów eskalacji blokady przez takie zapytanie. Jest to szczególnie ważne, jeśli okaże się, że eskalowana blokada jest udostępnioną blokadą tabeli, chociaż nie są one często widoczne na domyślnym poziomie izolacji "read-committed". Jeśli klauzula Lookup Lookup WITH PREFETCH zakładki powoduje eskalację, rozważ dodanie kolumn do indeksu nieklastrowanego wyświetlanego w funkcji Wyszukiwania indeksu lub operatora logicznego Skanowanie indeksu poniżej operatora logicznego Odnośnik zakładki w planie zapytania. Może być możliwe utworzenie indeksu obejmującego (indeks zawierający wszystkie kolumny w tabeli użytej w zapytaniu) lub co najmniej indeks, który obejmuje kolumny używane do kryteriów sprzężenia lub w klauzuli WHERE, jeśli niepraktyczne jest uwzględnianie wszystkiego na liście "wybierz kolumnę".

Sprzężenie zagnieżdżonej pętli może również używać metody PREFETCH, co powoduje takie samo zachowanie blokujące.
Nie można eskalować blokady, jeśli inny identyfikator SPID obecnie przechowuje niezgodną blokadę tabeli. Eskalacja blokady zawsze jest eskalowania do blokady tabeli i nigdy nie jest blokowana przez stronę. Ponadto jeśli próba eskalacji blokady nie powiedzie się, ponieważ inny identyfikator SPID przechowuje niezgodną blokadę TAB, zapytanie, które próbowało eskalacji, nie blokuje się podczas oczekiwania na blokadę TAB. Zamiast tego nadal uzyskuje blokady na oryginalnym, bardziej szczegółowym poziomie (wiersz, klucz lub strona), okresowo podejmowania dodatkowych prób eskalacji. W związku z tym jedną z metod zapobiegania eskalacji blokady dla określonej tabeli jest uzyskanie i zablokowanie blokady na innym połączeniu, które nie jest zgodne z eskalowany typ blokady. Blokada IX (wyłączna intencja) na poziomie tabeli nie blokuje żadnych wierszy ani stron, ale nadal nie jest zgodna z eskalowaną blokadą TAB S (udostępnioną) lub X (wyłączną). Załóżmy na przykład, że należy uruchomić zadanie wsadowe, które modyfikuje wiele wierszy w tabeli mytable i które spowodowało blokowanie z powodu eskalacji blokady. Jeśli to zadanie zawsze kończy się w mniej niż jedną godzinę, możesz utworzyć zadanie języka Transact-SQL zawierające następujący kod i zaplanować uruchomienie nowego zadania kilka minut przed godziną rozpoczęcia zadania wsadowego:
```
BEGIN TRAN;
SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;
```
To zapytanie uzyskuje i przechowuje blokadę IX w tabeli mytable przez jedną godzinę. Zapobiega to eskalacji blokady tabeli w tym czasie. Ta partia nie modyfikuje żadnych danych ani nie blokuje innych zapytań (chyba że inne zapytanie wymusza blokadę tabeli przy użyciu wskazówki TABLOCK lub jeśli administrator wyłączył strony lub blokady wierszy przy użyciu funkcji ALTER INDEX).
Wyeliminuj eskalację blokady spowodowaną brakiem sargability— terminem relacyjnej bazy danych używanym do opisania, czy zapytanie może używać indeksów dla predykatów i kolumn sprzężenia. Aby uzyskać więcej informacji na temat sargability, zobacz Inside Design Guide Query Considerations (Zagadnienia dotyczące zapytań w przewodniku projektowania wewnętrznego). Na przykład dość proste zapytanie, które nie wydaje się żądać wielu wierszy — lub być może jednego wiersza — nadal może skończyć się skanowaniem całej tabeli/indeksu. Taka sytuacja może wystąpić, jeśli po lewej stronie klauzuli WHERE znajduje się funkcja lub obliczenia. Takie przykłady, które nie mają możliwości SARGability obejmują niejawne lub jawne konwersje typów danych, funkcję systemową ISNULL(), funkcję zdefiniowaną przez użytkownika z kolumną przekazaną jako parametr lub obliczenia w kolumnie, takiej jak WHERE CONVERT(INT, column1) = @a lub WHERE Column1*Column2 = 5. W takich przypadkach zapytanie nie może wyszukać istniejącego indeksu, nawet jeśli zawiera odpowiednie kolumny, ponieważ wszystkie wartości kolumn muszą zostać pobrane jako pierwsze i przekazane do funkcji. Prowadzi to do skanowania całej tabeli lub indeksu i powoduje uzyskanie dużej liczby blokad. W takich okolicznościach program SQL Server może osiągnąć próg eskalacji liczby blokad. Rozwiązaniem jest unikanie używania funkcji względem kolumn w klauzuli WHERE, zapewniając warunki SARGable.

Wyłączanie eskalacji blokady

Chociaż można wyłączyć eskalację blokady w programie SQL Server, nie zalecamy jej. Zamiast tego należy użyć strategii zapobiegania opisanych w sekcji Zapobieganie eskalacji blokady.

Poziom tabeli: możesz wyłączyć eskalację blokady na poziomie tabeli. Zobacz: ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Aby określić, która tabela ma być docelowa, sprawdź zapytania T-SQL. Jeśli to nie jest możliwe, użyj zdarzeń rozszerzonych, włącz zdarzenie lock_escalation i sprawdź kolumnę object_id . Alternatywnie użyj zdarzenia Lock:Escalation i sprawdź kolumnę ObjectID2 przy użyciu programu SQL Profiler.
Poziom wystąpienia: możesz wyłączyć eskalację blokady, włączając flagi śledzenia 1211 lub 1224 lub oba dla wystąpienia. Jednak te flagi śledzenia wyłączają globalnie wszystkie eskalacje blokady w wystąpieniu programu SQL Server. Eskalacja blokady służy przydatnemu celowi w programie SQL Server, maksymalizując wydajność zapytań, które w przeciwnym razie spowalniają obciążenie związane z pozyskiwaniem i zwalnianiem kilku tysięcy blokad. Eskalacja blokady pomaga również zminimalizować wymaganą pamięć, aby śledzić blokady. Pamięć, którą program SQL Server może dynamicznie przydzielać dla struktur blokady, jest skończona. W związku z tym, jeśli wyłączysz eskalację blokady, a pamięć blokady będzie wystarczająco duża, każda próba przydzielenia dodatkowych blokad dla dowolnego zapytania może zakończyć się niepowodzeniem i wygenerować następujący wpis błędu:

Błąd: 1204, Ważność: 19, Stan: 1
W tej chwili program SQL Server nie może uzyskać zasobu BLOKADY. Uruchom ponownie instrukcję, gdy jest mniej aktywnych użytkowników lub poproś administratora systemu o sprawdzenie konfiguracji blokady i pamięci programu SQL Server.

Uwaga 16.

Gdy wystąpi błąd 1204, zatrzymuje przetwarzanie bieżącej instrukcji i powoduje wycofanie aktywnej transakcji. Wycofanie może blokować użytkowników lub powodować długi czas odzyskiwania bazy danych w przypadku ponownego uruchomienia usługi PROGRAMU SQL Server.

Możesz dodać te flagi śledzenia (-T1211 lub -T1224) przy użyciu programu SQL Server Configuration Manager. Aby nowy parametr uruchamiania został zastosowany, należy ponownie uruchomić usługę PROGRAMU SQL Server. Jeśli uruchomisz DBCC TRACEON (1211, -1) zapytanie lub DBCC TRACEON (1224, -1) , flaga śledzenia będzie obowiązywać natychmiast.
Jeśli jednak nie dodasz parametru -T1211 lub -T1224 jako parametru uruchamiania, efekt DBCC TRACEON polecenia zostanie utracony po ponownym uruchomieniu usługi PROGRAMU SQL Server. Włączenie flagi śledzenia zapobiega wszelkim przyszłym eskalacjom blokady, ale nie odwraca żadnych eskalacji blokad, które już wystąpiły w aktywnej transakcji.

Jeśli używasz wskazówki dotyczącej blokady, takiej jak ROWLOCK, zmienia to tylko początkowy plan blokady. Wskazówki dotyczące blokady nie uniemożliwiają eskalacji blokady.

Progi eskalacji blokady

Eskalacja blokady może wystąpić w jednym z następujących warunków:

Osiągnięto próg pamięci — osiągnięto próg pamięci wynoszące 40 procent pamięci blokady. Gdy pamięć blokady przekracza 24 procent puli, można wyzwolić eskalację blokady. Pamięć blokady jest ograniczona do 60 procent widocznej puli. Próg eskalacji blokady jest ustawiony na 40% pamięci blokady. Jest to 40 procent z 60 procent puli lub 24 procent. Jeśli pamięć blokady przekroczy limit 60 procent (jest to znacznie bardziej prawdopodobne, jeśli eskalacja blokady jest wyłączona), wszystkie próby przydzielenia dodatkowych blokad kończą się niepowodzeniem i 1204 są generowane błędy.
Próg blokady jest osiągany — po sprawdzeniu progu pamięci liczba blokad uzyskanych w bieżącej tabeli lub indeksie jest oceniana. Jeśli liczba przekroczy 5000, zostanie wyzwolona eskalacja blokady.

Aby zrozumieć, który próg został osiągnięty, użyj zdarzeń rozszerzonych, włącz zdarzenie lock_escalation i sprawdź kolumny escalated_lock_count i escalation_cause . Alternatywnie należy użyć zdarzenia Lock:Escalation i zbadać EventSubClass wartość, gdzie "0 - LOCK_THRESHOLD" wskazuje, że instrukcja przekroczyła próg blokady, a "1 — MEMORY_THRESHOLD" wskazuje, że instrukcja przekroczyła próg pamięci. Sprawdź również kolumny IntegerData i IntegerData2 .

Zalecenia

Metody omówione w sekcji Zapobieganie eskalacji blokady są lepszymi opcjami niż wyłączanie eskalacji na poziomie tabeli lub wystąpienia. Ponadto metody prewencyjne zwykle dają lepszą wydajność zapytania niż wyłączanie eskalacji blokady. Firma Microsoft zaleca włączenie tej flagi śledzenia tylko w celu ograniczenia poważnego blokowania spowodowanego eskalacją blokady, podczas gdy są badane inne opcje, takie jak te omówione w tym artykule.

Udostępnij za pośrednictwem