Is ALLOW_SNAPSHOT_ISOLATION required when enabling READ_COMMITTED

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.

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

I am 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 am also unable to find tempdb inside the System Databases in the Azure SQL instance.

CodePudding user response：

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.