In a previous post, we discussed how to work with batches where we created a table named Accounting.BankAccounts and inserted two records. We will continue to use this table to continue our blog series on transaction isolation levels.

SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
BEGIN TRAN
	UPDATE Accounting.BankAccounts
	SET Balance -= 300
	WHERE AcctID = 1
		WAITFOR DELAY '00:00:10:000'
	ROLLBACK TRAN
	SELECT AcctID, AcctName, Balance
	FROM Accounting.BankAccounts
	WHERE AcctID = 1

To begin we will start with a new session and write the code as shown above. This will set the Transaction Isolation Level to Read Uncommitted and begin a new transaction. The original balance for AcctID 1 should be $500 with the UPDATE statement subtracting 300. Now to simulate running two sessions at the same time, we have added a WAITFOR DELAY statement to pause 10 seconds to allow us time to run the second session. In a real-world environment, you most likely would not have this statement in your code. After the 10 seconds have expired, we will ROLLBACK the transaction and then run a SELECT statement to see the balance has returned to $500.

However, during those 10 seconds, we will run the code for the second session. This code will also set the Transaction Isolation Level to Read Uncommitted.

SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
	SELECT * FROM Accounting.BankAccounts 
	WHERE AcctID = 1

When the SELECT statement from the second session is executed during the period the first session is waiting for the 10-second delay, we see an example of a dirty read. To be more specific, session two is reading a data page located in the buffer cache that is currently being modified.

Finally, once the 10-second delay is finished in session one the transaction will roll back to the original $500 balance. However, session two observed a balance of $200 or was reading uncommitted data. This is an example of a dirty read.