ROWLOCK Escalation

  • Comments posted to this topic are about the item ROWLOCK Escalation

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

  • 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

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

  • 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

  • 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

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

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

  • 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

  • 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

  • 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

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

    Cheers

  • 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 25 total)

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