Rozwiązywanie problemów z blokowaniem spowodowanych eskalacją blokady w SQL Server

Artykuł
04/03/2023

Podsumowanie

Eskalacja blokady to proces konwertowania wielu precyzyjnych blokad (takich jak blokady wierszy lub stron) na blokady tabeli. Firma Microsoft SQL Server dynamicznie określa, kiedy należy zablokować eskalację. Podczas podejmowania tej decyzji SQL Server uwzględnia liczbę blokad przechowywanych podczas określonego skanowania, liczbę blokad przechowywanych przez całą transakcję oraz pamięć używaną do blokad w całym systemie. Zazwyczaj domyślne zachowanie SQL Server powoduje eskalację blokady tylko wtedy, gdy zwiększy to wydajność lub gdy należy zmniejszyć nadmierną ilość pamięci blokady systemu do bardziej rozsądnego poziomu. 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żądaną 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 blokujących. Aby określić, czy eskalacja blokady występuje w czasie, gdy 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 ma miejsca na serwerze, a informacje zawarte w tym artykule nie mają zastosowania do Twojej sytuacji.

Jeśli nastąpi eskalacja blokady, sprawdź, czy eskalowana blokada tabeli blokuje innych użytkowników.

Aby uzyskać więcej informacji na temat identyfikowania blokady głowy i zasobu blokady przechowywanego przez bloker główny, który blokuje inne identyfikatory procesów serwera (SPID), zobacz INF: Understanding and resolving SQL Server blocking problems (INF: Understanding and resolving SQL Server blocking problems( Inf: Understanding and resolving SQL Server blocking problems( Inf: Understanding and resolving SQL Server blocking problems).

Jeśli blokada blokująca innych użytkowników jest czymś innym niż blokada TAB (na poziomie tabeli), która ma tryb blokady S (udostępniony) lub X (wyłączność), eskalacja blokady nie jest problemem. W szczególności jeśli blokada TAB jest blokadą intencji (na przykład trybem 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 resolving SQL Server blocking problems troubleshooting steps (Inf: Opis i rozwiązywanie SQL Server blokowania problemów).

Zapobieganie eskalacji blokady

Najprostszą i najbezpieczniejszą metodą zapobiegania eskalacji blokady jest utrzymywanie krótkich transakcji i zmniejszanie ś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 uruchom następujące zapytanie, aby usunąć ponad 100 000 starych rekordów z tabeli inspekcji, a następnie ustalisz, ż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 na raz, można znacznie zmniejszyć liczbę blokad, które gromadzą się na transakcji. Zapobiegnie to eskalacji blokady. Na przykład uruchom 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, czyniąc zapytanie tak wydajnym, jak to tylko możliwe. Duże skanowania lub wiele wyszukiwań zakładek może zwiększyć prawdopodobieństwo eskalacji blokady. Ponadto zwiększają one prawdopodobieństwo zakleszczenia i negatywnie 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 indeksu lub tabeli oraz zmaksymalizowania wydajności wyszukiwania indeksu. Przejrzyj plan wykonywania i potencjalnie utwórz nowe indeksy nieklastruowane, aby zwiększyć wydajność zapytań. Aby uzyskać więcej informacji, zobacz SQL Server Index Architecture and Design Guide (Przewodnik po architekturze i projektowaniu indeksu SQL Server).

Jednym z celów tej optymalizacji jest sprawić, by indeks dążył do zwrócenia jak najmniejszej liczby wierszy, aby zminimalizować koszt wyszukiwania zakładek (zmaksymalizować selektywność indeksu dla zapytania). Jeśli SQL Server szacuje, że operator logiczny wyszukiwania zakładek zwróci wiele wierszy, może użyć PREFETCH klauzuli do wyszukiwania zakładki. Jeśli SQL Server używa PREFETCH wyszukiwania zakładek, musi zwiększyć poziom izolacji transakcji części zapytania do "powtarzalnego odczytu" dla części zapytania. Oznacza to, że instrukcja na poziomie izolacji "zatwierdzona do odczytu" może uzyskać wiele tysięcy blokad kluczy (zarówno w indeksie klastrowanym, jak SELECT i w jednym indeksie nieklastruowanym). Może to spowodować, że takie zapytanie przekroczy progi eskalacji blokady. 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 WITH PREFETCH zakładki powoduje eskalację, rozważ dodanie kolumn do indeksu nieklastruowanego wyświetlanego w wyszukiwaniu indeksu lub operatora logicznego skanowania indeksu poniżej operatora logicznego odnośnika zakładki w planie zapytania. Może być możliwe utworzenie indeksu pokrywającego (indeksu zawierającego wszystkie kolumny w tabeli, które były używane w zapytaniu) lub przynajmniej indeksu, który obejmuje kolumny, które były używane do kryteriów sprzężenia, lub klauzuli WHERE, jeśli niepraktyczne jest uwzględnienie wszystkiego na liście "wybierz kolumnę".

Sprzężenie pętli zagnieżdżonej może również używać funkcji PREFETCH, co powoduje takie samo zachowanie blokowania.
Eskalacja blokady nie może wystąpić, jeśli inny identyfikator SPID obecnie przechowuje niezgodną blokadę tabeli. Eskalacja blokady zawsze eskaluje do blokady tabeli i nigdy do blokady strony. Ponadto jeśli próba eskalacji blokady nie powiedzie się, ponieważ inny identyfikator SPID zawiera 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 podejmując dodatkowe próby eskalacji. W związku z tym jedną z metod zapobiegania eskalacji blokady w określonej tabeli jest uzyskanie i zablokowanie innego połączenia, które nie jest zgodne z typem blokady eskalacji. Blokada IX (wyłączność intencji) na poziomie tabeli nie blokuje żadnych wierszy ani stron, ale nadal nie jest zgodna z eskalacją blokady TAB S (udostępnionej) lub X (wyłącznej). Załóżmy na przykład, że należy uruchomić zadanie wsadowe, które modyfikuje wiele wierszy w tabeli mytable, co spowodowało zablokowanie z powodu eskalacji blokady. Jeśli to zadanie zawsze kończy się w czasie krótszym niż godzinę, możesz utworzyć zadanie języka Transact-SQL zawierające następujący kod i zaplanować uruchomienie nowego zadania na kilka minut przed godziną rozpoczęcia zadania wsadowego:
```
BEGIN TRAN;
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;
```
To zapytanie uzyskuje i przechowuje blokadę IX w tabeli mytable przez godzinę. Zapobiega to eskalacji blokady w 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ówek TABLOCK lub jeśli administrator wyłączył blokady stron lub wierszy przy użyciu funkcji ALTER INDEX).
Eliminowanie eskalacji blokady spowodowanej brakiem możliwości sargability — terminem relacyjnej bazy danych używanym do opisywania, czy zapytanie może używać indeksów dla predykatów i kolumn sprzężenia. Aby uzyskać więcej informacji na temat możliwości sargability, zobacz Zagadnienia dotyczące zapytań w przewodniku po projekcie. Na przykład dość proste zapytanie, które nie wydaje się żądać wielu wierszy — a może pojedynczego wiersza — może nadal skanować całą tabelę/indeks. Może się to zdarzyć, 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 typu danych, funkcję systemową ISNULL(), funkcję zdefiniowaną przez użytkownika z kolumną przekazaną jako parametr lub obliczenia w kolumnie, takie jak WHERE CONVERT(INT, column1) = @a lub WHERE Column1*Column2 = 5. W takich przypadkach zapytanie nie może szukać 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 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ż istnieje możliwość wyłączenia eskalacji blokady w SQL Server, nie zalecamy tego. Zamiast tego użyj strategii zapobiegania opisanych w sekcji Zapobieganie eskalacji blokady .

Poziom tabeli: Eskalację blokady można wyłączyć na poziomie tabeli. Zobacz ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Aby określić tabelę docelową, przeanalizuj zapytania języka T-SQL. Jeśli nie jest to 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 jedną z flag śledzenia 1211 lub 1224 lub obie dla wystąpienia. Jednak te flagi śledzenia wyłączają całą eskalację blokady globalnie w wystąpieniu SQL Server. Eskalacja blokady służy użytecznemu celowi w SQL Server przez maksymalizację wydajności zapytań, które w przeciwnym razie zostały spowolnione przez obciążenie związane z uzyskiwaniem i wydawaniem kilku tysięcy blokad. Eskalacja blokady pomaga również zminimalizować wymaganą pamięć, aby śledzić blokady. Pamięć, którą 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 SQL Server nie może uzyskać zasobu LOCK. Uruchom ponownie instrukcję, gdy jest mniej aktywnych użytkowników lub poproś administratora systemu o sprawdzenie konfiguracji blokady SQL Server i pamięci.

Uwaga

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

Możesz dodać te flagi śledzenia (-T1211 lub -T1224) przy użyciu SQL Server Configuration Manager. Aby nowy parametr uruchamiania został uruchomiony, należy ponownie uruchomić usługę SQL Server. Jeśli uruchomisz DBCC TRACEON (1211, -1) zapytanie lub DBCC TRACEON (1224, -1) , flaga śledzenia zostanie natychmiast uruchomiona.
Jeśli jednak nie dodasz parametru -T1211 lub -T1224 jako parametru uruchamiania, efekt DBCC TRACEON polecenia zostanie utracony po ponownym uruchomieniu usługi SQL Server. Włączenie flagi śledzenia uniemożliwia ewentualne przyszłe eskalacje 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, spowoduje to tylko zmianę początkowego planu 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ący 40 procent pamięci blokady. Gdy pamięć blokady przekroczy 24 procent puli buforów, można wyzwolić eskalację blokady. Pamięć blokady jest ograniczona do 60 procent widocznej puli buforów. Próg eskalacji blokady jest ustawiony na 40 procent pamięci blokady. Jest to 40 procent z 60 procent puli buforów, czyli 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.
Osiągnięto próg blokady — po sprawdzeniu progu pamięci oceniana jest liczba blokad uzyskanych w bieżącej tabeli lub indeksie. 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 użyj zdarzenia Lock:Escalation i sprawdź EventSubClass wartość, gdzie wartość "0 - LOCK_THRESHOLD" wskazuje, że instrukcja przekroczyła próg blokady, a wartość "1 — MEMORY_THRESHOLD" wskazuje, że instrukcja przekroczyła próg pamięci. IntegerData Sprawdź również kolumny iIntegerData2.

Zalecenia

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

Udostępnij za pośrednictwem