Figure 2-4 illustrates how the demand lock scheme works for serial query execution. It shows four tasks with shared locks in the active lock position, meaning that all four tasks are currently reading the page. These tasks can access the same page simultaneously because they hold compatible locks. Two other tasks are in the queue waiting for locks on the page. Here is a series of events that could lead to the situation shown in Figure 2-4:
Originally, task 2 holds a shared lock on the page.
Task 6 makes an exclusive lock request, but must wait until the shared lock is released because shared and exclusive locks are not compatible.
Task 3 makes a shared lock request, which is immediately granted because all shared locks are compatible.
Tasks 1 and 4 make shared lock requests, which are also immediately granted for the same reason.
Task 6 has now been skipped three times, and is granted a demand lock.
Task 5 makes a shared lock request. It is queued behind task 6’s exclusive lock request because task 6 holds a demand lock. Task 5 is the fourth task to make a shared page request.
After tasks 1, 2, 3, and 4 finish their reads and release their shared locks, task 6 is granted its exclusive lock.
After task 6 finishes its write and releases its exclusive page lock, task 5 is granted its shared page lock.
Figure 2-4: Demand locking with serial query execution
