Lock Escalation Limit

  • I don't much like the question, because the correct answer is (row locks are never escalated to page locks) is not available as an option. Of course it's easy enough to say "OK, that was just a mistake, I 'll pick 5000 because that's the threshold for escalation to table (or sometime partition) locks" if you already know how escalation works. That however doesn't help someone who reads the question and doesn't know the answer, makes a guess, sees that the answer is 5000, and goes away with the impression that he has now learnt that row locks are escalated to page locks. The idea of QOTD is to help people learn (while having a bit of fun) not to mislead them.


  • That depends. There is a lock escalation hierarchy, usually after page it is an extent (group of 8 pages). For more on escalation hierarchy, refer the link below:


    Furthermore, this hierarchy can be altered with ALTER TABLE commanding using SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

    There's no it depends situation for lock escalation. Row or Page lock only ever escalate to partition or table lock. There's no lock escalation as row to page to table\partition. I've not seen such behaviour.

    Before SQL 2008, row or page lock only escalate to table lock

    From SQL 2008 onwards, the below LOCK_ESCALATION

    AUTO - escalate row or page lock to table partition if available, otherwise the behaviour will be similar to TABLE if partition is not present

    TABLE - as the description imply, escalate row or page to table lock. This is the default behaviour

    DISABLE - do not escalate row or page lock to table lock in "most cases". Scanning a heap under serializable isolation will still require table lock to protect data integrity

    Hope this helps to debunk a myth.

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • Nice question, but the right option is missing. Row locks are never escalated to page locks according to BOL.

    "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."

    /Håkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

  • Nice question. 🙂

  • Very good question with nice explanation !

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply