Jakie są główne przyczyny impasu i czy można im zapobiec?

Ostatnio jedna z naszych aplikacji ASP.NET wyświetliła błąd zakleszczenia bazy danych i zostałem poproszony o sprawdzenie i naprawienie błędu. Udało mi się znaleźć przyczynę impasu w procedurze przechowywanej, która rygorystycznie aktualizowała tabelę w obrębie kursora.

Po raz pierwszy widziałem ten błąd i nie wiedziałem, jak skutecznie go śledzić i naprawić. Wypróbowałem wszystkie możliwe sposoby, które znam, i w końcu odkryłem, że aktualizowana tabela nie ma klucza podstawowego! na szczęście była to kolumna tożsamości.

Później znalazłem programistę, który napisał skrypt do bazy danych do wdrożenia. Dodałem klucz podstawowy i problem został rozwiązany.

Czułem się szczęśliwy i wróciłem do mojego projektu i przeprowadziłem badania, aby znaleźć przyczynę impasu ...

Najwyraźniej impas spowodował okrągły cykl oczekiwania. Aktualizacje najwyraźniej trwają dłużej bez klucza podstawowego niż z kluczem podstawowym.

Wiem, że nie jest to dobrze określony wniosek, dlatego zamieszczam tutaj ...

• Czy brakujący klucz podstawowy stanowi problem?
• Czy istnieją inne warunki, które powodują impas inne niż (wzajemne wykluczenie, wstrzymanie i oczekiwanie, brak uprzedzenia i cykliczne oczekiwanie)?
• Jak zapobiegać i śledzić zakleszczenia?

śledzenie zakleszczeń jest łatwiejsze z dwóch:

Domyślnie zakleszczenia nie są zapisywane w dzienniku błędów. Możesz spowodować, że SQL zapisuje zakleszczenia w dzienniku błędów za pomocą flag śledzenia 1204 i 3605.

Zapisz informacje o zakleszczeniu w dzienniku błędów programu SQL Server: DBCC TRACEON (-1, 1204, 3605)

Wyłącz: DBCC TRACEOFF (-1, 1204, 3605)

Zobacz „Rozwiązywanie problemów z zakleszczeniami”, aby uzyskać omówienie flagi śledzenia 1204 i danych wyjściowych, które otrzymasz po włączeniu. https://msdn.microsoft.com/en-us/library/ms178104.aspx

Zapobieganie jest trudniejsze, zasadniczo musisz zwrócić uwagę na następujące kwestie:

Blok kodu 1 blokuje zasób A, a następnie zasób B, w tej kolejności.

Blok kodu 2 blokuje zasób B, a następnie zasób A, w tej kolejności.

Jest to klasyczny warunek, w którym może wystąpić zakleszczenie, jeśli blokowanie obu zasobów nie jest atomowe, Blok kodu 1 może zablokować A i zostać uprzednio opróżniony, a następnie blok kodu 2 zablokuje B, zanim A odzyska czas przetwarzania. Teraz masz impas.

Aby temu zapobiec, możesz wykonać następujące czynności

Blok kodu A (kod psuedo)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...

Blok kodu B (pseudo kod)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...

nie zapominając o odblokowaniu A i B po ich zakończeniu

zapobiegnie to zakleszczeniu między blokiem kodu A i blokiem kodu B.

Z perspektywy bazy danych nie jestem pewien, jak poradzić sobie z zapobieganiem tej sytuacji, ponieważ blokady są obsługiwane przez samą bazę danych, tj. Blokady wierszy / tabel podczas aktualizacji danych. Widziałem, że najwięcej problemów występuje w miejscu, w którym widziałeś swój, wewnątrz kursora. Kursory są notorycznie nieefektywne, unikaj ich, jeśli to w ogóle możliwe.

moimi ulubionymi artykułami do przeczytania i zapoznania się z zakleszczeniami są: Prosta rozmowa - Wyśledzenie zakleszczeń i SQL Server Central - Korzystanie z programu Profiler do rozwiązywania zakleszczeń . Dadzą ci próbki i porady, jak poradzić sobie z sytuacją.

Krótko mówiąc, aby rozwiązać bieżący problem, spowodowałbym, że transakcje były krótsze, wyjąłem z nich niepotrzebną część, zadbałem o porządek użycia obiektów, zobaczę, jaki poziom izolacji jest rzeczywiście potrzebny, nie czytam niepotrzebnego dane...

Ale lepiej przeczytaj artykuły, będą one o wiele ładniejsze w poradach.

Czasami impas można rozwiązać, dodając indeksowanie, ponieważ pozwala to bazie danych na blokowanie pojedynczych rekordów, a nie całej tabeli, dzięki czemu zmniejsza się rywalizacja i możliwość zakleszczenia.

Na przykład w InnoDB :

Jeśli nie masz indeksów odpowiednich dla twojej instrukcji, a MySQL musi przeskanować całą tabelę w celu przetworzenia instrukcji, każdy wiersz tabeli zostaje zablokowany, co z kolei blokuje wszystkie wstawki innych użytkowników do tabeli. Ważne jest, aby tworzyć dobre indeksy, aby zapytania nie skanowały niepotrzebnie wielu wierszy.

Innym powszechnym rozwiązaniem jest wyłączenie spójności transakcyjnej, gdy nie jest potrzebne, lub zmiana poziomu izolacji , na przykład długotrwałe zadanie obliczania statystyk ... generalnie wystarczająca jest ścisła odpowiedź, nie potrzebujesz dokładnych liczb, gdy zmieniają się spod ciebie. A jeśli zajmie to 30 minut, nie chcesz, aby zatrzymywała wszystkie inne transakcje na tych tabelach.

...

Jeśli chodzi o ich śledzenie, zależy to od używanego oprogramowania bazy danych.

Wystarczy rozwinąć kursor. to jest naprawdę bardzo złe. Blokuje całą tabelę, a następnie przetwarza wiersze jeden po drugim.

Najlepiej jest przechodzić między wierszami w stylu kursora za pomocą pętli while

W pętli while zostanie dokonany wybór dla każdego wiersza w pętli, a blokada nastąpi tylko w jednym rzędzie naraz. Reszta danych w tabeli jest bezpłatna do tworzenia zapytań, zmniejszając w ten sposób ryzyko wystąpienia impasu.

Plus jest szybszy. Sprawia, że ​​zastanawiasz się, dlaczego w ogóle istnieją kursory.

Oto przykład tego rodzaju struktury:

DECLARE @LastID INT = (SELECT MAX(ID) FROM Tbl)
DECLARE @ID     INT = (SELECT MIN(ID) FROM Tbl)
WHILE @ID <= @LastID
    BEGIN
    IF EXISTS (SELECT * FROM Tbl WHERE ID = @ID)
        BEGIN
        -- Do something to this row of the table
        END

    SET @ID += 1  -- Don't forget this part!
    END

Jeśli twoje pole identyfikatora jest rzadkie, możesz pobrać oddzielną listę identyfikatorów i iterować:

DECLARE @IDs TABLE
    (
    Seq INT NOT NULL IDENTITY PRIMARY KEY,
    ID  INT NOT NULL
    )
INSERT INTO @IDs (ID)
    SELECT ID
    FROM Tbl
    WHERE 1=1  -- Criteria here

DECLARE @Rec     INT = 1
DECLARE @NumRecs INT = (SELECT MAX(Seq) FROM @IDs)
DECLARE @ID      INT
WHILE @Rec <= @NumRecs
    BEGIN
    SET @ID = (SELECT ID FROM @IDs WHERE Seq = @Seq)

    -- Do something to this row of the table

    SET @Seq += 1  -- Don't forget this part!
    END

Brak klucza podstawowego nie jest problemem. Przynajmniej sam. Po pierwsze, nie potrzebujesz podstawowego, aby mieć indeksy. Po drugie, nawet jeśli wykonujesz skanowanie tabeli (co musi się zdarzyć, jeśli twoje zapytanie nie korzysta z indeksu, blokada tabeli sama w sobie nie spowoduje zakleszczenia. Proces zapisu czekałby na odczyt, a proces odczytu czekać na napis, i oczywiście czytanie nie musi wcale na siebie czekać.

Dodając do innych odpowiedzi, poziom izolacji transakcji ma znaczenie, ponieważ powtarzalne odczytywanie i serializacja powodują, że blokady „odczytu” są utrzymywane do końca transakcji. Zablokowanie zasobu nie powoduje zakleszczenia. Trzymanie go w ryzach robi. Operacje zapisu zawsze utrzymują swoje zasoby zablokowane do końca transakcji.

Moją ulubioną strategią zapobiegania blokowaniu jest używanie funkcji „migawki”. Funkcja Snapshot odczytu zatwierdzonego oznacza, że ​​odczyty nie używają blokad! A jeśli potrzebujesz większej kontroli niż „Odczyt zatwierdzony”, dostępna jest funkcja „Poziom izolacji migawki”. Ta zezwala na wystąpienie szeregowej (przy użyciu warunków MS) transakcji bez blokowania innych graczy.

Wreszcie, jednej klasie zakleszczeń można zapobiec, stosując blokadę aktualizacji. Jeśli czytasz i przytrzymujesz odczyt (HOLD lub przy użyciu Repeatable Read), a inny proces robi to samo, to oboje próbują zaktualizować te same rekordy, będziesz mieć impas. Ale jeśli oba zażądają blokady aktualizacji, drugi proces będzie czekać na pierwszy, umożliwiając innym procesom odczyt danych przy użyciu blokad współdzielonych, aż dane zostaną faktycznie zapisane. To oczywiście nie zadziała, jeśli jeden z procesów nadal zażąda udostępnionej blokady HOLD.

Podczas gdy kursory są wolne w programie SQL Server, można uniknąć zakleszczenia kursora, przeciągając dane źródłowe kursora do tabeli Temp i uruchamiając na nim kursor. Dzięki temu kursor nie blokuje faktycznej tabeli danych, a jedyne blokady, jakie otrzymujesz, dotyczą aktualizacji lub wstawek wykonywanych wewnątrz kursora, które są utrzymywane tylko przez czas wstawiania / aktualizacji, a nie przez czas trwania kursora.