none
How i can change Default Isolation Level only in single place in code RRS feed

  • Question

  • Hi,

    How can i change default isolation i.e. read committed to snapshot, There are thousands of transaction used in code and i only want to fix it on only one place.

    Thanks


    • Edited by jawad g Tuesday, April 10, 2012 8:23 AM
    Tuesday, April 10, 2012 4:52 AM

All replies

  • Hi,

    You can change it at the Database server side. In this case you do not need to specify the isolation level for your transaction, you can keep the default, read committed.  For more details please read this article: http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.100).aspx

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Tuesday, April 10, 2012 12:54 PM
  • Hi janos,

    In that article check the following, It is saying that you must intiate a sqlTransaction using the Isolation Level.Snapshot......

    kindly read the following again. Thanks 

    Working with Snapshot Isolation in ADO.NET

    Snapshot isolation is supported in ADO.NET by the SqlTransaction class. If a database has been enabled for snapshot isolation but is not configured for READ_COMMITTED_SNAPSHOT ON, you must initiate a SqlTransaction using the IsolationLevel.Snapshot enumeration value when calling the BeginTransaction method. This code fragment assumes that connection is an open SqlConnection object.

    Wednesday, April 11, 2012 5:32 AM
  • hi jawad,

    I have read the article and understand too. What I'm talking about is correct: change the isolation level at the database side, then you can use the default isolation level - read committed - as the db level isolation will take place. 

    "Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb). You must enable snapshot isolation in each database that uses it with the Transact-SQL ALTER DATABASE statement. In this respect, snapshot isolation differs from the traditional isolation levels of READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and READ UNCOMMITTED, which require no configuration. The following statements activate snapshot isolation and replace the default READ COMMITTED behavior with SNAPSHOT:

    ALTER DATABASE MyDatabase
    SET ALLOW_SNAPSHOT_ISOLATION ON
    
    ALTER DATABASE MyDatabase
    SET READ_COMMITTED_SNAPSHOT ON
    

    Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level. If the READ_COMMITTED_SNAPSHOT option is set to OFF, you must explicitly set the Snapshot isolation level for each session in order to access versioned rows."

    I'm using this way and I do no need to specify the isolation level in my code. Please read the article again.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    Wednesday, April 11, 2012 7:15 AM
  • Hi janos,

    As per my knowledge, If we do not set Isolation Level in code then by default it is read committed, And On database end if we set read committed snapshot on and allow snapshot isolation level on

    then there is possibility that non repeatable reads or phantom  reads may occur.

    Thursday, April 12, 2012 3:05 PM