Post reply

Resetting the default isolation level

  • November 11, 2009 at 8:15 am

    #213647

    Is there a way of resetting the default isolation level of a database to Read Uncommited? Or is this a permanent stamp when the database is first created?

    Thank you for your replies.

  • November 11, 2009 at 8:35 am

    #1077843

    No and no. The default isolation level for SQL is read committed. It's not a database level setting that can be defined on creation of a DB. If you're connecting from a front end app, you can change the connection string properties to always use a particular isolation level. Be careful of the side effects if you do.

    Why do you want to use read uncommitted throughout? Are you aware this allows SQL to potentially return incorrect data from time to time?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 11, 2009 at 8:55 am

    #1077860

    Thank you Gail. We are in the process of doing a very large migration from Oracle to SQL Server 2005. In doing so we are experiencing a great deal of blocking based on some long running SELECT statements. Obviously, we need to clean these up, but in the mean time, we were looking for a way to move the transactions through and tolerating the 'dirty reads' for the time being.

    David

  • November 11, 2009 at 9:02 am

    #1077863

    Consider read-committed snapshot, just watch the impact on TempDB.

    It's not just dirty reads that read uncommitted allows. It can result in rows missed in a scan or rows read twice in the scan. See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

    I would highly suggest you work on the problem (poor indexes or sub-optimal queries) rather than hiding the symptoms, otherwise when the symptoms are hidden, management may decide that there's no need to fix the root cause.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 11, 2009 at 9:10 am

    #1077870

    Thank you Gail. we will definitely look into Read Uncommited snapshot and yes it is our intention of getting the underlying code fixed as part of our final solution to this.

    Thanks!

    David

  • November 11, 2009 at 9:13 am

    #1077874

    daveFromIll (11/11/2009)

    Thank you Gail. we will definitely look into Read Uncommited snapshot

    Read Committed Snapshot Isolation Level.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 11, 2009 at 9:16 am

    #1077875

    Whoops! Sorry. A little tired. Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply