Move from: bobsql.com
I received a question from Jonathan as he read over prior posts on locking and I cannot take credit for the question nor the answer, just being the middle man for this transaction.
Question:
“The way I have understood lock partitioning and a regular index rebuild to interact, is that the final SCH-M lock for the object is taken by acquiring it across all of the lock partitions, in ascending order to avoid any deadlock conditions. More precisely I understood that it does not even attempt to acquire the SCH-M in lock partition 4 say, until it has acquired it successfully in lock partition 3. Once the SCH-M is acquired in the final partition it's good to go.
If we attempt the same with low priority waits for the index rebuild ... on a non-partitioned system it seems straightforward, if my SCH-M request is blocked by an existing SCH-S, and a new SCH-S request arrives, it is not blocked by me. If we apply the same behavior on each lock partition in ascending order, then at the point where I am trying to acquire SCH-M in the final partition, that would mean I already have acquired it in all the lower partitions, and I would be blocking processes on every other scheduler.
Do you know how this works?” - Jonathan Kehayias
The developer’s (Panagiotis) answer:
The goal of WAIT_AT_LOW_PRIORITY is to avoid blocking any other requests while waiting for a lock. In the case of lock partitioning, the lock might be acquired on a few partitions and then end up waiting on partition 4 because there is a conflicting lock being held there. If we simply waited with low priority on partition 4, we would not block any new requests on partition 4, but since we are holding locks on earlier partitions the user requests would be blocked. Based on that, when WAIT_AT_LOW_PRIORITY is used, we wait with low priority on the first partition and if acquired we attempt to lock all other partitions without waiting. If we can’t take the lock on partition 4, we will unlock all earlier partitions to eliminate blocking and start waiting with low priority on 4. Once the lock on partition 4 is acquired, we follow the same process of acquiring the next partitions without waiting, cycling back to partition 0 once we hit the last partition.
Bob Dorr
Microsoft
