Concurrency: Dirty Reads

Read Uncommitted: Dirty Read Session 1

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.

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 has expired, we will ROLLBACK the transaction and then run a SELECT statement to see the balance has returned to $500.

The results of session one before and after code execution.

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.

Read Uncommitted: Dirty Read Session 2

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.

Result of a Dirty Read
The result of session two reading dirty data.

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.

Be the first to comment on "Concurrency: Dirty Reads"

Leave a comment

Your email address will not be published.


This site uses Akismet to reduce spam. Learn how your comment data is processed.