May 24, 2011 at 11:15 pm
Comments posted to this topic are about the item Lock Escalation
May 24, 2011 at 11:31 pm
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
May 25, 2011 at 12:36 am
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
May 25, 2011 at 2:23 am
I went for 'table' escalation only.
Maybe I should have guessed 'partition' as well...
Nice question though.
May 25, 2011 at 2:35 am
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
May 25, 2011 at 3:04 am
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.
May 25, 2011 at 3:04 am
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
May 25, 2011 at 3:14 am
Thank you Michael and Richard. It is clear to me now 🙂
M&M
May 25, 2011 at 3:15 am
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
May 25, 2011 at 3:20 am
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
May 25, 2011 at 4:22 am
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
May 25, 2011 at 5:23 am
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
May 25, 2011 at 5:45 am
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
May 25, 2011 at 7:54 am
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?
May 25, 2011 at 8:03 am
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)
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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply