SQL ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

SQL Server ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT database options enable/disable the use of row versioning. These two options are often discussed independently and collectively due to their behaviors, and may have resulted some confusion on how these two database options correlate and affect each other on transaction isolation level.


I have came across multiple forum and blog online that suggest having both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT database options set to ON whenever someone asks for assistance and recommendation regarding snapshot, row versioning or similar question.

Perhaps the word SNAPSHOT in both setting causes some confusion.

Although these two isolation level utilize row versioning, they should be independent from each other setting. Let's do some test to evaluate how these two database options affect the transaction isolation level,

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
The test above shows 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 options set to ON is when the intention is to have both READ COMMITTED row versioning AND snapshot isolation level.