Find what rows are locked for a given table and who is locking them in SQL Server

Question

Is there a way to find out what rows are locked for a specific table in SQL Server 2008? I'd also like to know the user who is locking them.

Answer 1

sys.dm_tran_locks, as already said in 694581. To identity which rows are actually locked, you need to understand the locking hierarchy (table->rowset->page->row) and you need to crack the lock resource description. For table locks is the object id from sys.objects, for rowsets is the partition_id from sys.partitions and for pages is the actual page id. For rows it depends whether is a heap or a btree, but you can use the (undocumented) %%lockres%% virtual column to find the row. If this is too simple, you need to consider also range locks as they impact all the rows in the specified range.

When you add up the difficulty of navigating the physical hierarchy, specially when page locks are involved, with the complex model of the lock compatibility matrix, the complications added by hash collisions and consider the the pace at which the locks you're looking at change, I would say that at the very best you can do a very rough approximation. Besides doing a specific problem investigation, there is little point into digging into this. I would be horrified to hear of an application that looks actively at locks held and makes any kind of decision based on the information seen.

Answer 2

Here is an example how to find the main key of a locked records in a table:

SELECT <main_key>
FROM <table>
WHERE %%lockres%% IN ( select dm_tran_locks.resource_description from sys.dm_tran_locks )