Reset Isolation level when using Transaction Scop

Question

We're setting the isolation level to Read Uncommitted as shown below.

    TransactionOptions to = new TransactionOptions();
    to.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
    using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.RequiresNew, to))
    {
       // Do Stuff
       transaction.Complete();
    }

The trouble is once the connection is returned to the pool it doesn't get reset back to the default isolation level, which I understand is by design (The transaction isolation level is not reset when you reuse a connection from the connection pool). So when the transaction finishes, anything that reuses that connection from the pool will be run with the Read Uncommitted isolation level.

I've tried calling "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" using a SqlCommand, but there's no way to guarantee it will reuse the same connection from the pool. Nothing in // Do Stuff exposes the underlying connection.

Is there anyway to reset the isolation level without explicitly setting it on all calls to the DB just in case this code has been run previously?

score 5 · Answer 1 · answered Aug 09 '13 at 12:49

5

I would use a different connection string (possibly one that sets Pooling to false, or just fiddles with the Application Name) for the connection(s) you use within this transaction scope. That way, this connection ends up in (a different|no) pool to the other connections, and cannot accidentally be picked up by other code.

answered Aug 09 '13 at 12:49

Damien_The_Unbeliever

234,701
27
340
448

This is the approach we use. Something as "simple" as appending spaces will make the Connection String 'different' and thus fall into a different pool. Code checks the ambient scope isolation to determine which CS to use. It's not perfect by any means, but it's better than naught.. – user2864740 Oct 02 '17 at 18:18

score 2 · Accepted Answer · edited May 23 '17 at 11:53

2

I concerned myself with this problem a while ago. Short answer: there is no good solution. I consider it best-practice now to execute everything under an explicit transaction because that provides you with a guaranteed isolation level. Never use implicit transactions.

edited May 23 '17 at 11:53

Community

1
1

answered Aug 09 '13 at 14:22

usr

168,620
35
240
369

Reset Isolation level when using Transaction Scop

2 Answers2