9

I have a .NET 4 C# service that is using the TPL libraries for threading. We recently switched it to also use connection pooling, since one connection was becoming a bottle neck for processing.

Previously, we were using a lock clause to control thread safety on the connection object. As work would back up, the queue would exist as tasks, and many threads (tasks) would be waiting on the lock clause. Now, in most scenarios, threads wait on database IO and work processes MUCH faster.

However, now that I'm using connection pooling, we have a new issue. Once the max number of connections is reached (100 default), if further connections are requested, there is a timeout (see Pooling info). When this happens, an exception is thrown saying "Connection request timed out".

All of my IDisposables are within using statements, and I am properly managing my connections. This scenario happens due to more work being requested than the pool can process (which is expected). I understand why this exception is thrown, and am aware of ways of handling it. A simple retry feels like a hack. I also realize that I can increase the timeout period via the connection string, however that doesn't feel like a solid solution. In the previous design (without pooling), work items would process because of the lock within the application.

What is a good way of handling this scenario to ensure that all work gets processed?

Andy Christianson
  • 361
  • 2
  • 8
  • Can you post some of your code? It would be helpful to see how you are creating and scheduling Tasks. – Phil Klein Jan 06 '12 at 18:36
  • Tasks are created roughly like this: Task.Factory.StartNew(() => ProcessItem(item)); I'm not keeping track of the task objects enough to limit the creation of tasks (if that's the route you were thinking). Not all tasks require database work, and not all database work is using the same database (there are many different, oracle, mssql, etc). – Andy Christianson Jan 06 '12 at 18:39
  • Is this to handle surges in load, or are the requests constantly coming in too fast for you to process? If the latter, you will have to admit defeat and return failure at some point. – Nick Butler Jan 06 '12 at 18:40
  • This is surges in load, yes. However, these same surges were processed with 1 connection in the past, without errors. I don't anticipate the surges to be "too large" that I would have to admit defeat anyway. – Andy Christianson Jan 06 '12 at 18:46

3 Answers3

11

Another approach is to use a semaphore around the code that retrieves connections from the pool (and, hopefully, returns them). A sempahore is like a lock statement, except that it allows a configurable number of requestors at a time, not just one.

Something like this should do:

//Assuming mySemaphore is a semaphore instance, e.g. 
// public static Semaphore mySemaphore = new Semaphore(100,100);
try {
  mySemaphore.WaitOne(); // This will block until a slot is available.
  DosomeDatabaseLogic();
} finally {
  mySemaphore.Release();
}
Chris Shain
  • 50,833
  • 6
  • 93
  • 125
  • I like this, I didn't realize it was (potentially) as easy as that. I will try this and report back. – Andy Christianson Jan 06 '12 at 19:14
  • 1
    There are, of course, some dangers in doing this. Specifically, semaphores are ignorant of re-entrancy, so if your DoSomeDatabaseLogic() call can recursively attempt to re-enter the semaphore, you have the potential for deadlocks. As an example of that, imagine that 100 threads are already using semaphore slots. Then each of the threads attempts to re-enter, and blocks waiting on a free slot (which will never happen). You need to be aware of the possibility and code carefully. – Chris Shain Jan 06 '12 at 19:19
  • The DoSomeDatabaseLogic part that I'm using here is one unit of work, and is currently thread safe, so I don't think that should be a problem. In my test scenario, I've tried this semaphore and it is indeed working. This is what I was looking for, thank you! – Andy Christianson Jan 06 '12 at 19:22
3

You could look to control the degree of parallelism by using the Parallel.ForEach() method as follows:

var items = ; // your collection of work items
var parallelOptions = new ParallelOptions { MaxDegreeOfParallelism = 100 };
Parallel.ForEach(items, parallelOptions, ProcessItem)

In this case I chose to set the degree to 100, but you can choose a value that makes sense for your current connection pool implementation.

This solution of course assumes that you have a collection of work items up front. If, however, you're creating new Tasks through some external mechanism such as incoming web requests the exception is actually a good thing. At that point I would suggest that you make use of concurrent Queue data structure where you can place the work items and pop them off as worker threads become available.

Phil Klein
  • 7,344
  • 3
  • 30
  • 33
  • I'm using Task.Factory.StartNew() to create my tasks. I don't see an overload that accepts ParllelOptions as a parameter. – Andy Christianson Jan 06 '12 at 18:59
  • The approach above assumes an available set of `item` objects to create `ProcessItem(item)` tasks for. This may not be representative of your scenario. The mechanism above uses `Parallel.ForEach()` in place of `Task.Factory.StartNew()`. – Phil Klein Jan 06 '12 at 19:02
  • +1 , I ran into the similar problem that Andy posted, and I am using Parallel.ForEach. This is helpful for me. I didn't know of such option until you mention it now. – Harvey Darvey Oct 18 '13 at 13:32
0

The simplest solution is to increase the connection timeout to the length of time you are willing to block a request before returning failure. There must be some length of time that is "too long".

This effectively uses the connection pool as a work queue with a timeout. It's a lot easier than trying to implement one yourself. You would have to check the connection pool is fair ( FIFO ).

Nick Butler
  • 24,045
  • 4
  • 49
  • 70
  • I do mention I've considered this in the question, however I can't help but feel it's a bad way to handle it. It surely will resolve the problem in the short term, but perhaps the real problem might be that the service is greedy with respect to processing work? I can't help but shake the idea that with 1 connection it worked perfectly, if a little slow. The processing speed improvement is too great to go back to one connection. – Andy Christianson Jan 06 '12 at 19:10