Compare Repeatable Read and Serializable SQL Server Transaction Isolation Levels

By:   |   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

As mentioned above, the Repeatable Read SQL Server isolation level prevents dirty reads and not-repeatable reads. It is achieved by placing shared locks on all data that is read by each statement in a transaction and all the locks are held until the transaction completes. As a result other transactions are unable to modify the data that has been read by the current transaction. However, it does not prevent other transactions from inserting new rows into the tables which have been selected in the current transaction, moreover these new rows can match the select conditions in the current transaction statements. So, if these statements are issued in the current transaction more than once and the mentioned new rows are inserted between executions of these statements, phantom reads occur.

SQL Server Serializable Isolation Level

The Serializable SQL Server isolation level protects from all three consistency anomalies and this is done by placing range locks in the range of key values that match search conditions for all statements in the transaction. These locks are held until the transaction ends. This ensures that not only dirty reads and not-repeatable reads are prevented, but also phantom reads are excluded. Serializable is the strictest isolation level, but concurrency is lower than in other transaction isolation levels.

SQL Server Isolation Level Examples

Now let's move from theory to practice. We will investigate and compare these two isolation levels and understand the difference visually. First let's create a test environment:
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':

SQL Server Non-Repeatable Read Example

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 Repeatable Read Example

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:

SQL Server Phantom Reads Example

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:

SQL Server Serializable Transaction Isolation Level Example

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan 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



Comments For This Article




Saturday, February 15, 2020 - 12:37:15 AM - Victor Yang Back To Top (84508)

I think that doesn't call phantom read 


Friday, July 19, 2019 - 8:19:16 PM - Rafael Back To Top (81810)

Thank you so much for this post!


Sunday, October 21, 2018 - 2:33:08 AM - OMKAR Back To Top (78003)

 

 Very good explanation! :)


Friday, December 29, 2017 - 5:06:17 PM - Vineel Kumar Reddy Kovvuri Back To Top (74581)

 This is by far the best explanation I could find.

 















get free sql tips
agree to terms