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

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.