Minimum transaction isolation level to avoid "Lost Updates"

Question

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??

Answer 1

I dont know if it is too late to answer but I am just learning about transaction isolation levels in college and as part of my research I came across this link:

Microsoft Technet

Specifically the paragraph in question is:

Lost Update

A lost update can be interpreted in one of two ways. In the first scenario, a lost update is considered to have taken place when data that has been updated by one transaction is overwritten by another transaction, before the first transaction is either committed or rolled back. This type of lost update cannot occur in SQL Server 2005 because it is not allowed under any transaction isolation level.

The other interpretation of a lost update is when one transaction (Transaction #1) reads data into its local memory, and then another transaction (Transaction #2) changes this data and commits its change. After this, Transaction #1 updates the same data based on what it read into memory before Transaction #2 was executed. In this case, the update performed by Transaction #2 can be considered a lost update.

So in essence both people are right.

Personally (and I am open to being wrong, so please correct me as I am just learning this) I take from this the following two points:

1. The whole point of a transaction enviorment is to prevent lost updates as described in the top paragraph. So if even the most basic transaction level cant do that then why bother using it.

2. When people talk about lost updates, they know the first paragraph applies, and so generally speaking mean the second type of lost update.

Again, please correct me if anything here is wrong as I would like to understand this too.

Answer 2

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)

Answer 3

Lost updates may occur even if reads and writes are in separate transactions, like when users read data into Web pages, then update. In such cases no isolation level can protect you, especially when connections are reused from a connection pool. We should use other approaches, such as rowversion. Here is my canned answer.

Answer 4

My experience is that with Read Uncommitted you no longer get 'lost updates', you can however still get 'lost rollbacks'. The SQL trainer was probably referring to that concurrency issue, so the answer you're likely looking for is Repeatable Read.

That said, I would be very interested if anyone has experience that goes against this.

Answer 5

As marked by Francis Rodgers, what you can rely on SQL Server implementation is that once a transaction updated some data, every isolation level always issue "update locks" over the data, and denying updates and writes from another transaction, whatever it's isolation level it is. You can be sure this kind of lost updates are covered.

However, if the situation is that a transaction reads some data (with an isolation level different than Repeatable Read), then another transaction is able to change this data and commits it's change, and if the first transaction then updates the same data but this time, based on the internal copy that he made, the management system cannot do anything for saving it.

Your answer in that scenario is either use Repeatable Read in the first transaction, or maybe use some read lock from the first transaction over the data (I don't really know about that in a confident way. I just know of the existence of this locks and that you can use them. Maybe this will help anyone who's interested in this approach Microsoft Designing Transactions and Optimizing Locking).

Answer 6

The following is quote from 70-762 Developing SQL Databases (p. 212):

Another potential problem can occur when two processes read the same row and then update that data with different values. This might happen if a transaction first reads a value into a variable and then uses the variable in an update statement in a later step. When this update executes, another transaction updates the same data. Whichever of these transactions is committed first becomes a lost update because it was replaced by the update in the other transaction. You cannot use isolation levels to change this behavior, but you can write an application that specifically allows lost updates.

So, it seems that none of the isolation levels can help you in such cases and you need to solve the issue in the code itself. For example:

DROP TABLE IF EXISTS [dbo].[Balance];

CREATE TABLE [dbo].[Balance]
(
    [BalanceID] TINYINT IDENTITY(1,1)
   ,[Balance] MONEY
   ,CONSTRAINT [PK_Balance] PRIMARY KEY
   (
        [BalanceID]
   )
);

INSERT INTO [dbo].[Balance] ([Balance])
VALUES (100);

-- query window 1
BEGIN TRANSACTION;

    DECLARE @CurrentBalance MONEY;

    SELECT @CurrentBalance = [Balance]
    FROM [dbo].[Balance]
    WHERE [BalanceID] = 1;

    WAITFOR DELAY '00:00:05'

    UPDATE [dbo].[Balance]
    SET [Balance] = @CurrentBalance + 20
    WHERE [BalanceID] = 1;

COMMIT TRANSACTION;

-- query window 2
BEGIN TRANSACTION;

    DECLARE @CurrentBalance MONEY;

    SELECT @CurrentBalance = [Balance]
    FROM [dbo].[Balance]
    WHERE [BalanceID] = 1;

    UPDATE [dbo].[Balance]
    SET [Balance] = @CurrentBalance + 50
    WHERE [BalanceID] = 1;

COMMIT TRANSACTION;

Create the table, the execute each part of the code in separate query windows.

Changing the isolation level does nothing. For example, the only difference between read committed and repeatable read is that the last, blocks the second transaction while the first is finished and then overwrites the value.

The above can be demonstrated only under on read committed isolation level, as under repeatable read the first transaction is rollbacked as deadlock victim.

Answer 7

Cloudy enough, what we mean on lost updates.

Suppose we mean we read something, then based on that value, later, execute an update based on that value. This can cause so called "lost update" unless the read operation takes and holds an S lock on the resource until the end of the transaction. This can be achieved by either elevate the isolation level at least repeatable reads (which does exactly this), or use some lock hint which has equivalent effect. However this introduces the increased probability of deadlock, see Martin Smith comment about it, below his answer. (so in case if one insist to read something, then keep it for a while, then use the previuosly read value, better to use update lock hint, to prevent deadlock)

begin
-- without holding at least an S lock, this can cause "lost update", but better practice to get an update lock, to prevent deadlock
select @c = counter from mytable where ...
update mytable set counter = @c + 1 where ...
commit

However the statement below never will cause lost update, because it takes and holds an U->X lock on the resource, regardless of isolation level:

update mytable set counter = counter + 1 where ...

Probably the term "lost updates" can not interpreted without a particular code context. A particular code can cause lost updates because of incorrectly handling race conditions.