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

Algonquin

Answer 1

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)

Petromilli

Having the initial SELECT use a WITH (UPDLOCK) hint also seems to work just fine, for any isolation level - thanks for your input! – Algonquin

@Algonquin - Yes. That avoids the deadlock risk as well. – Petromilli

@MartinSmith I know this is an old answer. I try to reproduce and solve the issue using repeatable read, but even one of the session is block, during the other completes, I still get a lost update. – Lunnete

Where's a deadlock in this scenario? Isn't there only one resource? – Foundling

@Foundling you don't need more than one resource to deadlock. Both transactions can acquire a shared lock on the same resource and block each other from converting it to exclusive lock – Petromilli

Answer 2

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.

Kalif

I actually wanted to ask the OP question because of bold selection in your answer. The emphasized text actually brings up the question, not a solution :) – Beware

@LittleAlien - I'm not sure I understand. Can you clarify please? – Kalif

The first scenario is usually referred to as "dirty write". Even the most basic isolation level - read uncommitted in some DMBSs - can prevent this. – Languishing

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.

Foolscap

What does it mean the separatetransactionsseparatetransactionsseparate transactions? Aren't concurrent transaction separate enough? You seem to speak about consolidating multiple separate databases into a single distributed DB, "an ad-hoc, informally-specified, bug-ridden, slow implementation of half of transactions”" and the problem of scope: wrapping individual operations in the transactions does mean that transaction holds between the operations. – Beware

No, by μ<ip≤co∩ectionsμ<ip≤co∩ectionsmultiple connections you mean a single database. So, it is impossible that humankind overlooked the fact that serializability does not prevent the updated lost. Multiple connection is exactly about concurrency, a concurrent access to a single DB and serializability is about effect that ensures that transactions keep the data consistent. So, what you say makes sense only when you forget to wrap your multiple operations into a transaction, which means to run at no-isolation level. 'No isolation' is very different from 'no matter which one' level. – Beware

So, despite your reminder Do not forget to wrap your operations into a transaction is not completely worthless, it must be supplied as a comment, not answer. You do not have real answer other than advertisment of the book. – Beware

Answer 4

TL;DR

Depending on what database you are using, here's the minimum isolation level that can prevent the Lost Update anomaly:

• Oracle - Serializable
• SQL Server - Repeatable Read
• PostgreSQL - Repeatable Read
• MySQL - Serializable

If you want a proof, then you can use one of the following GitHub resources:

• The scrips you could run manually from this repository
• The Java integration tests in this repository

Oracle

Oracle only supports Read Committed and Serializable, so you have to use Serializable to prevent Lost Update.

SQL Server

When using the default Two-Phase Locking mechanism, Repeatable Read can prevent Lost Update.

If you switch to the MVCC mechanism, then you need to use Snapshot Isolation.

PostgreSQL

In PostgreSQL, Repeatable Read is basically Snapshot Isolation, which prevents Lost Update.

MySQL

Although Repeatable Read is the default isolation level in MySQL, it does not prevent Lost Update, even if it's an MVCC implementation of Repeatable Read.

So you need to use Serializable instead. When using Serializable, MySQL takes shared locks on reads, therefore behaving like the 2PL-based Serializable isolation level in SQL Server.

Colorful

Answer 5

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.

Antecede

It seems if I try this scenario with Read Uncommitted or Read Committed, it fails - I can have "lost updates", indeed. With Repeatable Read, there's a deadlock and one of the two transactions will be rolled back, so there's no "lost update" in the end (but one transaction is killed as the deadlock victim) – Algonquin

But I'm puzzled that a well-know expert like Kalen Delaney would write in her book (that definitely was reviewed intensively) that "lost updates" don't occur even with Read Uncommitted - makes me wonder if she talks about a different sceneario....(and if so: wonder what that scenario is) – Algonquin

Maybe she meant because an UPDATE statement can be atomic and do the read and write in one operation. – Petromilli

But if we're just talking exclusively about atomic UPDATE statements, then can you ever really have a "lost update" scenario by definition? – Antecede

@Antecede - No. Which is presumably why she says that this situation can't occur under any of the isolation levels, even readuncommitted – Petromilli

This is a terrible answer. The theory says that Read Committed is insufficient for preventing the lost updates. So, your understanding means nothing. You should say what it is based on. You should say my experience or something in this spirit to be informative. Secondly, why do they look for Repeatable Read? They have a concrete question. They need a concrete answer. Your 'answer' gives zero information. It is an empty talk. You see the key words and start talking. – Beware

Answer 6

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).

Baksheesh

Answer 7

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.

Lunnete

Definitely NO. If you run both transactions on repeatable read level, then the first one allocates S lock on the resource, then when run the second that also allocates an S lock (because of the read operation), then instantly tries to allocate an U->X lock (because the write operation). Then when the first transaction finishes its delay, it also tries to allocate an U->X lock on the resource, which is a deadlock, some of them after this point in time will be chosen as the victim, and its transaction will be rolled back – Laundes

@Laundes You need to read again, and understand :-) – Lunnete

I tried, but I still find the statement false. With repeateble reads, we end up with deadlock and one transaction is rolled back as deadlock victim. This is definitely not what you state. – Laundes

@Laundes Apologies. I have tested this again on SQL Server 2019 and 2022 and the initial query is a deadlock victim on both environments. I am not sure on what version I have tested this back in 2018. The lost update seems to happened only on READ COMMITED mode. – Lunnete

Answer 8

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.

Laundes