SQL Server Isolation Level Comparison – Repeatable Read and Snapshot Isolation

Problem

Someone suggested that we start using Repeatable Read in SQL Server as an isolation level because they found it helpful in their environment. However, I have no idea what benefits and drawbacks it brings. How is it different from the default Read Committed? Let’s take a look and also explore SQL Server snapshot isolation as another option SQL Server isolation level.

Solution

In this article, I will explore SQL’s lesser-known isolation level, Repeatable Read, and the phenomenon it prevents—a hint lies in its name. Next, we will compare it to the more optimistic Read Committed and review the locks it places on rows to avoid misreading data. Additionally, why would you choose Repeatable Read over Snapshot? By the end of this article, you’ll know if Repeatable Read is right for you and how to respond when someone suggests using it because it worked for them.

Consistency and Concurrency

Your isolation level determines the degree of consistency and concurrency that sessions experience. Ken Henderson refers to concurrency as the simultaneous access and modification of a dataset by multiple users. In contrast, consistency means having accurate data at the transaction’s start and end. Imagine a seesaw; on one end is consistency, and on the other is concurrency. When you use an isolation level like Read Uncommitted, the concurrency side touches the ground. On the other hand, if you use Serializable, the balance shifts to the consistency side.

By default, SQL Server enables Read Committed Isolation (RCI), which we often refer to as pessimistic because writers block readers. RCI protects against dirty reads (a consistency factor) but not much else. A dirty read occurs when you read data across transactions that have not yet been committed. However, RCI does not safeguard against non-repeatable reads.

Non-repeatable Read Phenomena

A non-repeatable read occurs when a transaction retrieves the same row multiple times, and another transaction updates that row, returning a different value. I’ve included a simple example below to help illustrate the concept.

Creating our Demo Environment

The code below creates a single database with one table. The DBCC USEROPTIONS command returns the current isolation level, which should be Read Committed.

/*
* MSSQLTips.com
* Create two tables with three rows.
*/
USE [master];
GO
 
IF DB_ID('RepeatableReadDemo') IS NOT NULL
BEGIN
    ALTER DATABASE RepeatableReadDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RepeatableReadDemo;
END;
GO
 
CREATE DATABASE RepeatableReadDemo;
GO
 
ALTER DATABASE RepeatableReadDemo SET RECOVERY SIMPLE;
GO
 
USE RepeatableReadDemo;
GO
 
CREATE TABLE dbo.Colors
(
    Id INT,
    ColorName VARCHAR(10),
    ColorType VARCHAR(10)
);
 
INSERT INTO dbo.Colors
(
    Id,
    ColorName,
    ColorType
)
VALUES
(1, 'red', 'warm'),
(2, 'yellow', 'warm'),
(3, 'green', 'cool'),
(4, 'blue', 'cool');
GO
 
DBCC USEROPTIONS;
GO

With our demo data in place, let’s run two sessions simultaneously.

/*
* MSSQLTips.com
* Session A
*/
USE RepeatableReadDemo;
GO
 
BEGIN TRANSACTION;
 
SELECT ColorName
FROM dbo.Colors
WHERE Id = 1;
 
WAITFOR DELAY '00:00:05';
 
SELECT ColorName
FROM dbo.Colors
WHERE Id = 1;
 
COMMIT TRANSACTION;
 
/* 
* MSSQLTips.com
* Session B
*/
USE RepeatableReadDemo;
GO
 
BEGIN TRANSACTION;
 
UPDATE dbo.Colors
SET ColorName = 'orange'
WHERE Id = 1;
 
COMMIT TRANSACTION;

Results:

For session A, SQL takes a shared (S) lock for the duration of the first SELECT statement. Then, when you read the same data again, it takes a new shared lock, but before that, connection B sneaks in and updates the value. With the second SELECT statement, the value changes in the same transaction. Depending on your expected outcome, this behavior might be detrimental.

But wait, there is another way, but it comes at a cost.

Repeatable Read Isolation Level

SQL expert Ken Henderson wrote that Repeatable Read prevents changes to the data that the transaction has already accessed. There is no database-wide setting to enable Repeatable Read, and that’s likely a good thing.

Let’s look at an example below of how Repeatable Read works. You issue the SET TRANSACTION ISOLATION LEVEL REPEATABLE READ command to start the session. The example below assumes that you have two connections running concurrently.

/*
* MSSQLTips.com
* Session A
*/
USE RepeatableReadDemo;
GO
 
SET TRANSACTION ISOLATION LEVEL 
REPEATABLE READ;
 
BEGIN TRANSACTION;
 
SELECT ColorName
FROM dbo.Colors
WHERE Id = 1;
 
WAITFOR DELAY '00:00:05';
 
SELECT ColorName
FROM dbo.Colors
WHERE Id = 1;
 
COMMIT TRANSACTION;
 
/*
* MSSQLTips.com
* Session B
*/
USE RepeatableReadDemo;
GO
 
BEGIN TRANSACTION;
 
UPDATE dbo.Colors
SET ColorName = 'orange'
WHERE Id = 1;
 
COMMIT TRANSACTION;

Results:

Our example above shows that the first session (A) did not read the updated data. However, session A held the shared locks for the duration of the transaction. This means we blocked session B until session A took its sweet time to finish. Depending on your situation, you might need the behavior where session A holds the locks so session B can’t update the data mid-transaction.

Repeatable Read works because it holds locks for longer, thus reducing concurrency while improving consistency. With higher consistency, there is always a cost of reduced concurrency.

A Better Way Through Snapshot

If you need the transaction’s behavior to prevent non-repeatable reads, a better approach is to use Snapshot isolation. Snapshot is not one of the official ANSI SQL isolation levels, but it is available in several database platforms like SQL Server and PostgreSQL. Microsoft refers to Snapshot as an optimistic row-versioning type of isolation. With Snapshot, writers don’t block readers; you also avoid non-repeatable reads and phantoms. The phenomenon known as phantoms is like non-repeatable reads, except they occur when you insert new records into a result set.

I’ve included the code below to achieve basically the same thing as Repeatable Read without all the blocking. To start out, make sure you run the option to enable Snapshot for the database: ALTER DATABASE RepeatableReadDemo SET ALLOW_SNAPSHOT_ISOLATION ON.

/*
* MSSQLTips.com
* Session A
*/
USE RepeatableReadDemo;
GO
 
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
 
BEGIN TRANSACTION;
 
SELECT ColorName
FROM dbo.Colors
WHERE Id = 1;
 
WAITFOR DELAY '00:00:05';
 
SELECT ColorName
FROM dbo.Colors
WHERE Id = 1;
 
COMMIT TRANSACTION;
 
/*
* MSSQLTips.com
* Session B
*/
USE RepeatableReadDemo;
GO
 
BEGIN TRANSACTION;
 
UPDATE dbo.Colors
SET ColorName = 'orange'
WHERE Id = 1;
 
COMMIT TRANSACTION;

Results:

Based on the results, we achieved the same consistency as Repeatable Read with Snapshot.

Snapshot Considerations

Before enabling Snapshot, consider the following points:

• Optimize your tempdb, since it’s where SQL stores the row-versioned data at least before SQL Server 2019, and if you don’t have Accelerated Database Recovery (ADR) enabled. Rethink your tempdb approach if you’re rocking one tiny file on a spinning disk.
• Snapshot isn’t magic; you can still encounter write conflicts. If two transactions update the same rows and a conflict arises, SQL throws the “Snapshot isolation transaction aborted due to update conflict” message.
• Unlike Repeatable Read, you need to enable Snapshot for the entire database using the ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ONoption. I mentioned this above, but thought it was worth repeating.
• Snapshot doesn’t automatically start working. Like Repeatable Read, we need to inform developers to use the SET TRANSACTION ISOLATION LEVEL SNAPSHOT command at the beginning of their transactions.
• Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI) are completely different. RCSI uses row versioning, but it’s at the statement level, not the transaction level like SI. You can have one or both settings enabled. Remember, RCSI will be enabled for all sessions, so you use it whether you intend to or not.

Clean Up

When you finish with the demo database, execute the statement below to drop the database.

/*
 * MSSQLTips.com
 * Clean up when you are finished
*/
USE [master];
GO
 
IF DB_ID('RepeatableReadDemo') IS NOT NULL
BEGIN
    ALTER DATABASE RepeatableReadDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RepeatableReadDemo;
END;
GO

Summary

This article examined the primary differences between Repeatable Read, Read Committed, and Snapshot isolation. All isolation levels have their place, including Read Uncommitted, which can be useful in data warehousing scenarios where data changes only once a day through a refresh mechanism.

If someone asks me whether they should use Repeatable Read, my answer is that it depends, but I would at least consider Snapshot. While it’s possible to encounter write conflicts with Snapshot, hopefully not every transaction you run applies it. Ultimately, I would choose Snapshot over Repeatable Read any day of the week.

Next Steps

• In the article, I talked about consistency and isolation, two of the pillars of the ACID principle. If you’re interested in learning more about the ACID principle related to databases, check out Shekhar Jha’s article “Database Management Systems.”
• Gerald Britton wrote a fantastic article titled “Snapshot Isolation in SQL Server.” If you’re considering enabling this option, please read his article first. Gerald has also written several articles on MSSQLTips.com.
• Bob Pusateri presented at SQLBits and shared valuable information about isolation levels. The video “Maximizing Database Performance With Isolation Levels on isolation levels” is available on YouTube.