Post reply

"Lost update" Concurrency Problem. Explain it to me please on my example

  • February 11, 2013 at 1:27 am

    #289251

    There is an example I thought up:

    Transaction 1 started first It changes the value from 0 to 1, Transaction 2 started second It changes the value from 1 to 2. Where is the problem?

  • February 13, 2013 at 8:09 am

    #1587482

    konstantinov.maxim (2/11/2013)

    There is an example I thought up:

    Transaction 1 started first It changes the value from 0 to 1, Transaction 2 started second It changes the value from 1 to 2. Where is the problem?

    I see a COMMIT, but no BEGIN TRAN. SQL Server would throw an error if you did that.

    Are you coming from an Oracle background where autocommit is turned off by default? In SQL Server it is on.

    Assuming the default tran iso level READ COMMITTED with added steps for BEGIN TRAN your sample would go like this:

    1. Transaction 1 calls BEGIN TRAN and reads some data

    2. Transaction 2 calls BEGIN TRAN and reads some data

    3. Transaction 1 updates a row

    4. Transaction 2 tries to update the same row but is blocked by Transaction 1

    5. Transaction 1 calls COMMIT TRAN

    6. row updated by Transaction 1 is no longer locked

    7. Transaction 2 is no longer being blocked by Transaciton 1 and its update now succeeds

    8. Transaction 2 calls COMMIT TRAN

    No lost update. Transaction 1's update was completed and logged as was Transaction 2's.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 1 (of 1 total)

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