50

Most of the forum and example online always suggest to have both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT set to ON whenever someone is asking snapshot, row versioning or similar question.

I guess the word SNAPSHOT in both setting get a little confusing. I thought that, in order for database engine to use row versioning instead of locks for READ_COMMITTED default behavior, the database READ_COMMITTED_SNAPSHOT is set to ON regardless of what ALLOW_SNAPSHOT_ISOLATION setting.

The ALLOW_SNAPSHOT_ISOLATION setting is set to ON only to allow snapshot isolation when starting a transaction (e.g. SET TRANSACTION ISOLATION LEVEL SNAPSHOT) regardless of READ_COMMITTED_SNAPSHOT setting.

The only reason to have these two settings set to ON is when it needs to have READ COMMITTED row versioning AND snapshot isolation.

My question is, is my understanding incorrect in some way? And that these two setting have to be always set to ON together (especially for READ COMMITTED row versioning)?

5 Answers 5

33

Your understanding is correct. It does get a little confusing.

Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation (direct MP4 download link (544MB)). Fast forward to 41:45 in the video to get to the part where she answers your question.

If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will not get any of the benefits.

If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to modify any code. MS SQL Server automatically applies snapshot isolation for that table.

I haven't tested to see what happens if you ask for a different isolation level in your code, I suspect it will overwrite this option but test it first.

A quick look at performance overhead using Snapshot Isolation.

Good article on how snapshot isolation can change the expected behavior of your app. It shows examples of how a update statement and a select statement might return totally different and unexpected results.

3
  • Thanks for the link. Like other BOL, she discussed these two settings independently and collectively (that is where it gets a little confusing, or perhaps I over think it). I had to test it out to get a better understanding.
    – Travis
    Commented Apr 24, 2013 at 14:01
  • 4
    This is a great answer and I would only like to clarify a couple items. First, if you're just scanning the video, start at 23:18 as well as 41:45. The early time adds more detail. Although Kim mentions an answer to the original question, there is still a need to modify code if using both. Read_Committed_Snapshot is statement-level isolation, in other words only applies to the statement currently running. Allow_Snapshot_Isolation is transaction-level isolation, everything between the Begin Tran and Commit. They can be engaged separately, but the same 14-byte overhead per row is established.
    – Delux
    Commented Jun 6, 2013 at 14:12
  • 1
    The video has moved, but you can still find it here: learn.microsoft.com/en-us/previous-versions/dn912438(v=msdn.10) if you search on-page for "Overview of isolation levels, controlling isolation levels, statement-level read consistency, transaction-level read consistency, overhead and monitoring." Commented Feb 16, 2021 at 9:07
16

OK, went back home and tested. Here is the observation.

CREATE DATABASE TEST;
GO
CREATE TABLE TABLE1
(
    ID tinyint,
    Details varchar(10)
);
GO
INSERT INTO TABLE1
VALUES (1, 'Original');
GO

SELECT
    name,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'TEST';
GO

First test with both settings confirmed to be OFF.

Query 1

USE TEST;

BEGIN TRAN
UPDATE TABLE1
SET Details = 'Update'
WHERE ID = 1;

--COMMIT;
--ROLLBACK;
GO

Query 2

USE TEST;

SELECT ID, Details
FROM TABLE1
WHERE ID = 1;
GO

In this test, query 2 is waiting for query 1 to commit, dm_tran_locks DMV shows that exclusive lock on TABLE1 incurred by query 1.

USE TEST;

SELECT
    DB_NAME(tl.resource_database_id) AS DBName,
    resource_type,
    OBJECT_NAME(resource_associated_entity_id) AS tbl_name,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks tl
WHERE 
    resource_database_id = db_id('TEST')
    AND resource_type = 'OBJECT'

Second test, rollback previous transaction, set READ_COMMITTED_SNAPSHOT ON but leave ALLOW_SNAPSHOT_ISOLATION OFF.

ALTER DATABASE TEST
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
GO

Run Query 1, and run query 2. DMV shows query 1 incur exclusive lock, but query 2 returns details with 'Original' without query 1 commit the transaction. It appears that READ_COMMITTED row versioning is in place.

Adding SET TRANSACTION ISOLATION LEVEL SNAPSHOT; on query 1 and query 2, and run query 1 or query 2 returns error - Snapshot isolation transaction failed accessing database 'TEST' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

Third test, rollback previous transaction. Set READ_COMMITTED_SNAPSHOT OFF and ALLOW_SNAPSHOT_ISOLATION ON.

ALTER DATABASE TEST
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE TEST
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

Run query 1, and then query 2. DMV shows exclusive lock incurred by query 1. Query 2 appears to be waiting for query 1 to complete. Turning ALLOW_SNAPSHOT_ISOLATION ON doesn't appear to enable READ COMMITTED row versioning.

Adding SET TRANSACTION ISOLATION LEVEL SNAPSHOT; to both query 1 and query 2. Run query 1 and then query 2. While DMV shows query 1 incur exclusive lock, query 2 return details with 'Original'. Snapshot isolation appears to be in place.

Observation from the test shows that READ_COMMITTED_SNAPSHOT itself enable/disable the READ COMMITTED row versioning regardless of ALLOW_SNAPSHOT_ISOLATION setting, and vice versa.

0
7

Your understanding is correct. I like the short, clean and simple definition from here:

When the READ_COMMITTED_SNAPSHOT database option is ON, transactions setting the read committed isolation level use row versioning.

When the ALLOW_SNAPSHOT_ISOLATION database option is ON, transactions can set the snapshot isolation level.

Seems like a lot of misunderstanding comes from MS itself. For example, here they say:

If you set the READ_COMMITTED_SNAPSHOT database option to ON, the database engine uses row versioning and snapshot isolation as the default, instead of using locks to protect the data.

But the mentioned "snapshot isolation" is not equal to behavior of transaction for which set transaction isolation level snapshot is applied.

As for the difference, nice explanation is here.

Probably it would be better if READ_COMMITTED_SNAPSHOT was named as READ_COMMITTED_ROW_VERSIONING or something like that. :)

1

READ_COMMITTED_SNAPSHOT mode can be viewed as an extension to ALLOW_SNAPSHOT_ISOLATION. The thing is that the database has a default isolation mode, and it is set to READ_COMMITTED in normal blocking isolation modes. This happens whenever you make a query without explicitly specifying the isolation level for your query requests. In order for the database to start using SNAPSHOT isolation in this mode, you need to enable this option: ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON. Thus, you ensure that all your requests will always go through SNAPSHOT isolation

0

I like this summary from Microsoft:

Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level. If the READ_COMMITTED_SNAPSHOT option is set to OFF, you must explicitly set the Snapshot isolation level for each session in order to access versioned rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.