[SOLVED] Minimum transaction isolation level to avoid "Lost Updates"

Minimum transaction isolation level to avoid "Lost Updates"

With SQL Server's transaction isolation levels, you can avoid certain unwanted concurrency issues, like dirty reads and so forth.

The one I'm interested in right now is lost updates - the fact two transactions can overwrite one another's updates without anyone noticing it. I see and hear conflicting statements as to which isolation level at a minimum I have to choose to avoid this.

Kalen Delaney in her "SQL Server Internals" book says (Chapter 10 - Transactions and Concurrency - Page 592):

In Read Uncommitted isolation, all the behaviors described previously, except lost updates, are possible.

On the other hand, an independent SQL Server trainer giving us a class told us that we need at least "Repeatable Read" to avoid lost updates.

So who's right?? And why??

Solution

The example in the book is of Clerk A and Clerk B receiving shipments of Widgets.

They both check the current inventory, see 25 is in stock. Clerk A has 50 widgets and updates to 75, Clerk B has 20 widgets and so updates to 45 overwriting the previous update.

I assume she meant this phenomena can be avoided at all isolation levels by Clerk A doing

UPDATE Widgets
SET StockLevel = StockLevel + 50
WHERE ...

and Clerk B doing

UPDATE Widgets
SET StockLevel = StockLevel + 20
WHERE ...

Certainly if the SELECT and UPDATE are done as separate operations you would need repeatable read to avoid this so the S lock on the row is held for the duration of the transaction (which would lead to deadlock in this scenario)