We start by looking at all current locks:
SELECT txt.text,
sysprocesses.spid,
sysprocesses.blocked,
CASE
WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
ELSE OBJECT_NAME(partitions.OBJECT_ID)
END AS ObjectName,
indexes.name AS index_name,
dm_tran_locks.request_mode,
dm_tran_locks.request_status,
CASE
WHEN resource_type = 'KEY'
THEN 'ROW IN INDEX'
ELSE resource_type
END as resource_type,
dm_tran_locks.resource_description
FROM sys.sysprocesses
LEFT OUTER JOIN sys.dm_exec_connections ON dm_exec_connections.session_id = sysprocesses.spid
OUTER APPLY sys.dm_exec_sql_text(dm_exec_connections.most_recent_sql_handle) AS txt
LEFT OUTER JOIN sys.dm_tran_locks ON dm_tran_locks.request_session_id = sysprocesses.spid
LEFT OUTER JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE dm_tran_locks.resource_type <> 'DATABASE'
ORDER BY sysprocesses.spid, ObjectName, index_name, resource_type;
We can see that process 54 (spid) is waiting (request_status=WAIT) for lock on a row in an index that is currently held by process 57 (blocked).
We can also see the SQL (text) of process 54 that caused SQL Server to acquire a lock. Looking at process 57, we see the lock process 54 is waiting on:
We know it is the lock where are looking for, because it has the same object- and index name and the same resource description, which in this case is used to identify the row in the table. We can resolve the resource description to the actual row using this query:
SELECT %%lockres%%, *
FROM <TABLE_NAME> WITH(NOLOCK)
WHERE %%lockres%% IN
(
SELECT l.resource_description
FROM sys.dm_tran_locks as l
WHERE l.resource_type IN ('KEY', 'RID') --index lock, KEY
);
The above example does not depict a dead lock situation, because process 57 is not blocked by another process.
In dead lock scenario, you would have to follow the blocked column until you arrive at your starting process. Know you know what process (transaction) is holding locks on which rows (or tables) and can fix it be reordering statements or changing the transaction isolation level.
Links and credits:
- https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15#arguments
- https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#isolation-levels-in-the-
- https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-ver15#resource-details
- https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175519(v=sql.105)
- https://www.mssqltips.com/sqlservertip/1968/understanding-sql-server-locking/
- https://stackoverflow.com/questions/61080868/finding-out-which-row-in-a-table-is-locked-sql-server