On ADO.NET Connection Pooling and SQL Server Error 10054

So our internal order processing application at work securely connects to our SQL Server database via an ADO.NET connection. And if you leave the application up and running for a long time period–say, an entire weekend–you sometimes come to work on Monday morning to see an error message like the following:

Something Didn't Work

A transport-level error has occurred while sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)

(Think that’s a sexy error dialog? It’s from the SQL Server 2005 SDK. Just Google it; MS lets you use it in your own applications. Pretty cool for displaying an exception and all of the inner exceptions that it wraps.)

If you dismiss the dialog and retry the command that failed, it works fine. What’s up?

The ADO.NET connection pool is great…

Well, we’re using the connection pooling feature of ADO.NET. Usually, connection pooling is a behind-the-scenes feature that makes our database connection code run faster. Instead of setting up and tearing down the connection to the database server with each call to Open() and Close() on a SqlConnection instance, ADO.NET is instead acquiring and releasing connections to and from a pool of connections. If, while executing some command against a connection and a fault occurs, ADO.NET will automatically remove the faulting connection from the connection pool and spin up a new connection. Sounds nifty, right?

…until the network farts

Well, sometimes the connections fault in a way that ADO.NET isn’t aware of until the next time you try to use that connection. In my case, the ADO.NET connection pool is happily diddling along when, for example, our office’s craptastic Comcast SMC unholy-marriage-of-router-and-modem device hiccups and reboots itself, severing any sockets that were once open.

My code then tries to do something innocuous like call Open() on a SqlConnection, execute a command, and then Close() the connection, as it has done a million times before. ADO.NET pulls a connection from the connection pool, notes that the socket is broken, clears out the connection pool, creates a new connection, and executes my command successfully, and I and my users are none the wiser of the unimportant detail that this connection pool repair job just happened.

Ha ha, no, that’s not what happens; that would make sense. What actually happens is that ADO.NET does all of those things but not before throwing a SqlException as described above instead of executing my command. Why I would care about this error, I’m not sure–my code isn’t even aware that the connection pool exists, its calling of Open() and Close() is quite explicit in terms of the behavior that it is expecting, and ADO.NET throwing error 10054 is, in my opinion, leaking implementation details that should be hidden beneath the SqlConnection abstraction. But this is what we’ve got.

Extension methods are great

When C# 3.0 introduced extension methods, I really thought they were a silly feature that would lead to developer confusion. I was instantly reminded of my PHP days where everyone in the world had devised their own unique set of HTML escape functions, and moving to any new Web site involved learning a whole new suite of strange extensions or redefinitions of built-in functions. But, in .NET, I’ve found myself using extension functions more and more often, especially since their usage is neatly held in check through the use of namespaces. It is through some extension methods that I work around the above error in my code.

Essentially, instead of calling command.ExecuteReader() or command.ExecuteScalar() on my SqlCommand instances, I call command.SafeExecuteReader() or command.SafeExecuteScalar() instead, with the SafeExecute* functions simply being extension methods that I defined as similar to the following, which I lifted with some modifications off some developer’s forum somewhere some time ago:

namespace Skiviez.Porcupine.Model
{
    using System.Data;
    using System.Data.SqlClient;
 
    /// <summary>
    /// Contains extensions to the SqlCommand class.
    /// </summary>
    internal static class SqlCommandExtensions
    {
        /// <summary>
        /// The maximum failed attempts that can occur before a standard
        /// SqlException is thrown.
        /// </summary>
        private const int MaxFailedAttempts = 3;
 
        /// <summary>
        /// Obtains a reader safely by automatically retrying the command a
        /// number of times in case an invalid connection in the connection pool
        /// is encountered.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="commandBehavior">The command behavior.</param>
        /// <returns>the data reader</returns>
        /// <exception cref="T:System.Data.SqlClient.SqlException">if a non-
        /// recoverable error is encountered</exception>
        internal static SqlDataReader SafeExecuteReader(
            this SqlCommand command, 
            CommandBehavior commandBehavior)
        {
            SqlDataReader reader;
            bool retry;
            int failedAttemptsCount;
 
            reader = null;
            retry = false;
            failedAttemptsCount = 0;
 
            do
            {
                try
                {
                    reader = command.ExecuteReader(commandBehavior);
                }
                catch (SqlException e)
                {
                    if (e.ErrorCode == 10054)
                    {
                        // Transport error occurred, meaning the connection pool
                        // had a bad entry. We'll probably be okay if we retry
                        // one more time.
                        retry = true;
                        failedAttemptsCount++;
                    }
                    else
                    {
                        throw;
                    }
                }
            } 
            while (retry && failedAttemptsCount <= MaxFailedAttempts);
 
            return reader;
        }
}

Essentially, if executing the command results in a SqlException, I check to see if it is this stupid 10054 error. If it is, I’ll retry execution of the command up to 3 times, even though a single retry almost always fixes the problem. If it still doesn’t work after that–or if the error wasn’t 10054 to begin with–I assume that something really is hosed and throw the exception anyway.

Conclusions and delusions

Thanks to an extension method, it’s pretty easy to safeguard my users against an error that is not really an error without resorting to lots of boilerplate code around each ExecuteReader() call. It’s one more step of making sure that my software simply works (most of the time!).