Click here to Skip to main content
11,584,455 members (63,701 online)
Click here to Skip to main content

Tagged as

DMV-13: Finding Locking & Blocking……..sys.dm_tran_locks

, 31 Jan 2014 CPOL 3.3K 1
Rate this:
Please Sign up or sign in to vote.
Finding, locking and blocking

sys.dm_tran_locks DMV (Dynamic Management View) is described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms190345.aspx

Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

This DMV is very useful in helping to identify locking and blocking issues on your data­base instances.

Query 1: Details of currently active locks: 

SELECT
CASE DTL.REQUEST_SESSION_ID
WHEN -2 THEN ‘ORPHANED DISTRIBUTED TRANSACTION’
WHEN -3 THEN ‘DEFERRED RECOVERY TRANSACTION’
ELSE DTL.REQUEST_SESSION_ID END AS SPID,
DB_NAME(DTL.RESOURCE_DATABASE_ID) AS DATABASENAME,
SO.NAME AS LOCKEDOBJECTNAME,
DTL.RESOURCE_TYPE AS LOCKEDRESOURCE,
DTL.REQUEST_MODE AS LOCKTYPE,
ST.TEXT AS SQLSTATEMENTTEXT,
ES.LOGIN_NAME AS LOGINNAME,
ES.HOST_NAME AS HOSTNAME,
CASE TST.IS_USER_TRANSACTION
WHEN 0 THEN ‘SYSTEM TRANSACTION’
WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,
AT.NAME AS TRANSACTIONNAME,
DTL.REQUEST_STATUS
FROM
SYS.DM_TRAN_LOCKS DTL
JOIN SYS.PARTITIONS SP ON SP.HOBT_ID = DTL.RESOURCE_ASSOCIATED_ENTITY_ID
JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = SP.OBJECT_ID
JOIN SYS.DM_EXEC_SESSIONS ES ON ES.SESSION_ID = DTL.REQUEST_SESSION_ID
JOIN SYS.DM_TRAN_SESSION_TRANSACTIONS TST ON ES.SESSION_ID = TST.SESSION_ID
JOIN SYS.DM_TRAN_ACTIVE_TRANSACTIONS AT ON TST.TRANSACTION_ID = AT.TRANSACTION_ID
JOIN SYS.DM_EXEC_CONNECTIONS EC ON EC.SESSION_ID = ES.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EC.MOST_RECENT_SQL_HANDLE) AS ST
WHERE
RESOURCE_DATABASE_ID = DB_ID()
ORDER BY DTL.REQUEST_SESSION_ID

Sample Result

Remarks

  1. Column request_session_id display the session ID owns the lock or raise request.

    If value display is -2 indicates that the request belongs to an orphaned distributed transaction.

    If value display is -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.

  2. Column request_owner_type details:
    • TRANSACTION = The request is owned by a transaction.
    • CURSOR = The request is owned by a cursor.
    • SESSION = The request is owned by a user session.
    • SHARED_TRANSACTION_WORKSPACE = The request is owned by the shared part of the transaction workspace.
    • EXCLUSIVE_TRANSACTION_WORKSPACE = The request is owned by the exclusive part of the transaction workspace.
    • NOTIFICATION_OBJECT = The request is owned by an internal SQL Server component.
  3. To use this DMV, User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150603.1 | Last Updated 31 Jan 2014
Article Copyright 2014 by Rohitmssqlfun
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid