Isolation Levels and the Effects on Select Statements, Part II

Last week I wrote a post on Isolation Levels and the Effects on Select Statements, and I had a conversation on Twitter with Rob Farley ( b | t ) regarding one of my explanations.  For READ COMMITTED, I had written the following:

If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will read the original copy of the data (not the modified data).

Rob asked me to read it again, and I realized that this explanation was not accurate for READ COMMITTED.  In fact, what I described was the SNAPSHOT isolation level.

What I should have said for READ COMMITTED was:

If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will wait until the transaction commits or rolls back to read the data.

(I have noted and modified my original post with this information.)

To be clear, in a READ COMMITTED isolation level, you will only read committed data.  This is different than in the READ UNCOMMITTED isolation level, where can you read uncommitted, or dirty, data.

Here’s a quick example using the HumanResources.Employees table in the AdventureWorks database.  Let’s look at the output if I select from the table.  Notice that for EmployeeID 3, the Title is Engineering Manager.

Original output from Employees table

In one query window, I run the following:

USE AdventureWorks;
GO

BEGIN TRANSACTION;
UPDATE HumanResources.Employee
SET Title = 'Senior Engineering Manager'
WHERE EmployeeID = 3;

Notice that I haven’t committed the data yet.  Also notice that I didn’t set the isolation level, and that’s because the isolation level of the UPDATE does not affect what happens when I SELECT.

Now I open a second query window and run the following:

USE AdventureWorks;
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

SELECT * FROM HumanResources.Employee;

What happens?  I wait.  And I wait.  And I wait.  This query will continue to wait until I either COMMIT or ROLLBACK my UPDATE statement.  Once I do that, the results will return.  But in this case, let’s kill the query, and then run the following statements:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

SELECT * FROM HumanResources.Employee;

Now I get data back, and notice that the value for EmployeeID 3 is now Senior Engineering Manager, even though that data hasn’t been committed yet.  Yuck.  A dirty read.

Output after a dirty read

Feel free to change the isolation level for the UPDATE statement to prove out that it doesn’t affect what happens when you SELECT – and take it a step further by checking out the lock that gets taken with the SELECT in both isolation levels (I use sp_lock <SPID>).  Have fun!