Dirty Read Concurrency Problem in SQL Server

Dirty Read Concurrency Problem in SQL Server

In this article, I am going to discuss the Dirty Read Concurrency Problem in SQL Server with an example. Please read our previous article before proceeding to this article where we discussed the basics of Concurrency Problem and Transaction Isolation in SQL Server.

What is Dirty Read in SQL Server?

The Dirty Read in SQL Server happens when one transaction is allowed to read the uncommitted data. That is the data has been modified by another transaction but not yet committed. In most of the scenarios, it would not cause any problem. However, if the first transaction is rolled back after the second transaction reads the uncommitted data, then the second transaction has dirty data that does not exist anymore in the database. 

Let us understand this with an example. We are going to use the following Products table.

Dirty Read Concurrency Problem in SQL Server

Please use the 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)
Dirty Read in SQL Server Example: 

In the following example, we have two transactions (Transaction 1 and Transaction 2) which are going to work with the same data. The Transaction 1, updates the value of Quantity to 5 for the Product let say the product Id is 1001. Then it starts to bill the customer. While Transaction 1 is in progress, the Transaction 2 starts and it reads the Quantity value of the same Product whose Id is 1001 which is 5 at the moment. At this point of time, Transaction 1 fails because of insufficient funds or for some other reason and the transaction 2 is rolled back. The Quantity is reverted back to the original value of 10, but the Transaction 2 is working with a different value i.e. 5 which does not exist in the database anymore. The following diagram shows this. 
Dirty Read Concurrency Problem in SQL Server
Open two instances of SQL server. In the first instance write the Transaction1 code and in the second instance write the Transaction 2 code.

Transaction1: 
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:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Products WHERE Id=1001

First, run the transaction 1 and then immediately run the Transaction2 and you will see that the Transaction returns the Quantity as 5.

Here we use the Read Uncommitted Transaction Isolation Level (use in Transaction2). This is the only transaction isolation level provided by SQL Server which has 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 then it is possible to read the uncommitted or dirty data.

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

SELECT * FROM Products (NOLOCK) WHERE Id=1001

Please look at the below table which describes which isolation level solve which concurrency problems in SQL Server.

Dirty Read Concurrency Problem in SQL Server

In the next article, I am going to discuss the Lost Update Concurrency Problem in SQL Server with examples.

SUMMARY

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 your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *