Lock compatibility and lock sufficiency

Two basic concepts underlie issues of locking and concurrency:

Lock compatibility affects performance when users needs to acquire a lock on a row or page, and that row or page is already locked by another user with an incompatible lock. The task that needs the lock waits, or blocks, until the incompatible locks are released.

Lock sufficiency works with lock compatibility. If a lock is sufficient, the task does not need to acquire a different type of lock. For example, if a task updates a row in a transaction, it holds an exclusive lock. If the task then selects from the row before committing the transaction, the exclusive lock on the row is sufficient; the task does not need to make an additional lock request. The opposite case is not true: if a task holds a shared lock on a page or row, and wants to update the row, the task may need to wait to acquire its exclusive lock if other tasks also hold shared locks on the page.

Table 2-4 summarizes the information about lock compatibility, showing when locks can be acquired immediately.

Table 2-4: Lock compatibility

Can another process immediately acquire:

If one process has:

A Shared Lock?

An Update Lock?

An Exclusive Lock?

A Shared Intent Lock?

An Exclusive Intent Lock?

A Shared Lock

Yes

Yes

No

Yes

No

An Update Lock

Yes

No

No

N/A

N/A

An Exclusive Lock

No

No

No

No

No

A Shared Intent Lock

Yes

N/A

No

Yes

Yes

An Exclusive Intent Lock

No

N/A

No

Yes

Yes

Table 2-5 shows the lock sufficiency matrix.

Table 2-5: Lock sufficiency

Is that lock sufficient if the task needs:

If a task has:

A Shared Lock

An Update Lock

An Exclusive Lock

A Shared Lock

Yes

No

No

An Update Lock

Yes

Yes

No

An Exclusive Lock

Yes

Yes

Yes