How do you detect if a SqlConnection's "blocking period" is active?

Question

According to Microsoft's article (SQL Server Connection Pooling (ADO.NET)),

When connection pooling is enabled, and if a timeout error or other login error occurs, an exception will be thrown and subsequent connection attempts will fail for the next five seconds, the "blocking period". If the application attempts to connect within the blocking period, the first exception will be thrown again. After the blocking period ends, another connection failure by the application will result in a blocking period that is twice as long as the previous blocking period. Subsequent failures after a blocking period ends will result in a new blocking periods that is twice as long as the previous blocking period, up to a maximum of five minutes.

How would you detect that the blocking period is active? I would assume that there is some property to check before attempting the connection so that you could avoid extending the blocking period.

Answer 1

In addition to the ClientConnectionId field,

The SqlException.Message will also be reference-equal. That is, the cached string will be returned for connections that fail within the "blocking period".

However, this is also an implementation detail and may change.

Answer 2

Unfortunately there is no easy way to detect if you are in the ADO.NET "Blocking Period" or not (without resorting to something fragile like reflection).

However, if you are using .Net 4.5 or later, then you can detect if the last exception you observed from Open/OpenAsync is duplicated or not by looking at the ClientConnectionId of the SqlException and comparing that to the id of the last SqlException that you've seen (since the exceptions are duplicated, the ids are duplicated as well).

Assuming that you have a single place where you create\open SqlConnections for a single connection string, you can do the following:

public static class DataAccessLayer
{
    // Single connection string that all connections use
    private static readonly string _connectionString = "server=(local);integrated security=true;";

    // Stores that last observed connection if when opening a connection
    // NOTE: Using an object so that the Volatile methods will work
    private static object _lastErrorConnectionId = Guid.Empty;

    public static SqlConnection GetOpenedConnection()
    {
        try
        {
            SqlConnection connection = new SqlConnection(_connectionString);
            connection.Open();
            return connection;
        }
        catch (SqlException ex)
        {
            // Did the connection open get to the point of creating an internal connection?
            if (ex.ClientConnectionId != Guid.Empty)
            {
                // Verify that the connection id is something new
                var lastId = (Guid)Volatile.Read(ref _lastErrorConnectionId);
                if (ex.ClientConnectionId != lastId)
                {
                    // New error, save id and fall-through to re-throw
                    // NOTE: There is a small timing window here where multiple threads could end up switching this between
                    //       a duplicated id and a new id. Since this is unlikely and will only cause a few additional exceptions to be
                    //       thrown\logged, there isn't a large need for a lock here.
                    Volatile.Write(ref _lastErrorConnectionId, (object)ex.ClientConnectionId);
                }
                else
                {
                    // Duplicate error
                    throw new DuplicatedConnectionOpenException(_connectionString, ex);
                }
            }

            // If we are here, then this is a new exception
            throw;
        }
    }
}

public class DuplicatedConnectionOpenException : Exception
{
    public string ConnectionString { get; private set; }

    internal DuplicatedConnectionOpenException(string connectionString, SqlException innerException)
        : base("Hit the connection pool block-out period and a duplicated SqlException was thrown", innerException)
    {
        ConnectionString = connectionString;
    }
}

Now if you call GetOpenedConnection and you see a DuplicatedConnectionOpenException being thrown, you will know that you have hit the "Blocking Period".

NOTE: I'm using Volatile Read/Write here instead of a lock since I'm opting for better performance versus being 100% accurate about being in the "Blocking Period". If you would prefer the accuracy you can use a lock instead.

Additionally, I do have code that works as an extension method on SqlConnection and can handle multiple connection strings, but its performance is much worse since it uses a ConcurrentDictionary to map connection strings to connection ids.

Answer 3

There shouldn't be a need to check if you're in a blocking period to avoid extending it. As it says in the excerpt above, any attempts to connect during the blocking period will re-throw the first exception, it says nothing about extending the blocking period. However, each new blocking period will be twice as long as the previous.

In my experience, the exceptions that get thrown (due to timeouts, connection leaks, etc.) are either environmental issues or failing to properly close/dispose connections. It's a good idea to log these exceptions so that you can track down the real issue.

If you do keep coming across a timeout exception, you could catch it and try to clear all the pools, but it's likely due to a connection leak. You'll want to make sure you're wrapping your connections with a using statement, which will help to close/dispose of your connections when you're done with them or if an exception occurs.

using(SqlConnection connection = new SqlConnection("connection_string"))
{
    using(SqlCommand command = new SqlCommand())
    {

        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandType = CommandType.Text;
        command.CommandTimeout = [some timeout value]; 
        command.CommandText = "Update SomeTable Set Value = 1";

        connection.Open();

        command.ExecuteNonQuery();
    }
}