|
See Also: Main_Page - Architecture & Configuration - Dynamic Management Objects - Transaction Related Dynamic Objects
Dynamic objects of this category display information about current transactions and corresponding locks. All objects of this category will begin with "dm_tran" prefix. Sys.dm_tran_locks returns lock manager resources and current requests for these resources at the SQL Server instance level. The following table documents name and description of each column returned by dm_tran_locks:
| Column Name | Description | | Resource_type | Can be one of the following values: database, (database or transaction log) file, object, page, key, extent, RID (row id), application, metadata, HOBT (heap or B-tree) or allocation unit. | | Resource_subtype | If applicable, this column provides additional information about the request type. | | Resource_database_id | Identifies the database in which the requested resource is hosted. | | Resource_description | If applicable, this column provides information not available in other columns about the requested resource. For example, it could contain identifier for objects stored in sys.objects catalog view, file identifier from sys.database_files catalog view, file and page identifier for a specific database file or principal database identifier for scoping the application lock resource. | | Resource_additional_entity_id | Identifies objects if the lock resources requested are for tables, views, stored procedures, or extended procedures. If locks are requested for lower level objects such as data pages, rows, or keys; this column contains HOBT ID from sys.partitions catalog view. The column has a value of 0 for databases, files, extents or metadata requests. The column will contain allocation_unit_id from sys.allocation_units for lock requests on allocation units. | | Resource_lock_partition | Partitioned lock resource (table or index) identifier. For non-partitioned lock resources the column will display 0. | | Request_mode | Either granted or requested lock mode. As an example: shared, schema-sharing, exclusive, etc. | | Request_type | The value of LOCK is displayed for all requests. | | Request_status | This column has one of three values: GRANT, WAIT or CONVERT. The value of CONVERT means that the requestor has been granted a request but is waiting to upgrade to the initial request to be granted. | | Request_reference_count | Approximate number of times the same requestor has submitted a request for this resource. | | Request_lifetime | Internal column. Do not use. | | Request_session_id | Identifies the session that owns the current request. This is session_id from sys.dm_exec_connections DMV (or spid from sysprocesses view which can also be found in the output of sp_who2 system procedure). This identifier can change for distributed and bound transactions. If the column has a value of -2, the request is owned by an orphaned distributed transaction. If the column has a value of -3, the request is owned by a transaction for which recovery could not be successfully completed therefore its rollback is deferred. | | Request_exec_context_id | Execution context id of the process that owns the transaction. | | Request_request_id | Batch identifier of the Multiple Active Result Set (MARS) connection that currently owns the request. | | Request_owner_type | Entity type which owns the request. This column could have following values: TRANSACTION, CURSOR, SESSION, SHARED_TRANSACTION_WORKSPACE or EXCLUSIVE_TRANSACTION_WORKSPACE. | | Request_owner_id | For transactions requesting a lock, this column will contain transaction identifier found in sys.dm_tran_active_transactions DMV. | | Request_owner_guid | This column is only relevant for distributed transactions; it displays globally unique identifier for MS DTC transactions. | | Request_owner_lockspace_id | Internal column. Do not use. | | Lock_owner_address | Memory address of the internal data structure used to track the current request. You can use this column to join sys.dm_tran_locks with sys.dm_os_waiting_tasks (on resource_address column). Such join will display additional information (wait type, blocking session id) for requests that haven't been granted and are waiting. |
The following query returns a few columns from dm_tran_locks and translates resource_associated_entity_id column into requested object's name:
SELECT resource_type, (CASE WHEN resource_type = 'OBJECT' THEN object_name(resource_associated_entity_id) WHEN resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN 'N/A' WHEN resource_type IN ('KEY', 'PAGE', 'RID') THEN ( SELECT object_name(object_id) FROM sys.partitions WHERE hobt_id=resource_associated_entity_id ) ELSE 'Undefined' END) AS requested_object_name, request_mode AS lock_type, request_status, request_owner_id AS transaction_id FROM sys.dm_tran_locks
You can use lock_owner_address column to join sys.dm_tran_locks with sys.dm_os_waiting_tasks (on resource_address column). Such a join will display additional information (wait type, blocking session id) for requests that haven't been granted and are waiting, as in the following query:
SELECT resource_type, request_mode, request_type, request_status, request_lifetime, session_id, wait_duration_ms, wait_type, blocking_session_id, b.resource_description FROM sys.dm_tran_locks a INNER JOIN sys.dm_os_waiting_tasks b ON a.lock_owner_address = b.resource_address
Other transaction related dynamic objects include:
|