2

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!

2

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.

  • Thanks for that clarification @Alberto Morillo, what lock hint should I use for the SQL statement ? So far I only got Azure SQL to respect "xlock, holdlock, rowlock" if I use Repeatable Read for the transaction, anything below that the lock hints get ignored for Select, however, since this transaction needs to be a bit longer than it should to be able to rollback if anything fails, the transaction enters a deadlock if we use Repeatable Read. – Jorge Rodrigues dos Santos Aug 9 at 9:12
  • READCOMMITTEDLOCK is the hint. "Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using locking. The Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed, regardless of the setting of the READ_COMMITTED_SNAPSHOT database option" – Alberto Morillo Aug 9 at 14:37
  • Combining HOLDLOCK with READCOMMITTEDLOCK is not permitted as they are conflicting. HOLDLOCK is already equivalent to SERIALIZABLE and does not prevent multiple requests to acquire the lock in Azure SQL, switching out HOLDLOCK with READCOMMITTEDLOCK we get the same behaviour. – Jorge Rodrigues dos Santos Aug 13 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.