Lock Escalation Limit
-
Comments posted to this topic are about the item Lock Escalation Limit
Thanks.
-
Got to learn something. I have never looked into Lock escalation mechanism in depth. Thanks Sourav:-)
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lokesh Vij (6/19/2013)
Got to learn something. I have never looked into Lock escalation mechanism in depth. Thanks Sourav:-)+1
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!! -
Thank you for your question. I admit I got it wrong, but I've learnt something new.
Incidentally, you state that
[Note: This is for SQL Server 2008 R2 Onwards]
According to msdn, the escalation threshold is also 5000 in SQL Server 2005 and 2008 as well.
Curiously, I can't find any explicit reference that states this threshold for SQL Server 2012.
-
martin.whitton (6/20/2013)
Curiously, I can't find any explicit reference that states this threshold for SQL Server 2012.If you follow the link in the explanation, you'll see (right at the top) the text: "Applies to: SQL Server 2008 R2 and higher versions". I think this is a standard disclaimer that MS puts in to save them the effort of researching whether or not articles apply to older versions.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
(from the question today)
> [...]triggers Lock Escalation (i.e. Row lock to Page level lock)
This would seem to refer to an event that sets off the mental alarms...
(from the reference)
> The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks.
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...
-
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 } )
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
This was removed by the editor as SPAM
-
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.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Nice question and explanation. This is something I definitely need to brush up on. Thank you Sourav.
-
Nice, straightforward question. Thanks, Sourav!
-
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
-
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[/url]
Learn Extended Events -
vinu512 (6/19/2013)
A very good question and well explained as well. 🙂Agreed.....
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply