Read committed Snapshot VS Snapshot Isolation Level

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

No comparison of Snapshot and Snapshot Read Committed is complete without a discussion of the dreaded "snapshot update conflict" exception that can happen in Snapshot, but not Snapshot Read Committed.

In a nutshell, Snapshot isolation retrieves a snapshot of committed data at the start of a transaction, and then uses optimistic locking for both reads and writes. If, when attempting to commit a transaction, it turns out that something else changed some of that same data, the database will rollback the entire transaction and raise an error causing a snapshot update conflict exception in the calling code. This is because the version of data affected by the transaction is not the same at the end of the transaction as it was at the start.

Snapshot Read Committed does not suffer from this problem because it uses locking on writes (pessimistic writes) and it obtains snapshot version information of all committed data at the stat of each statement.

The possibility of snapshot update conflicts happening in Snapshot and NOT Snapshot Read Committed is an extremely significant difference between the two.

[![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

Still relevant, starting with Bill's comments I read more and made notes that might be useful to someone else.

By default single statements (including SELECT) work on "committed" data (READ COMMITTED), the question is: do they wait for data to be "idle" and stop others from working when reading?

Setting via right click DB "Properties -> Options -> Miscellaneous":

Concurrency/Blocking: Is Read Committed Snapshot On [defaults off, should be on]:

• Use SNAPSHOT for select (read), do not wait for others, nor block them.
• Effects operation without code change
• ALTER DATABASE <dbName> SET READ_COMMITTED_SNAPSHOT [ON|OFF]
• SELECT name, is_read_committed_snapshot_on FROM sys.databases

Consistency: Allow Snapshot Isolation [defaults off, debatable – OK off]:

• Allow client to request SNAPSHOT across SQL statements (transactions).
• Code must request "transaction" snapshots (like SET TRANSACTION ...)
• ALTER DATABASE <dbName> SET ALLOW_SNAPSHOT_ISOLATION [ON|OFF]
• SELECT name, snapshot_isolation_state FROM sys.databases

To the question: it is not one or the other between Read Committed Snapshot and Allow Snapshot Isolation. They are two cases of Snapshot, and either could be on or off independently, with Allow Snapshot Isolation a bit more of an advanced topic. Allow Snapshot Isolation allows code to go a step further controlling Snapshot land.

The issue seems clear if you think about one row: by default the system has no copy, so a reader has to wait if anyone else is writing, and a writer also has to wait if anyone else is reading – the row must lock all the time. Enabling "Is Read Committed Snapshot On" activates the DB to support "snapshot copies" to avoid these locks.

Rambling on...

In my opinion "Is Read Committed Snapshot On" should be TRUE for any normal MS SQLServer databases, and that it is a premature optimization that it ships FALSE by default.

However, I'm told the one row lock gets worse not only because you may be addressing multiple rows across tables, but because in SQL Server row locks are implemented using "block" level locks (locking random rows associated by storage proximity) and that there is a threshold where multiple locks trigger table locking - presumably more "optimistic" performance optimizations at the risk of blocking issues in busy databases.