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 already locked by another transaction, a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock. If the mode of the requested lock is not compatible with the existing lock, the transaction requesting the new lock waits for the existing lock to be released or for the lock timeout interval to expire. For example, no lock modes are compatible with exclusive locks. While an exclusive (X) lock is held, no other transaction can acquire a lock of any kind (shared, update, or exclusive) on that resource until the exclusive (X) lock is released. Alternatively, if a shared (S) lock has been applied to a resource, other transactions can also acquire a shared lock or an update (U) lock on that item even if the first transaction has not completed. However, other transactions cannot acquire an exclusive lock until the shared lock has been released.
The following table shows the compatibility of the most commonly encountered lock modes.
| Existing granted mode | ||||||
|---|---|---|---|---|---|---|
Requested mode |
IS |
S |
U |
IX |
SIX |
X |
Intent shared (IS) |
Yes |
Yes |
Yes |
Yes |
Yes |
No |
Shared (S) |
Yes |
Yes |
Yes |
No |
No |
No |
Update (U) |
Yes |
Yes |
No |
No |
No |
No |
Intent exclusive (IX) |
Yes |
No |
No |
Yes |
No |
No |
Shared with intent exclusive (SIX) |
Yes |
No |
No |
No |
No |
No |
Exclusive (X) |
No |
No |
No |
No |
No |
No |
Note
An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions.
Complete Lock Compatibility Matrix
Use the following table to determine the compatibility of all the lock modes available in Microsoft SQL Server 2005.
.gif "Diagram showing lock compatibility matrix")
See Also
Concepts
Lock Granularity and Hierarchies
Lock Modes
Key-Range Locking