SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 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 However, you can control the isolation level explicitly on a connection. You cannot change the database default isolation level. We spent some time trying to go around this problem with no success so far, help!Īzure 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. Rerun the transaction.'.Įven if we run the alter with rollback immediate:ĪLTER DATABASE SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE ODBC error: State: 40001: Error: 1205 Message:'Transaction (Process ID 120) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. 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: Great, so now we just needed to set READ_COMMITTED_SNAPSHOT to OFF but this is where we are stuck. 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). Instead of the second transaction setting the value to 3. 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: 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.
0 kommentar(er)
