sys.dm_tran_locks – Day 47 – One DMV a Day

Hello Geeks and welcome to the Day 47 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

So far we have covered index, IO, database, OS, memory and execution related DMVs. Today I am going to start with transaction related DMVs. These DMVs give more granular information at the transaction level. I will start with most commonly used and well known DMV, sys.dm_tran_locks.

Sys.dm_tran_locks give lock level information for each transaction. This returns one row for each lock held by current transactions on SQL Server Instance. It gives the below important columns.

resource_type – Type of resource on which the lock is applied.
resource_database_id – The database ID to which the resource belongs.
resource_description – Additional description of the resource.
resource_associated_entity_id – ID of the entity to which the resource is associated. This includes Object ID, HOBT ID or Allocation Unit ID based on the resource type.
resource_lock_partition – Partition of the resource on which lock is acquired.
request_mode – Granted requests are shown as grant. Waiting requests show the mode of request.
request_type – It is always LOCK.
request_status – Status of the requested lock. The values can be GRANTED, CONVERT, WAIT, LOW_PRIORITY_CONVERT/WAIT or ABORT_BLOCKERS.
request_session_id – Session IS of the requested session. -2 is used for orphaned transactions and -3 is used for deferred transactions.
request_request_id – Execution contect of the request to which the lock request belongs.
request_owner_type – Entity type that owns the request. Possible values are TRANSACTION, SESSION, SHARED_TRANSACTION_WORKSPACE, EXCLUSIVE_TRANSACTION_WORKSPACE and NOTIFICATION_OBJECT.
request_owner_id – ID of the owner of the resource. E.g.: Transaction ID.
lock_owner_address – Maps to resource_address in sys.dm_os_waiting_tasks.

Let us use sys.dm_tran_locks to see the above details of a particular session. I will demonstrate a scenario were we will observe the lock escalation to table level lock.

BEGIN TRANSACTION

UPDATE pubLogger_tbl
	 SET eName = eName
WHERE eId < 100

SELECT Resource_type,
	 Resource_database_id,
	 Resource_description,
	 Resource_associated_entity_id,
	 Resource_lock_partition,
	 Request_mode,
	 Request_type,
	 Request_status,
	 Request_session_id,
	 Request_request_id,
	 Request_owner_type,
	 Request_owner_id,
	 Lock_owner_address
FROM sys.dm_tran_locks
WHERE request_session_id = 57

UPDATE pubLogger_tbl
	 SET eName = eName

SELECT Resource_type,
	 Resource_database_id,
	 Resource_description,
	 Resource_associated_entity_id,
	 Resource_lock_partition,
	 Request_mode,
	 Request_type,
	 Request_status,
	 Request_session_id,
	 Request_request_id,
	 Request_owner_type,
	 Request_owner_id,
	 Lock_owner_address
FROM sys.dm_tran_locks
WHERE request_session_id = 57

--COMMIT

There are two result sets from sys.dm_exec_tran_locks. The first one has 100 key locks acquired, one Intent exclusive for object, one Intent exclusive of page and one shared for the database level resources. When you make a connection to database a shared lock is acquired. The request_owner_type will be SHARED_TRANSACTION_WORKSPACE. The page level intent exclusive lock on page is needed in case it wants to hold page level lock. We check the page header of the page 1:311 by using DBCC PAGE command. The object ID matches to the object ID in above result set.

DBCC TRACEON(3604)
DBCC PAGE(12,1,311,3)

The second result from sys.dm_tran_locks you will see only two locks. The Shared lock on database is still valid. But the key level locks are escalated to Table level lock. The escalation happens if in a single transaction there are more than 5000 locks on a single partition of a table. In this case I am updating 100000 rows in the table. This leads to lock escalation to table level exclusive lock.

Holding locks need space in memory. Locks are logical entry in memory specifying the lock held on a resource. They require space in memory. So to optimize memory usage for locks they are escalated to higher level. Thus reducing the memory used for locks. For more information on locks read this.

So now you know how to check locks for a session using sys.dm_tran_locks. Tomorrow I will be covering one more DMV related to transactions. So stay tuned. Till then.

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook