Back to: SQL Server Tutorial For Beginners and Professionals
Dirty Read Concurrency Problem in SQL Server with Examples
In this article, I am going to discuss the Dirty Read Concurrency Problem in SQL Server with Examples. Please read our previous article before proceeding to this article where we discussed the basics of Concurrency Problems and Transaction Isolation Levels in SQL Server.
What is 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. That is the data has been modified by another transaction but not yet committed or rollback. 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.
Understanding Dirty Read Concurrency Problem:
Let us understand 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 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)
Dirty Read Concurrency Problem Example in SQL Server:
Please have a look at the following image. In the following example, we have two transactions (Transaction 1 and Transaction 2) that are going to work with the same data. Currently, the available Quantity of the Product whose productId is 1001 is 10. Transaction 1, updates the value of Quantity to 5 for the Product whose productId is 1001. Then it starts to bill the customer or doing some other task. While Transaction 1 is in progress, 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 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, but Transaction 2 is working with a different value i.e. 5 which does not exist in the database anymore and that data is said to be dirty data that does not exist anymore. This Dirty Read Concurrency Problem Example in SQL Server.
If you want to see this practical, then 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:
In the first instance copy and paste the following code. This is going to be our first transaction. Here, once the transaction update the Quantity to 5 for the ProductId 1001, then we intentionally delay the execution to 15 seconds by using 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 SQL Server will not allow reading the uncommitted data of 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 below code.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM Products WHERE Id=1001
First, run transaction 1 and then immediately run the Transaction2 and you will see that the Transaction returns the Quantity as 5. Then Transaction 1 is rollback and it will update the quantity to its previous state i.e. 10. But Transaction 2 working with the value 5 which does not exist in the database anymore and this is nothing but dirty data.
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.
How to Overcome the Dirty Read Concurrency Problem Example in SQL Server?
If you want to restrict the dirty read concurrency problem in SQL Server, then you have to use any Transaction Isolation Level except the Read Uncommitted Transaction Isolation Level. So, modify transaction 2 as shown below and hear we are using 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 is not completed, you will not get the result in the second transaction. Once the first transaction execution is completed, then you will get the data in the second transaction and this time you will not get the uncommitted data rather you will get the committed data that exist in the database.
NOLOCK table hint in SQL Server:
Another option provided by 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 have a look at the below table which describes which Transaction Isolation Level solves which Concurrency Problems in SQL Server.
In the next article, I am going to discuss the Lost Update Concurrency Problem in SQL Server with examples. Here, 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 Dirty Read Concurrency Problem article.