SQL Server lock mechanism

How to view Locks

Understanding the locking of SQL Server at a certain point in time is undoubtedly an effective means of learning to lock and diagnose database deadlock and performance. The most common way to view database locks is in two ways:

Use sys.dm_tran_locks this DMV

SQL Server provides sys.dm_tran_locks this DMV to view the locks in the current database, as shown in the previous Figure 2 by this DMV.

It is worth noting that sys.dm_tran_locks this DMV sees a database lock at a point in time, and does not contain any history lock records. Can be understood as a snapshot of how the database locks up at the point of query time. The information contained in Sys.dm_tran_locks is divided into two categories, the information describing the resource in which the lock is located at the beginning of the resource, and another class of information that describes the requested lock itself, starting with request.

Select
STR (request_session_id,4,0) as SPID,
CONVERT (varchar), db_name (resource_database_id)) as Db_name,
case when resource_data_id = db_id () and resource_type= ' OBJECT '
Then convert (char (), object_name (resource_associated_entity_id))
else convert (char (), resource_associated_entity_id)
End As Object,
CONVERT (varchar (), resource_type) as Resrc_type,
CONVERT (varchar (), request_type) as Req_type,
Convert (char (3), Request_mode) as mode,
CONVERT (varchar (8), request_status) as Status
From sys.dm_tran_locks
ORDER BY request_session_id Desc

SQL Server lock mechanism