SQL Server supports lock escalation – when the server decides to move from a large number of row or page locks on an object to a table-level lock. Sunil Agarwal posted a great description of lock escalation in SQL Server 2005 on the Storage Engine blog so I won’t repeat it all here.


The problem with lock escalation is that it can be tricky to manage on systems that have conflicting requirements.


Disabling lock escalation


For example, if a table needs to support large batch updates with concurrent user queries, then having the batch update cause an escalation to a table-level exclusive lock prevents the user queries from running. There are a couple of documented trace flags that can be used to disable lock escalation:



  • 1211 – disables lock escalation totally and will allow lock memory to grow to 60% of dynamically allocated memory (non-AWE memory for 32-bit and regullar memory for 64-bit) and will then further locking will fail with an out-of-memory error
  • 1224 – disables lock escalation until 40% of memory is used and then re-enables escalation

The problem with these two trace flags are that they are instance-wide and turning them on can cause huge performance issues if a poorly-written application takes too many locks. It’s not possible to disable lock escalation for a single table – until now!


SQL Server 2008 includes the ability to disable lock escalation per-table!! This is a fantastic step forward in concurrency management.


Changing the escalation mechanism


To extend the example above, what about if the table has multiple partitions? With the batch update only affecting a single partition and concurrent user queries going against other partitions, the escalation policy in SQL Server 2005 means that the batch update will escalate to a table-level exclusive lock and freeze out the user queries, even though they’re going against different partitions. The only recourse is to disable lock escalation – until now!


SQL Server 2008 includes the ability to specify partition-level lock escalation instead of table-level lock escalation. And this is per-table! Very cool.


Summary


SQL Server 2008 will have ALTER TABLE syntax to specify per-table lock escalation management. The options will be:



  • Automatic determination of the level to escalate to. If the table is partitioned, locks will be escalated to the partition-level.
  • Table-level lock escalation (even if the table is partitioned).
  • Disable lock escalation

Once this feature is available in a CTP I’ll blog about the syntax and supporting infrastructure, along with some examples.