How It Works: SQL Server Locking WAIT_WITH_LOW_PRIORITY


Move from:


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.



“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