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

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.

  • MS SQL: Select from a split string and retain the original order
  • ADO .NET vs. SQL Server Management Studio - ADO performs worse
  • sql server 2008 - find which tables contain specific text
  • Fastest way to determine if record exists
  • Copying tables among databases
  • How to return local temporary table from generated sql
  • One Solution collect form web for “Find what rows are locked for a given table and who is locking them in SQL Server”

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.