How to know what locks are held on the database.

Problem: How to know what locks are held on the database.

Solution: sys.dm_tran_locks (Transact-SQL)

This dynamic management view stores the information about the locks and resources, results are categorized by requests and resources.

Locks can happen inside SQL server database for access rights in between sessions or resources fighting on the same objects. When you have a OLTP database where concurrent transactions occur this can be helpful to resolve the problems.

Alternatively, there is a system procedure as well which provides information about locks. SP_Lock, but this system procedure is going to be deprecated in the future versions.

Example:

In this article I am going to show you how we can retrieve Lock information, information related to processes or resources.

Example 1:

There is a lot of information that results out from this view out here, we have to be cautious of few things and rest can be ignored.

select   resource_type,

request_mode,

request_status,

request_session_id,

*

 from sys.dm_Tran_locks

OUTPUT :

Example 2:

This gives out a minimal amount of locks information, spid along with the locks.

EXEC  sp_lock

OUTPUT :