Last week, I was helping a developer to debug a lock information query that involves a join between DMVs sys.dm_os_waiting_tasks and sys.dm_tran_locks. I created two sessions. In session 1, simulated a lock by starting a transaction (in READCOMMITED isolation level) without committing and in the session 2, issued a SELECT query in the same table from the session 1 that has been blocked by the query in session 1. Then I executed the following query for lock information.
OBJECT_NAME(p.object_id) AS ObjectName FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address
INNER JOIN sys.partitions p
ON p.hobt_id = resource_associated_entity_id;
However the query doesn’t return the expected results. I queried the DMVs individually and found that the values in lock_owner_address column and resource_address column are different. I repeated the same test in another server and got the expected result! Then I checked the versions between the servers and found that the developer was working in SQL Server 2012 (RTM) and my second test was in SQL Server 2012 SP1. So this is a bug in the RTM version.
I searched the Microsoft connect website and found the below connect item by Kalen Delaney.
So I requested the system admin to install SP1 in the RTM version and issue sorted out.