SQL Server - Use sys.dm_tran_locks to find requests waiting for an exclusive lock

19 November,2012 by Jack Vamvas

Users reported  an application delay on one of the queries. Upon analysis I noticed a transaction lock request on CONVERT. The  DMV sys.dm_tran_locks reported the information I needed to troubleshoot the delay

An example of a typical scenario is where two requests have a shared (S) lock on the same row. Then one of the requests will attempt to upgrade to an exclusive (X) lock. As there is already an existing Shared (S) Lock – which is incompatible with Exclusive (X) Locks – the lock request  changes to CONVERT and remains on CONVERT until the other Shared (S) Lock is released

 Use the DMV sys.dm_tran_locks to identify Exclusive Locks and attempts on obtaining an Exclusive Lock

 What is the current status ? check the request_status  column

GRANTED = locked request is granted state

WAIT = a request is waiting for a lock type

CONVERT = request already granted a lock but attempting escalate

Use this query to obtain information on existing locks broken down per database.

 

  select resource_type,db_name(resource_database_id),
resource_associated_entity_id,
request_mode,request_type,
request_status
from sys.dm_tran_locks

  


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - Use sys.dm_tran_locks to find requests waiting for an exclusive lock


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer