What is a dirtyread?

A dirty read is phenomena that you can observe when dealing in certain transaction levels. Because this blog is mainly about SQL Server I will focus on how obtaining a dirty read on that platform.

Definition

When transaction 2 reads data from transaction 1 and transaction 1 is not yet committed or roll backed then transaction 2 read dirty data so there was a dirty read from transaction.

It is called dirty because transaction 1 might roll back and transaction 2 did read data that never actually existed in terms of the ACID criteria.

Occurrence

A dirty read in SQL server can only happen when you are operating in transaction level READ UNCOMMITTED or you use the query hint WITH(NOLOCK).

Demonstration

You can simply recreate a dirty read by executing the following steps. Please open Management Studio and open two tabs in AdventureWorks database. The first transaction will be executed in the first tab and the second one in the second tab.

















Transaction 1

--transaction 1
USE AdventureWorks2012;

BEGIN TRAN;
UPDATE	Person.Person
SET		FirstName = 'Ron'
WHERE	BusinessEntityID = 1;





















ROLLBACK;
-- now that transaction 1 rolled back, 
-- 'Ron' never was in the database
-- but transaction 2 read 'Ron' nonetheless
-- Transaction 2 did a dirty read

Transaction 2

--transaction 2
USE AdventureWorks2012;

BEGIN TRAN;
SELECT 	FirstName 
FROM	Person.Person
WHERE 	BusinessEntityID = 1;
COMMIT;

Result:

FirstName
-------------------------------
Ken

(1 row(s) affected)










BEGIN TRAN;
SELECT  FirstName
FROM    Person.Person WITH(NOLOCK)
WHERE   BusinessEntityID = 1;
COMMIT;

Result

FirstName
------------------------------------
Ron

(1 row(s) affected)

What happend here is that we used the WITH (NOLOCK) query hint hence we could read data that is still locked (because an ongoing transaction 1).

If we had used no query hint we would have gotten no response but the query would just have not finished until transaction 1 was over.







BEGIN TRAN;
SELECT  FirstName
FROM    Person.Person WITH(NOLOCK)
WHERE   BusinessEntityID = 1;
COMMIT;

Result 

FirstName
------------------------------------
Ken

(1 row(s) affected)