Lock Compatibility
If a resource is already locked by another transaction and a second transaction needs access to the resource, Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) determines whether the second lock is granted based on the compatibility of the lock mode that is used by the first transaction.
Only compatible lock types can be put on a resource that is already locked. For example, while an exclusive (X) lock is held, no other transaction can acquire a shared, update, or exclusive lock on that resource until the X 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 by increasing lock strength.
The following table shows the requested lock modes and their compatibility with the existing lock 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 mode because IX indicates that the intention is to update only some of the rows instead of all of them. Other transactions are also permitted to read or update some of the rows, provided that the affected rows are not the same rows being updated by other transactions.
Lock Compatibility Matrix
SQL Server Compact Edition supports the following range of locks:
- Sch-S, Sch-X, S, U, X, IS, IU, IX, SIU, SIX, UIX
Note
The schema stability (Sch-S) lock is compatible with all lock modes except the schema modification (Sch-M) lock mode. The Sch-M lock is incompatible with all lock modes.
For the matrix of lock compatibility, see SQL Server Books Online.
See Also
Concepts
Understanding Locking
Displaying Locking Information
Customizing Locking