The most important utility to display lock information is a dynamic management view called sys.dm_tran_locks. This view returns information about currently active lock manager resources. Each row represents a currently active request for a lock that has been granted or is waiting to be granted. The columns of this view relate to two 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.
The most important columns of this view are as follows:
- resource_type Represents the resource type
- resource_database_id Specifies the ID of the database under which this resource is scoped
- request_mode Specifies the mode of the request
- request_status Specifies the current status of the request
Example 1 displays all the locks that are in a wait state.