Identyfikacja zapytania blokującego inne zapytania w SQL Server

Brak możliwości wykonywania zapytań

Jednoczesny dostęp wielu użytkowników do tych samych danych musi powodować konflikty. Dwóch robotników budowlanych nie może pracować tą samą łopatą jednocześnie - mogą się natomiast łopatą wymieniać. Dwie osoby nie będą również piły z tej samej szklanki jednocześnie, ewentualnie przez słomki. Nie da się w końcu modyfikować tych samych danych w bazie jednocześnie bez ryzyka, że jeden użytkownik drugiemu coś nadpisze. SQL Server, Oracle i inne silniki baz danych, także systemy operacyjne, wszystkie one stosują jakiś mechanizm synchronizacji wątków. To te mechanizmy, wykorzystujące blokady, mogą sprawić, że nasze zapytanie zatrzyma się. Nie ważne jak długo będę opisywał i tak lepszy okaże się odpowiedni przykład. Popatrzmy zatem na pokazany skrypty:

CREATE TABLE LockTest
(
  value int
)

Teraz należy zasymulować dostęp wielu użytkowników. Należy zatem otworzyć dwa połączenia (lub dwie zakładki w SQL Server Management Studio) i wykonać pokazane skrypty:

--Wykonaj w oknie/sesji 1
BEGIN TRAN
INSERT LockTest VALUES (1)

Warto zwrócić uwagę, że transakcja rozpoczyna się, ale nie kończy. W drugim oknie wystarczy teraz puścić następujący skrypt:

--Wykonaj w oknie/sesji 2
SELECT * FROM LockTest

Zapytanie w oknie 2 zostanie wstrzymane do czasu odblokowania tabeli przez skrypt w oknie 1. Wyobraźmy sobie, że wykonujemy proste zapytanie w oknie 2 i nie możemy otrzymać wyniku. To właśnie ten problem chciałem dzisiaj poruszyć. Doświadczają go często programiści różnych języków programowania - wystarczy że jedna osoba użyje debuggera i zastawi pułapkę wewnątrz transakcji - reszta może sobie czekać i czekać. Spróbujmy namierzyć delikwenta.

Jakie zapytanie blokuje instrukcję SELECT?

SQL Server udostępnia mnóstwo funkcji i widoków systemowych. Jest ich tak dużo, że trudno znaleźć te właściwe. Popatrzmy na przykładowy skrypt, który pozwala uzyskać najważniejsze informacje:

SELECT blocking_session_id, Blocking.[text] blocking_sql,
S.[host_name] blocking_host, S.[program_name] blocking_program, S.login_name blocking_login,
R.session_id blocked_session_id, Blocked.[text] blocked_sql,
RS.[host_name] blocked_host, RS.[program_name] blocked_program, RS.login_name blocked_login
FROM sys.dm_exec_requests R
JOIN sys.dm_exec_connections C ON R.blocking_session_id=C.session_id
JOIN sys.dm_exec_sessions S ON C.session_id=S.session_id
JOIN sys.dm_exec_sessions rS ON R.session_id=rS.session_id
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) Blocked
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) Blocking
WHERE DB_NAME(R.database_id)='Test'

Zapytanie wybiera być może więcej kolumn niż jest to potrzebne, ale zawsze można ograniczyć ich liczbę. W naszym testowym przypadku zapytanie zwróci następujący rezultat:

Dostajemy następujące informacje:

• numer sesji blokującej i blokowanej,
• zapytania powodujące blokowanie i zablokowane,
• nazwach komputerów, na których uruchomiono zapytania blokujące i zablokowane,
• nazwy programów, które wykonywały zapytania,
• nazwy użytkowników SQL, które są stronami sporu.

W pokazany sposób możemy namierzyć sporne zapytanie (być może sami sobie blokujemy dostęp), odnaleźć osobę, która blokuje zasoby (po nazwie komputera w sieci, jeżeli grupa programistów pracuje na tej samej bazie), czy też zidentyfikować aplikację, z której przyszło żądanieTylko od dobrej woli aplikacji zależy, czy będzie się ona przedstawiała podczas wykonywania zapytań. Problem w tym, że większość aplikacji tego nie robi. Wartość nie jest wymagana, ale zasady Savoir-vivre sugerują przekazanie atrybutu Application Name w łańcuchu połączeniowym z bazą.. Gdy każdy z użytkowników ma swój login w bazie, również łatwo namierzyć sprawcę. I w końcu, gdy nikt się nie chce przyznać do spornego zapytania, możemy takie połączenie zlikwidować używając rozwiązania siłowego:

kill [numer sesji blokującej]

Nie jest to rozwiązanie eleganckie, ale przecież nie będziemy czekać w nieskończoność.

Warto zadać sobie trud i poczytać dokumentację użytych w skrypcie funkcji i dynamicznych widoków. Jest tam dużo różnych pól, które również mogą pomoc w zidentyfikowaniu blokującego zapytania. Warto również wspomnieć o mechanizmie Extended Events. Przy jego udziale również możemy uzyskać dużo informacji na temat blokad. O tym jednak innym razem.

Kategoria:SQL Server