5

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.

Jeff Stock
  • 3,796
  • 12
  • 46
  • 61
  • possible duplicate of [How to check which locks are held on a table](http://stackoverflow.com/questions/694581/how-to-check-which-locks-are-held-on-a-table) – Oded Sep 15 '11 at 20:17
  • I saw that, but was wondering how to get the specific records that are locked as well, which I don't see in that post. – Jeff Stock Sep 15 '11 at 20:51
  • @Jeff: You are free to edit that into your question. – Andriy M Sep 16 '11 at 00:00

3 Answers3

7

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.

Community
  • 1
  • 1
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
1

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 )
AGR
  • 321
  • 6
  • 8
0

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: https://github.com/LucBos/SqlLockFinder Or download the executable on: http://sqllockfinder.com

SQL lock finder gives you an overview of what is exactly locked in each session.

Luc Bos
  • 1,722
  • 1
  • 13
  • 24