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!