SQL Server Concurrency–Dirty Reads

on February 9, 2013


Database concurrency ensures that when multiple operations are occurring at once, the final result is still in agreement — that they concur. The problems described in this three-part article only occur when multiple sessions are occurring at once in your database, including lost updates (Part 1), dirty reads and non-repeatable reads (Part 3).

What is a Dirty Read?

A dirty read takes no notice of any lock taken by another process. The read is officially “dirty” when it reads data that is uncommitted. This can become problematic if the uncommitted transaction fails or for some other reason is rolled back.
Imagine a scenario in which you are shopping on a website and place an item into your basket and proceed to payment. The site’s checkout process decrements the stock by one and starts to charge your card all in the one transaction. At that time, a second unrelated process starts. The website’s back office stock interface runs and makes a dirty read of all the product inventory levels, reading the reduced value. Unfortunately, there is a problem with your transaction (insufficient funds), and your purchase transaction is rolled back. The website stock level has now reverted to the original level, but the stock interface has just reported a different value.
You can run the following example against the AdventureWorks2012 database. Session 1 starts an explicit transaction to update all persons with a last name of “Jones” to have the same first name of “James.” This transaction will be rolled back after five seconds, and a SELECT is run to show the original values (code file Ch6DirtyReads.sql):

/* SESSION 1 */
USE AdventureWorks2012;
BEGIN TRANSACTION;
UPDATE Person.Person
SET FirstName = 'James'
WHERE LastName = 'Jones';
WAITFOR DELAY '00:00:05.000';
ROLLBACK TRANSACTION;
SELECT FirstName
,LastName
FROM Person.Person
WHERE LastName = 'Jones';
Once Session 1 is running, quickly switch over to a second session and execute the following SQL statement. The SQL in this second session will perform a dirty read. If you time it right and execute this query while the transaction in Session 1 is open (it has not yet been rolled back), then your output will match Figure 1 and every person with a surname of “Jones” now has a first name of “James”:
C 06f 003
Figure 1.
/* SESSION 2 */

USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
SELECT FirstName ,LastName 
FROM Person.Person 
WHERE LastName = 'Jones';

 

Read Part 1 of this article about lost updates and Part 3 about non-repeatable reads.

Related Posts

Trackbacks

Leave a Reply