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.

How to block users updating records I have selected/read

We know that, with the default behavior, records are getting exclusively locked when we perform modifications against data, disallowing other users to access them. Until we complete the transaction, exclusive locks will be held by SQL Server, making sure that no one not only modify but execute SELECT statements too. However default settings does not hold locks obtained for SELECT statements blocking other users reading data you have read. But what if you need it?

If you want to make sure that no one modify records which you have read for your operation, you need to change the default Isolation Level and select data within a Transaction. The Isolation Level determines the behavior of concurrent users who read or write. Default Isolation Level is READ COMMITTED which locks records using shared locks for reading but does not hold them until the transaction is completed. In order to keep the obtained shared locks until the transaction is completed, Isolation Level has to be brought up to REPEATABLE READ Isolation Level.

REPEATABLE READ keeps acquired shared locks for read data till the end of transaction. This makes sure that no one can modify records we have read and avoid a concurrency issue called Inconsistency Analysis.

Here is the way of doing it.

Open a new connection and execute the following;

-- First connection
-- setting the isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- start a transaction
BEGIN TRAN

 -- executing a select query
 SELECT * 
 FROM Sales.SalesOrderHeader
 WHERE YEAR(OrderDate) = 2011;

Then try to modify a record related to above query with another connection.

-- Second connection

-- Try to retirve data
-- This works
SELECT * 
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2011;

-- Try to update a record
-- This will NOT work
UPDATE Sales.SalesOrderHeader
 SET ShipDate = DATEADD(dd, 1, shipdate)
WHERE SalesOrderID = 43660;

As you see, second connection will not be able to modify records until the transaction related to first connection is done.