1

Is it really necessary to enable ALLOW_SNAPSHOT_ISOLATION before enabling READ_COMMITTED_SNAPSHOT as per snapshot isolation level in SQL Server?.

Azure SQL created instances don't seem to have it enabled, despite their isolation level set to READ_COMMITTED_SNAPSHOT.

I don't really understand whether ALLOW_SNAPSHOT_ISOLATION is required at all or the consequences of it not being set before enabling READ_COMMITTED_SNAPSHOT

I'm running into a deadlock in our production database which is hosted inside a VM.

Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

One of these transactions is a SELECT (holding an S lock) while the other one is an INSERT (holding an IX lock).

After trying to replicate this against a SQL Azure database that contains a backup of our production data I was unable until I realized that there was a difference in the isolation level (obtained through DBCC USEROPTIONS)

Azure (SQLServer 12.0.2000.8): read committed snapshot

VM hosted (SQLServer 15.0.2080.9): read committed

After matching the isolation level in the Azure DB by running ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT OFF I was able to reproduce the issue.

Now I want set read committed snapshot as our isolation level in the production database. The above mentioned link states that I need to run these two commands:

ALTER DATABASE MyDatabase  
SET ALLOW_SNAPSHOT_ISOLATION ON  
  
ALTER DATABASE MyDatabase  
SET READ_COMMITTED_SNAPSHOT ON

However, it does not seem that snapshot isolation is allowed in the Azure DB (SELECT snapshot_isolation_state_desc FROM sys.databases WHERE NAME = 'MyDatabase' returns OFF)

I'm also unable to find tempdb inside the System Databases in the Azure SQL instance.

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
luisgepeto
  • 763
  • 1
  • 11
  • 36
  • `ALLOW_SNAPSHOT_ISOLATION` doesn't affect the isolation level unless `SET TRANSACTION ISOLATION LEVEL SNAPSHOT` is used - using either though turns on row versioning in tempdb. – Stu May 25 '22 at 21:58
  • I am not using explicit transactions in my code and I am just using EF to run the queries. I am interested in just updating this setting for my DB so that EF can run with the default isolation level. – luisgepeto May 25 '22 at 22:01
  • 1
    if `READ_COMMITTED_SNAPSHOT` is enabled (default in Azure) then that's all you need. RSCI reduces contention but deadlocks are still possible, writers still block writers. – Stu May 25 '22 at 22:03

2 Answers2

3

Is it really necessary to enable ALLOW_SNAPSHOT_ISOLATION before enabling READ_COMMITTED_SNAPSHOT as per snapshot isolation level in SQL Server?.

Azure SQL created instances do not seem to have it enabled, despite their isolation level set to READ_COMMITTED_SNAPSHOT.

It is not necessary to enable ALLOW_SNAPSHOT_ISOLATION in order to enable READ_COMMITTED_SNAPSHOT and visa-versa. ALLOW_SNAPSHOT_ISOLATION is required only if you explicitly use SNAPSHOT isolation (SET TRANSACTION ISOLATION LEVEL SNAPSHOT) whereas READ_COMMITTED_SNAPSHOT changes the behavior of the READ_COMMITTED isolation level (the default level) to use row-versioning instead of locking for statement-level read consistency.

Although both use row-versioning, an important difference is READ_COMMITTED_SNAPSHOT returns a snapshot of data as of the time the statement began whereas the SNAPSHOT isolation level returns a snapshot of data as of the time the transaction began, an important consideration with a transaction containing multiple queries. Both will provide the same behavior for single-statement autocommit transactions.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Then how does READ_COMMITTED_SNAPSHOT works? What is the difference to the ALLOW_SNAPSHOT_ISOLATION? – luisgepeto May 25 '22 at 22:06
  • @LuisBecerril, I updated my answer to help answer your question. The doc link you referenced explains the difference in depth. – Dan Guzman May 25 '22 at 22:32
  • This article is also helpful to understand this difference: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188277(v=sql.105) – luisgepeto May 26 '22 at 14:23
0

No, ALLOW_SNAPSHOT_ISOLATION is not required when enabling READ_COMMITTED_SNAPSHOT isolation level.

When enabling READ_COMMITTED_SNAPSHOT isolation level, you need to set READ_COMMITTED isolation level and turn on READ_COMMITTED_SNAPSHOT with the MSSQL command below:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON
GO

In addition, when enabling SNAPSHOT isolation level, you need to set SNAPSHOT isolation level and turn on ALLOW_SNAPSHOT_ISOLATION with the MSSQL command below:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
ALTER DATABASE <database_name> SET ALLOW_SNAPSHOT_ISOLATION ON
GO
Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129