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.

  • Pivot Query - Missing records
  • sql server bcp xml data
  • Same query with the same query plan takes ~10x longer when executed from ADO.NET vs. SMSS
  • Sql server which is better “Rank” or “Left join with own table” to filter latest records
  • Converting from integer to binary and back in SQL Server
  • identity from sql insert via jdbctemplate
  • 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.