Connection pool corrupted by nested ADO.NET transactions (with MSDTC)

Question

I can't find answer anywhere.

I will show simple code fragment which presents how to easily corrupt connection pool.
Connection pool corruption means that every new connection open try will fail.

To experience the problem we need:

1. to be in distributed transaction
2. nested sqlconnection and its sqltransaction in other sqlconnection and sqltransaction
3. do rollback (explict or implict - simply do not commit) nested sqltransaction

When connection pool is corrupted each sqlConnection.Open() throws one of:

• SqlException: New request is not allowed to start because it should come with valid transaction descriptor.
• SqlException: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

There is some kind of threads race inside ADO.NET. If I put Thread.Sleep(10) somewhere in the code it could change received exception to second one. Sometimes it changes witout any modifications.

How to reproduce

1. Enable Distributed Transaction Coordinator windows service (it's enabled by default).
2. Create empty console application.
3. Create 2 databases (can be empty) or 1 database and uncomment line: Transaction.Current.EnlistDurable[...]
4. Copy&paste following code:

var connectionStringA = String.Format(@"Data Source={0};Initial Catalog={1};Integrated Security=True;pooling=true;Max Pool Size=20;Enlist=true",
            @".\YourServer", "DataBaseA");
var connectionStringB = String.Format(@"Data Source={0};Initial Catalog={1};Integrated Security=True;pooling=true;Max Pool Size=20;Enlist=true",
            @".\YourServer", "DataBaseB");

try
{
    using (var transactionScope = new TransactionScope())
    {
        //we need to force promotion to distributed transaction:
        using (var sqlConnection = new SqlConnection(connectionStringA))
        {
            sqlConnection.Open();
        }
        // you can replace last 3 lines with: (the result will be the same)
        // Transaction.Current.EnlistDurable(Guid.NewGuid(), new EmptyIEnlistmentNotificationImplementation(), EnlistmentOptions.EnlistDuringPrepareRequired);

        bool errorOccured;
        using (var sqlConnection2 = new SqlConnection(connectionStringB))
        {
            sqlConnection2.Open();
            using (var sqlTransaction2 = sqlConnection2.BeginTransaction())
            {
                using (var sqlConnection3 = new SqlConnection(connectionStringB))
                {
                    sqlConnection3.Open();
                    using (var sqlTransaction3 = sqlConnection3.BeginTransaction())
                    {
                        errorOccured = true;
                        sqlTransaction3.Rollback();
                    }
                }
                if (!errorOccured)
                {
                    sqlTransaction2.Commit();
                }
                else
                {
                    //do nothing, sqlTransaction3 is alread rolled back by sqlTransaction2
                }
            }
        }
        if (!errorOccured)
            transactionScope.Complete();
    }
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
}

Then:

for (var i = 0; i < 10; i++) //all tries will fail
{
    try
    {
        using (var sqlConnection1 = new SqlConnection(connectionStringB))
        {
            // Following line will throw: 
            // 1. SqlException: New request is not allowed to start because it should come with valid transaction descriptor.
            // or
            // 2. SqlException: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
            sqlConnection1.Open();
            Console.WriteLine("Connection successfully open.");
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
}

Known poor solutions and what interesting can be observed

Poor solutions:

1. Inside nested sqltransaction using block do:
   sqlTransaction3.Rollback(); SqlConnection.ClearPool(sqlConnection3);

2. Replace all SqlTransactions with TransactionScopes (TransactionScope has to wrap SqlConnection.Open())

3. In nested block use sqlconnection from outer block

Interesting observations:

1. If apllication wait couple minutes after connection pool coruption then everything work fine. So connection pool coruption lasts only couple minutes.

2. With debugger attached. When execution leaves outer sqltransaction using block SqlException: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. is thrown. That exception is not catchable by try ... catch .....

How to solve it ?

That problem makes my web application almost dead (cannot open any new sql connection).
Presented code fragment is extracted from whole pipeline which consist calls to 3rd party frameworks too. I cannot simply change the code.

• Does anybody know what exactly goes wrong ?
• Is it ADO.NET bug ?
• Perhaps I (and some frameworks...) do something wrong ?

My environment (it doesn't seem to be very important)

• .NET Framework 4.5
• MS SQL Server 2012

Saunderson

I would say that your "Poor Solution" #2 is the correct way to do it. I'm not sure why you favor SqlTransaction to TransactionScope. – Baltic

Answer 1

I know this question was asked a long while ago, but I think I have the answer for anyone still having this problem.

Nested Transactions in SQL are not as they would appear in the structure of the code that creates them.

No matter how many nested transactions there are, only the outer transaction matters.

For the outer transaction to be able to commit, the inner transactions must commit, in other words, the inner transactions have no effect if they commit - the outer one must still commit for the transaction to complete.

However, if an inner transaction rolls back, the outer transaction is rolled back to its start. The outer transaction must still roll back or commit - or it is still in its started state.

Therefore, in the above example, the line

//do nothing, sqlTransaction3 is alread rolled back by sqlTransaction2

should be

sqlTransaction2.Rollback();

unless there are other transactions that could complete and therefore complete the outer transaction.

Tabber