Notes on SQL

In a previous article (here) I described how the isolation level read committed works, with some examples. There is also an optimistic version of this – read committed snapshot, which uses row versions.
The behaviour of read committed and read committed snapshot is similar and repeating the examples used previously will show the similarities and the differences.

First of all, the database needs to be configured for snapshot isolation.

ALTER DATABASE IsolationLevels
SET READ_COMMITTED_SNAPSHOT ON;

As with the article on read committed, there are 5,000,000 rows with the ‘ID’ column starting from 10 and a value of 1 in ‘Test_Value’ for each row.

This code is executed again and as soon as it starts the row with ‘ID’ of 10 is moved to the end of the index, in another tab.

UPDATE dbo.Test2
SET id = id + 6000000
WHERE id = 10;

This time the result is slightly different. The result shows that move of the row with ‘ID’ 10 to 6000010 has not affected the first SELECT. This is because it used a snapshot of the data, taken when the statement started (not when the transaction started), so the move had no impact upon this version of the data.

Similarly, moving the last row in the index to the start of the index does not have the impact it did with read committed isolation, for the same reason. Having reset the data and executed the appropriate UPDATE does not have the same affect, because it processed a snapshot of the data.

UPDATE dbo.Test2
SET id = 9
WHERE id = 5000009;

For the same reason, dirty reads cannot happen with read committed snapshot, because it is using as snapshot of the data taken when the statement started.

A smaller demonstration
In the read committed isolation level article, an UPDATE was executed to cause a lock, thereby blocking a subsequent SELECT.
Trying the same demonstration here results in different behaviour.

1000 rows, with an ‘ID’ column and ‘Test_Value’ column matching in values.

Executing an UPDATE in a separate window without a COMMIT/ROLLBACK will lock the selected row.

BEGIN TRANSACTION;

UPDATE dbo.Test2
SET Test_Value = 1000
WHERE id = 50;

And rerunning the query with the two SELECT statements would wait for the release of the lock from the UPDATE, if this were read committed isolation level.

However, with read committed snapshot the row with the ‘ID’ of 50 is unchanged, and the SELECT was not blocked, because a snapshot was used by the transaction.

A different demonstration, to show that each SELECT within a transaction uses a different snapshot.
Two identical SELECT queries, separated by a 10 second delay.

Before the WAITFOR command completes an UPDATE is executed that will move a row from the start of the index to the end.

BEGIN TRANSACTION;

UPDATE dbo.Test2
SET ID = 1001
WHERE ID = 10;

COMMIT TRANSACTION

Looking at completed initial transaction, the moved row is shown in the second SELECT but not in the first, so the second SELECT read a new snapshot, showing the moved data.

So, as with read committed isolation, multiple queries within the same transaction may return different results, even when the criteria are identical.