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.

IS S U IX SIX X
Intent shared (IS) Compatible Compatible Compatible Compatible Compatible Not Compatible
Shared (S) Compatible Compatible Compatible Not Compatible Not Compatible Not Compatible
Update (U) Compatible Compatible Not Compatible Not Compatible Not Compatible Not Compatible
Intent exclusive (IX) Compatible Not Compatible Not Compatible Compatible Not Compatible Not Compatible
Shared with intent exclusive (SIX) Compatible Not Compatible Not Compatible Not Compatible Not Compatible Not Compatible
Exclusive (X) Not Compatible Not Compatible Not Compatible Not Compatible Not Compatible Not Compatible

 

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

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *