Wykrywanie zablokowanej tabeli lub wiersza w programie SQL Server

Próbuję zrozumieć / nauczyć się, jak wyśledzić szczegóły zablokowanej sesji.

Więc stworzyłem następującą konfigurację:

create table foo (id integer not null primary key, some_data varchar(20));
insert into foo values (1, 'foo');
commit;

Teraz łączę się z bazą danych dwukrotnie od dwóch różnych klientów.

Problemy z pierwszą sesją:

begin transaction
update foo set some_data = 'update'
  where id = 1;

Wyraźnie nie popełniam tam, aby zachować zamki.

W drugiej sesji wystawiam to samo oświadczenie i oczywiście, że czeka się na zablokowanie. Teraz próbuję użyć różnych zapytań unoszących się wokół, aby zobaczyć, że sesja 2 czeka na footabelę.

sp_who2 pokazuje następujące (usunąłem niektóre kolumny, aby pokazać tylko ważne informacje):

SPID | Status | BlkBy | DBName | Command | SPID | ŻĄDANIE ID
----- + -------------- + ------- + ---------- + ---------- -------- + ------ + ----------
52 | spanie | . | foodb | OCZEKUJĄCE NA POLECENIE | 52 | 0        
53 | spanie | . | foodb | OCZEKUJĄCE NA POLECENIE | 53 | 0        
54 | ZAWIESZONY | 52 | foodb | AKTUALIZACJA | 54 | 0        
56 | RUNNABLE | . | foodb | WYBIERZ W | 56 | 0        

Jest to oczekiwane, sesja 54 jest blokowana przez nie zatwierdzone zmiany z sesji 52.

Zapytanie sys.dm_os_waiting_tasksrównież to pokazuje. Wyrok:

select session_id, wait_type, resource_address, resource_description
from sys.dm_os_waiting_tasks
where blocking_session_id is not null;

zwroty:

session_id | wait_type | adres_zasobu | opis_zasobu                                                            
----------- + ----------- + -------------------- + ----- -------------------------------------------------- --------------------------
        54 | LCK_M_X | 0x000000002a35cd40 | keylock hobtid = 72057594046054400 dbid = 6 id = tryb lock4ed1dd780 = X relatedObjectId = 72057594046054400

Ponownie oczekuje się tego.

Mój problem polega na tym, że nie potrafię znaleźć rzeczywistej nazwy obiektu, na który czeka sesja 54.

Znalazłem kilka zapytań, które łączą się sys.dm_tran_locksi sys.dm_os_waiting_taskspodobają:

SELECT ....
FROM sys.dm_tran_locks AS l
  JOIN sys.dm_os_waiting_tasks AS wt ON wt.resource_address = l.lock_owner_address

Ale w moim powyższym scenariuszu testowym to połączenie nic nie zwraca. Więc to dołączenie jest złe lub dm_tran_locksfaktycznie nie zawiera informacji, których szukam.

Tak więc szukam zapytania, które zwraca coś takiego:
„ sesja 54 czeka na blokadę w tabelifoo ”.

Niektóre informacje w tle:

Prawdziwy problem, który próbuję rozwiązać, jest nieco bardziej skomplikowany, ale sprowadza się do pytania „na który stół czeka sesja 54”. Problem dotyczy dużej procedury składowanej, która aktualizuje kilka tabel i wybiera z widoku, który uzyskuje dostęp do niektórych z tych tabel. selectOświadczenie jest zablokowany chociaż mamy izolacji migawka i czytać zaangażowana migawka włączona. Zrozumienie, dlaczego wybór jest zablokowany (co, moim zdaniem, nie byłoby możliwe, jeśli włączona jest izolacja migawek), będzie następnym krokiem.

Na początek chciałbym dowiedzieć się, na co czeka ta sesja.

Myślę, że to robi to, czego potrzebujesz.

USE 'yourDB'
GO
SELECT  
    OBJECT_NAME(p.[object_id]) BlockedObject
FROM    sys.dm_exec_connections AS blocking
    INNER JOIN sys.dm_exec_requests blocked
        ON blocking.session_id = blocked.blocking_session_id
    INNER JOIN sys.dm_os_waiting_tasks waitstats
        ON waitstats.session_id = blocked.session_id
    INNER JOIN sys.partitions p ON SUBSTRING(resource_description, 
        PATINDEX('%associatedObjectId%', resource_description) + 19, 
        LEN(resource_description)) = p.partition_id

Możesz tego spróbować :

SELECT 
db_name(rsc_dbid) AS 'DATABASE_NAME',
case rsc_type when 1 then 'null'
              when 2 then 'DATABASE' 
              WHEN 3 THEN 'FILE'
              WHEN 4 THEN 'INDEX'
              WHEN 5 THEN 'TABLE'
              WHEN 6 THEN 'PAGE'
              WHEN 7 THEN 'KEY'
              WHEN 8 THEN 'EXTEND'
              WHEN 9 THEN 'RID ( ROW ID)'
              WHEN 10 THEN 'APPLICATION' end  AS 'REQUEST_TYPE',

CASE req_ownertype WHEN 1 THEN 'TRANSACTION'
                   WHEN 2 THEN 'CURSOR'
                   WHEN 3 THEN 'SESSION'
                   WHEN 4 THEN 'ExSESSION' END AS 'REQUEST_OWNERTYPE',

OBJECT_NAME(rsc_objid ,rsc_dbid) AS 'OBJECT_NAME', 
PROCESS.HOSTNAME , 
PROCESS.program_name , 
PROCESS.nt_domain , 
PROCESS.nt_username , 
PROCESS.program_name ,
SQLTEXT.text 
FROM sys.syslockinfo LOCK JOIN 
     sys.sysprocesses PROCESS
  ON LOCK.req_spid = PROCESS.spid
CROSS APPLY sys.dm_exec_sql_text(PROCESS.SQL_HANDLE) SQLTEXT