SQL Server 2008: Lock escalation changes


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.

2 thoughts on “SQL Server 2008: Lock escalation changes

  1. Do you ever resort to using another connection and running (select top 0 * from bigTable) to stop escalation being possible?
    I wanted to target just a particular action that happens each hour…
    This gets me the fine grained control without an alter table

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.