Post reply

Row versioning

  • September 3, 2011 at 8:04 pm

    #243319

    Hi All,

    Does row versioning works on only UPDATE and no DELETE?

    Can anyone explain why only UPDATE and why not UPDATE?

    INSERT we can keep it aside.

    Thanks in advance.

  • September 4, 2011 at 6:05 am

    #1376852

    What do you mean by row versioning? Do you mean the data type rowversion? It's meant as a mechanism for concurrency, to determine if someone has modified a record that you are modifying. If someone deletes the record out from under your... it's been modified and you don't need rowversion to check it. In fact, because it's gone, you can't use rowversion to check it. You'll have to put some other logic in place to deal with that.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 4, 2011 at 6:10 am

    #1376857

    Or do you mean one of the optimistic concurrency models based on row versions? If so, they most definitely do work on deletes.

    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

  • September 4, 2011 at 6:58 pm

    #1376926

    Hi,

    Am talking about optimistic concurrency control (read committed and snapshot isolation levels),

    My question is, if i have 2 sessions and how does INSERT, UPDATE, DELETE maintains row versioning in version store(tempdb). Wanted to understand how it manages row versions. I checked the msdn but it is on top of my head.

    I was looking for someone who can put it in simple words for heads up and i can dig it from there after.

    Thanks.

  • September 5, 2011 at 12:31 am

    #1376952

    In very simple terms, the old version of the row goes into the version store until it's no longer needed by any active statements/transactions (depending which of the optimistic concurrency isolation levels you're using)

    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

  • September 5, 2011 at 6:53 pm

    #1377158

    Thanks Gila 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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