Understanding RCSI Example – Dangers of Read Committed Snapshot Isolation

isolation-levelsnapshot-isolationsql servertransaction

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 ?

Best Answer

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.

Related Solutions

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

SQL Server – READ COMMITTED SNAPSHOT vs SNAPSHOT

READ COMMITTED SNAPSHOT uses a new snapshot after each statement. That means that less row versions are being kept alive. (The statement you quoted from the docs is slightly misleading because it suggest that this is always true - it is only true in case of long-running SNAPSHOT transactions.) Snapshot row versions are created on writes. The reads do not influence what gets put into tempdb. Writers cannot possibly foresee what reads will be carried out in the future. Readers only influence only what can be purged.
When a SNAPSHOT transaction T1 writes to a row that was modified by another transaction T2 in the time between T1 started and T1 attempted the write, the statement fails with an update conflict error. This is an optimistic concurrency model. With READ COMMITTED SNAPSHOT T1 would wait for T2 to release the X-lock on the row and continue normally.

Best Answer

Related Solutions

Postgresql – Read Committed Isolation Level

SQL Server – READ COMMITTED SNAPSHOT vs SNAPSHOT

Related Question