Detecting the locked table or row in SQL Server

Question

I'm trying to understand/learn how to track down the details of a blocked session.

So I created the following setup:

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

Now I connect to the database twice from two different clients.

The first session issues:

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

I explicitly do not commit there in order to keep the locks.

In the second session I issue the same statement and of course that one waits due to locking. Now I'm trying to use the different queries floating around in order to see that session 2 is waiting for the foo table.

sp_who2 shows the following (I removed some columns to only show the important information):

SPID | Status       | BlkBy | DBName   | Command          | SPID | REQUESTID
-----+--------------+-------+----------+------------------+------+----------
52   | sleeping     |   .   | foodb    | AWAITING COMMAND | 52   | 0        
53   | sleeping     |   .   | foodb    | AWAITING COMMAND | 53   | 0        
54   | SUSPENDED    | 52    | foodb    | UPDATE           | 54   | 0        
56   | RUNNABLE     |   .   | foodb    | SELECT INTO      | 56   | 0        

This is expected, session 54 is blocked by the un-committed changes from session 52.

Querying sys.dm_os_waiting_tasks also shows this. The statement:

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

returns:

session_id | wait_type | resource_address   | resource_description                                                            
-----------+-----------+--------------------+---------------------------------------------------------------------------------
        54 | LCK_M_X   | 0x000000002a35cd40 | keylock hobtid=72057594046054400 dbid=6 id=lock4ed1dd780 mode=X associatedObjectId=72057594046054400

Again this is expected.

My problem is, that I can't figure out how to find the actual object name that session 54 is waiting for.

I have found several queries that are joining sys.dm_tran_locks and sys.dm_os_waiting_tasks like this:

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

But in my above test scenario this join does not return anything. So either that join is wrong or dm_tran_locks doesn't actually contain the information I'm looking for.

So what I am looking for is a query that returns something like:
"session 54 is waiting for a lock in table foo".

---

Some background info:

The real life problem I'm trying to solve is a bit more complicated, but boils down to the question "on which table is session 54 waiting for". The problem in question involves a largish stored procedure that updates several tables and a select from a view that accesses some of those tables. The select statement is blocked even though we have snapshot isolation and read committed snapshot enabled. Figuring out why the select is blocked (which I thought would not be possible if snapshot isolation is enabled) will be the next step.

As a first step I'd like to find out on what that session is waiting.

Answer 1

I think this does what you need.

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

Answer 2

You can try it :

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