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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » "Lost update" Concurrency Problem. Explain it...

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

Rate Topic

Display Mode

Topic Options

Author

Message

MaxNevermind

Posted Monday, February 11, 2013 1:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 11:51 AM
Points: 6, Visits: 28

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?

Post #1418249

opc.three

Posted Wednesday, February 13, 2013 8:09 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 6,737, Visits: 11,791

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419543

« Prev Topic | Next Topic »

Permissions