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 ««12

Lock Escalation Limit Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 8:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 3,354, Visits: 2,001
Nice question and explanation. This is something I definitely need to brush up on. Thank you Sourav.
Post #1465745
Posted Thursday, June 20, 2013 9:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 10:48 AM
Points: 4,126, Visits: 3,427
Nice, straightforward question. Thanks, Sourav!
Post #1465782
Posted Thursday, June 20, 2013 1:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 1,061, Visits: 2,572
Hugo Kornelis (6/20/2013)
Lokesh Vij (6/20/2013)
Rich Weissler (6/20/2013)
My memory was that Lock Escalation from row or page level always jumps to table. I assume I'm simply misinterpreting the question, but figured I'd ask/point just 'cause I haven't had my coffee yet...


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:

http://msdn.microsoft.com/en-us/library/ms189849(v=sql.105).aspx

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

I'm sorry, but you are wrong. SQL Server will never escalate row level locks to page level locks.
By default, SQL Server will take row level locks, and at the threshold indicated in today's question they will escalate to either table level or partition level (for partitioned tables) locks.
With index-level options or query hints, you can cause SQL Server to start out by taking page level locks instead of row level locks. The same threshold is then still used to escalate, again either to table level or partition level.

The ALTER_ TABLE option you mention can only be used to force SQL Server to escalate to table level locks instead of partition level locks for a partitioned table, or to (almost) completely eliminate lock escalation.


Note that the default LOCK_ESCALATION setting is TABLE, which means that SQL Server will escalate directly to table locks, even on partitioned tables. You have to change the LOCK_ESCALATION setting to AUTO to allow SQL Server to escalate from row/page locks to partition locks. This does increase chances of deadlocks among SPIDs accessing the table, though, because when they take the partition lock, they'll also take an intent lock on the table. The classic deadlock scenario plays out like this:

SPID 1 takes an X lock on partition 10 (and an IX lock on the table)
SPID 2 takes an X lock on partition 20 (and an IX lock on the table)
SPID 2 tries to escalate its IX table lock to an X table lock, which is incompatible with SPID 1's IX lock, so it waits.
SPID 1 requests X locks on rows in partition 20, which is incompatible with SPID 2's X lock, so it waits, and whammo - deadlock.

It's really no different than any other deadlock scenario, it's just that adding the additional layer of escalation increases the chance that two SPIDs will get crossed up.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1465921
Posted Thursday, June 20, 2013 4:14 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:24 PM
Points: 17,807, Visits: 15,728
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1465980
Posted Friday, June 21, 2013 5:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 853, Visits: 163
vinu512 (6/19/2013)
A very good question and well explained as well.


Agreed.....
Post #1466134
Posted Friday, June 21, 2013 10:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:12 AM
Points: 7,791, Visits: 9,545
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.

Tom
Post #1466295
Posted Saturday, June 22, 2013 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:22 PM
Points: 2,651, Visits: 1,555

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:

http://msdn.microsoft.com/en-us/library/ms189849(v=sql.105).aspx

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
Post #1466454
Posted Wednesday, June 26, 2013 1:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 2,604, Visits: 572
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
Post #1467491
Posted Monday, July 1, 2013 4:06 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
Nice question.
Post #1468988
Posted Wednesday, June 11, 2014 3:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:53 AM
Points: 4, Visits: 79
Very good question with nice explanation !
Post #1579553
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse