.net – Connection using enlist=false does not close after being manually enlisted in distributed transaction

connection-stringdistributed-transactionsnetsql serversqlconnection

I have a distributed transaction context using ServiceDomain. Inside it, I open an SQL connection with connection string specifying Enlist=false, so that it is not automatically enlisted in the transaction. Then, if I manually enlist the connection in the distributed transaction using EnlistDistributedTransaction, the connection does not get closed, which can end in an InvalidOperationException with:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Try the following:

try
{
    var configuration = new ServiceConfig
        {
            Transaction = TransactionOption.Required,
            TransactionTimeout = 1000
        };
    ServiceDomain.Enter(configuration);

    for (var i = 0; i < 500; ++i)
    {
        Console.WriteLine(i);
        using (var conn = new SqlConnection("Data Source=localhost;Initial Catalog=dotest;Integrated Security=SSPI;Enlist=False;"))
        {
            conn.Open();
            if (i % 2 == 0) conn.EnlistDistributedTransaction((ITransaction) ContextUtil.Transaction);
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO [Test]([ID]) VALUES(@num)";
                cmd.Parameters.AddWithValue("@num", i);
                cmd.ExecuteNonQuery();
            }
        }
    }

    ContextUtil.SetAbort();
}
finally
{
    ServiceDomain.Leave();
}

This gets stuck (and dies after a timeout) at 200 connections, as all 100 enlisted connections obviously do not get closed (and the default connection pool size is 100). (Note that you can remove the command altogether if you want to test it without creating the table.)

What am I missing or doing wrong?

Best Answer

Try setting conn.EnlistDistributedTransaction(null); at the end of query execution. 

using (var conn = new SqlConnection("Data Source=localhost;Initial Catalog=dotest;Integrated Security=SSPI;Enlist=False;"))
{
    conn.Open();
    if (i % 2 == 0) conn.EnlistDistributedTransaction((ITransaction) ContextUtil.Transaction);
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO [Test]([ID]) VALUES(@num)";
        cmd.Parameters.AddWithValue("@num", i);
        cmd.ExecuteNonQuery();
    }
   conn.EnlistDistributedTransaction(null);
}
Related Topic