Monday , May 20 2024

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.

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


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


About 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 *