SQLConnection Pooling - Handling InvalidOperationExceptions

Question

I am designing a Highly Concurrent CCR Application in which it is imperative that I DO NOT Block or Send to sleep a Thread.

I am hitting SQLConnection Pool issues - Specifically getting InvalidOperationExceptions when trying to call SqlConnection.Open

I can potentially retry a hand full of times, but this isn't really solving the problem.

The ideal solution for me would be a method of periodically re-checking the connection for availablity that doesn't require a thread being tied up

Any ideas?

[Update] Here is a related problem/solution posted at another forum

The solution requires a manually managed connection pool. I'd rather have a solution which is more dynamic i.e. kicks in when needed

Answer 1

Harry, I've run into this as well, also whilst using the CCR. My experience was that having completely decoupled my dispatcher threads from blocking on any I/O, I could consume and process work items much faster than the SqlConnection pool could cope with. Once the maximum-pool-limit was hit, I ran into the sort of errors you are seeing.

The simplest solution is to pre-allocate a number of non-pooled asynchronous SqlConnection objects and post them to some central Port<SqlConnection> object. Then whenever you need to execute a command, do so within an iterator with something like this:

public IEnumerator<ITask> Execute(SqlCommand someCmd)
{
    // Assume that 'connPort' has been posted with some open
    // connection objects.
    try
    {
        // Wait for a connection to become available and assign
        // it to the command.
        yield return connPort.Receive(item => someCmd.Connection = item);

        // Wait for the async command to complete.
        var iarPort = new Port<IAsyncResult>();
        var iar = someCmd.BeginExecuteNonQuery(iarPort.Post, null);
        yield return iarPort.Receive();

        // Process the response.
        var rc = someCmd.EndExecuteNonQuery(iar);
        // ...
    }
    finally
    {
        // Put the connection back in the 'connPort' pool
        // when we're done.
        if (someCmd.Connection != null)
            connPort.Post(someCmd.Connection);
    }
}

The nice thing about using the Ccr is that it is trivial to add the following the features to this basic piece of code.

1. Timeout - just make the initial receive (for an available connection), a 'Choice' with a timeout port.
2. Adjust the pool size dynamically. To increase the size of the pool, just post a new open SqlConnection to 'connPort'. To decrease the size of the pool, yield a receive on the connPort, and then close the received connection and throw it away.

Answer 2

Yes, connections are kept open and out of the connection pool. In the above example, the port is the pool.