What are the differences in transaction behavior for Snapshot Isolation level and Read Committed Snapshot in SQL Server?

Question

I am doing the testing below to grasp the behavior of Snapshot Isolation level and Read Committed Snapshot.

Change Snapshot Isolation level ON & Read Committed Snapshot OFF

ALTER DATABASE AdventureWorks2019
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE AdventureWorks2019 SET READ_COMMITTED_SNAPSHOT OFF

Transaction 1:

BEGIN TRANSACTION

    UPDATE tblInventory
    SET ItemsInStock = 5 WHERE Id = 1

COMMIT TRANSACTION


BEGIN TRANSACTION

    SELECT ItemsInStock FROM tblInventory WHERE Id = 1

COMMIT TRANSACTION

Transaction 2:

BEGIN TRANSACTION
    
    SELECT ItemsInStock FROM tblInventory WHERE Id = 1


COMMIT TRANSACTION

BEGIN TRANSACTION
    

    UPDATE tblInventory
    SET ItemsInStock = 8 WHERE Id = 1

COMMIT TRANSACTION

During transaction 1 UPDATE, transaction 2 can return the committed data in the SELECT statement. However, UPDATE statement in transaction 2 will be blocked, as the transaction 1 committed, transaction 2 returns the error:

Msg 3960, Level 16, State 6, Line 47
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.tblInventory' directly or indirectly in database 'AdventureWorks2019' to update, delete, 
or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

During transaction 1 SELECT, transaction 2 can SELECT and UPDATE successfully.

Change Snapshot Isolation level ON & Read Committed Snapshot ON:

ALTER DATABASE AdventureWorks2019
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE AdventureWorks2019 SET READ_COMMITTED_SNAPSHOT ON

I performed the same test above with the new database settings and it yields the same behaviors.

Can someone please helps me to understand the differences between (Snapshot Isolation level ON & Read Committed Snapshot OFF) and (Snapshot Isolation level ON & Read Committed Snapshot ON) in SQL Server?

Answer 1

First of all you must distinghuish what is an ISOLATION LEVEL and what is the LOCKING STRATEGY.

LOCKING STRATEGY is when you use PESSIMISTIC locking or OPTIMISTIC locking...

ISOLATION LEVEL ensures the tightness of data access between different users according to the processing

PESSIMISTIC LOCKING is a locking strategy that set locks over resources before the beginning of the process. Those locks are maintenaid during the eecution of the query or the transaction depending of the ISOLATION LEVEL

As an example when executing an UPDATE on a table in pessimistic locking, locks a aquired on table before UPDATing and release after updating.

OPTIMISTIC LOCKING is a strategy that copy the data to be read or write, process the transaction on the copy, and if the transaction writes some data in the copy then dump the new data of the copy into the live data of the database. To do so every rows to be copyed have a timestamp information and the dump can be done only if all the timestamps of the rows that have been modified have the same value, otherwise the update is loss...

A database in SQL Server is by default on PESSIMISTIC LOCKING but can be passed to OPTIMISTIC by two ways ;

• Optimistic and pessimistic locking conjunction when database locking state is elevated at ALLOW_SNAPSHOT_ISOLATION
• Optimistic instead of pessimistic locking when database locking state is elevated to READ_COMMITTED_SNAPSHOT

ISOLATION LEVEL ensure the "when" and the "how" of locking force.

When READ UNCOMMITED is used, no isolation at all is done, and if other transactions lockeds the rows to be read, those locks are ignored. READ UNCOMMITTED cannot applied when OPTIMISTIC LOCKING is done. The results is that you can have uncommitted data. It is why it is often call "chaos" or "dirty read".

When READ COMMITED is used, row locks are set only the time of the SQL command (SELECT...) other locks set by concurrent users will stop the acces to the rows. READ COMMITTED cannot applied when OPTIMISTIC LOCKING is done. When reading sequentially many time the same data, the values can change from reads to reads... No dirty read can have any effect.

When REPETABLE READ is used, row locks are set the time of the transaction that can involves many SQL command (SELECTs...). Other locks set by concurrent users will stop the acces to the rows. READ COMMITTED is the standard level of isolation when OPTIMISTIC LOCKING is done. This ensure stable reads until the transaction (not the SQL command) is ending. No dirty read, no changes for data between the multiple reads of the same data. But some new rows can appear when reading sequentially with the same query... (phantom reads).

When SERIALIZABLE is used, table locks are set the time of the transaction that can involves many SQL command (SELECTs...). Other locks set by concurrent users will stop the acces to the rows. READ COMMITTED highest leval of isolation that you can use. No phantom read and no other transaction anomaly...

SNAPSHOT isolation level does not exists in the SQL ISO standard, but has been added by Microsoft when a database is set to ALLOW_SNAPSHOT_ISOLATION and you want your transaction to use OPTIMISTIC locking strategy instead of by default pessimistic...