Dirty Read Concurrency Problem in SQL Server
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 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.
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) that are going to work with the same data. The Transaction 1, updates the value of Quantity to 5 for the Product let say the productId is 1001. Then it starts to bill the customer. 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 of time, Transaction 1 fails because of insufficient funds or for some other reason and the transaction 2 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. The following diagram shows this.
Open two instances of SQL Server. In the first instance write the Transaction1 code and in the second instance write the Transaction 2 code.
BEGIN TRANSACTION UPDATE Products SET Quantity = 5 WHERE Id=1001 -- Billing the customer Waitfor Delay '00:00:15' -- Insufficient Funds. Rollback transaction ROLLBACK TRANSACTION
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.
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 article.