How to handle Lost Updates Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to handle Lost Updates Problem

Author

Topic

Praveen Babu
Starting Member

3 Posts

Posted - 2010-07-18 : 10:35:46

I have a small transactions shown below. How do I handle Lost Update problem?

START TRANSACTION

SELECT col1 FROM table1 WHERE <cond>

UPDATE table1 SET COL1= <new val> WHERE <cond>

COMMIT

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-18 : 11:01:08

praveen, what is your problem?

Sachin.Nand

2937 Posts

Posted - 2010-07-19 : 03:19:49

The best way go about it is to set your Transaction Isolation level to Snapshot.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Praveen Babu
Starting Member

3 Posts

Posted - 2010-07-19 : 10:39:59

Problem:
By setting read commited isolation level it takes care of dirty reads. I want to prevent Lost Update too where two transactions select at the same time and update the values by which the first update is lost.

Idera,

Can you elaborate on using SNAPSHOT.
I see READ_COMMITTED_SNAPSHOT and a simple snapshot.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-19 : 11:03:54

The usual way to handle this is to include a column of data type TIMESTAMP (ROWVERSION) in the table and use optimistic locking to verify that the row has not changed since you selected the row to be updated.

START TRANSACTION

declare @col1 int
declare @ts varbinary(8)

SELECT @col1= col1, @ts=ts_column FROM table1 WHERE <cond>

-- Update only if the TIMESTAMP has not changed.
UPDATE table1 SET COL1= <new val> WHERE <cond> and ts_column = @ts

IF @@rowcount <> 1
	begin
	<handle update conflict>
	end 

COMMIT

CODO ERGO SUM

Praveen Babu
Starting Member

3 Posts

Posted - 2010-07-19 : 14:43:53

Is this row versioning better than pessimistic approach I mean putting XLOCK on select?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources