Read committed Snapshot VS Snapshot Isolation Level
Could some one please help me understand when to use SNAPSHOT isolation level over READ COMMITTED SNAPSHOT in SQL Server?
I understand that in most cases READ COMMITTED SNAPSHOT works, but not sure when go for SNAPSHOT isolation.
Thanks
READ COMMITTED SNAPSHOT
does optimistic reads and pessimistic writes. In contrast, SNAPSHOT
does optimistic reads and optimistic writes.
Microsoft recommends READ COMMITTED SNAPSHOT
for most apps that need row versioning.
Read this excellent Microsoft article: Choosing Row Versioning-based Isolation Levels. It explains the benefits and costs of both isolation levels.
And here's a more thorough one: http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx
[![Isolation levels table][2]][2]
See the example below:
Read Committed Snapshot
Change the database property as below
ALTER DATABASE SQLAuthority
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO
Session 1
USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 4
WHERE i = 1
Session 2
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM DemoTable
WHERE i = 1
Result – Query in Session 2 shows old value (1, ONE) because current transaction is NOT committed. This is the way to avoid blocking and read committed data also.
Session 1
COMMIT
Session 2
USE SQLAuthority
GO
SELECT *
FROM DemoTable
WHERE i = 1
Result – Query in Session 2 shows no rows because row is updated in session 1. So again, we are seeing committed data.
Snapshot Isolation Level
This is the new isolation level, which was available from SQL Server 2005 onwards. For this feature, there is a change needed in the application as it has to use a new isolation level.
Change database setting using below. We need to make sure that there is no transaction in the database.
ALTER DATABASE SQLAuthority SET AllOW_SNAPSHOT_ISOLATION ON
Now, we also need to change the isolation level of connection by using below
Session 1
USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 10
WHERE i = 2
Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM DemoTable
WHERE i = 2
Result- Even if we have changed the value to 10, we will still see old record in session 2 (2, TWO).
Now, let’s commit transaction in session 1
Session 1
COMMIT
Let’s come back to session 2 and run select again.
Session 2
SELECT *
FROM DemoTable
WHERE i = 2
We will still see the record because session 2 has stated the transaction with snapshot isolation. Unless we complete the transaction, we will not see latest record.
Session 2
COMMIT
SELECT *
FROM DemoTable
WHERE i = 2
Now, we should not see the row as it's already updated.
See: SQL Authority, Safari Books Online