Looking at locks being held

 

Quote

Looking at locks being held

I was looking at locks that were being held by a query today to try to help solve a locking problem so I went hunting in the dynamic management views.  Very nice stuff in there, and I was able to formulate the following query to get a nice view of the locks.
 
select  login_name,
        case des.transaction_isolation_level
            when 0 then ‘Unspecified’ when 1 then ‘ReadUncomitted’
            when 2 then ‘ReadCommitted’ when 3 then ‘Repeatable’
            when 4 then ‘Serializable’ when 5 then ‘Snapshot’
        end as transaction_isolation_level,
        request_session_id, resource_type, resource_subtype, request_mode,
        request_type, request_status, request_owner_type,
        case when resource_type = ‘object’ then object_name(resource_associated_entity_id)
             when resource_type = ‘database’ then db_name(resource_associated_entity_id)
             when resource_type in (‘key’,’page’) then
                                 (select object_name(object_id) from sys.partitions
                                  where hobt_id = resource_associated_entity_id)
             else cast(resource_associated_entity_id as varchar(20))
        end
from sys.dm_tran_locks dtl
        left outer join sys.dm_exec_sessions des
            on dtl.request_session_id = des.session_id
where request_session_id <> @@spid
 
Really nice stuff and really easy to follow without much explanation. (look up shared locks in BOL to see the Lock Compatibility table for the request mode if you don’t know those.)  So much easier than the old syslockinfo table, that is for darn sure…
 
Know more?  Have a resource for these types of queries to share?  Leave me a comment…
 
Edit: Missing comma before the request_session_id caused the request_session_id to show the isolation level
This entry was posted in Uncategorized. Bookmark the permalink.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s