Sample Header Ad - 728x90

Understanding of SQL Server version store

5 votes
2 answers
2294 views
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 update statement 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 update statement 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?
Asked by Fajela Tajkiya (1239 rep)
Aug 21, 2022, 01:04 PM
Last activity: Aug 21, 2022, 06:13 PM