Lock Escalation Limit

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    Comments posted to this topic are about the item Lock Escalation Limit

    Thanks.

  • Lokesh Vij

    SSChampion

    Points: 10836

    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 questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    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 !!!

  • vinu512

    SSCoach

    Points: 15729

    A very good question and well explained as well. 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • martin.whitton

    SSCrazy

    Points: 2568

    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.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Rich Weissler

    Hall of Fame

    Points: 3235

    (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...

  • Lokesh Vij

    SSChampion

    Points: 10836

    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 questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • This was removed by the editor as SPAM

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Ken Wymore

    SSCoach

    Points: 16588

    Nice question and explanation. This is something I definitely need to brush up on. Thank you Sourav.

  • Revenant

    SSC-Forever

    Points: 42467

    Nice, straightforward question. Thanks, Sourav!

  • Jason Wolfkill

    SSCrazy Eights

    Points: 9772

    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

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • Bangla

    Hall of Fame

    Points: 3137

    vinu512 (6/19/2013)


    A very good question and well explained as well. 🙂

    Agreed.....

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply