Lock Compatibility On SQL Server

Lock Compatibility provides the necessary control when multiple transactions have the same lock request (row, page).

If the resource is previously locked by a transaction (assuming Exclusive Lock on the resource, for example), then the transaction that wants to put a lock on this resource must be compatible with the first type of lock that the lock wants to put.

If the second incoming lock request is incompatible with the first incoming lock request , it waits for the first lock to finish and then it can put the lock on it.

In our example, the first lock type is Exclusive Lock and the second is Shared Lock.

As you can see in the table below, Exclusive Lock is not compatible with any kind of lock, so the second lock will have to wait for the first lock to finish.

You can find the Lock compatibility table below.

You can find detailed information about the above lock variants in the article “SQL Server Lock Types“.