Joining sys.dm_os_waiting_tasks with sys.dm_tran_locks doesn’t work in SQL Server 2012 (RTM)

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.

SELECT

t1.resource_type,

        t1.resource_database_id,

        t1.resource_associated_entity_id,

        t1.request_mode,

        t1.request_session_id,

        t2.blocking_session_id,

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.

http://connect.microsoft.com/SQLServer/feedback/details/760877/sample-code-for-sys-dm-tran-locks-doesnt-work-in-sql-server-2012

So I requested the system admin to install SP1 in the RTM version and issue sorted out.

Advertisements

Leave a comment

Filed under DMV, Internals

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s