I'm trying to understand SQL Server version store and related isolation levels. As I understand it, when a database enabled read committed snapshot option, this could happen:
- An item (id = 1) has price $1000 in database
- Session 1 starts an update statement:
update products set price = price * 1.5. Since this touches all rows of the table, it takes long time.
- While the
updatestatement still in progress, Session 2 starts a query:
select * from products where id = 1. Since the database is in read committed snapshot mode, writers do not block readers. So session 1 read the old version of the row from version store and thinks the product is of price $1000.
- The user of session 1 thought the price was not bad, so he decided to buy it. But ...
- Before the user add the product to his shopping cart, the aforementioned
updatestatement is finished executing, and the new price for product (id = 1) is $1500. If the user knows the new price for the product, he would not buy it.
In this case, what will happen? Is this scenario actually possible? If so, what's the norm to prevent this?