May 25, 2011 at 8:07 am
Difficult question. Thanks everyone for the explanations.
May 25, 2011 at 8:41 am
GilaMonster (5/25/2011)
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.
That is a beautifully simple explanation. Thanks Gail.
Tom,
apparently Partition Lock is wording used in the BOL.
May 25, 2011 at 9:13 am
michael.kaufmann (5/25/2011)
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
That makes a lot of sense.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 25, 2011 at 9:13 am
Nice question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 25, 2011 at 9:31 am
SanDroid (5/25/2011)
GilaMonster (5/25/2011)
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.
That is a beautifully simple explanation. Thanks Gail.
Tom,
apparently Partition Lock is wording used in the BOL.
I don't see "partition lock" as a term in there at all. You do understand that there's a difference between having the two words "partition" and "lock" adjacent to each other in the right order so as to form a term where one directly qualifies the other and having them (in the opposite order and) separated by a verb of which "lock" is the subject and "partition" the indirect object, don't you? If not you may find this useful. 😀
Tom
May 25, 2011 at 10:15 am
Tom.Thomson (5/25/2011)
I don't see "partition lock" as a term in there at all. You do understand that there's a difference between having the two words "partition" and "lock" adjacent to each other in the right order so as to form a term where one directly qualifies the other and having them (in the opposite order and) separated by a verb of which "lock" is the subject and "partition" the indirect object, don't you? If not you may find this useful. 😀
Tom,
What I "realize" is that I understand what these documents are saying.
msdn.microsoft.com/en-us/library/ms?190273
msdn.microsoft.com/en-us/library/ms?177411
msdn.microsoft.com/en-us/library/ms?187504.aspx
I dont have explinations for your understandings. 😛
Professional documentation writen by American companies are usually proof read using a set of rules differant from English Literature.
Does youre link imply that if I learned english in Britin or New Zealand this would be different? Seems Off Topic to me.
May 25, 2011 at 10:19 am
I've searched a dozen links and I don't find "partition lock" as a term in BOL. The idea is there, and even in the ALTER TABLE options, it does say that locks can escalate to the partition level, but it doesn't say partition lock.
So I've changed the explanation to make this clear. I've also changed the question to the "level" of lock, not the type.
I don't think that type was that confusing, but I agree that a partition lock does not seem to be listed as a term.
May 25, 2011 at 10:33 am
Steve Jones - SSC Editor (5/25/2011)
I've searched a dozen links and I don't find "partition lock" as a term in BOL. The idea is there, and even in the ALTER TABLE options, it does say that locks can escalate to the partition level, but it doesn't say partition lock.So I've changed the explanation to make this clear. I've also changed the question to the "level" of lock, not the type.
I don't think that type was that confusing, but I agree that a partition lock does not seem to be listed as a term.
Steven I agree with you that the idea is there even though it is not worded exactly as such so.
It also fits that you change from type to level since the newer 2008 R2 BOL documentation on locking uses the wording level when describing locking much more than type. The newer ADO documentation uses the wording Lock Type a lot. often. 😎
May 26, 2011 at 6:31 pm
*facepalm* MUST LEARN TO READ, CHOOSE 2... and I go and click 3 of them.
D'oh! :blush:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply