Invitation Digital Tech Blog

Building Scalable & Responsive Architecture

By

SQL Locks. Table Locks. How to find out what is locking your table?

We have for a while been using sp_lock and sp_lock2 to determine locks on the database object but I have always found that to be cumbersome. Now that this is going to be deprecated in the future version of SQL Server we will need an alternative.

Luckily, Microsoft has a new view that provides this information in a much friendly easy to understand way, the sys.dm_tran_locks. As per MSDN, this view returns information about currently active lock manager resources in SQL Server. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. It is also quite lightweight as the view is populated from internal lock manager data structures, and maintaining this does not add extra overhead.

Usage is pretty simple, you can just call SELECT * FROM sys.dm_tran_locks. But to make better sense of the information you should join it either to sys.partitions view on sys.partitions.hobt_id (short for Heap Or B-Tree and pronounced hobbit) to sys.dm_tran_locks.resource_associated_entity_id OR the sys.dm_tran_locks.resource_associated_entity_id to sys.objects view on object_id and a required object type such as ‘U’ for Table.

The example below joing to the sys.partitions.hobt_id and gives you a lot more information including any key or page locks on a Table with the corresponding session id or SPID.

    SELECT  OBJECT_NAME(p.object_id) AS TableName ,
            DB_NAME(l.resource_database_id) AS DbName ,
            l.request_session_id as SPID,
            resource_type ,
            resource_description ,
            l.request_type ,
            l.request_status ,
            l.request_owner_type
    FROM    sys.dm_tran_locks l
            JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id

The following example joins on sys.partitions.object_id which limits the information to the resource type of database object and reduces the number of rows returned.

    SELECT  OBJECT_NAME(p.object_id) AS TableName ,
            DB_NAME(l.resource_database_id) AS DbName ,
            l.request_session_id as SPID,
            resource_type ,
            resource_description ,
            l.request_type ,
            l.request_status ,
            l.request_owner_type
    FROM    sys.dm_tran_locks l
            JOIN sys.partitions p ON l.resource_associated_entity_id = p.object_id

The following example joins to sys.objects of object type Table.

    SELECT  OBJECT_NAME(s.object_id) AS TableName ,
            DB_NAME(l.resource_database_id) AS DbName ,
            l.request_session_id as SPID,
            resource_type ,
            resource_description ,
            l.request_type ,
            l.request_status ,
            l.request_owner_type
    FROM    sys.dm_tran_locks l
            JOIN sys.objects s ON s.object_id = l.resource_associated_entity_id
                                  AND s.type = 'U'

Now that you know which object has the lock and which process is locking (i.e. the request_session_id or SPID) you can choose to kill that process.

To kill just use the KILL command i.e. KILL SPID

Alternatively, you can view this in Activity monitor and kill from there.

Kill Process via Activity Monitor

That’s It.