Understanding of SQL Server version store

Question

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?

Answer 1

The issue you are referring to is known as write skew, and happens when optimistic concurrency is used to read and write data.

Yes, this is absolutely possible under SNAPSHOT, because the versioned data is not locked and can be read under that isolation level. This is irrespective of the isolation level used to actually do the update products ..., as the session that is reading will just go to the version store.

To get around this, one common solution is to confirm assumptions while doing the new write (for the actual booking). The new write also needs to be under a non-SNAPSHOT isolation level, and must be at least REPEATABLE READ level (or you can use a READCOMMITTEDLOCK and a UPDLOCK table hint together).

So you would read the data you want for UI purposes using SNAPSHOT, this allows non-blocking reads for UI purposes. Then when actually creating the booking you would do something like this:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

IF @price <> (
    SELECT p.price
    FROM products p
    WHERE p.id = @productId
)
    THROW 50001, 'New price detected', 1;


INSERT OrderDetail (....)
VALUES (....);

COMMIT;

Another variation of this method is to return the rowversion value for the row in question, and then check (using the same transaction semantics as above) that the rowversion is the same. This is what ORMs such as Entity Framework do if configured.

Answer 2

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