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.

2 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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s