Is it possible to read data while they are being modified in Azure SQL Database with default settings?

Somewhere in 2011, I wrote an article titled Isolation Level in SQL Server that explains different Isolation Levels in SQL Server. It is based on an older version of SQL Server, but it is still valid for new versions as well.

Since now we have SQL Server as a SaaS, we slowly move to Azure SQL Database. As you know, Azure SQL Database is not same as On-Premises SQL Database, it has some differences; one is related to Isolation Level.

Before talking about the difference, let's write a sample code and see how it works in both Azure SQL Server and On-Premises SQL Server databases. For this, I have created a database called Sales in both environments.

Once databases are availble, the blow code that adds a table and inserts records, needs to be run in both local and Azure SQL Databases;

CREATE TABLE dbo.Products
(
 ProductId int identity(1,1) Primary Key
 , Name varchar(100) not null
 , Color varchar(20) not null
);
GO

INSERT INTO dbo.Products
 (Name, Color)
VALUES
 ('Product 01', 'Black')
 , ('Product 02', 'Red')
 , ('Product 03', 'Blue');

Now we have the same table in both databases. Let's first test with local database.

Open a new connection and execute the following code. It creates a transaction and updates one of the records. Note that, we do not either rollback or commit.

-- First connection

BEGIN TRAN
 UPDATE dbo.Products
  SET Color = 'Green'
 WHERE ProductId = 2;

Open another connection and execute the code given below. This connection tries to retrieve the record that is being modified by first connection.

-- Second connection

SELECT * FROM dbo.Products
WHERE ProductId = 2;

The result of the second connection is shown in the below image. As you see, we cannot immediately see the record as the record is being modified.

Let's go back to the first connection and rollback the transaction.

-- First connection

ROLLBACK TRAN

Once it is done, second connection will be able to see records.

This is the standard/default behavior of SQL Server. Generally, we cannot access records that are being modified because default Isolation Level set is Read Committed. Now let's do the same in Azure SQL Database.

Open a new connection for Azure Sales Database and execute the same code that starts the transaction.

Open another connection for Azure Sales Database and execute the same code for retrieving records. Here is the result of it.

As you see, there is no issue with Azure Sales database. Second connection can retrieve records even though the record is being modified by another connection.

This is one of the differences between Azure SQL Database and Local SQL Database. The reason for this is, by default Snapshot Isolation Level is enabled in Azure SQL Database. Because of that, a version of the old record is created before the modification and it will be used for other users if request.

Can we turn this behavior off in Azure SQL Server? No, it is not possible, if you try to change it via SSMA, you will experience the below error;

However, you can change Is Read Committed Snapshot On that changes the default behavior.

If you set this to false, then second connection will not be able to see records while first connection modifying records but second connection can set the Isolation Level manually and see records.