SQL Server request status in sys.dm_tran_locks – GRANT, WAIT and CONVERT

Hi Friends,

sys.dm_tran_locks is a very frequently used DMV to observe the status of locks being held in a SQL Server instance. When we look at the results of the DMV, looking at request_status column is inevitable. This column can have 3 values:

GRANT: the lock request was granted

WAIT: The request to acquire a particular lock type is waiting.

CONVERT: the request was granted earlier with a particular lock status but now is trying to upgrade to another status and is being blocked.

While we see GRANT & WAIT too often and really does not need any explanation, I just wanted to show you the CONVERT status.

Suppose you run the following code in REPEATABLE READ isolation level:

-- query window 1/connection 1
USE AdventureWorks
GO
 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 
BEGIN TRAN
 
SELECT * from Person.Contact
WHERE ContactID = 1

The above code will acquire S (shared) lock on the row and the lock will be held for the entire duration of the transaction because we are running in REPEATABLE READ isolation level. Notice that we have not committed or rolled back the transaction.

In another query window, you run the same code again:

-- query window 2/connection 2
 
USE AdventureWorks
GO
 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 
BEGIN TRAN
 
SELECT * from Person.Contact
WHERE ContactID = 1

The above code will acquire another S (shared) lock on the same row. Since shared locks are compatible to each other both the queries will go through. So, 2 shared locks are being held now. Let us check:

-- query window 3
 
select resource_type,
resource_database_id,
resource_description,
resource_associated_entity_id,
request_mode,
request_type,
request_status
from sys.dm_tran_locks

1_SQL_Server_request_status_in_sysdm_tran_locks_GRANT_WAIT_CONVERT

   

You can see that both the shared locks are being granted. Now if any connection wants to update the same row on which it has already held a shared lock, the connection will be blocked since it wants a X (exclusive) lock which is not compatible with shared lock (which is still) held by the other transaction. In such an event, the request_status will show “CONVERT” for the transaction trying to acquire exclusive lock.

So go back to connection 1 and run the following code:

UPDATE Person.Contact
SET LastName = 'BANSAL'
WHERE ContactID = 1

The above query will now wait indefinitely trying to acquire X lock. Let us check the status:

-- query window 3
 
select resource_type,
resource_database_id,
resource_description,
resource_associated_entity_id,
request_mode,
request_type,
request_status
from sys.dm_tran_locks

2_SQL_Server_request_status_in_sysdm_tran_locks_GRANT_WAIT_CONVERT

You can observe CONVERT status for the transaction trying to acquire X lock.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

One Comment on “SQL Server request status in sys.dm_tran_locks – GRANT, WAIT and CONVERT”

  1. In the query window 3 , in row no. 10 can you tell us why lock SCH-s is coming eventhough we are specifying Contact_id in our query which is type of int not a xml type. Can you please give a more light on this.

Leave a Reply

Your email address will not be published.