1

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 ?

3

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.

5
  • Thanks very much for replying.In particular how did the RCSI query got a Qty of 3?Is it a random number,or can it be calculated precisely due to the way the two transactions interfere? I mean T1 reads Qtyremain=5 and then tries the update 5-4=1.Then T2 reads under RCSI the same sum Qtyremain 5 thus the update should be again 5-4=1 but when T2 tries to actually update, it blocks since T1 is already trying to update.After T1 commits, the Qty will now be 1.Then T2 will try to do Qty=Qty-QtyRemain that is Qty=1-4.Where is my flaw? – microwth Jan 3 at 22:57
  • 1
    @microwth The flaw is that the new updated value is already calculated before the block. RC does not detect this. RCSI does but you don't get a block, you get an update conflict Error 3960 – Charlieface Jan 3 at 23:14
  • @Charlieface You don't get update conflicts in RCSI, since RCSI doesn't guarantee repeatable reads in the first place. In here, for instance, Brent mentions this: brentozar.com/archive/2013/01/… – Tibor Karaszi Jan 4 at 13:02
  • 1
    @microwth: Where is the value 3 mentioned? I didn't see that number in the article, nor in David's answer? I see the value -3, however (which is an expected number). Perhaps you misread -3 as 3? – Tibor Karaszi Jan 4 at 13:13
  • @tibor- yes you are right.it is -3 ,I've edited the question – microwth Jan 4 at 13:17
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.