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:

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:

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:


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:

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


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