So what is the default isolation level for Azure SQL Database? I ran the following code to check it out.
CREATE DATABASE CRMDB ( EDITION = 'BASIC', MAXSIZE = 2GB ) SELECT name, snapshot_isolation_state, is_read_committed_snapshot_on FROM sys.databases WHERE name = 'CRMDB'
Or you can use this DBCC command.
DBCC useroptions
Quite simply the default isolation level for Azure SQL Database is RCSI – Read Committed Snapshot Isolation level. What if you decide to not use this and you want to turn it off and go back to read committed? Pretty easy.
ALTER DATABASE CRMDB SET READ_COMMITTED_SNAPSHOT OFF ALTER DATABASE CRMDB SET ALLOW_SNAPSHOT_ISOLATION OFF SELECT name, snapshot_isolation_state,snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases WHERE name = 'CRMDB'
-- OR DBCC useroptions
We need to know this because there are differences between RCSI and Read Committed isolation levels. They are quite different and known as an optimistic flavour (RCSI) and a pessimistic one (read committed). The difference between optimistic and pessimistic concurrency is that with optimistic concurrency, writers and readers don’t block each other. Via RCSI it allows a reader to get to a previously committed value (from tempdb) of the row without blocking, so concurrency is increased in the system.
Pingback: Dew Drop - May 31, 2018 (#2736) - Morning Dew
Pingback: Szumma #112 – 2018 22. hét – ./d/fuel
Does this apply if TransactionScope is used?
LikeLike
I have a feeling that defaults to a different isolation level. Can’t remember which one.
LikeLike