sql-server-2008 - SQL Server 锁定罪魁祸首查询？

Question

我从 http://sqlserverdb.blogspot.com/2011/06/find-all-system-and-user-defined-error.html 找到了这个:

SELECT
    t.resource_type,
    t.resource_database_id,
    t.resource_associated_entity_id,
    t.request_mode,
    t.request_session_id,
    w.blocking_session_id
    FROM sys.dm_tran_locks as t
    INNER JOIN sys.dm_os_waiting_tasks AS w
    ON t.lock_owner_address =w.resource_address

从那里我试图导出来自 w.blocking_session_id 的实际 SQL 语句，但我似乎无法找到正确的位。谁能指出我的线索？

我在一个 SSMS (SQL2008) 查询窗口中使用它:

begin transaction
update MyTable with (tablockx)
set MyTableColumn = MyTableColumn
where 1 = 0

在另一个新窗口中我执行了这个:

select * from MyTable

并且 SSMS 显示它“永远”等待执行查询，因此它是块受害者。但我想找出问题代码究竟是谁。

谢谢。

Answer 1

鉴于您提供的示例查询，以下完整示例显示了使用 sys.dm_os_waiting_tasks 的阻塞 SQL。 DMV 优先于 sys.dm_tran_locks车管所:

SELECT DISTINCT
  TheBlockingSQL.text AS [The blocking SQL]
FROM sys.dm_exec_connections AS Conns
  INNER JOIN sys.dm_exec_requests AS BlockedReqs
ON Conns.session_id = BlockedReqs.blocking_session_id
  INNER JOIN sys.dm_os_waiting_tasks AS w
ON BlockedReqs.session_id = w.session_id
  CROSS APPLY sys.dm_exec_sql_text(Conns.most_recent_sql_handle) AS TheBlockingSQL

这是输出的屏幕截图: