When queries are running in parallel, demand locking treats all the shared locks from a family of worker processes as if they were a single task. The demand lock permits reads from three families (or a total of three serial tasks and families combined) before granting the exclusive lock.
Figure 10-5 illustrates how the demand lock scheme works when parallel query execution is enabled. The figure shows six worker processes from three families with shared locks. A task waits for an exclusive lock, and a worker process from a fourth family waits behind the task. Here is a series of events that could lead to the situation shown in Figure 10-5:
Originally, worker process 1:3 (worker process 3 from a family with family ID 1) holds a shared lock on the page.
Task 9 makes an exclusive lock request, but must wait until the shared lock is released.
Worker process 2:3 requests a shared lock, which is immediately granted because shared locks are compatible. The skip count for task 9 is now 1.
Worker processes 1:1, 2:1, 3:1, task 10, and worker processes 3:2 and 1:2 are consecutively granted shared lock requests. Since family ID 3 and task 10 have no prior locks queued, the skip count for task 9 is now 3, and task 9 is granted a demand lock.
Finally, worker process 4:1 makes a shared lock request, but it is queued behind task 9’s exclusive lock request.
Any additional shared lock requests from family IDs 1, 2, and 3 and from task 10 are queued ahead of task 9, but all requests from other tasks are queued after it.
After all the tasks in the active lock position release their shared locks, task 9 is granted its exclusive lock.
After task 9 releases its exclusive page lock, task 4:1 is granted its shared page lock.
Figure 10-5: Demand locking with parallel query execution
