Read committed Snapshot VS Snapshot Isolation Level

Could some one please help me understand when to use SNAPSHOT isolati开发者_Go百科on 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

---

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.

---

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.

---

Let me describe 2 points that have not been mentioned.

Firstly let's make it clear how to use both because it's not intuitive.

SNAPSHOT and READ_COMMITTED_SNAPSHOT are two different isolation levels.

SNAPSHOT is isolation level you can use in your transaction explicitly as usual:

begin transaction
set transaction isolation level snapshot;
-- ...
commit

READ_COMMITTED_SNAPSHOT can't be use like this. READ_COMMITTED_SNAPSHOT is both a database level option and an implicit/automatic isolation level. To use it, you need to enable it for the whole database:

alert database ... set read_committed_snapshot on;

What above database setting does, is that every time you run transaction like this:

begin transaction
set transaction isolation level read committed;
-- ...
commit

With this option ON, all READ_COMMITTED transactions will run under READ_COMMITTED_SNAPSHOT isolation level instead. This happens automatically, affecting all READ_COMMITTED transactions issued against database with this setting set to ON. It's not possible to run transaction under READ_COMMITTED isolation level because all transactions with this level will be automatically converted to READ_COMMITTED_SNAPSHOT.

Secondly you shouldn't blindly use READ_COMMITTED_SNAPSHOT option.

To illustrate the kind problems it can create, imagine you have simple events table like this:

create table Events (
  id int not null identity(1, 1) primary key,
  name nvarchar(450) not null
  -- ...
)

And you poll it periodically with query like this:

begin transaction
set transaction isolation level read committed; -- automatically set to read committed snapshot when this setting is ON on database level 
select top 100 * from Events where id > ${lastId} order by id asc; 
commit

Above query doesn't need to be enclosed with transaction and explicit isolation level. READ_COMMITTED is default isolation level and if you invoke query without wrapping it in transaction block - it'll be implicitly run in READ_COMMITTED transaction.

You'll find that under READ_COMMITTED_SNAPSHOT isolation level auto-increment identity values may have gaps that later appear.

You can easily simulate it with insert like this:

begin transaction
insert into Events (name) values ('test 1');
waitfor delay '00:00:10'
commit

...followed by normal insert:

insert into Events (name) values ('test 2');

Your polling function invoked within 10s will return single row with id 2.

Following poll after updating lastId will return nothing. Row with id 1 had will appear after 10s.

Event with id 1 will be effectively skipped.

This will not happen if you use READ_COMMITTED without READ_COMMITTED_SNAPSHOT auto promotion option.

It's worth understanding this scenario. It's not related to the fact that IDENTITY column doesn't guarantee uniqueness. It's not related to the fact that IDENTITY column doesn't guarantee strict monotonicity. Even when both uniqueness and strict monotonicity are not violated, you still end up with gaps - possibility of seeing commits with higher ids before seeing commits with lower ids.

Under READ_COMMITTED this problem doesn't exist.

Under READ_COMMITTED you can also see gaps - ie. by transactions that rolled back. But those gaps will be permanent - ie. you are not skipping events because they will never reappear. Ie. you won't see lower ids reappearing later after you've seen higher ids.

Please understand above issue and its implications before turning READ_COMMITTED_SNAPSHOT on.

Control of this option lies in the gray area of developer vs db admin responsibility. If you're admin, you should not blindly use it as developers may have relied on READ_COMMITTED isolation semantics when developing application and turning READ_COMMITTED_SNAPSHOT may violate those assumptions in very implicit, hard to find bug way.