Dirty Read Concurrency Problem in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Dirty Read Concurrency Problem in SQL Server with Examples

In this article, I will discuss the Dirty Read Concurrency Problem in SQL Server with Examples. Please read our previous article discussing the basic concepts of Concurrency Problems and Transaction Isolation Levels in SQL Server.

What is the Dirty Read Concurrency Problem in SQL Server?

The Dirty Read Concurrency Problem in SQL Server happens when one transaction is allowed to read the uncommitted data of another transaction. Another transaction has modified the data but has not yet been committed or rolled back. In most of the scenarios, it would not cause any problems. However, suppose the first transaction is rolled back after the second transaction reads the uncommitted data. In that case, the second transaction has dirty data that no longer exists in the database. 

A “dirty read” in the context of databases refers to a situation where one transaction can read data being modified by another transaction but not yet committed. This can lead to inconsistent or incorrect results if the second transaction rolls back its changes after the first transaction has already read them.

Understanding Dirty Read Concurrency Problem:

Here’s an example of how a dirty read can occur in SQL Server:

• Transaction A begins and updates a row in a table.
• Transaction B begins and reads the same row before Transaction A commits its changes.
• Transaction A rolls back its changes.
• Transaction B has already read the modified data, which is now inconsistent or incorrect.

Dirty reads can lead to various issues, such as inconsistent query results, data corruption, or incorrect business logic. Therefore, it’s essential to understand the implications of different isolation levels and design transactions accordingly to minimize the risk of dirty reads.

Let us understand the Dirty 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)

Dirty Read Concurrency Problem Example in SQL Server: 

Please have a look at the following image. Two transactions (Transaction 1 and Transaction 2) will work with the same data in the following example. Currently, the available Quantity of Product ID 1001 is 10. Transaction 1 updates the Quantity value to 5 for the Product Id 1001. Then, it starts to bill the customer or do some other task. While Transaction 1 is in progress, Transaction 2 starts and reads the Quantity value of the same Product ID 1001, which is 5 at the moment. At this point in time, Transaction 1 fails because of insufficient funds or for some other reason, and Transaction 1 is rolled back. Quantity is reverted back to the original value of 10. However, Transaction 2 is working with a different value, i.e., 5, which no longer exists in the database. That data is said to be dirty data that does not exist anymore. This is a Dirty Read Concurrency Problem in the SQL Server.

If you want to see this practical, open two instances of SQL Server. Write the Transaction 1 code in the first instance, and in the second instance, write the Transaction 2 code.

Transaction1: 

In the first instance, copy and paste the following code. This is going to be our first transaction. Once the transaction updates the Quantity to 5 for the ProductId 1001, we intentionally delay the execution to 15 seconds by using the Waitfor Delay statement.

BEGIN TRANSACTION
  UPDATE Products SET Quantity = 5 WHERE Id=1001

  -- Billing the customer
  Waitfor Delay '00:00:15'
  -- Insufficient Funds. Rollback transaction

ROLLBACK TRANSACTION

Transaction2:

By default, the SQL Server will not allow the reading of uncommitted data from one transaction. So, to understand the Dirty Read Concurrency Problem here, we set the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED, and then we try to read the uncommitted data as shown in the code below.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Products WHERE Id=1001

First, run Transaction 1 and then immediately run Transaction2, and you will see that the Transaction returns the Quantity as 5. Then Transaction 1 is rolled back and will update the quantity to its previous state, i.e., 10. However, Transaction 2 works with the value 5, which no longer exists in the database, and this is nothing but dirty data.

Here, we use the Read Uncommitted Transaction Isolation Level (used in Transaction 2). This is the only transaction isolation level the SQL server provides with the dirty read concurrency problem. The Read Uncommitted Transaction Isolation Level is the least restrictive isolation level among all the isolation levels provided by SQL Server. When we use this transaction isolation level, it is possible to read the uncommitted or dirty data.

How Does Read Uncommitted Transaction Isolation Level Work in SQL Server?

The Read Uncommitted transaction isolation level in SQL Server is the lowest level of isolation, allowing transactions to read data that has been modified but not yet committed by other transactions. This isolation level is used to improve performance by reducing the locking and blocking of resources that can occur with higher levels of isolation. Here is how the Read Uncommitted transaction isolation level works:

• Non-blocking Reads: Transactions operating under the Read Uncommitted level do not issue shared locks when reading data. This means they do not block other transactions from writing to the same data they read. Similarly, they are not blocked by exclusive locks that other transactions may hold on the data, allowing reads to proceed even if another transaction modifies the same data.
• Dirty Reads: Because this isolation level allows a transaction to read data changes that have not been committed, there’s a risk of “dirty reads.” A dirty read occurs when a transaction reads a value that another transaction has modified but not yet committed. If the second transaction rolls back its changes, the first transaction will have read a value that was never actually committed to the database, leading to potential inaccuracies in data processing or decision-making.
• Reduced Lock Overhead: By allowing dirty reads and not acquiring shared locks for reads, the Read Uncommitted isolation level reduces the overhead associated with lock management. This can improve query performance, especially in high-concurrency environments where transactions frequently access the same data.
• Use Cases: The Read Uncommitted isolation level is typically used in scenarios where the accuracy of the data read is not critical and performance is of higher priority. It is suitable for operations where the risks of encountering uncommitted changes are acceptable or where data is mostly read-only and rarely changes. For example, in reporting or analytics applications where real-time consistency is not critical, Read Uncommitted may be suitable.

How Do We Overcome the Dirty Read Concurrency Problem Example in SQL Server?

If you want to restrict the dirty read concurrency problem in SQL Server, you must use any Transaction Isolation Level except the Read Uncommitted Transaction Isolation Level. So, modify transaction 2 as shown below, and here, we are using the Read Committed Transaction Isolation Level.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM Products WHERE Id=1001

Now, run the first transaction and then immediately run the second transaction. You will see that until the first transaction is completed, you will not get the result for the second transaction. Once the first transaction execution is completed, you will get the data in the second transaction; this time, you will not get the uncommitted data. Instead, you will get the committed data that exists in the database.

How Does Read Committed Transaction Isolation Level Work in SQL Server?

In SQL Server, the Read Committed isolation level primarily uses shared locks for read operations and exclusive locks for write operations. Here’s how it works in the context of this isolation level:

Shared Locks in Read Committed

• Read Operations: When a transaction reads data (using a SELECT statement, for example), SQL Server acquires a shared lock on the data being read. This ensures that other transactions cannot modify the data until the shared lock is released.
• Lock Duration: In the Read Committed isolation level, shared locks are typically held only for the duration of the data read operation and are released once the data has been read.

Exclusive Locks in Read Committed

• Write Operations: For write operations (INSERT, UPDATE, DELETE), SQL Server acquires an exclusive lock on the data being modified. The exclusive lock ensures that no other transaction can read or write the data until the lock is released.
• Lock Duration: Exclusive locks are held until the transaction that acquired them either commits or rolls back, ensuring data integrity during modification.

So, in summary:

• In the Read Committed Isolation Level, shared locks are used for read operations but are held only while the data is being read. This allows other transactions to read the same data concurrently but prevents them from modifying it while the read is happening.
• Exclusive locks are used for write operations and are held until the transaction completes, preventing other transactions from accessing the locked data (either for reading or writing) during this time.

NOLOCK table hint in SQL Server:

Another option provided by SQL Server to read dirty data is the NOLOCK table hint option. The below query is equivalent to what we wrote in Transaction 2. 

SELECT * FROM Products (NOLOCK) WHERE Id=1001

In SQL Server, the NOLOCK table hint is used to specify that a query should read data without taking any locks. This hint is also known as a “dirty read” because it can potentially return uncommitted data or data that is in the process of being changed by other transactions.

Using NOLOCK can improve query performance in certain situations, especially in read-heavy environments, as it reduces contention on resources by allowing the query to proceed without waiting for locks to be released.

Drawbacks of using NOLOCK:

• Dirty Reads: As mentioned earlier, NOLOCK allows reading uncommitted data, which can lead to inconsistent or incorrect results if another transaction is in progress and has not yet committed its changes.
• Unrepeatable Reads: Data read with NOLOCK may change between subsequent reads within the same transaction, leading to inconsistent results.
• Phantom Reads: NOLOCK may allow the query to read rows that are being inserted or deleted by other transactions, leading to phantom rows appearing or disappearing between separate executions of the same query.

Please look at the table below, which describes which Transaction Isolation Level solves which Concurrency Problems in SQL Server.

In the next article, I will discuss the Lost Update Concurrency Problem in SQL Server with examples. In this article, I try to explain the dirty read concurrency problem in SQL Server using the read uncommitted transaction isolation level with an example. I would like to have your feedback. Please post feedback, questions, or comments about this Dirty Read Concurrency Problem article.