Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Lock Compatibility

If a resource is already locked by another transaction and a second transaction needs access to the resource, Microsoft SQL Server Compact 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 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.

SQL Server Compact supports the following range of locks:

  • Sch-S, Sch-X, S, U, X, IS, IU, IX, SIU, SIX, UIX

Note 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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.