Trying to understand an RCSI example-The Potential Dangers of the Read Committed Snapshot Isolation Level 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 &gt;= 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 &quot;last-known-good&quot; 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.