--- PDF PAGE 694 ---
687
Observing Transactions, Locks, and Deadlocks
SQL Server provides a set of DMVs that expose information about current transactions 
and locks. The following sections explore the metadata available.
Observing Transactions
The sys.dm_tran_active_transactions DMV details the current transactions within 
the instance. This DMV returns the columns described in Table 18-11.
Note  Undocumented columns have been omitted from DMVs in this chapter.
Table 18-11.  Columns Returned by sys.dm_tran_active_transactions
Column
Description
transaction_id
The unique ID of the transaction.
name
The name of the transaction. If the transaction has not been 
marked with a name, then the default name is displayed—e.g.,  
"user_transaction".
transaction_begin_time
The date and time that the transaction started.
transaction_type
An integer value depicting the type of transaction.
•  1 indicates a read/write transaction.
•  2 indicates a read-only transaction.
•  3 indicates a system transaction.
•  4 indicates a distributed transaction.
transaction_uow
A unit of work ID that MSDTC (Microsoft Distributed Transaction 
Coordinator) uses to work with distributed transactions.
(continued)
Chapter 18  Locking and Blocking

--- PDF PAGE 695 ---
688
Table 18-11.  (continued)
Column
Description
transaction_state
The current status of the transaction.
•  0 indicates that the transaction is still initializing.
•  1 indicates that the transaction is initialized but has not yet 
started.
•  2 indicates that the transaction is active.
•  3 indicates that the transaction has ended. This status is 
only applicable to read-only transactions.
•  4 indicates that the commit has been initiated. This status 
is only applicable to distributed transactions.
•  5 indicates that the transaction is prepared and awaiting 
resolution.
•  6 indicates that the transaction has been committed.
•  7 indicates that the transaction is being rolled back.
•  8 indicates that the rollback of a transaction has finished.
dtc_state
Indicates the state of a transaction on an Azure database.
•  1 indicates that the transaction is active.
•  2 indicates that the transaction is prepared.
•  3 indicates that the transaction is committed.
•  4 indicates that the transaction is aborted.
•  5 indicates that the transaction is recovered.
The script in Listing 18-13 indicates how to use sys.dm_tran_active_transactions 
to find details of long-running transactions. The query looks for transactions that have 
been running for longer than 10 minutes and returns information including their current 
state, the amount of resources they are consuming, and the login that is executing them.
Tip  In a test environment, begin a transaction but do not commit it 10 minutes 
before running this query.
Chapter 18  Locking and Blocking

--- PDF PAGE 697 ---
690
        ,SUSER_SNAME(es.security_id) LoginRunningTransaction
        ,es.memory_usage * 8 MemUsageKB
        ,es.reads
        ,es.writes
        ,es.cpu_time
 FROM sys.dm_tran_active_transactions tat
 INNER JOIN sys.dm_tran_session_transactions st
        ON tat.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions es
        ON st.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
        ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT
 WHERE st.is_user_transaction = 1
        AND tat.transaction_begin_time < DATEADD(MINUTE,-10,GETDATE()) ;
The query works by joining to sys.dm_exec_sessions via sys.dm_tran_session_
transactions. This DMV can be used to correlate transactions with sessions, and it 
returns the columns described in Table 18-12.
Table 18-12.  sys.dm_tran_session_transactions Columns
Column
Description
session_id
The ID of the session in which the transaction is running.
transaction_id
The unique ID of the transaction.
transaction_descriptor
The ID used to communicate with the client driver.
enlist_count
The number of active requests in the session.
is_user_transaction
Indicates if the transaction is a user or a system transaction. 0 
indicates a system transaction and 1 indicates a user transaction.
is_local
Indicates if the transaction is distributed. 0 indicates a distributed 
transaction and 1 indicates a local transaction.
is_enlisted
Indicates that a distributed transaction is enlisted.
is_bound
Indicates if the transaction is running in a bound session.
open_transaction_count
A count of open transactions within the session.
Chapter 18  Locking and Blocking

--- PDF PAGE 698 ---
691
Observing Locks and Contention
Details of current locks on the instance are exposed through a DMV called sys.dm_tran_
locks. This DMV returns the columns detailed in Table 18-13.
Table 18-13.  sys.dm_tran_locks
Column
Description
resource_type
The resource type on which the lock has been placed.
resource_subtype
The subtype of the resource type that has a lock placed 
on it. For example, if you are updating the properties of a 
database, then the resource_type is METADATA and 
the resource_subtype is DATABASE.
resource_database_id
The ID of the database that contains the resource that has 
a lock placed on it.
resource_description
Additional information about the resource that is not 
contained in other columns.
resource_associated_entity_id
The ID of the database entity with which the resource is 
associated.
resource_lock_partition
The partition number of the lock, if lock partitioning is 
being used.
request_mode
The locking mode that has been requested or acquired. 
For example, S for a shared lock or X for an exclusive lock.
request_type
The request_type is always LOCK.
request_status
The current status of the lock request. Possible values 
are ABORT_BLOCKERS, CONVERT, GRANTED, LOW_
PRIORITY_CONVERT, LOW_PRIORITY_WAIT, and WAIT.
request_reference_count
The number of times that the requestor has requested a 
lock on the same resource.
request_session_id
The session ID that currently owns the request. It is 
possible for the session ID to change if the transaction is 
distributed.
(continued)
Chapter 18  Locking and Blocking

--- PDF PAGE 699 ---
692
Table 18-13.  (continued)
Column
Description
request_exec_context_id
The execution ID of the process that requested the lock.
request_request_id
The Batch ID of the batch that currently owns the request. 
This ID can change if Multiple Active Result Sets (MARS) 
are being used by the application.
request_owner_type
The type of the owner of the lock request. Possible vales 
are TRANSACTION, SESSION, and CURSOR for user 
operations. Values can also be SHARED_TRANSACTION_
WORKSPACE and EXCLUSIVE_TRANSACTION_
WORKSPACE, which are used internally to hold locks for 
enlisted transactions, or NOTIFICATION_OBJECT, which 
is used by internal SQL Server operations.
request_owner_id
The ID of the transaction that owns the lock request, 
unless the request was made by a FileTable, in which case 
-3 indicates a table lock, -4 indicates a database lock, 
and other values indicate the file handle of the file.
request_owner_guid
A GUID identifying the request owner. Only applicable to 
distributed transactions.
lock_owner_address
Memory address of the request’s internal data structure.
The sys.dm_os_waiting_tasks DMV returns information about tasks that are 
waiting on resources, including locks. The columns returned by this DMV are detailed 
in Table 18-14. This DMV can be used with sys.dm_tran_locks to find the details of 
processes that are blocked and blocking, due to locks.
Chapter 18  Locking and Blocking

--- PDF PAGE 699 ---
692
Table 18-13.  (continued)
Column
Description
request_exec_context_id
The execution ID of the process that requested the lock.
request_request_id
The Batch ID of the batch that currently owns the request. 
This ID can change if Multiple Active Result Sets (MARS) 
are being used by the application.
request_owner_type
The type of the owner of the lock request. Possible vales 
are TRANSACTION, SESSION, and CURSOR for user 
operations. Values can also be SHARED_TRANSACTION_
WORKSPACE and EXCLUSIVE_TRANSACTION_
WORKSPACE, which are used internally to hold locks for 
enlisted transactions, or NOTIFICATION_OBJECT, which 
is used by internal SQL Server operations.
request_owner_id
The ID of the transaction that owns the lock request, 
unless the request was made by a FileTable, in which case 
-3 indicates a table lock, -4 indicates a database lock, 
and other values indicate the file handle of the file.
request_owner_guid
A GUID identifying the request owner. Only applicable to 
distributed transactions.
lock_owner_address
Memory address of the request’s internal data structure.
The sys.dm_os_waiting_tasks DMV returns information about tasks that are 
waiting on resources, including locks. The columns returned by this DMV are detailed 
in Table 18-14. This DMV can be used with sys.dm_tran_locks to find the details of 
processes that are blocked and blocking, due to locks.
Chapter 18  Locking and Blocking

--- PDF PAGE 700 ---
693
Table 18-14.  sys.dm_os_waiting_tasks Columns
Column
Description
waiting_task_address
The address of the task that is waiting.
session_id
The ID of the session in which the waiting task is running.
exec_context_id
The ID of the thread and sub-thread that is running the task.
wait_duration_ms
The duration of the wait, specified in milliseconds.
wait_type
The type of wait that is being experienced. Waits are discussed 
in Chapter 17.
resource_address
The address of the resource the task is waiting for.
blocking_task_address
Indicates the address of the task that is currently consuming the 
resource.
blocking_session_id
The Session ID of the task that is currently consuming the 
resource.
blocking_exec_context_id
The ID of the thread and sub-thread of the task that is currently 
consuming the resource.
resource_description
Additional information about the resource, which is not contained 
in other columns, including the lock resource owner.
The script in Listing 18-14 demonstrates how to use sys.dm_tran_locks and sys.
dm_os_waiting_tasks to identify blocking on the instance. The script contains three 
parts, each of which you should run in a separate query window. The first two parts of 
the script cause contention. The third part identifies the source of the contention.
Listing 18-14.  Using sys.dm_tran_locks
--Part 1 - Run in 1st query window
BEGIN TRANSACTION
UPDATE Customers
SET CreditLimit = CreditLimit ;
Chapter 18  Locking and Blocking