Recommended practice for stopping transactions escalating to distributed when using transactionscope

Question

Using the TransactionScope object to set up an implicit transaction that doesn't need to be passed across function calls is great! However, if a connection is opened whilst another is already open, the transaction coordinator silently escalates the transaction to be distributed (needing MSDTC service to be running and taking up much more resources and time).

So, this is fine:

        using (var ts = new TransactionScope())
        {
            using (var c = DatabaseManager.GetOpenConnection())
            {
                // Do Work
            }
            using (var c = DatabaseManager.GetOpenConnection())
            {
                // Do more work in same transaction using different connection
            }
            ts.Complete();
        }

But this escalates the transaction:

        using (var ts = new TransactionScope())
        {
            using (var c = DatabaseManager.GetOpenConnection())
            {
                // Do Work
                using (var nestedConnection = DatabaseManager.GetOpenConnection())
                {
                    // Do more work in same transaction using different nested connection - escalated transaction to distributed
                }
            }
            ts.Complete();
        }

Is there a recommended practise to avoid escalating transactions in this way, whilst still using nested connections?

The best I can come up with at the moment is having a ThreadStatic connection and reusing that if Transaction.Current is set, like so:

public static class DatabaseManager
{
    private const string _connectionString = "data source=.\\sql2008; initial catalog=test; integrated security=true";

    [ThreadStatic]
    private static SqlConnection _transactionConnection;

    [ThreadStatic] private static int _connectionNesting;

    private static SqlConnection GetTransactionConnection()
    {
        if (_transactionConnection == null)
        {
            Transaction.Current.TransactionCompleted += ((s, e) =>
            {
                _connectionNesting = 0;
                if (_transactionConnection != null)
                {
                    _transactionConnection.Dispose();
                    _transactionConnection = null;
                }
            });

            _transactionConnection = new SqlConnection(_connectionString);
            _transactionConnection.Disposed += ((s, e) =>
            {
                if (Transaction.Current != null)
                {
                    _connectionNesting--;
                    if (_connectionNesting > 0)
                    {
                        // Since connection is nested and same as parent, need to keep it open as parent is not expecting it to be closed!
                        _transactionConnection.ConnectionString = _connectionString;
                        _transactionConnection.Open();
                    }
                    else
                    {
                        // Can forget transaction connection and spin up a new one next time one's asked for inside this transaction
                        _transactionConnection = null;
                    }
                }
            });
        }
        return _transactionConnection;
    }

    public static SqlConnection GetOpenConnection()
    {
        SqlConnection connection;
        if (Transaction.Current != null)
        {
            connection = GetTransactionConnection();
            _connectionNesting++;
        }
        else
        {
            connection = new SqlConnection(_connectionString);
        }
        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }
        return connection;
    }
}

Edit: So, if the answer is to reuse the same connection when it's nested inside a transactionscope, as the code above does, I wonder about the implications of disposing of this connection mid-transaction.

So far as I can see (using Reflector to examine code), the connection's settings (connection string etc.) are reset and the connection is closed. So (in theory), re-setting the connection string and opening the connection on subsequent calls should "reuse" the connection and prevent escalation (and my initial testing agrees with this).

It does seem a little hacky though... and I'm sure there must be a best-practise somewhere that states that one should not continue to use an object after it's been disposed of!

However, since I cannot subclass the sealed SqlConnection, and want to maintain my transaction-agnostic connection-pool-friendly methods, I struggle (but would be delighted) to see a better way.

Also, realised that I could force non-nested connections by throwing exception if application code attempts to open nested connection (which in most cases is unnecessary, in our codebase)

public static class DatabaseManager
{
    private const string _connectionString = "data source=.\\sql2008; initial catalog=test; integrated security=true; enlist=true;Application Name='jimmy'";

    [ThreadStatic]
    private static bool _transactionHooked;
    [ThreadStatic]
    private static bool _openConnection;

    public static SqlConnection GetOpenConnection()
    {
        var connection = new SqlConnection(_connectionString);
        if (Transaction.Current != null)
        {
            if (_openConnection)
            {
                throw new ApplicationException("Nested connections in transaction not allowed");
            }

            _openConnection = true;
            connection.Disposed += ((s, e) => _openConnection = false);

            if (!_transactionHooked)
            {
                Transaction.Current.TransactionCompleted += ((s, e) =>
                {
                    _openConnection = false;
                    _transactionHooked = false;
                });
                _transactionHooked = true;
            }
        }
        connection.Open();
        return connection;
    }
}

Would still value a less hacky solution :)

Answer 1

One of the primary reasons for transaction escalation is when you have multiple (different) connections involved in a transaction. This almost always escalates to a distributed transaction. And it is indeed a pain.

This is why we make sure that all our transactions use a single connection object. There are several ways to do this. For the most part, we use the thread static object to store a connection object, and our classes that do the database persistance work, use the thread static connection object (which is shared of course). This prevents multiple connections objects from being used and has eliminated transaction escalation. You could also achieve this by simply passing a connection object from method to method, but this isn't as clean, IMO.