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’:
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:
- SQL Server Locking and Blocking Tips
