SYS.DM_TRAN_LOCKS

sys.dm_tran_locks to kluczowe DMV do analizy blokad, zastępujące starszą procedurę sp_lock.

SYS.DM_TRAN_LOCKS — czym jest widok blokad w SQL Server

SYS.DM_TRAN_LOCKS jest dynamicznym widokiem zarządzania, który pokazuje blokady utrzymywane przez aktywne transakcje oraz obiekty, na których te blokady występują. Dzięki temu można ustalić, która sesja posiada określone uprawnienie do zasobu, jaka sesja oczekuje na dostęp oraz jakiego rodzaju zasób został objęty synchronizacją. Widok ten służy przede wszystkim do badania sytuacji, w których zapytanie nie kończy się w oczekiwanym czasie, a aplikacja sprawia wrażenie zawieszonej lub działa wyraźnie wolniej.

Wynik zapytania do SYS.DM_TRAN_LOCKS nie opisuje wyłącznie tabel. W zależności od poziomu, na którym aparat bazy danych musi zapewnić spójność, mogą być widoczne blokady dotyczące bazy danych, obiektu, strony danych, klucza indeksu, zakresu kluczy, alokacji albo całej relacji. Wśród istotnych kolumn znajdują się między innymi request_session_id, resource_type, resource_database_id, resource_associated_entity_id, request_mode i request_status. Każda z nich opisuje inny fragment sytuacji i dopiero ich zestawienie pozwala poprawnie odczytać przyczynę oczekiwania.

SYS.DM_TRAN_LOCKS nie powinien być traktowany jako pojedynczy alarm wskazujący winowajcę. Sam fakt obecności blokady nie oznacza błędu. Blokady są normalnym mechanizmem ochrony danych przed jednoczesnymi, sprzecznymi zmianami. Problem pojawia się wtedy, gdy utrzymują się zbyt długo, obejmują nadmiernie duży zakres danych albo powodują łańcuch oczekiwania pomiędzy wieloma sesjami.

SYS.DM_TRAN_LOCKS — kontekst techniczny i porównanie z innymi DMV

Najważniejszą cechą SYS.DM_TRAN_LOCKS jest pokazanie stanu żądań blokad w danym momencie. Widok nie jest dziennikiem historycznym, dlatego wynik może zmienić się natychmiast po zatwierdzeniu transakcji, wycofaniu zmian lub zakończeniu sesji. Do analizy należy więc zapisywać kolejne obserwacje albo połączyć ten widok z innymi źródłami informacji, takimi jak sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_sql_text oraz sys.dm_tran_active_transactions.

sys.dm_exec_requests pomaga sprawdzić, czy żądanie czeka, jaki ma status i jaki identyfikator sesji je wykonuje. sys.dm_exec_sessions dostarcza informacji o połączeniu, użytkowniku i aplikacji. sys.dm_exec_sql_text pozwala odczytać tekst wykonywanego polecenia, o ile jest dostępny w pamięci. Z kolei sys.dm_tran_active_transactions ułatwia powiązanie blokady z transakcją oraz ocenę jej czasu trwania. Połączenie tych danych jest dokładniejsze niż interpretowanie pojedynczego wiersza z SYS.DM_TRAN_LOCKS.

Ważne jest rozróżnienie pomiędzy właścicielem blokady a oczekującym żądaniem. Sesja posiadająca blokadę może mieć w kolumnie status oznaczający przyznane uprawnienie, natomiast sesja oczekująca na zgodę innej sesji będzie widoczna jako żądająca. Sama kolumna request_mode określa tryb, na przykład współdzielony, wyłączny, aktualizacyjny lub zamiar wykonania operacji na niższym poziomie. Zgodność dwóch trybów zależy od reguł silnika, dlatego nie należy wyciągać wniosków wyłącznie na podstawie nazwy trybu.

Interpretacja resource_associated_entity_id także wymaga ostrożności. Dla blokady obiektu identyfikator może odpowiadać identyfikatorowi obiektu, ale dla blokady klucza lub strony znaczenie jest inne. W celu przełożenia identyfikatora na nazwę tabeli albo indeksu stosuje się dodatkowe zapytania do katalogu systemowego, a nie ręczne zgadywanie na podstawie samej liczby. Ma to szczególne znaczenie przy analizie indeksów, partycjonowania oraz blokad zakresowych.

SYS.DM_TRAN_LOCKS — typowe problemy ujawniane przez widok

Jednym z częstych przypadków jest długa transakcja, która wykonała zmianę, lecz nie została zatwierdzona. Wszystkie kolejne sesje próbujące odczytać lub zmodyfikować te same dane mogą wtedy oczekiwać na zwolnienie zasobu. W SYS.DM_TRAN_LOCKS pojawia się wiele żądań powiązanych z jednym właścicielem blokady. Przydatne jest wtedy sprawdzenie czasu rozpoczęcia transakcji, tekstu polecenia oraz tego, czy połączenie pozostaje aktywne mimo braku bieżącej pracy.

Inny problem stanowią różne kolejności modyfikowania tabel. Jeżeli jedna transakcja blokuje najpierw zasób A, a następnie próbuje uzyskać dostęp do zasobu B, podczas gdy druga robi to w odwrotnej kolejności, może powstać zakleszczenie. W takim przypadku samo SYS.DM_TRAN_LOCKS może pokazać jedynie chwilowy stan, ponieważ aparat bazy danych zwykle wybiera jedną transakcję jako ofiarę i wycofuje ją. Do pełnej analizy potrzebny jest zapis zdarzenia zakleszczenia, na przykład z mechanizmu rozszerzonych zdarzeń.

Opóźnienia mogą wynikać również z nadmiernego zakresu blokady. Operacja obejmująca dużą liczbę wierszy może uzyskać blokady na wielu kluczach albo doprowadzić do eskalacji do poziomu tabeli. Wtedy inne zapytania, które normalnie działałyby równolegle, zaczynają czekać. Warto sprawdzić plan wykonania, selektywność predykatów, dostępne indeksy oraz wielkość partii modyfikowanych danych. Zwiększenie współbieżności przez zmianę ustawień bez rozpoznania przyczyny może jedynie ukryć problem lub przenieść go w inne miejsce.

Nie każda blokada powoduje mierzalne opóźnienie. Krótkie blokady pojawiają się podczas normalnej pracy i znikają, zanim użytkownik je zauważy. Niepokój powinny budzić dopiero korelacje pomiędzy czasem oczekiwania, liczbą sesji, czasem trwania transakcji i wpływem na konkretne operacje aplikacji. W diagnostyce należy unikać pochopnego kończenia sesji, ponieważ może to spowodować wycofanie dużej transakcji i jeszcze dłuższe zajęcie zasobów.

SYS.DM_TRAN_LOCKS — praktyczne wskazówki analizy

Podstawowe zapytanie diagnostyczne powinno zwracać co najmniej identyfikator sesji, typ zasobu, identyfikator bazy danych, tryb blokady i status żądania:

SELECT request_session_id, resource_type, resource_database_id, resource_associated_entity_id, request_mode, request_status FROM sys.dm_tran_locks;

Takie zapytanie jest punktem wyjścia, a nie kompletną diagnozą. W środowisku z wieloma bazami danych należy połączyć identyfikator bazy z nazwą w katalogu systemowym. Następnie można zestawić request_session_id z sys.dm_exec_requests.session_id i sys.dm_exec_sessions.session_id. Pozwala to odróżnić sesję oczekującą od sesji, która faktycznie przetrzymuje zasób, oraz zobaczyć, czy oczekiwanie dotyczy odczytu, modyfikacji, zatwierdzenia czy innej czynności.

Przy analizie powinno się zapisać kilka próbek w odstępach czasu. Jednorazowy wynik może być przypadkowym obrazem krótkiej operacji. Powtarzający się identyfikator właściciela przy stale rosnącym czasie oczekiwania stanowi mocniejszą przesłankę do dalszych działań. Należy także sprawdzić, czy sesja korzysta z połączenia pozostawionego w stanie otwartej transakcji, czy polecenie wykonuje się długo z powodu planu, czy też rzeczywiście czeka na inną blokadę.

Warto ograniczać zakres transakcji, zatwierdzać zmiany możliwie szybko i nie wykonywać długich czynności użytkowych pomiędzy rozpoczęciem transakcji a jej zakończeniem. Zapytania powinny pobierać tylko potrzebne kolumny i wiersze, a operacje masowe można dzielić na mniejsze partie, jeśli charakter danych na to pozwala. Równocześnie należy zachować ostrożność przy zmianach poziomu izolacji, ponieważ mniejsza liczba blokad może oznaczać inne kompromisy dotyczące spójności i widoczności danych.

Do automatyzacji obserwacji można wykorzystać rozszerzone zdarzenia, procedury zbierające próbki albo narzędzia monitorujące, które zapisują tekst zapytania i czas oczekiwania. Dzięki temu można porównać moment pojawienia się blokady z wdrożeniem zmiany w aplikacji, wzrostem obciążenia lub konkretnym zadaniem okresowym. Istotne jest zabezpieczenie dostępu do wyników, ponieważ tekst poleceń i nazwy obiektów mogą ujawniać informacje wrażliwe.

SYS.DM_TRAN_LOCKS — kiedy zgłosić się do serwisu komputerowego

Jeżeli problem z SYS.DM_TRAN_LOCKS jest tylko jednym z objawów niestabilnej pracy laptopa albo komputera PC, można zgłosić urządzenie do Warszawskiego Pogotowia Komputerowego 24/7. W ramach sprawdzenia analizuje się między innymi uruchamianie systemu, działanie narzędzi administracyjnych, stabilność aplikacji oraz możliwe problemy z nośnikiem danych lub pamięcią. Przed przekazaniem sprzętu warto zanotować godzinę wystąpienia problemu, nazwę używanej aplikacji i przykładowe polecenie, które powoduje oczekiwanie.

Do serwisu warto zgłosić się wtedy, gdy komputer regularnie przestaje odpowiadać, narzędzia diagnostyczne zamykają się bez komunikatu, system ma problemy z odczytem danych albo występują powtarzające się restarty. Sama blokada w bazie danych zwykle wymaga analizy konfiguracji i zapytań po stronie środowiska bazodanowego, natomiast komputer może dodatkowo ujawnić problemy sprzętowe, uszkodzenie systemu plików lub nieprawidłowe działanie oprogramowania. Rozdzielenie tych obszarów pozwala uniknąć przypisywania każdej awarii samemu mechanizmowi blokad.

Przed rozpoczęciem prac należy przygotować kopię ważnych danych oraz, jeśli to możliwe, bezpieczny opis objawów. Nie powinno się usuwać dzienników, kończyć przypadkowych procesów ani zmieniać ustawień bez zapisania stanu wyjściowego. Dzięki temu można porównać zachowanie przed i po działaniach serwisowych oraz łatwiej ustalić, czy źródłem opóźnień jest zapytanie, transakcja, system operacyjny czy podzespoły komputera.