Connections will not close when using Transaction Binding = Explicit Unbind; in the connection string

Question

I use Transaction Binding=Explicit Unbindthe connection string as recommended here , since I also use TransactionScope with a timeout. The problem is that the connections do not close after they are deleted, and, ultimately, there are no more connections in the connection pool. I got the same result when I changed TransactionTimeoutIssueDemo (see Link) and ran TransactionScopeTest () (with an explicit unbind connection string) enough time in the loop to use all available connections in the connection pool. The default value for connections in the pool is 100, but this can be changed, for example, using the parameter Max Pool Size =10. It seems that the connections will not be released using explicit unbind, although both SqlConnection and TransactionScope are used with the clauseusing. Does anyone know how to handle this?

Answer 1

It seems that the connections remain in the pool and are not reused if you get an exception, as in the example. If you increase the wait time, the connection will be reused.

A workaround to this problem is to clean up the connection pool if you get this exception:

using (SqlConnection con = new SqlConnection(connectionString))
{
    con.Open();
    try
    {
        Console.WriteLine("Server is {0}", con.ServerVersion);
        Console.WriteLine("Clr is {0}", Environment.Version);
        for (int i = 0; i < 5; i++)
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "insert into TXTEST values ( " + i + " )";
                cmd.ExecuteNonQuery();
            }
            Console.WriteLine("Row inserted");
        }
        Thread.Sleep(TimeSpan.FromSeconds(1));
    }
    catch
    {
        SqlConnection.ClearPool(con);
        throw;
    }
}

In most cases, the transaction will be completed within a timeout, and everything will be fine and dandy. When the transaction actually does time out, you clear the pool to clear dirty connections that will not be reused. This, of course, will affect other connections in the pool that are not affected by this problem.

, , , .

Answer 2

, ​​ .Net 4.0.