patternsqlMinor
Understanding of SQL Server version store
Viewed 0 times
understandingsqlversionstoreserver
Problem
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:
In this case, what will happen? Is this scenario actually possible? If so, what's the norm to prevent this?
- 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?
Solution
"Before the user add the product to his shopping cart...If the user knows the new price for the product, he would not buy it." - These are things that happen on the application side and are of no concern from the database, so it's kind of hard to answer what if type of questions around them from the database perspective.
Is this scenario actually possible?
Yes, absolutely.
In this case, what will happen?
It depends on how you code your application. If the application is always pulling the latest data from the database, since the user didn't add the item to their cart yet, then they'll see the latest price of $1,500 after they add the item to their cart. If you're using some kind of asynchronous application code, then you can even have the price automatically update on the screen that the user is currently on so they see the latest price before adding the item to their cart. But again, these are all application layer decisions that are independent of the database layer.
If so, what's the norm to prevent this?
Use a pessimistic isolation level, such as SQL Server's default of Read Committed, which will block the user from seeing a price until the update finishes.
Those are really your two options (from the database layer), either the price of $1,000 (optimistic isolation level) is the correct price at the time the application queries the database or $1,500 (pessimistic isolation level) is the correct price at that time. And with an optimistic isolation level, there's nothing incorrect about the timing of an update occurring such that the price changes after the time the user first sees the price. That's just order of events.
It's kind of like eBay in a sense, where the price of an item is only valid at the time the user views that price. At any moment after (such as when the user goes to buy or bid on that item) that price is liable to change or expire (since auction items are time limited).
Is this scenario actually possible?
Yes, absolutely.
In this case, what will happen?
It depends on how you code your application. If the application is always pulling the latest data from the database, since the user didn't add the item to their cart yet, then they'll see the latest price of $1,500 after they add the item to their cart. If you're using some kind of asynchronous application code, then you can even have the price automatically update on the screen that the user is currently on so they see the latest price before adding the item to their cart. But again, these are all application layer decisions that are independent of the database layer.
If so, what's the norm to prevent this?
Use a pessimistic isolation level, such as SQL Server's default of Read Committed, which will block the user from seeing a price until the update finishes.
Those are really your two options (from the database layer), either the price of $1,000 (optimistic isolation level) is the correct price at the time the application queries the database or $1,500 (pessimistic isolation level) is the correct price at that time. And with an optimistic isolation level, there's nothing incorrect about the timing of an update occurring such that the price changes after the time the user first sees the price. That's just order of events.
It's kind of like eBay in a sense, where the price of an item is only valid at the time the user views that price. At any moment after (such as when the user goes to buy or bid on that item) that price is liable to change or expire (since auction items are time limited).
Context
StackExchange Database Administrators Q#315869, answer score: 4
Revisions (0)
No revisions yet.