Only compatible lock types can be placed on a resource that is already locked. For example, 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 lock is released at the end of the first transaction. 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.
Resource lock modes have a compatibility matrix that shows which locks are compatible with other locks obtained on the same resource (listed in increasing lock strength).
| 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 IX lock is compatible with an IX lock because IX means the intention to update only some of the rows rather than all of them. Other transactions that want to read or update some of the rows are also permitted providing they are not the same rows being updated by other transactions.
The schema stability lock (Sch-S) is compatible with all lock modes except the schema modification lock (Sch-M).
The schema modification lock (Sch-M) is incompatible with all lock modes.
The bulk update (BU) lock is compatible only with schema stability and other bulk update locks.