Back to: SQL Server Tutorial For Beginners and Professionals
Phantom Read Concurrency Problem in SQL Server with Examples
In this article, I am going to discuss the Phantom Read Concurrency Problem in SQL Server with Examples. Please read our previous article where we discussed the Non-Repeatable Read Concurrency Problem in SQL Server with an example. At the end of this article, you will understand what is phantom read problem is and when it occurs in SQL Server as well as you will also understand how to solve the phantom read problem in SQL Server.
What is Phantom Read Concurrency Problem in SQL Server?
The Phantom Read Concurrency Problem happens in SQL Server when one transaction executes a query twice and it gets a different number of rows in the result set each time. This generally happens when a second transaction inserts some new rows in between the first and second query execution of the first transaction that matches the WHERE clause of the query executed by the first transaction.
Understanding Phantom Read Concurrency Problem in SQL Server
Let’s understand Phantom Read Concurrency Problem in SQL Server with an example. We are going to use the following Employees table to understand this concept.
Please use the below SQL Script to create and populate the Employees table with the required sample data.
-- Create Employee table
CREATE TABLE Employees
(
Id INT PRIMARY KEY,
Name VARCHAR(100),
Gender VARCHAR(10)
)
Go
-- Insert some dummy data
INSERT INTO Employees VALUES(1001,'Anurag', 'Male')
INSERT INTO Employees VALUES(1002,'Priyanka', 'Female')
INSERT INTO Employees VALUES(1003,'Pranaya', 'Male')
INSERT INTO Employees VALUES(1004,'Hina', 'Female')
Phantom Read Concurrency Problem in SQL Server:
Let say we have two transactions Transaction 1 and Transaction 2. Transaction 1 starts first and it reads the data from the Employees table where Gender is Male and it returns 2 rows for the first read and then Transaction 1 is doing some other work. At this point, Transaction 2 started and it inserts a new employee with Gender Male. Once Transaction 2 inserts the new employee, then Transaction 1 makes a second read and it returns 3 rows, resulting in a Phantom Read Concurrency Problem in SQL Server.
Phantom Read Concurrency Problem in SQL Server with an Example:
Let us understand Phantom Read Concurrency Problem in SQL Server with an example. Open 2 instances of the SQL Server Management Studio. From the first instance execute the Transaction 1 code and from the second instance execute the Transaction 2 code. Notice that when Transaction 1 is completed, it gets a different number of rows for reading 1 and reading 2, resulting in a phantom read problem. The Read Committed, Read Uncommitted, and Repeatable Read Transaction Isolation Level causes Phantom Read Concurrency Problem in SQL Server. In the below Transactions, I am using REPEATABLE READ Transaction Isolation Level, even you can also use Read Committed and Read Uncommitted Transaction Isolation Levels.
Transaction 1
-- Transaction 1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM Employees where Gender = 'Male' -- Do Some work WAITFOR DELAY '00:00:10' SELECT * FROM Employees where Gender = 'Male' COMMIT TRANSACTION
Transaction 2
-- Transaction 2 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION INSERT into Employees VALUES(1005, 'Sambit', 'Male') COMMIT TRANSACTION
How to solve the Phantom Read Concurrency Problem in SQL Server?
You can use the Serializable or Snapshot Transaction Isolation Level to solve the Phantom Read Concurrency Problem in SQL Server. Please have a look at the following table which shows which isolation level solves which concurrency problems in SQL Server.
In our example, to fix the Phantom Read Concurrency Problem let set the transaction isolation level of Transaction 1 to serializable. The Serializable Transaction Isolation Level places a range lock on the rows returns by the transaction based on the condition. In our example, it will place a lock where Gender is Male, which prevents any other transaction from inserting new rows within that Gender. This solves the phantom read problem in SQL Server.
When you execute Transaction 1 and 2 from 2 different instances of SQL Server Management Studio. Transaction 2 is blocked until Transaction 1 completes and at the end of Transaction 1, both the reads get the same number of rows. Modify the Transaction 1 code as follows:
-- Transaction 1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT * FROM Employees where Gender = 'Male' -- Do Some work WAITFOR DELAY '00:00:10' SELECT * FROM Employees where Gender = 'Male' COMMIT TRANSACTION
What are the Differences between Repeatable Read and Serializable Isolation levels?
The Repeatable Read Transaction Isolation Level prevents Non-Repeatable Read, Dirty Read, and Lost Update concurrency problems. In order to do this, the Repeatable Read adds additional locks on the data read by the transaction, which ensures that once a transaction reads the data, then those data will be prevented from being read, update or delete by any other transaction. But it allows to insertion of new data into the database, as a result, it does not prevent Phantom Read Concurrency Problem.
On the other hand, the Serializable Isolation Level prevents all sorts of concurrency problems such as Non-Repeatable Read, Dirty Read, Lost Update, and Phantom Read. In order to do this, it places a range lock on the data return by the transaction which ensures that once a transaction reads the data, then no other transaction can read, update, delete or insert new data within that range.
In the next article, I am going to discuss the Snapshot Isolation Level in SQL Server with an example. Here, in this article, I try to explain the Phantom Read Problem in SQL Server with Examples. I would like to have your feedback. Please post your feedback, question, or comments about this article.
