Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Lock Escalation Expand / Collapse
Author
Message
Posted Tuesday, May 24, 2011 11:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 9:02 PM
Points: 33,153, Visits: 15,284
Comments posted to this topic are about the item Lock Escalation






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1114515
Posted Tuesday, May 24, 2011 11:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:41 AM
Points: 13,537, Visits: 10,405
Great question, learned something today.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1114518
Posted Wednesday, May 25, 2011 12:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 2:45 AM
Points: 2,531, Visits: 536
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
Post #1114531
Posted Wednesday, May 25, 2011 2:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
I went for 'table' escalation only.

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

Nice question though.
Post #1114563
Posted Wednesday, May 25, 2011 2:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:16 PM
Points: 2,270, Visits: 3,783
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?


Mohammed Moinudheen
Post #1114565
Posted Wednesday, May 25, 2011 3:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 15, 2014 4:01 AM
Points: 2,108, Visits: 1,706
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.
Post #1114580
Posted Wednesday, May 25, 2011 3:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Post #1114581
Posted Wednesday, May 25, 2011 3:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:16 PM
Points: 2,270, Visits: 3,783
Thank you Michael and Richard. It is clear to me now

Mohammed Moinudheen
Post #1114585
Posted Wednesday, May 25, 2011 3:15 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 8,677, Visits: 9,203
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
Post #1114586
Posted Wednesday, May 25, 2011 3:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:16 PM
Points: 2,270, Visits: 3,783

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


Mohammed Moinudheen
Post #1114589
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse