.NET Junkie

The Basics

Over a dozen articles and a thousand blogs must have been written about this topic; ‘You should make sure you always close your connections when you're done with them’. (This holds for the entire family of ADO.NET connections such as Odbc, OleDb, Oracle, Sql and SqlCe). You should either call Close or Dispose inside a finally block or wrap your connection inside a using block (in which case Dispose is called for you). Neglecting to close a connection, will lead to a connection “leak”. In that case the connection will only be closed and returned to the connection pool after the garbage collector (GC) has kicked in and cleaned up the mess (more information about the connection pool can be found here).

When the GC runs once in a while to free memory (and by doing that unknowingly returning open connections to their pool), everything goes smoothly and your application runs fine. But when there's enough memory available and the GC decides to sit back for a while, things might go wrong all of a sudden. Your users might experience an InvalidOperationException with the following message:

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.

Here is a bad code fragment, which could lead to that exception:

public static void ExecuteSomeQuery()
{
    SqlConnection connection = BLHelper.CreateConnection();
    
    SqlCommand command = new SqlCommand("QUERY", connection);
    
    conection.Open();
    command.ExecuteNonQuery();
    conection.Close();
}

Your first impression might be that this code is correct, because we’ve closed the connection. But we're simply not sure if the connection will be closed every time. The ExecuteNonQuery method might toss an exception, which will result in skipping the next statement which is the call to Close. Remember that Murphy's law definitely applies to software development. It’s really amazing to see how the internet is flooded with bad code examples [1,2,3,4,5] like the previous fragment. Here is the correct way to do this:

public static void ExecuteSomeQuery()
{
    // SqlConnection implements IDisposable
    SqlConnection connection = BLHelper.CreateConnection();
    try
    {
        // SqlCommand implements IDisposable
        SqlCommand command = new SqlCommand("QUERY", connection);
        try
        {
            connection.Open();
    
            command.ExecuteNonQuery();
        }
        finally
        {
            command.Dispose();
        }
    }
    finally
    {
        connection.Dispose();
    }
} 

Or when you can, write the using statement:

public static void ExecuteSomeQuery()
{
    using (SqlConnection connection = 
        BLHelper.CreateConnection())
    {
        using (SqlCommand command = 
            new SqlCommand("QUERY", connection))
        {
            conection.Open();
            command.ExecuteNonQuery();
        }
    }
    // No need for calling Dispose anymore
}

Calling Close on a connection is enough to prevent the connection from leaking. Calling Dispose has currently no real advantages over calling Close. However keep in mind that we can’t be sure if this still holds in the next version of the .NET framework. So the only thing we can do is following the guidelines. They note to ‘always call Dispose on objects that implement IDisposable’. So take that advice and always call Dispose.

Handling third-party code

These are the basics I think most of you already knew. We should write good code. I hope everybody agrees with me. But what about calling code we don’t maintain? For instance when a third-party library throws the max-pool-size-was-reached exception? Let me start by saying that code behaving like this is simply wrong. You should track the person or organization that built that library and force them to fix that bug.

It could however, take time for the other party to repair that error, while your customers are having problems that you need to fix right away. Or maybe your company needs to pay for the problem to get solved. So for those situations I propose a workaround that may temporarily fix this issue. Note however that this workaround will NOT be a durable solution!

Like I described earlier, this exception appears because connections are not closed and are therefore not returned to the pool. To resolve this issue we need to close those connections. But because we have no reference to those objects, there’s only one possible thing to do; force disposal of those objects by asking the GC to collect them. After that we can re-execute the third-party code. That code will than be able to get a connection from the pool. See the following code snippet:

public static void ExecuteSomeQuery()
{
    try
    {
        3rdParty.LegacyLib.ExecuteSomeQuery();
        
        // The internal ADO.NET connection pool
        // will wait 15 seconds before throwing
        // the max-pool-size exception!
    }
    catch (InvalidOperationException e)
    {
        if (!e.Message.Contains("max pool size"))
            throw;
        
        // The connection pool is empty, so
        // let’s collect (dispose) all unreach-
        // able objects from the heap.
        System.GC.Collect();

        3rdParty.LegacyLib.ExecuteSomeQuery();
    }
}

A BIG WARNING HERE: You cannot simply re-execute every single method that way. You must analyze the methods you need to handle, using a tool like Lutz Roeder's .NET Reflector, to make sure that re-executing a method is completely safe. This because it is possible that multiple connections are opened during a single method call. Calling that method a second time could lead to corruption of the database, because you may trigger an insert or update statement again (note that lack of transactional use within such library isn’t the issue here). Therefore you can't be sure if it's safe to call such method twice until you’ve examined the source of that decompiled library.

A SECOND WARNING: You normally should never call System.GC.Collect(). Explicitly calling GC.Collect() changes the GC's autotuning capabilities. Repeatedly calling GC.Collect() could greatly impact performance.

I hope I made my point pretty clear. This code does not solve your problems, it's merely a patch and if not used with caution and thorough investigation it will make things even worse!

Also be aware that your application by default halts for 15 seconds before ADO.NET decides to throw that exception!!! This means that once in a while a user will still experience a delay in your web or desktop application, before you're able to handle that exception.

A wrapper class

Now I want to take the given code fragment to the next level by wrapping it in a class. I’ll use delegates and .NET 2.0 generics and will also try to prevent the described 15 second delay to emerge. Here is the PoolTimeoutWatcher class:

public static class PoolTimeoutWatcher
{
    // We define two delegates, that may point to
    // unsupported code
    public delegate void Executer();
    public delegate T Executer<T>();

    // This Method executes the 'execute' method
    // delegate and returns void
    public static void Execute(Executer execute)
    {
        try
        {
            Collect(CollectType.CollectWhenNeeded);
            execute();
        }
        catch (InvalidOperationException e)
        {
            if (!e.Message.Contains("max pool size"))
                throw;

            Collect(CollectType.ForceCollection);
            execute();
        }
    }

    // This Method executes the generic 'execute'
    // method delegate and returns an object of
    // the given type T.
    public static T Execute<T>(Executer<T> execute)
    {
        try
        {
            Collect(false);
            return execute();
        }
        catch (InvalidOperationException e)
        {
            if (!e.Message.Contains("max pool size"))
                throw;

            Collect(true);
            return execute();
        }
    }

    // We assume all pools have the default max
    // pool size of 100 connections.
    private const int POOLSIZE = 100;
    private static int count;
    private static object locker =
        new object();

    // This method calls the Garbage Collector
    // after every 90 calls, or immediately when
    // the force argument is set to true.
    private static void Collect(CollectType type)
    {
        bool mustCollect = false;
        lock (locker)
        {
            count++;
            if (type == CollectType.ForceCollection || 
                count >= POOLSIZE * 0.9)
            {
                count = 0;
                mustCollect = true;
            }
        }
        if (mustCollect)
            System.GC.Collect();
    }

    enum CollectType { CollectWhenNeeded, ForceCollection }
}

And we can use the class as follows:

class Program
{
    static void Main(string[] args)
    {
        // The ExecuteSomeQuery method matches the
        // PoolTimeoutWatcher.Executer delegate, so
        // we can use it directly as argument.
        PoolTimeoutWatcher.Execute(
            3rdParty.LegacyLib.ExecuteSomeQuery
        );
        
        // Here we create an anonymous method that
        // returns a DataTable and matches the
        // Executer<DataTable>'s signature.
        DataTable dataTable =
            PoolTimeoutWatcher.Execute<DataTable>(
                delegate {
                    return 3rdParty.LegacyLib.
                        ExecuteDataTable();
                }
            );
    }
}

The private PoolTimeoutWatcher.Collect method counts the number of executes to predict when the pool might flood (assuming a single pool) and it calls the GC before that happens. However the forecast can be wrong, because there could be other methods leaking connections. The maximum pool size could also be configured differently than we’d expected. Therefore I advise to also write every caught exception to a log file or the event trace.

I hope I didn't give anyone any bad ideas ;-). Good luck!

The code samples on my weblog are colorized using javascript, but you disabled javascript (for my website) on your browser. If you're interested in viewing the posted code snippets in color, please enable javascript.