How It Works: SQL Server Locking WAIT_WITH_LOW_PRIORITY

How It Works: SQL Server Locking WAIT_WITH_LOW_PRIORITY

 

 

I received a question from Jonathan as he read over prior posts on locking (https://bobsql.com/how-it-works-sql-server-lock-partitioning​​ and​​ https://bobsql.com/how-it-works-sql-server-lock-iteration-enumeration) ​​​​ 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