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

Dotyczy: SQL Server

Oryginalny numer KB: 323630

Podsumowanie

Blokada eskalacji konwertuje wiele precyzyjnych blokad (takich jak blokady wierszy lub stron) na jedną blokadę tabeli. SQL Server wyzwala dynamiczne eskalacje na podstawie liczby blokad lub pamięci blokady, a jego domyślne zachowanie poprawia przepływność w większości obciążeń. Jednak niektóre projekty zapytań lub transakcji powodują eskalację czasami, gdy wynikowa blokada tabeli blokuje innych użytkowników. W tym artykule wyjaśniono, jak określić, czy eskalacja blokady powoduje blokowanie w SQL Server, jak go zapobiec oraz jak (i kiedy) wyłączyć.

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 zawiera 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 blokadę nagłówka i blokującego inne identyfikatory procesów serwera (SPID), zobacz Omówienie i rozwiązywanie problemów z blokowaniem 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 (współużytkowany) 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 spowodowana eskalacją blokady. Jeśli problemy z blokowaniem nie są spowodowane eskalacją blokady, zobacz kroki rozwiązywania problemów z blokowaniem, zobacz Opis i rozwiązywanie SQL Server problemów z blokowaniem.

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 są przekraczane. Użyj co najmniej jednej z poniższych strategii.

Podział dużych operacji na mniejsze transakcje

Podziel duże operacje wsadowe na kilka mniejszych operacji. Na przykład uruchomisz następujące zapytanie, aby usunąć ponad 100 000 starych rekordów z tabeli inspekcji, a następnie stwierdzisz, ż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ę. Takie podejście uniemożliwia eskalację 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;

Optymalizowanie zapytań i indeksów w celu zmniejszenia liczby blokad

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 skanowania indeksu lub tabeli 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.

Blokada zgodnego poziomu tabeli na osobnym połączeniu

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.

Uwaga 16.

Ta technika jest obejściem, a nie ogólnym zaleceniem. Wymaga to przechowywania otwartej transakcji i blokady IX na czas trwania, który chcesz pominąć eskalację. Preferuj ulepszenia zapytania i transakcji opisane wcześniej.

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ę. Ta blokada uniemożliwia eskalację 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ówki TABLOCK lub jeśli administrator wyłączył blokady stron lub wierszy przy użyciu funkcji ALTER INDEX).

Upewnij się, że predykaty są sargable

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 lub 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, ISNULL() funkcję systemową, 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 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 SQL Server mogą 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 zbadaj 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 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. Microsoft zaleca włączenie tej flagi śledzenia tylko w celu ograniczenia poważnego blokowania, które powoduje eskalację blokady, podczas gdy inne opcje, takie jak te omówione w tym artykule, są badane.

Treści powiązane

  • Last updated on