檢測 SQL Server 中鎖定的表或行

我正在嘗試了解/學習如何追踪被阻止會話的詳細資訊。

所以我創建了以下設置：

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

現在我從兩個不同的客戶端連接到數據庫兩次。

第一屆會議問題：

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

為了保留鎖，我明確地不在那裡送出。

在第二個會話中，我發出相同的語句，當然，由於鎖定而等待。現在我正在嘗試使用不同的查詢來查看會話 2 正在等待foo表。

sp_who2顯示以下內容（我刪除了一些列以僅顯示重要資訊）：

SPID | 狀態 | BlkBy | 數據庫名稱 | 命令 | SPID | 請求ID
-----+--------------+-------+----------+------------------+------+----------
52 | 睡覺| . | 食物b | 等待命令 | 52 | 0 
53 | 睡覺| . | 食物b | 等待命令 | 53 | 0 
54 | 暫停 | 52 | 食物b | 更新 | 54 | 0 
56 | 可執行 | . | 食物b | 選擇進入 | 56 | 0 

這是預期的，會話 54 被會話 52 中未送出的更改阻止。

查詢sys.dm_os_waiting_tasks也顯示了這一點。該聲明：

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

返回：

session_id | 等待類型 | 資源地址 | 資源描述 
-----------+-----------+--------------------+---------------------------------------------------------------------------------
54 | LCK_M_X | 0x000000002a35cd40 | 鑰匙鎖 hobtid=72057594046054400 dbid=6 id=lock4ed1dd780 mode=X associatedObjectId=72057594046054400

這也是意料之中的。

我的問題是，我不知道如何找到會話 54 正在等待的實際對象名稱。

我發現了幾個正在加入的查詢，sys.dm_tran_locks如下sys.dm_os_waiting_tasks所示：

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

但是在我上面的測試場景中，這個連接不會返回任何東西。所以要麼加入是錯誤的，要麼dm_tran_locks實際上不包含我正在尋找的資訊。

所以我正在尋找的是一個返回類似內容的查詢：

“會話 54 正在等待表中的鎖定foo”。

---

一些背景資料：

我試圖解決的現實生活中的問題要復雜一些，但歸結為“會話 54 正在等待哪個表”的問題。有問題的問題涉及更新多個表的大型儲存過程以及從訪問其中一些表的視圖中進行選擇。select即使我們啟用了快照隔離和讀取送出的快照，該語句也會被阻止。下一步是弄清楚選擇被阻止的原因（我認為如果啟用快照隔離就不可能）。

作為第一步，我想了解該會話正在等待什麼。

我認為這可以滿足您的需求。

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

你可以試試看 ：

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

引用自：https://dba.stackexchange.com/questions/78683