Azure SQL Database – The Default Isolation Is?

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'

crmdb

Or you can use this DBCC command.

DBCC useroptions

RCSI

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'

crmdboff

-- 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.

Advertisements

4 thoughts on “Azure SQL Database – The Default Isolation Is?

  1. Pingback: Dew Drop - May 31, 2018 (#2736) - Morning Dew

  2. Pingback: Szumma #112 – 2018 22. hét – ./d/fuel

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.