System.Transaction implicit transaction messing with my other connections

Question

I'm trying to use System.Transaction.TransactionScope to create a transaction to call a few stored procedures but it doesn't seem to clean up after itself. Once the transaction is finished (commited or not and the transaction scope object is disposed) subsequent connections to the database open up with the read commit level of serializable instead of read commited like they normally would.

I'm opening and closing a connection for each call (well closing and returning to a pool of connections like normal in .NET), am I missing some way to explicitly reset the connection when I'm done using it for a transaction? I thought the idea behind System.Transaction.TransactionScope was to hide all the complexity.

So the code I have looks like this:

      using (var scope = new TransactionScope())
      {
       ... make my 3 stored procedure calls ...

       scope.Complete();

       return returnCode;
      }

which I guess is the normal way to do it. But then if I look in sqlserver profiler I can see connections being opened with

set transaction isolation level serializable

which is messing with subsequent non-transaction related database activity and also is apparently not as fast. I can get around this by setting a transaction option to explicity do the transaction with ReadCommited but this is not the ideal behaviour for this operation in my opinion.

I've also tried explicitly creating a Commitabletransaction object, creating explict new transactions instead of using the ambient one and still no luck.

Any ideas on how to fix this would be much appreciated as any calls that use the serializable connection will throw an error if they try to use a readpast locking hint.

Answer 1

You should also see a reset (sp_reset_connection) between uses of the same connection in the pool; will that not reset the isolation level? Have you tried reproducing a serializable issue (for example, lock escalation deadlocks)

Answer 2

Use TransactionOptions.IsolationLevel

By default, it's serializable

TransactionOptions transactionoptions1 = new TransactionOptions();
transactionoptions1.IsolationLevel = IsolationLevel.ReadCommitted;
using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionoptions1))
{
    ... make my 3 stored procedure calls ...

    scope.Complete();

    return returnCode;
}