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.

score 6 · Accepted Answer · answered Feb 19 '13 at 12:47

6

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:

Reset the isolation level before returning: This is your approach.
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?

answered Feb 19 '13 at 12:47

usr

168,620
35
240
369

Thanks for the response, I am ready to accept other better options, but as per you are suggesting to open all the connections with an explicit transactions, don't you think this will bring lots of overhead in the system. I have a huge application hundreds of end points but there are only couple of scenarios where i am using TransactionScope. What do you suggest for such things? – Manvinder Feb 19 '13 at 12:59
The transaction overhead is almost nothing as all statements use an implicit transaction anyway.; If you can't migrate everything your approach might actually be a good idea. A hack, but a pragmatic solution. I can't see any major problems with it. – usr Feb 19 '13 at 13:47
does conn.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL XXX"); work? Has anyone tried it? – hazimdikenli Jun 26 '15 at 11:02
@hazimdikenli sure it does – AgentFire Sep 19 '19 at 20:58

score 3 · Answer 2 · answered Oct 14 '13 at 16:23

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.

Change the TransactionScope IsolationLevel after completing the transaction

2 Answers2