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.
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.