主页 / dba / 问题 / 78683
Accepted
a_horse_with_no_name
Asked: 2014-10-09 05:33:33 +0800 CST

检测 SQL Server 中锁定的表或行

  • 772

我正在尝试了解/学习如何追踪被阻止会话的详细信息。

所以我创建了以下设置：

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即使我们启用了快照隔离和读取提交的快照，该语句也会被阻止。下一步是弄清楚选择被阻止的原因（我认为如果启用快照隔离就不可能）。

作为第一步，我想了解该会话正在等待什么。

locking sql-server-2012

2 个回答

  1. Best Answer

    我认为这可以满足您的需求。

    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
    • 24

  2. 你可以试试 ： 

    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
    • 4

相关问题