Viewing Lock Activity

This recipe shows you how to monitor locking activity in the database using the new SQL Server
2005 sys.dm_tran_locks dynamic management view. It uses a table locking hint
======================================================================

    ■Note The sys.dm_tran_locks view is a replacement of the deprecated sp_lock system stored procedure
       used to monitor activity in SQL Server 2000.
======================================================================

In the first part of this recipe, a new query editor window is opened, and the following command
is executed:

USE AdventureWorks
BEGIN TRAN
SELECT ProductID, DocumentID, ModifiedDate
FROM Production.ProductDocument
WITH (TABLOCKX)

In a second query editor window, the following query is executed:

SELECT request_session_id sessionid,
resource_type type,
resource_database_id dbid,
OBJECT_NAME(resource_associated_entity_id) objectname,
request_mode rmode,
request_status rstatus
FROM sys.dm_tran_locks

This returns information about the locking session identifier (server process id spid), the

resource being locked, the database, object, resource mode, and lock status:

How It Works ??

The example began by starting a new transaction and executing a query against the Production.ProductDocument table using a TABLOCKX locking hint (this hint places an exclusive lock on the table). In order to monitor what locks are open for the current SQL Server instance, the sys.dm_tran_locks dynamic management view was queried. It returned a list of active locks in the AdventureWorks database. The exclusive lock on the ProductDocument table could be seen in the last row(7th) of the results.

Use sys.dm_tran_locks to troubleshoot unexpected concurrency issues. For example, a query session may be holding locks longer than desired, or issuing a lock resource granularity or lock mode that you hadn’t expected (perhaps a table lock instead of a finer grained row or page lock). Understanding what is happening at the locking level can help you troubleshoot your queries more effectively.