8 responses

  1. Jeffrey Langdon
    March 15, 2012

    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

      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

        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

        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

        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

    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

      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

    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

 

 

 

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Back to top
mobile desktop