Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
    
@SqlRyan You're welcome. In response to your question about how SQL Server decides to start with row- or page-level locking, that's a storage engine decision, the basis of which is not documented. That said, it is a fair bet that the decision is based at least in part on the expected number of rows/pages that would need to be touched and the effective isolation level (determining how long locks will be held). Secret sauce, no doubt :) –  Paul White Dec 7 '12 at 23:08

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
@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
    
There's also the issue of backward-compatibility. –  Paul White Dec 7 '12 at 0:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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