Lock Escalation

  • Comments posted to this topic are about the item Lock Escalation

  • Great question, learned something today.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I knew the answer, but I havent thought about that this is a good reason to use partitioning.

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

  • I went for 'table' escalation only.

    Maybe I should have guessed 'partition' as well...

    Nice question though.

  • I thought row level locks would be escalated to table locks only.

    I referred the link provided in the answer, but couldn't find any information regarding escalation to partition.

    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.

    Did anyone find more information regarding the escalation of row level locks to partition in the link provided?

    M&M

  • mohammed moinudheen (5/25/2011)


    I thought row level locks would be escalated to table locks only.

    I referred the link provided in the answer, but couldn't find any information regarding escalation to partition.

    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.

    Did anyone find more information regarding the escalation of row level locks to partition in the link provided?

    I inferred it from the same paragraph that you quoted. If a row lock can escalate to a table lock, and a table lock can escalate to a partition lock then a row lock can cause a partition lock.

  • mohammed moinudheen (5/25/2011)


    I thought row level locks would be escalated to table locks only.

    I referred the link provided in the answer, but couldn't find any information regarding escalation to partition.

    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.

    Did anyone find more information regarding the escalation of row level locks to partition in the link provided?

    I'll give it a try (in a technically simplified way): The HoBT level (HoBT = Heap or B-Tree, see http://technet.microsoft.com/en-us/library/ms189849.aspx) is the structure underlying a table. If the table is not partitioned, you could say HoBT(a) = table(a).

    For a partitioned table, each partition is one HoBT, or HoBT(a1) = p(1)table(a), HoBT(a2) = p(2)table(a), etc.

    So locking just one HoBT of a partitioned table = locking the partition, but not the entire table.

    Or as BOL puts it: A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.

    Personally I'd rephrase that to: A HoBT-level lock does not necessarily lock all aligned HoBTs for an entire partitioned table.

    Regards,

    Michael

  • Thank you Michael and Richard. It is clear to me now πŸ™‚

    M&M

  • Nice question.

    But "partition" locks? Surely Heap locks, B-Tree locks (HoBT locks for short)? I don't think the term "partition lock" occurs anywhere in BoL for SQL 2008 R2.

    Tom

  • I'll give it a try (in a technically simplified way): The HoBT level (HoBT = Heap or B-Tree, see http://technet.microsoft.com/en-us/library/ms189849.aspx) is the structure underlying a table. If the table is not partitioned, you could say HoBT(a) = table(a).

    For a partitioned table, each partition is one HoBT, or HoBT(a1) = p(1)table(a), HoBT(a2) = p(2)table(a), etc.

    So locking just one HoBT of a partitioned table = locking the partition, but not the entire table.

    Or as BOL puts it: A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.

    Personally I'd rephrase that to: A HoBT-level lock does not necessarily lock all aligned HoBTs for an entire partitioned table.

    Regards,

    Michael

    Excellent explanation really, easy to follow. May be you should add this in that MSDN link πŸ™‚

    M&M

  • Tom.Thomson (5/25/2011)


    Nice question.

    But "partition" locks? Surely Heap locks, B-Tree locks (HoBT locks for short)? I don't think the term "partition lock" occurs anywhere in BoL for SQL 2008 R2.

    Try the following link: http://msdn.microsoft.com/en-us/library/ms177411.aspx

    Regards,

    Michael

  • michael.kaufmann (5/25/2011)


    Tom.Thomson (5/25/2011)


    Nice question.

    But "partition" locks? Surely Heap locks, B-Tree locks (HoBT locks for short)? I don't think the term "partition lock" occurs anywhere in BoL for SQL 2008 R2.

    Try the following link: http://msdn.microsoft.com/en-us/library/ms177411.aspx

    Regards,

    Michael

    It doesn't use that term, and I'm talking terminology here, not anything else.

    It can be confusing when people use home-rolled terminology (such as "partition lock" for "HoBT lock"), and I though the question and answer and the explanation, with its "partition or table locks", were combining to invent the term "partition lock". Doesn't detract from the niceness of the question, the accuracy of the answer, or the relevance of the explanation (all of which are top grade) but avoiding inventing terms might reduce the risk of confusing learners.

    And yes, before anyone asks, sometimes I take perfectionism to extremes (and I would be very happy if I could produce question, answer, and explanation as well as Steve does it).

    Tom

  • Tom.Thomson (5/25/2011)


    It doesn't use that term, and I'm talking terminology here, not anything else.

    [...]

    And yes, before anyone asks, sometimes I take perfectionism to extremes [...].

    Ah, got your point--and understood.

    Wish BOL (or any other documentation for that matter) would be written by your standards. πŸ™‚

    Thanks,

    Michael

  • Tom.Thomson (5/25/2011)


    Nice question.

    But "partition" locks? Surely Heap locks, B-Tree locks (HoBT locks for short)? I don't think the term "partition lock" occurs anywhere in BoL for SQL 2008 R2.

    That is part of what helped me decide on the wrong answer also.

    The other part was that a Row Lock has to be Escalated to a Table Lock before the Lock can become a HoBT lock.

    So a Table Lock not a Row Lock is what is escalated beyond a Table Lock.

    A Row Lock can turn into a Table lock, but it can not directly become a Partition Lock. Unless you mean an IX and not X lock.

    I would have selected Partition also if the question said ROWLOCK instead of row lock.

    So Steve is this an M$ Master course question and answer?

  • Partition is not higher than a table lock, it's a lower level (tables are made of one or more partitions), and if the table allows lock escalation to partition level, you can get an X lock on the partition (not just an IX)

    http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Partition-level-lock-escalation-details-and-examples.aspx

    From ALTER TABLE:

    SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

    Specifies the allowed methods of lock escalation for a table.

    AUTO

    This option allows SQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema.

    If the table is partitioned, lock escalation will be allowed to partition. After the lock is escalated to the partition level, the lock will not be escalated later to TABLE granularity.

    If the table is not partitioned, the lock escalation will be done to the TABLE granularity.

    Oh, and Partition = HoBT = Allocation Unit. Different name, same thing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 23 total)

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