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

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

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!

 

8 Responses to Isolation Levels and the Effects on Select Statements, Part II
  1. Jeffrey Langdon
    March 15, 2012 | 11:15 am

    I agree with what you are saying, but I have seen different results in SSMS that don’t make any sense to me. I have a 2005 SP4 database that I just updated with the following:

    BEGIN TRANSACTION
    GO
    UPDATE email
    SET emailtypeid = 0
    FROM instrument i
    INNER JOIN email e
    ON i.mprid = e.mprid
    WHERE i.instrumenttypeid = 30

    The script updates the expected 524 records. I confirm this by running before COMMITing:

    SELECT e.emailtypeid
    FROM instrument i
    INNER JOIN tblemail e
    ON i.mprid = e.mprid
    WHERE instrumenttypeid = 30

    What doesn’t make sense is that the database is set to read committed which I confirm by running DBCC useroptions so I shouldn’t see the dirty reads, but I do.

    Great post by the way.

    Jeff
    @jlangdon

    • Erin Stellato
      March 15, 2012 | 1:32 pm

      Hi Jeff-
      Thanks for the comment! For the strange behavior that you’re seeing, remember that what data you can see (or not see) when you select depends on the isolation level for the session where you’re running the select. If you’re doing this in Management Studio, you can run DBCC USEROPTIONS and check the isolation level. The only time you can set isolation level at a database level is when you’re doing Snapshot Isolation. In your case, I’m guessing that the connection which runs your SELECT is Read Uncommitted.

      Erin

      • Jeffrey Langdon
        March 15, 2012 | 2:44 pm

        So the question is what is the default isolation level setting when connecting with SSMS? It has to be read uncommitted (as you mentioned) or I wouldn’t see the dirty reads. Going to have to Google that.

        Thanks,
        Jeff

        • Erin Stellato
          March 15, 2012 | 3:16 pm

          Jeff-
          The default isolation level for SSMS is READ COMMITTED, but you can change it to be READ UNCOMMITTED via Tools | Options | Query Execution | SQL Server | Advanced. I mentioned this in my previous post (http://erinstellato.com/2012/03/isolation-levels-effects-on-select-statements/). If you run DBCC USEROPTIONS, what do you get?
          Erin

          • Jeffrey Langdon
            March 15, 2012 | 3:22 pm

            Yeah, I see that. Don’t know why I never noticed the default Isolation Level settings in SSMS before. As I mentioned in the original comment I ran DBCC USEROPTIONS (that I knew about) 🙂 and it returned READ COMMITTED, the default setting in SSMS is also READ COMMITTED so I am still a little confused about why I can see the dirty reads. Oh well, I blame MS. LOL

  2. mike
    March 15, 2012 | 11:29 am

    Great explanation.

    And our favorite, Read Committed Snapshot (alter database X set read_committed_snapshot on). We consider this to be the most Oracle-like setting. Readers don’t block writers, writers don’t block readers. Readers still read only committed data, but rather than blocking – read from a version table.

    This RCS uses row versioning instead of locks, and differs a bit from Snapshot isolation.

    BOL “Choosing Row Versioning-based Isolation Levels”

    Our product used to support both DBMS’. When SQL Server 2005 came out we migrated our Oracle customers and haven’t looked back. Oracle was nice – but the cost of supporting two systems was expensive.

    • Erin Stellato
      March 15, 2012 | 1:33 pm

      Hi Mike-
      Thanks for reading! I did talk about behavior in Oracle in my previous post and mentioned the same thing about readers and writers not blocking each other. I understand the challenges that come with supporting both SQL Server and Oracle!
      Erin

  3. Aro
    March 24, 2012 | 1:25 pm

    Great explanation.And our fvoirate, Read Committed Snapshot (alter database X set read_committed_snapshot on). We consider this to be the most Oracle-like setting. Readers don’t block writers, writers don’t block readers. Readers still read only committed data, but rather than blocking read from a version table. This RCS uses row versioning instead of locks, and differs a bit from Snapshot isolation.BOL Choosing Row Versioning-based Isolation Levels Our product used to support both DBMS’. When SQL Server 2005 came out we migrated our Oracle customers and haven’t looked back. Oracle was nice but the cost of supporting two systems was expensive.

Leave a Reply to Aro

Wanting to leave an <em>phasis on your comment?

Trackback URL http://erinstellato.com/2012/03/isolation-levels-effects-on-select-statements-part-ii/trackback/