There's this article The Potential Dangers of the Read Committed Snapshot Isolation Level which demonstrates RC vs RCSI isolation levels.
I get the RC example but not the RCSI one.In particular how the query has gotten the result -3. Can somone explain how this query works under RCSI ?
Under locking read committed it's very likely (but not guaranteed) that when two sessions run this code
BEGIN TRANSACTION
DECLARE @QtyRequired int, @QtyRemain int
SELECT @QtyRequired = 4
SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1
IF @QtyRemain - @QtyRequired >= 0
BEGIN
UPDATE Inventory SET Qty = Qty - @QtyRequired
WHERE ItemID = 796 AND LocationID = 1
-- Do other stuff in other tables or databases to check out the item
WAITFOR DELAY '00:00:10'
SELECT 'Checkout complete'
END
ELSE
SELECT 'Not enough qty!'
COMMIT TRANSACTION
There will be enough difference in time that the second session runs
SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1
after the first session has run
UPDATE Inventory SET Qty = Qty - @QtyRequired
WHERE ItemID = 796 AND LocationID = 1
And so the second session will be unable to acquire an S lock on the row, and will be blocked until the first session commits. This isn't a guarantee, however, as it's quite possible that both sessions run
SELECT @QtyRemain = SUM(QTY) FROM Inventory WHERE ItemID = 796 AND LocationID = 1
before either of them runs the update.
With RCSI it's simply more likely that they will both read the same value for @QtyRemain as the SELECT is never blocked by the pending UPDATE on the other session, and just returns the "last-known-good" value for the row from the version store.
But since the locking read committed version has the same problem, albeit in a smaller window, they are both broken, and should be fixed by changing the first query to:
SELECT @QtyRemain = SUM(QTY) FROM Inventory with (UPDLOCK) WHERE ItemID = 796 AND LocationID = 1
Which guarantees that even if two sessions attempt the query at the same time, one will be granted the U lock, and the other will be blocked until the first session commits.
Trying to explain how we end up with -3 in the table: Let's call the transactions A and B. A starts first:
Begin tran
Reads the value 5, store it in @QtyRemain and verify that we have >= 0 after subtraction of @QtyRequired (being 4)
Update, value in table after update is 1 (current val 5 minus required 4 = 1).
Waits...
End tran
And here's B, starts during the wait for tran A:
Begin tran
Reads the value 5, store it in @QtyRemain and verify we have >= 0 after subtraction of @QtyRequired (being 4)
Update, blocked until end of tran A...
...Update now occurs. Value in table after update is -3 (current val 1 minus required 4 = -3)
