Non-Repeatable Read Concurrency Problem in SQL Server
In this article, I am going to discuss the Non-Repeatable Read Concurrency Problem in SQL Server and how to overcome this problem with an example. Please read our previous article before proceeding to this article where we discussed Lost Update Concurrency Problem and how to overcome Lost Update Concurrency Problem in SQL Server with an example.
What is Non-Repeatable Read Concurrency Problem in SQL Server?
The Non-Repeatable Read happens in SQL Server when one transaction reads the same data twice while another transaction updates that data in between the first and second read of the first transaction. Let’s understand Non-Repeatable Read Problem with an example.
We are going to use the following Products table for this example.
Please use below SQL Script to create and populate the Products table with the required data.
-- Create Products table CREATE TABLE Products ( Id INT PRIMARY KEY, Name VARCHAR(100), Quantity INT ) Go -- Insert test data into Products table INSERT INTO Products values (1001, 'Mobile', 10) INSERT INTO Products values (1002, 'Tablet', 20) INSERT INTO Products values (1003, 'Laptop', 30)
The following diagram explains the Non-Repeatable Read Problem in SQL Server:
As shown in the above image, we have two transactions. Transaction 1 starts first and it reads the Quantity available as 10 for the product Mobile. Once it reads the data, then it is going to do some other work. At this point in time, Transaction 2 starts and it updates the Quantity to 5 for the same product Mobile. Transaction 1 then makes a second read for the same product Mobile and it gets a value of 5, resulting in a non-repeatable read concurrency problem.
Non-Repeatable Read Example:
Open 2 instances of 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 completed, it gets a different value for reading 1 and reading 2, resulting in a non-repeatable read concurrency problem.
-- Transaction 1 BEGIN TRANSACTION SELECT Quantity FROM Products WHERE Id = 1001 -- Do Some work WAITFOR DELAY '00:00:15' SELECT Quantity FROM Products WHERE Id = 1001 COMMIT TRANSACTION -- Transaction 2 UPDATE Products SET Quantity = 5 WHERE Id = 1001
How to Solve the Non-Repeatable Read Concurrency Problem in SQL Server?
In order to solve the Non-Repeatable Read Problem in SQL Server, we need to use either Repeatable Read Transaction Isolation Level or any other higher isolation level such as Snapshot or Serializable. Please have a look at the following diagram for a better idea i.e. which isolation level solve which concurrency problems in SQL Server.
So, let us set the transaction isolation level of Transaction 1 to repeatable read (you can also use any higher transaction isolation level). This will ensure that the data that Transaction 1 has read will be prevented from being updated or deleted elsewhere. This solves the non-repeatable read concurrency issue.
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 value for Quantity of the same product mobile.
In the next article, I will discuss the Phantom Read Concurrency Problem in SQL Server with an example. Here, in this article, I try to explain the Non-Repeatable Read Concurrency Problem in SQL Server with an example. I would like to have your feedback. Please post your feedback, question, or comments about this article.