locked
What really happens in .net with Transactions and IsolationLevel?

    Question

  • Hi,

    Im using transactions scropes(sometime nested) in .NET (C#) against MSSQL 2008 R2 and Im not clear on the documentation.

    There is 4 isolationlevels according to this document, the default is read commited. In some cases this level could be set to Serializable by the framework and if so it looks somthing like this :

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
            #1
            using (TransactionScope scope = new TransactionScope())
            {
                  #2
                  using (TransactionScope scope = new TransactionScope())
                  {
                         #3
                         Scope.Complete();
                   }
                   #4
                   Scope.Complete();
            }
            #5
    }
    1. Transaction starts and the isolationLevel might be set to Serlizable
    2. We now starts a new transaction(nested transaction) will this also be set to Serlizable becouse of #1?
    3. A simple select is made, will this run in Serlizable or ReadCommited isolationLevel?
    4. The inner transaction is commited and accordigin to this(first note) it will be set to default(ReadCommited), is this true?
    5. The last transaction scope is commited and this should also be set to ReadCommited at this point?

    So the question is, If we do a update in the first transaction and then a select in the inner transaction, will both be running in Serlizable isolationLevel? And how will this effect a totaly new transaction that is created after these(above is commited)? Will it already be set to Serlizable isolationLevel or will it be set to ReadCommited?

    Is this isolationLevel bound to a connection pool on the SQL server? And will the last set isolationLevel presist untill a call is made on the same connection(from the pool) again even when using transaction scopes?

    BestRegards

    lundi 27 février 2012 16:06

Toutes les réponses

  • Hi SnowJim,

    Check below IsolationLevel Article. It may help you.

    http://beyondrelational.com/modules/2/blogs/28/posts/10464/sql-server-transaction-isolation-level-read-committed.aspx

    Regards

    Kayden

     
    lundi 12 mars 2012 18:59