Dirty Reads and Patience

When you are not careful with your transaction isolation levels or you get sneaky with the NOLOCK hint, one problem you can encounter is a dirty read. Let’s look at a short example to demonstrate a dirty read.

Choosing Favorites

We’ll create a table to hold a few products.

CREATE TABLE Product
(
Id INT NOT NULL PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL,
Quantity INT NOT NULL
);
GO

And we’ll add a test record to work with:

INSERT INTO Product
VALUES (1,'Your Favorite Product',1);
GO

Just Like a 1963 Corvette

To show a dirty read in action, we’re going to have split query windows open at one time in SSMS. We’ll open one new query window, open a second new query window, and right click on the new query window tab:

Select “New Vertical Tab Group” to get our two windows together:

With one window we’ll have:

BEGIN TRAN

-- Quantity set to 0 while user checking out

UPDATE Product
SET Quantity = 0
WHERE ID = 1;

-- Payment fails so set Quantity back to 1

UPDATE Product
SET Quantity = 1
WHERE ID = 1;

COMMIT TRAN

And in the other we’ll have:

SELECT * FROM Product WITH (NOLOCK)
WHERE Id = 1;
GO

SELECT * FROM Product
WHERE Id = 1;
GO

Buy Buy Buy

To start, we’ll run the highlighted section in one of our windows to simulate someone else starting a transaction of buying our favorite product:

At this point, the Quantity for ID 1 has been set to 0. Someone else has grabbed the last of our favorite product. Even though this may be a split second transaction, we might be checking for availability for ourselves at the same time. If we query the Product table at this time and our query has a transaction isolation level of read uncommitted or is using NOLOCK we will see the Quantity of 0:

But what if more data changed with the transaction before it completed? Maybe the person changed their mind or a problem occurred with the payment resulting in Quantity being set back to 1. We can simulate this by finishing the transaction:

In our case, we already checked Quantity and saw 0. We checked, got disappointed, and logged off thinking we missed our chance…even though the product is still available. We got burned by a dirty read, a.k.a. reading uncommitted data.

What would have benefited us would have been our query waiting for the transaction to complete before returning our result. If we run the highlighted section again and then run the SELECT statement without NOLOCK:

Our SELECT statement will be “Executing” until the transaction on the left is completed. If we leave the SELECT running and finish out the transaction:

We’ll see the SELECT finish and show us that the product is still available. If this whole process was a short interaction, we would have never known anything but seeing the Quantity of 1 and could have made our purchase.

Smarter, No Faster

A dirty read occurs when data is read while that data is part of a separate uncommitted transaction. Regarding the use of NOLOCK, if your results do not need to be accurate then feel free to include it in your query. Do not use it as a shortcut thinking you’re getting faster, accurate results because the risk will not be worth the reward.

Thanks for reading!