ROWLOCK Escalation

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3994

    tks for the question and tks Gail for the additional feedback. - cheers

  • SQLRNNR

    SSC Guru

    Points: 281243

    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 Gail for the explanation.

    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

  • zymos

    SSCommitted

    Points: 1960

    The question was great and I liked the explanation of the table lock mechanism in SQL Server.

    Thanks

  • Ken Wymore

    SSCoach

    Points: 16572

    Thanks for the question Chris. Learned something today!

    Thanks too to Gail for the additional explanation.

  • DBA_Dom

    SSCrazy

    Points: 2842

    Gail I liked your explaination as I have read about this several times. I am having to go through one of our databases to clean up performance left in the wake of non thoughtful developers. This table level lock is also more specifically because the table does not have a nonclustered index on what is being searched therefore needs to do a fulltable scan with intent to update which will lock the table regardless of escalation threshold and rowlock hint.

  • mtassin

    SSC-Insane

    Points: 23099

    thanks for the explanation Gail....

    I didn't this one for sure, but looking at 7500 rowlocks I figured it would escalate... and I kind of remembered that things don't go to page locks.... they have to start there... and went with the only answer that seemed possible.

    Glad my semi-educated guess hit it.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • tilew-948340

    Hall of Fame

    Points: 3431

    paul.knibbs (3/19/2012)


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

    +1

    In fact, I still do not understand, even after reading on it, why it does "escalation"

    - "Rowlock" locks the row being updated and (what I think) releases the lock once the update is done.. It loops for the 7500 rows to be updated, locking and unlocking the rows...

    So, why and where does the escalation comes from?

  • Gail Shaw

    SSC Guru

    Points: 1004474

    tilew-948340 (3/19/2012)


    - "Rowlock" locks the row being updated and (what I think) releases the lock once the update is done.. It loops for the 7500 rows to be updated, locking and unlocking the rows...

    Any data modification requires the locks be held until the transaction is complete, so until that last row completes the update, all locks must be held.

    It's read locks under read committed isolation that release the locks as soon as the row has been processed.

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

    Hall of Fame

    Points: 3431

    GilaMonster (3/20/2012)


    tilew-948340 (3/19/2012)


    - "Rowlock" locks the row being updated and (what I think) releases the lock once the update is done.. It loops for the 7500 rows to be updated, locking and unlocking the rows...

    Any data modification requires the locks be held until the transaction is complete, so until that last row completes the update, all locks must be held.

    It's read locks under read committed isolation that release the locks as soon as the row has been processed.

    Oh...transaction.. ya... makes sense now :doze:

    Thank you!

  • Britt Cluff

    SSCertifiable

    Points: 5083

    Good simple question, but the explanation could have been better.

    http://brittcluff.blogspot.com/

  • GPO

    SSCarpal Tunnel

    Points: 4556

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

    Of coarse he does!

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

Viewing 11 posts - 16 through 26 (of 26 total)

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