"Timeout expired" exception on code exclusively using using statements

Question

I have a multi-threaded application that talks to SQL server via Linq to Sql. The app is running fine on a quad core (Intel I-7) machine when the number of threads is artificially kept at 8:

            Parallel.ForEach(allIds, 
                    new ParallelOptions { MaxDegreeOfParallelism = 8 },
                    x => DoTheWork(x));

When the number of threads is left to the system to decide:

                Parallel.ForEach(allIds, x => DoTheWork(x));

After running for a little while, I get the following exception:

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.

There are only two patterns in my app for calling SQL:

first:

    using (var dc = new MyDataContext())
    {
        //do stuff
        dc.SafeSubmitChanges();
    }

second:

        using (var dc = new MyDataContext())
        {
            //do some other stuff
            DoStuff(dc);
        }

.....
    private void DoStuff(DataContext dc)
    {
       //do stuff
       dc.SafeSubmitChanges();
    }

I decided to throttle the calls by this form of logic:

public static class DataContextExtention
{
    public const int SQL_WAIT_PERIOD = 5000;
    public static void SafeSubmitChanges(this DataContext dc)
    {
        try
        {
            dc.SubmitChanges();
        }
        catch (Exception e)
        {
            if (e.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.")
            {
                System.Data.SqlClient.SqlConnection.ClearAllPools();
                System.Threading.Thread.Sleep(SQL_WAIT_PERIOD);
                dc.SafeSubmitChanges();
            }
            else
            {
                throw;
            }
        }
    }
}

This made absolutely no difference. Once the app throws the first exception of this kind, all sorts of random places in the app (even lines of code that have nothing to do with SQL server) start throwing this exception.

Q1: Isn't religiously employing using statement supposed to guard against exactly this scenario?

Q2: What is wrong and how do I fix this?

Note: There are approx 250,000 ids. I also tested at MaxDegreeOfParallelism = 16 and I get the same exception.

Answer 1

I suppose it depends on how many items there are in allIds. If Parallel.ForEach creates too many parallel concurrent tasks, it could be that each one tries to open connection to the database (in parallel) and thus exhausting connection pool and leaving it unable to provide connections to all concurrent tasks that are requesting new connections.

If satisfying the connection pool request takes longer than timeout, that error message would make sense. So when you set MaxDegreeOfParallelism = 8, you have no more than 8 concurrent tasks, and thus no more than 8 connections "checked out" from the pool. Before the task completes (and Parallel.ForEach now has an available slot to run new task) the connection is returned back to the pool, so that when Parallel.ForEach runs the next item, connection pool can satisfy the next request for the connection, and thus you don't experience the issue when you artificially limit concurrency.

EDIT 1

@hatched's suggestion above is on the right track - increase the pool size. However, there is a caveat. Your bottleneck likely isn't really in computing power, but in database activity. What I suspect (speculation, admittedly) is happening is that while talking to the database, the thread can't do much and goes blocked (or switches to another task). So thread pool sees that there are more tasks pending, but CPU is not utilized (because of outstanding IO operations), and thus decides to take on more tasks for the available CPU slack. This of course just saturates the bottleneck even more and back to square one. So even if you increase the connection pool size, you're likely to keep running into the wall until your pool size is as big as your task list. As such, you may actually want to have bounded parallelism such that it never exhausts thread pool (and fine tune by making thread pool larger / smaller depending on DB load, etc.).

One way to try to find out if the above is true is to see why connections are taking so long and not getting returned to the pool. I.e. analyze to see if there is db contention that is slowing all connections down. If so, more parallelization won't do you any good (in-fact, that would be making things worse).

Answer 2

I was thinking the following might help, in my experience with Oracle the DB Connection Pool has caused me issues before. So I thought there may be similar issue with SQL Server connection pool. Sometimes knowing the default connection settings and seeing connection activity on the DB is good information.

If you are using Sql Server 8 the default SQL Connection Pool is 100. The default Timeout is 15 seconds. I would want to have the SQL Admin track how many connections your making while running the app and see if your putting load on the DB Server. Maybe add some performance counters as well. Since this looks like a SQL Server exception I would gets some metrics to see what is happening. You could also use intellitrace to help see DB Activity.

Intellitrace Link: http://www.dotnetcurry.com/showarticle.aspx?ID=943

Sql Server 2008 Connection Pool Link: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

Performance Counters Link: http://msdn.microsoft.com/en-us/library/ms254503(v=vs.110).aspx