Non-Repeatable Read Concurrency Problem

Back to: SQL Server Tutorial For Beginners and Professionals

Non-Repeatable Read Concurrency Problem in SQL Server

In this article, I will discuss the Non-Repeatable Read Concurrency Problem in SQL Server and how to overcome this problem with an example. Please read our previous article discussing the 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 in SQL Server occurs when a transaction reads the same row twice and gets a different value each time. This situation arises in a multi-user environment where transactions can read, update, or delete data concurrently. The Non-Repeatable Read issue deals explicitly with the scenario where one transaction’s update affects the result of another transaction’s read operation, leading to inconsistent results within the same transaction. Here is how a Non-Repeatable Read can occur:

• Transaction 1 starts and reads a row from the database.
• Before Transaction 1 completes, Transaction 2 starts and updates or deletes that same row Transaction 1 read, and then commits the changes.
• Transaction 1 rereads the same row and finds a different value than the one it read the first time because of the update or deletion made by Transaction 2.

This inconsistency can lead to incorrect data being processed within the scope of Transaction 1. For example, a report generated based on these reads could display incorrect information or a decision made based on this data could be flawed.

Understanding Non-Repeatable Read Concurrency Problems 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 SQL script below 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 reads the quantity available as 10 for the mobile product. Once it reads the data, it will do some other work. At this point, Transaction 2 starts, and it updates the quantity to 5 for the same mobile product. 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 Levels produce the Non-Repeatable Read Concurrency Problem. So, here, we need to set the Transaction Isolation Level to either READ COMMITTED or READ UNCOMMITTED. I am using the READ COMMITTED Transaction Isolation Level in the transactions below 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 Do We Overcome the Non-Repeatable Read Concurrency Problem in SQL Server?

In order to overcome the Non-Repeatable Read Problem in SQL Server, we need to use either the 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 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 read-in transactions 1. When you execute Transaction 1 and 2 from 2 different SQL Server Management Studio instances, Transaction 2 is blocked until Transaction 1 completes. At the end of Transaction 1, both the reads get the same value for the quantity of the same mobile product.

How Does the REPEATABLE READ Isolation Level Prevent Non-Repeatable Read Concurrency Issues?

In SQL Server, the Repeatable Read isolation level aims explicitly to prevent the concurrency issue known as non-repeatable reads. A non-repeatable read occurs when a transaction reads the same row twice and gets different data each time because another transaction updated the row between the two reads. The Repeatable Read isolation level uses locks to ensure that once data is read within a transaction, no other transactions can modify that data until the first transaction completes, thus ensuring the data remains consistent for the duration of the transaction. Here’s how it accomplishes this:

Shared Locks for Read Operations

• Mechanism: When a transaction under the Repeatable Read isolation level reads data, the SQL Server places shared locks on the accessed data. These locks do not prevent other transactions from reading the same data, but they prevent modification to the data by other transactions. This means that if another transaction wants to update or delete the data that has a shared lock, it will have to wait until the shared lock is released.
• Duration: Unlike in the Read Committed isolation level, where shared locks are released as soon as the read operation is complete, in Repeatable Read, shared locks are held until the end of the transaction. This ensures that if the transaction reads the data again, it will find it unchanged, thereby preventing non-repeatable reads.

Exclusive Locks for Write Operations

• Mechanism: For write operations (INSERT, UPDATE, DELETE), the Repeatable Read isolation level uses exclusive locks. An exclusive lock ensures that no other transaction can read or modify the data until the lock is released. This is standard across isolation levels when dealing with write operations.
• Impact on Non-Repeatable Reads: While exclusive locks are primarily about protecting data being written, their use in conjunction with shared locks under Repeatable Read ensures that if a transaction is reading data, another transaction cannot modify that data simultaneously. This coordination between shared and exclusive locks is crucial for maintaining data consistency.

How Non-Repeatable Reads Are Prevented?

The Repeatable Read isolation level prevents non-repeatable reads by ensuring that once a transaction reads data, no other transaction can modify that data until the first transaction is completed. By holding shared locks on read data for the transaction’s duration, SQL Server ensures that any subsequent reads within the same transaction will see the same data, thus providing a consistent view of the data and preventing non-repeatable reads.

Limitations

While the Repeatable Read isolation level is effective at preventing non-repeatable reads, it does not prevent all types of concurrency issues:

• Phantom Reads: This isolation level does not prevent phantom reads, which occur when another transaction adds new rows that meet the criteria of a query after the initial read. 

Please have a look at the following diagram for a better idea, i.e., which isolation level solves which concurrency problems in SQL Server.

In the next article, I will discuss the Phantom Read Concurrency Problem in SQL Server with Examples. In this article, I try to explain the non-repeatable read concurrency problem in SQL Servers with examples. I would like to have your feedback. Please post feedback, questions, or comments about this Non-Repeatable Read Concurrency Problem in SQL Server with Examples article.