Lock compatibility and lock sufficiency

Two basic concepts underlie issues of locking and concurrency:

Lock compatibility: if task holds a lock on a page or row, can another row also hold a lock on the page or row?
Lock sufficiency: for the current task, is the current lock held on a page or row sufficient if the task needs to access the page again?

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 10-4 summarizes the information about lock compatibility, showing when locks can be acquired immediately.

**Table 10-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 10-5 shows the lock sufficiency matrix.

**Table 10-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