Back to: SQL Server Tutorial For Beginners and Professionals
Non-Repeatable Read Concurrency Problem in SQL Server with Examples
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 where we discussed Lost Update Concurrency Problem in SQL Server with Examples. At the end of this article, you will understand what the Non-Repeatable Read Concurrency Problem is, when this problem occurs and how to overcome this Non-Repeatable Read Concurrency Problem in SQL Server with Examples.
What is a Non-Repeatable Read Concurrency Problem in SQL Server?
The Non-Repeatable Read Concurrency Problem 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.
Understanding Non-Repeatable Read Concurrency Problem in SQL Server
Let’s understand the Non-Repeatable Read Concurrency Problem in SQL Server with an example. We are going to use the following Products table to understand this concept.
Please use the below SQL Script to create and populate the Products table with the required sample 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)
Non-Repeatable Read Concurrency Problem
Please have a look at the following diagram which shows the Non-Repeatable Read Problem in SQL Server. As shown in the below image, we have two transactions (Transaction 1 and Transaction 2). 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, 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 Concurrency Problem Example in SQL Server:
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 is completed, it gets a different value for reading 1 and reading 2, resulting in a non-repeatable read concurrency problem. As we already discussed READ COMMITTED and READ UNCOMMITTED Transaction Isolation Level produces the Non-Repeatable Read Concurrency Problem. So, here, we need to set the Transaction Isolation Level either READ COMMITTED and READ UNCOMMITTED. I am using READ COMMITTED Transaction Isolation Level in the below transactions to introduce the Non-Repeatable Read Concurrency Problem.
Transaction 1
-- Transaction 1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED 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
-- Transaction 2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED 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. So, let us set the transaction isolation level of both Transactions 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. Let us rewrite both the transactions using the Repeatable Read Transaction Isolation Level.
Modify the Transaction 1 code as follows:
-- Transaction 1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 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
Modify the Transaction 2 code as follows:
-- Transaction 2 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ UPDATE Products SET Quantity = 5 WHERE Id = 1001
With the above changes in place, now run transaction 1 first and then the second transaction and you will see that it gives the same result for both the read in transaction 1. 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 the Quantity of the same product mobile.
Please have a look at the following diagram for a better idea i.e. which isolation level solve which concurrency problems in SQL Server.
In the next article, I am going to discuss Phantom Read Concurrency Problem in SQL Server with Examples. Here, in this article, I try to explain the Non-Repeatable Read Concurrency Problem in SQL Server with Examples. I would like to have your feedback. Please post your feedback, question, or comments about this Non-Repeatable Read Concurrency Problem in SQL Server with Examples article.