ROWLOCK Escalation

  • chrisfradenburg

    SSCrazy Eights

    Points: 9592

    Comments posted to this topic are about the item ROWLOCK Escalation

  • baabhu

    SSCertifiable

    Points: 6202

    I completely miss read the question. And also I never heard of the lock escalation threshold. Good to knew it.

    Learned something knew.

    Good question !!!!.

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Learned something new, but the reference could be more complete.

    The MSDN article never states that the ROWLOCK hint can/will be ignored after the threshold has been passed.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Gail Shaw

    SSC Guru

    Points: 1004474

    The RowLock (or pageLock) hints just say what the locks should start as, nothing more. In this case they'll start as row, but once past the 5000 or so locks acquired, will escalate.

    What the explanation also doesn't say is that escalations are always1 to table. SQL never escalates to a page lock, when escalation is triggered it is to table.

    (1) The one exception is 2008, with a partitioned table where the table's escalation setting has been changed to AUTO. In that case (and only that case), escalation will be to the partition only, not the table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • andagr

    SSC-Addicted

    Points: 465

    GilaMonster (3/19/2012)


    The RowLock (or pageLock) hints just say what the locks should start as, nothing more. In this case they'll start as row, but once past the 5000 or so locks acquired, will escalate.

    What the explanation also doesn't say is that escalations are always1 to table. SQL never escalates to a page lock, when escalation is triggered it is to table.

    (1) The one exception is 2008, with a partitioned table where the table's escalation setting has been changed to AUTO. In that case (and only that case), escalation will be to the partition only, not the table.

    I agree that the explanation for this question is a bit vague, it says:

    "SQL will start out with rowlock, but once it reaches the lock escalation threshold, the lock will still escalate to a courser lock."

    This would indicate that page is a correct answer, but it is not. MSDN states:

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

    /@devandreas

  • paul.knibbs

    SSCoach

    Points: 15270

    This is one of those questions that I answered randomly because I wanted to see what the answer was...would have been nice to get a slightly more in-depth explanation. Still, Gail came through on that for me, which is what I like about this site--if it's not answered by the question author somebody in the comments will do it! 🙂

  • This was removed by the editor as SPAM

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    GilaMonster (3/19/2012)


    The RowLock (or pageLock) hints just say what the locks should start as, nothing more. In this case they'll start as row, but once past the 5000 or so locks acquired, will escalate.

    What the explanation also doesn't say is that escalations are always1 to table. SQL never escalates to a page lock, when escalation is triggered it is to table.

    (1) The one exception is 2008, with a partitioned table where the table's escalation setting has been changed to AUTO. In that case (and only that case), escalation will be to the partition only, not the table.

    Thanks for the explanation.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Skanker

    Hall of Fame

    Points: 3059

    Thanks for the explanations. Learned something today, which is always a good start to the day.

  • Carlo Romagnano

    SSC-Insane

    Points: 21873

    GilaMonster (3/19/2012)


    The RowLock (or pageLock) hints just say what the locks should start as, nothing more. In this case they'll start as row, but once past the 5000 or so locks acquired, will escalate.

    What the explanation also doesn't say is that escalations are always1 to table. SQL never escalates to a page lock, when escalation is triggered it is to table.

    (1) The one exception is 2008, with a partitioned table where the table's escalation setting has been changed to AUTO. In that case (and only that case), escalation will be to the partition only, not the table.

    Thanks.

  • Keld Laursen (SEGES)

    SSC Eights!

    Points: 841

    Missed one here :angry:

    I knew that locks would escalate, so I checked what the rowlock hint would do.

    This article hinted (to me at least) that the escalation would be put off until later on servers with more memory.

    If not, then I didn't really see the point of the rowlock hint as I would have assumed rowlock would be first always. (Other hinting aside, of course)

    Bummer

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Got it right because I remembered that lock escalations are always made directly to the table level. Thank you for the question! Very interesting subject.

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • TomThomson

    SSC Guru

    Points: 104772

    Nice easy question to start the week. I knew roughly what goes on so got it right, but the comments (thanks, Gail) have made it clearer.

    Tom

  • jfogel

    SSCarpal Tunnel

    Points: 4131

    Thanks Gila' for the explanation. I'm one of the dummies who thought it was a page lock.

    Cheers

  • richardd

    Hall of Fame

    Points: 3899

    Slight nit-picking issue: what is a "courser" lock? Do you mean a coarser lock?

    http://dictionary.reference.com/browse/Courser

    http://dictionary.reference.com/browse/Coarser

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

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