Change the TransactionScope IsolationLevel after completing the transaction

Question

When i save data in the database i used TransactionScope with IsolationLevel set to Serializable.

TransactionOptions options = new TransactionOptions
        {
            IsolationLevel=IsolationLevel.Serializable
        };


using (var transaction = new TransactionScope(TransactionScopeOption.Required,options))
{
    transation.Complete();
}

Now after the execution is over, i want to change the TransactionScopeIsolationLevel.

Edit

What I understand is this, if IsolationLevel set to Serializable then after completing the transaction, the connection object is closed and return to connection pool and when some other request arrives it fetch that connection object from the pool and thus effected by the previous IsolationLevel. So i want to change isolation level to default after every transaction.

Answer 1

You're right: the isolation level is not reset when returning a connection to the pool. This is horrible behavior but we are stuck with it...

There are two strategies:

1. Reset the isolation level before returning: This is your approach.
2. Always use a connection with an explicit transaction (or TransactionScope) so that the isolation level is guaranteed.

I recommend you do the latter.

If you insist on doing (1) you can simply change the isolation level after closing the TransactionScope but you have to do this with the connection object. Example:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (var transaction = new TransactionScope(TransactionScopeOption.Required,options))
    {
        connection.Open(); //open inside of scope so that the conn enlists itself
        transation.Complete();
    }
    //conn is still open but without transaction
    conn.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL XXX"); //pseudo-code
} //return to pool

Does this work for you?

Answer 2

I've been bit by this. Luckily the Connection String logic was centralized. What I did was to change the connection string's application setting if Transaction.Current is not null (which would imply that we're inside a TransactionScope).

This way the TransactionScope connections don't pool with the others.