Change the IsolationLevel TransactionScope value after the transaction completes

Question

When I save the data in a database, I used TransactionScope with the IsolationLevel parameter for Serializable.

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

Now, after the completion of the execution, I want to change the TransactionScopeIsolationLevel.

Edit

I understand that if IsolationLevel is set to Serializable, then after the transaction is completed, the connection object is closed and returned to the connection pool, and when some other request arrives, it retrieves this connection object from the pool and thus is executed by the previous IsolationLevel. Therefore, I want to change the default isolation level after each transaction.

Answer 1

You are right: the isolation level does not reset when the connection to the pool returns. This is terrible behavior, but we stuck with it ...

There are two strategies:

• Reset isolation level before returning: this is your approach.
• Always use a connection with an explicit transaction (or TransactionScope) to guarantee isolation.

I recommend you do the latter.

If you insist on doing (1), you can just change the isolation level after closing TransactionScope , but you have to do it 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 was a bit of it. Fortunately, the connector logic was centralized. What I did was change the connection string application setting if Transaction.Current is not null (which would imply that we are inside TransactionScope).

Therefore, TransactionScope connections are not merged with others.