In SQL Server, locks are normally escalated from row or page -> table. Starting with SQL Server 2008, a new level of lock escalation was added - partition level.

However, this isn't automatically enabled for partitioned tables - by default, the table is set to skip partition locking and go right from row or page -> table. Why would this be the case? Is there a reason why I wouldn't want to switch all my tables from TABLE to AUTO so that they escalate locks to partition level instead of to table level?

Since the default is still TABLE, I figure there must be something I'm missing about the downside of AUTO.

  • in SQL Server locks are never escalated from rows to the page; it is always escalated from rows to the table. – SQL Learner Dec 6 '12 at 21:11
  • @MartinSmith Thanks for the clarification - you learn something new every day, and the linked article + some Googling has confirmed that I'm mistaken! I light of this, why would SQL Server perform a page-level lock rather than a row-level one immediately (before escalating to table)? It seems like it would always start with row, but maybe page makes more sense if I'm updating a range rather than a single row (or very small subset of rows). – SqlRyan Dec 6 '12 at 21:24
  • @SQLKiwi: Thanks for the cleanup - it's clear my understanding of lock escalation was a bit off :) – SqlRyan Dec 7 '12 at 22:14

BOL has answer -- It is not ON by default as it may increases the potential for deadlock.

The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.

HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better.

  • @MartinSmith It seems that Paul also includes this line in the article you provided above: "You may ask why the AUTO option isn't the default in SQL Server 2008? It's because some early-adopters found that their applications started to deadlock using that option. So, just as with the lock escalation trace flags, be careful about turning on the AUTO option in SQL Server 2008." – SqlRyan Dec 6 '12 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.