Post reply

Transaction Isolation Levels

  • July 29, 2005 at 10:42 am

    #167822

    The default transaction isolation level for SQL 2000 is READ COMMITTED.

    This can be changed on a user session by SET TRANSACTION ISOLATION LEVEL ...

    Does anyone know of a way to change the default for all sessions? Is there a database wide option that I can set?

    Many thanks,

     

    Sara

     

  • July 29, 2005 at 11:10 am

    #578239

    Sara - here's something from BOL...you change the isolation level and confirm with dbcc useroption....

    USE pubs

    GO

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    GO

    DBCC USEROPTIONS

    GO







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 29, 2005 at 11:16 am

    #578241

    Thanks Sushila!

    BUT - that works only for the individual session.

    I'm looking to change it for the entire database, so that anyone who opens a session, whether in QA or through an application that comes in to view data, the session should automatically be read uncommitted.

     

    Sara

  • July 29, 2005 at 11:33 am

    #578246

    Sara..quoting from BOL again - "remains in effect until the session terminates, or until the isolation level is set to another level..." - haven't implemented this or tested anytime so cannot speak from personal knowledge...

    did you already test via different sessions after overriding the default with "set transaction isolation level" ????

    maybe I should get noel to answer this - he is the only one I know who seems to know everything!







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 29, 2005 at 11:44 am

    #578249

    It gets set only for the individual setting.

    Let's see what Noel has to say. He does seem to know it all!

     

  • July 29, 2005 at 11:48 am

    #578251

    'kay...I sent the great man this link...so hopefully you'll get your answer soon...







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 29, 2005 at 2:43 pm

    #578289

    OK I got the link

    Transaction Isolation level is a property of the Connection not of the Server or the Database. You can not force this at server or Database level  and the default is always read commited You can control this on the Application using either a centralized Connection scheme or with COM+ using then the Management console to specify the connection default isolation level

     

    HTH

     

    : I know enough to say that I don't know everything

     


    * Noel

  • July 29, 2005 at 2:59 pm

    #578290

    Noel - for my part I'm content with knowing that I know almost nothing about anything - but what I DO know is a single source that can provide ALL THE ANSWERS!

    Totally awesome!







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 1, 2005 at 9:06 am

    #578565

    Thanks.

    It would have been so much easier if such a database setting existed. Too bad.

    Sara

Viewing 9 posts - 1 through 8 (of 8 total)

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