TransactionScope breaking SqlConnection pooling?

Question

I have an odd situation with TransactionScope and async/synchronous SQL calls that I'm having difficulty understanding. I hope that someone with a deeper understanding of the ins and outs of these kinds of operations can shed some light on the issue.

The situation: I have a NUnit testfixture which creates a TransactionScope during [SetUp] and Disposes it at [TearDown] to let each test run on the same data. I have a series of tests which kick off an asynchronous operation on the database and then execute a synchronous operation on the database. The first such test completes successfully. The second such test fails with "There is already an open DataReader associated with this Command which must be closed first.".

1. If I comment out the TransactionScope entirely, all the tests pass.
2. I tried various different TransactionScope options, and Complete / Dispose, but the same issue occurs.
3. I am using the Resharper test runner on an NUnit test, .NET 4.5.1.
4. I realize the "correct" answer may be "make everything async await". That's not an option for me, unfortunately.
5. I don't want to enable MARS, as this issue only occurs in tests.
6. I don't want to use GetAwaiter().GetResult() due to the potential deadlocks.

What it looks like to me is that once a TransactionScope.Dispose/Complete is called, the automatic SQLConnection pooling loses track of which connections have open DataReaders. It hands out the same SqlConnection to two simultaneously running operations, and the second dies.

My primary question is "what is causing this behavior (specifically)?"
My secondary question is "is there anything that can be done to safely resolve the issue?"

The replicating code below prints out the client connection Ids. On my machine, the ClientConnectionId for the ASYNC and SYNC calls in the Second test case are always the same.

Replicating Code:

[TestFixture]
public class DataReaderTests
{
    private TransactionScope _scope;
    private string _connString = @"my connection string";

    [SetUp]
    public void Setup()
    {
        var options = new TransactionOptions()
        {
            IsolationLevel = IsolationLevel.ReadCommitted,
            Timeout = TimeSpan.FromMinutes(1)
        };
        _scope = new TransactionScope(TransactionScopeOption.RequiresNew, options, TransactionScopeAsyncFlowOption.Enabled);
    }

    [Test]
    [TestCase("First")]
    [TestCase("Second")]
    public void Test(string name)
    {
        DoAsyncThing().ConfigureAwait(false);
        using (var conn = new SqlConnection(_connString))
        {
            try
            {
                conn.Open();
                Console.WriteLine("SYNC: " + conn.ClientConnectionId);
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT 1";
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int id = reader.GetInt32(0);
                        }
                    }
                }
            }
            catch (TransactionAbortedException tax)
            {
                Console.WriteLine("ERROR: " + ((SqlException)tax.InnerException.InnerException).ClientConnectionId);
                throw;
            }
        }
    }

    private async Task DoAsyncThing()
    {
        using (var connection = new SqlConnection(_connString))
        {
            await connection.OpenAsync();
            Console.WriteLine("ASYNC: " + connection.ClientConnectionId);
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "WAITFOR DELAY '00:02';";
                await cmd.ExecuteNonQueryAsync();
                Console.WriteLine("ASYNC COMPLETE");
            }
        }
    }

    [TearDown]
    public void Teardown()
    {
        _scope.Dispose();            
    }
}`

Answer 1

Check out this answer

I think the gist is that you cannot have two active sql commands executing over the same connection at the same time without a special connection string property. When you are operating under the transaction scope, you should find that both SqlConnection objects have the same client ID. However, if you remove the transaction scope they are different, which I believe implies that they are operating on separate connections.

Adding "MultipleActiveResultSets=true" to the connection string fixed the issue for me. Another alternative is to replace

DoAsyncThing().ConfigureAwait(false); 

with

DoAsyncThing().ConfigureAwait(false).GetAwaiter().GetResult();

which will terminate the first command before starting the second command.