Dirty Reads and Phantom Reads in Sql Server
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 talk over the Phantom Read Concurrency Problem in SQL Server with Examples. Delight read our previous commodity where nosotros discussed the Non-Repeatable Read Concurrency Problem in SQL Server with an instance. At the finish of this article, y'all volition understand what is phantom read trouble is and when it occurs in SQL Server likewise every bit you will also empathize how to solve the phantom read trouble in SQL Server.
What is Phantom Read Concurrency Trouble in SQL Server?
The Phantom Read Concurrency Problem happens in SQL Server when 1 transaction executes a query twice and it gets a different number of rows in the result ready each time. This mostly happens when a 2nd 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 Trouble in SQL Server
Permit'due south sympathise Phantom Read Concurrency Problem in SQL Server with an example. We are going to use the post-obit Employees table to understand this concept.
Please employ the below SQL Script to create and populate the Employees tabular array with the required sample data.
-- Create Employee table CREATE Tabular array Employees ( Id INT PRIMARY KEY, Proper 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 nosotros take two transactions Transaction 1 and Transaction 2. Transaction i starts first and it reads the information from the Employees tabular array where Gender is Male person and it returns 2 rows for the first read and then Transaction one is doing some other work. At this signal, Transaction ii started and it inserts a new employee with Gender Male. Once Transaction ii inserts the new employee, then Transaction ane 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 two instances of the SQL Server Management Studio. From the first example execute the Transaction 1 code and from the second example execute the Transaction two code. Notice that when Transaction 1 is completed, information technology gets a unlike 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 tin besides use Read Committed and Read Uncommitted Transaction Isolation Levels.
Transaction 1
-- Transaction i SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM Employees where Gender = 'Male person' -- Do Some work WAITFOR DELAY '00:00:10' SELECT * FROM Employees where Gender = 'Male' COMMIT TRANSACTION
Transaction ii
-- Transaction two Gear up 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 wait at the post-obit table which shows which isolation level solves which concurrency problems in SQL Server.
In our example, to fix the Phantom Read Concurrency Problem let fix the transaction isolation level of Transaction 1 to serializable. The Serializable Transaction Isolation Level places a range lock on the rows returns past 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 inside that Gender. This solves the phantom read problem in SQL Server.
When you execute Transaction 1 and 2 from two different instances of SQL Server Direction Studio. Transaction 2 is blocked until Transaction 1 completes and at the finish of Transaction i, both the reads get the same number of rows. Modify the Transaction i code as follows:
-- Transaction 1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE Begin TRANSACTION SELECT * FROM Employees where Gender = 'Male' -- Do Some work WAITFOR Filibuster '00:00:10' SELECT * FROM Employees where Gender = 'Male person' COMMIT TRANSACTION
What are the Differences between Repeatable Read and Serializable Isolation levels?
The Repeatable Read Transaction Isolation Level prevents Non-Repeatable Read, Dingy Read, and Lost Update concurrency issues. In order to do this, the Repeatable Read adds additional locks on the data read by the transaction, which ensures that in one case a transaction reads the information, and then those data will be prevented from existence read, update or delete by whatever other transaction. Simply information technology allows to insertion of new data into the database, as a result, it does not foreclose Phantom Read Concurrency Problem.
On the other hand, the Serializable Isolation Level prevents all sorts of concurrency issues such every bit Not-Repeatable Read, Dirty Read, Lost Update, and Phantom Read . In order to do this, information technology places a range lock on the data return by the transaction which ensures that once a transaction reads the data, and then no other transaction tin 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 case. Here, in this article, I attempt to explain the Phantom Read Problem in SQL Server with Examples. I would like to take your feedback. Delight mail your feedback, question, or comments about this article.
Source: https://dotnettutorials.net/lesson/phantom-read-concurrency-problem-sql-server/
0 Response to "Dirty Reads and Phantom Reads in Sql Server"
Post a Comment