Compare Repeatable Read and Serializable SQL Server Transaction Isolation Levels

By: Sergey Gigoyan   |   Comments (4)   |   Related: > Locking and Blocking

Problem

Experienced SQL Server DBAs know that the Repeatable Read transaction isolation level protects queries from dirty reads and non-repeatable reads. The Serializable isolation level protects from all phenomena that a Repeatable Read does, plus prevents phantom inserts. So, the Serializable isolation level protects from all phenomena and it is the highest isolation level. But what is the phenomenon? How are phantom inserts different from non-repeatable reads? This tip will help explain these concepts and will be useful for any SQL Server Professional who needs to know the difference between the mentioned isolation levels.

To facilitate the explanation we will investigate these isolation levels with examples, which will enable us to understand the material not only theoretically, but see the behavior of these two isolation levels in practice.

Solution

Before investigating the differences between SQL Server Repeatable Read and Serializable isolation levels, let's briefly introduce all concurrency issues which are also known as read phenomena.

SQL Server Read Phenomena

There are three different consistency anomalies which are the same as read phenomena:

• Dirty read - occurs when a transaction reads uncommitted data. In other words, the transaction is allowed to read the data that has been changed by other transactions and is not yet committed.
• Non-repeatable read - occurs when in the same transaction we are retrieving the same row more than once, but the values for that row can be different. It occurs because after the first transaction reads the data in the row, other transactions are allowed to change this data.
• Phantom reads - occurs when in the same transaction identical queries return different rows. The reason for this behavior is that between the first and the second execution of the query, new rows have been inserted into the table by other transactions that meet the select criteria.
  

SQL Server Repeatable Read Isolation Level

SQL Server Serializable Isolation Level

SQL Server Isolation Level Examples

USE master
GO

CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE TABLE TestTable
(
 ID INT,
 Value CHAR(1)
)

INSERT INTO TestTable(ID, Value) 
VALUES (1,'A'),(2,'B'),(3, 'C')
 

SQL Server Non-Repeatable Read Example

Now it's time to see an example for a non-repeatable read. For that we will execute the same SELECT statement twice in a transaction with the default Read Committed isolation level.  The first set of code gets run in one query window and the second set in another query window.

-- run in query window 1
USE TestDB
GO

--Transaction isolation level is default - READ COMMITTED
BEGIN TRANSACTION Transaction1

 --Query 1
 SELECT *
 FROM TestTable
 WHERE Value='C'
 GO

 WAITFOR DELAY '00:00:05' 

 --Query 3
 SELECT *
 FROM TestTable
 WHERE Value='C'

COMMIT

After executing the first set of code, we run this code to update rows matching the select criteria:

-- run in query window 2
--Query 2
--Execute after Query 1 and before Query 3 
USE TestDB
GO

UPDATE TestTable
SET Value='C'
WHERE Value='A'
 

Let's open these queries in different windows and execute. When the first SELECT statement completes in 'Transaction1' and while the second SELECT statement has not yet started, the code in query 2 is executed. We can see that we get a different result for the second SELECT statement in 'Transaction1':

This is a typical example of a non-repeatable read.

SQL Server Repeatable Read Example

Now we will set the transaction isolation level to Repeatable Read:

-- run in query window 1
USE TestDB
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION Transaction2

 --Query 4
 SELECT *
 FROM TestTable
 WHERE Value='C'
 GO

 WAITFOR DELAY '00:00:05' 

 --Query 6
 SELECT *
 FROM TestTable
 WHERE Value='C'
 GO

COMMIT

WAITFOR DELAY '00:00:05' 

SELECT *
FROM TestTable
WHERE Value='C'
 

We will try to update the data which has already been read by 'Transaction2':

-- run in query window 2
--Query 5
--Execute after Query 4 and before Query 6
USE TestDB
GO

UPDATE TestTable
SET Value='A'
WHERE ID=1

In this case we can see that the second query waits until 'Transaction2' completes and then updates the row. As a result we get the same result for our two SELECT statements in 'Transaction2' and the third SELECT statement returns one row, because it runs after 'Transaction2' commits and after the second query updates the row:

SQL Server Phantom Reads Example

In the next example we will see a phantom read:

-- run in query window 1
USE TestDB
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION Transaction3

 --Query 7
 SELECT *
 FROM TestTable
 WHERE Value='C'
 GO

 WAITFOR DELAY '00:00:05' 

 --Query 9
 SELECT *
 FROM TestTable
 WHERE Value='C'

COMMIT
 

After the first SELECT statement complete in the query above, we will execute the next query:

-- run in query window 2
--Query 8
--Execute after Query 7 and before Query 9
USE TestDB
GO

INSERT INTO TestTable(ID, Value)
VALUES(4, 'C')
 

So we can see that the first SELECT statement returns one row and the second SELECT returns two rows:

The reason for this is that between these two queries the new row, which meets the select criteria, is inserted by the other transaction. This is an example of a phantom read.

SQL Server Serializable Transaction Isolation Level Example

To eliminate the Phantom Read anomaly, we will set the transaction isolation level to Serializable:

-- run in query window 1
USE TestDB
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION Transaction4

 --Query 10
 SELECT *
 FROM TestTable
 WHERE Value='C'
 GO

 WAITFOR DELAY '00:00:05' 

 --Query 12
 SELECT *
 FROM TestTable
 WHERE Value='C'
 GO
 
COMMIT

WAITFOR DELAY '00:00:05' 

SELECT *
FROM TestTable
WHERE Value='C'
 

Before the second SELECT starts in 'Transaction4', we run the insert statement:

-- run in query window 2
--Query 11
--Execute after Query 10 and before Query 12 
USE TestDB
GO

INSERT INTO TestTable(ID, Value)
VALUES(5, 'C')
 

Nothing is inserted until 'Transaction4' completes. Therefore the two SELECT statements in 'Transaction4' return the same result. This means we have prevented phantom reads by setting the transaction isolation level to Serializable. The new row is inserted only when 'Transaction4' finishes, so the third SELECT which is outside of the transaction returns 3 rows:

Conclusion

In this tip we illustrated with real examples, that both - Repeatable Read and Serializable isolation levels prevent dirty-reads and not-repeatable reads, but unlike Repeatable Read isolation level, Serializable also prevents phantom reads. With the Serializable isolation level, locks are placed in a wider range of data, so concurrency is lower than in other isolation levels. Understanding this difference is very useful when choosing a higher isolation level other than the default (read committed). Choosing the highest isolation level (Serializable) in cases when Repeatable Read is also acceptable, will increase unnecessary locks and vice versa, when phantom reads are possible. Choosing Repeatable Read isolation level can cause concurrency anomalies.

Next Steps

• Read this related information:
  • https://msdn.microsoft.com/en-us/library/ms709374(v=vs.85).aspx
  • https://msdn.microsoft.com/en-us/library/ms173763.aspx
  • https://msdn.microsoft.com/en-us/library/ms710099(v=vs.85).aspx
  • https://msdn.microsoft.com/en-us/library/ms712532(v=vs.85).aspx
• SQL Server Locking and Blocking Tips

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips