Detecting the locked table or row in SQL Server

  • 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');

    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;


    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.

    @MaxVernon: thanks for confirming that. But then I'm even more confused. Why does it not return anything although I _know_ there is a lock and a blocked session?

    I'm unable to recreate the problem you are seeing in SQL Server 2012. I created a test database, enabled RCSI, created your tables, and ran both update statements, and I see a row returned by your last query.

    If you want a visual aid in detecting your locks there is an open source tool available called SQL lock finder. You can find the source on: Or download the executable on: We also love any contributions you could make to the code so we can make it better.

  • I think this does what you need.

    USE 'yourDB'
        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
  • You can try it :

    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.program_name , 
    PROCESS.nt_domain , 
    PROCESS.nt_username , 
    PROCESS.program_name ,
    FROM sys.syslockinfo LOCK JOIN 
         sys.sysprocesses PROCESS
      ON LOCK.req_spid = PROCESS.spid

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM