Cannot set READ_COMMITTED_SNAPSHOT to OFF on Azure SQL

azure-sql-databaselocking

Me and my team are trying to use Azure SQL for our development environment and ran into some critical and unwanted differences in locking behaviour to our production environment. The main problem is duplication in increment values because with READ_COMMITTED_SNAPSHOT set to ON the xlock, holdlock, rowlock does not prevent selects to other transactions, so we get the typical:

  • [tran1] select value (1),
  • [tran2] select value (1),
  • [tran1] update value (++1),
  • [tran2] update value (++1)

Instead of the second transaction setting the value to 3.

We tracked it down to having READ_COMMITTED_SNAPSHOT set to ON in Azure SQL, which comes as a default, unlike if you do your own SQL Server setup (that's weird but lets not dwell).

Great, so now we just needed to set READ_COMMITTED_SNAPSHOT to OFF but this is where we are stuck. In Azure SQL we can't seem to do that, even if no other processes are connected to the server we always get a deadlock:

ODBC error: State: 40001: Error: 1205 Message:'[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 120) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.'.

Even if we run the alter with rollback immediate:
ALTER DATABASE [xxx] SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE

We spent some time trying to go around this problem with no success so far, help!

Best Answer

Azure SQL Database default database wide setting is to enable read committed snapshot isolation (RCSI) by having both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options set to ON. You cannot change the database default isolation level. However, you can control the isolation level explicitly on a connection. One way to do this, you can use any one of the following statements in Azure SQL Database before you BEGIN TRANSACTION:

SET TRANSACTION  ISOLATION LEVEL  SERIALIZABLE
SET TRANSACTION  ISOLATION LEVEL  SNAPSHOT
SET TRANSACTION  ISOLATION LEVEL  REPEATABLE READ
SET TRANSACTION  ISOLATION LEVEL  READ COMMITTED
SET TRANSACTION  ISOLATION LEVEL  READ UNCOMMITTED

SET TRANSACTON ISOLATION LEVEL controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server and spans batches (GO statement). All of the above works exactly the same as SQL Server.

In addition, the above statement "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" will set the isolation level to read committed snapshot isolation (RCSI). This isolation level is different from read committed (RC). In other words, the default behavior of “SET TRANSACTION ISOLATION LEVEL READ COMMITTED" in on-premise SQL Server is RC, but in Azure SQL Database is RCSI. If you want to use exact RC (not RCSI) behavior in Azure SQL Database, you have to set the lock hint to the SQL statement.

Related Question