Lost update in snapshot vs all the rest isolation levels

Question

Let's suppose we use create new table and enable snapshot isolation for our database:

alter database database_name set allow_snapshot_isolation on

create table marbles (id int primary key, color char(5)) 
insert marbles values(1, 'Black') insert marbles values(2, 'White')

Next, in session 1 begin a snaphot transaction:

set transaction isolation level snapshot
begin tran
update marbles set color = 'Blue' where id = 2

Now, before committing the changes, run the following in session 2:

set transaction isolation level snapshot 
begin tran 
update marbles set color = 'Yellow' where id = 2

Then, when we commit session 1, session 2 will fail with an error about transaction aborted - I understand that is preventing from lost update.

If we follow this steps one by one but with any other isolation level such as: serializable, repeatable read, read committed or read uncommitted this Session 2 will get executed making new update to our table. Could someone please explain my why is this happening? For me this is some kind of lost update, but it seems like only snapshot isolation is preventing from it.

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2018-07-31T23:08:55

Could someone please explain my why is this happening?

Because under all the other isolation levels the point-in-time at which the second session first sees the row is after the first transaction commits. Locking is a kind of time travel. A session enters a lock wait and is transported forward in time to when the resource is eventually available.

For me this is some kind of lost update

No. It's not. Both updates were properly completed, and the final state of the row would have been the same if the transactions had been 10 minutes apart.

In a lost update scenario, each session will read the row before attempting to update it, and the results of the first transaction are needed to properly complete the second transaction. EG if each is incrementing a column by 1.

And under locking READ COMMITTED, REPEATABLE READ, and SERIALIZABLE the SELECT would be blocked, and no lost update would occur. And under READ_COMMITTED_SNAPSHOT the SELECT should have a UPDLOCK hint, and it would block too.

Lost update in snapshot vs all the rest isolation levels

1 Answers