How to check which locks are held on a table

Pinpointing blocks

To identify blocked operations, use the below query that extracts processes being blocked and the notorious Session ID (spid) causing the blockade:

SELECT cmd, * 
FROM sys.sysprocesses 
WHERE blocked > 0;


Queue the Sherlock Holmes theme music as the blocked column reveals the spid of the blocking process! If the blockage is severly interrupting, a gentle "elimination" might be necessary using the KILL {spid} command. (hereby noting that this action comes with great responsibility!)

Tracing table locks

Want to track down the origin of the locks? This following query assists by bringing into light the session that initiated the lock and the command that actioned it:

SELECT t.text, r.session_id
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t;


Polishing lock data

For digestable lock information, consider baking a custom sp_lock function from the recipes circulating in the community, transforming those complex system views into a pleasant afternoon tea. Such a function could bring forth type of locks, impacted resources, and the transactions binding them.

Delving into lock types

Every lock tells a tale, signifying varying access controls. To unlock these tales, join sys.dm_tran_locks with pertinent system tables:

SELECT 
    t.name AS TableName,
    l.resource_type,
    l.resource_description,
    l.request_mode,
    l.request_status,
    OBJECT_NAME(p.object_id) AS ResourceName
FROM sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
JOIN sys.tables t ON p.object_id = t.object_id
WHERE t.name = 'YourTableName';


With this query, you're holding the looking glass over the resource description and request status for a closer examination.

Monitoring locks in real-time

For in-depth lock tracking, selectively filter out non-essential locks from sys.dm_tran_locks, focusing your attention on the active transactions stirring up a performance storm:

SELECT 
    DB_NAME(l.resource_database_id) AS DatabaseName,
    s.session_id,
    s.login_name,
    r.command,
    l.request_mode
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE l.resource_database_id = DB_ID('YourDatabaseName')
AND l.resource_type = 'OBJECT';


Navigating distributed transactions

In the vast ocean distributed transactions, don't lose sight of your locks. It's in these waters that the request_owner_guid in sys.dm_tran_locks serves as your lighthouse, guiding you to distributed transactions traversing multiple databases or servers.