Lock compatibility (Database engine)

Lock compatibility Controls whether multiple transactions can acquire locks on the same resource at the same time. If a resource is locked by another transaction, a new lock request is granted only if the mode of the request lock is compatible with the mode of the existing lock. If the mode of the request lock is incompatible with the mode of the existing lock, the transaction requesting the new lock waits for the existing lock to be freed or the lock timeout interval to expire. For example, there is no lock mode compatible with an exclusive lock. If you have an exclusive (x) lock, no other transaction can acquire any type of (shared, updated, or exclusive) lock on the resource until the exclusive lock (x Lock) is released. Alternatively, if a shared lock (S-Lock) has been applied to a resource, other transactions can acquire a shared or update lock (U Lock) for the item, even if the first transaction has not yet completed. However, other transactions cannot acquire an exclusive lock until the shared lock is released.

The following table shows the compatibility of the most common lock modes.

mode

	Existing Grant
Request mode	Is	S	U	Ix	SIX	X
Intent Sharing (IS)	Is	Is	Is	Is	Is	Whether
Share (S)	Is	Is	Is	Whether	Whether	Whether
Update (U)	Is	Is	Whether	Whether	Whether	Whether
Intent Exclusive (IX)	Is	Whether	Whether	Is	Whether	Whether
Intent exclusive Sharing (SIX)	Is	Whether	Whether	Whether	Whether	Whether
Exclusive (X)	Whether	Whether	Whether	Whether	Whether	Whether

Attention:
The intent exclusive Lock (IX Lock) is compatible with the IX lock mode because IX indicates that it intends to update only part of the row instead of all rows. It also allows other transactions to attempt to read or update portions of a row, as long as the rows are not the rows currently updated by other transactions.

Complete lock compatibility matrix

Use the following table to determine the compatibility of all available lock modes in Microsoft SQL Server.

Lock compatibility (Database engine)