Database connection pooling with multi-threaded service

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();}

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

var items = ; // your collection of work itemsvar 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.

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 ).