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

  • 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
    Wednesday, May 21, 2014 2:26 PM

All replies