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

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

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