As I believe I have already opined, dealing with databases is not one of my all time favourite activities. It’s not right up there with drinking, skiing and World of Warcraft, for example. For someone in my position, dealing with databases is naturally an occupational hazard; but I do attempt to minimise the amount of time I spend doing it.

Obscure errors connecting to databases can suck up a lot of time. There are so many things can can go wrong. Fortunately, .NET makes this rather easy to handle in many cases (catch SqlException, do something sensible), but there are occasions when this all falls down.

One exception in particular has been something of a major niggle over the last few weeks, as from time to time my colleague James Moore and  I tried to establish why the application we’re working on was suffering from it:

InvalidOperationException

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.

It looks so straightforward. Googling suggests that the error message is telling the truth: this error occurs when I, niave fool that I am, open dastardly numbers of pooled connections to a server in a cavalier fashion, cackling as I disregard all sensible cleanup procedures, spilling objects out all over the place and generally jumping up and down on System.Data and grinning.

Unfortunately, this is not the case. The error message is entirely misleading. You can get this exception when not doing anything dangerous or complicated, or opening hoards of connections. In fact, as we discovered when trying to reproduce the problem outside of our application, you can get this exception when hardly doing anything at all.

Here’s some sample .NET 2 code. To achieve the exception, simply stop your SQL Server (2000 or 2005, dealer’s choice), and run this code to attempt to connect to it.

using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;

namespace CrashTest
{
    static class Program
    {
        [STAThread]
        static void Main()
        {
            ThreadPool.QueueUserWorkItem(new WaitCallback(GetData));
            GetData(null);
        }
   
        static void GetData(object state)
        {
            using (SqlConnection connection = new SqlConnection(“Data Source=DAN”))
            {
                connection.Open();
            }
        }
    }
}

As you can see, we simply attempt to open two simultaneous connections to a non-existent server. Not a hundred, or any other arbitrary limit, but two. 100% of the time that I run this, I get the InvalidOperationException.

Having flicked through the underlying System.Data code with Reflector, the best theory we have come up with is that there’s some kind of race condition in System.Data which is causing the problem. If a connection cannot be obtained from the pool for more or less any reason, System.Data throws this particular exception. It’s not necessarily limited to timeouts of any variety; hence the juicily choice, “this may have occurred because…” in the exception message. An excellent get-out clause for providing exceedingly unhelpful information, in my view.

To preserve our sanity, we haven’t drilled down further into exactly what the offending code is doing that causes this issue. It turns out we don’t need to. All we need to do is ensure that our application never attempts to open two connections to the same server at the same time. We may do something clever, but certainly one easy way to do this is to replace the line:

                connection.Open();

with a simple monitor lock:
            
                lock (m_Lock)
                {  
                     connection.Open();
                }

on an appropriate object, which for the purposes of this example we can just declare elsewhere in the class:

          private static readonly object m_Lock = new object();

And bingo, checkmate, ding, and other gameplay-oriented culminatory expostulations; Bob is a close relative.