Avoid connection timeout when using multiple threads and connection pool

Question

I'm splitting up a readonly database operation into multiple chunks, (Each of which reads a subset of a very large amount of data, analyzes it and writes the results to a disk file).

Each chunk performs a Select (into a datatable) on a new .net thread (using a delegate and BeginInvoke)

There are more subsets of the data than there are available connections in the pool, so when I run out of connections, before the first one is released, subsequent connection requests are queued up... until the connection timeout expires, and then I get a timeout exception.

How do I either, A) inhibit the timeout connection exception when the connections in the pool are all in use, or B) detect that they are all in use before I even ask for another one so I can wait until one is available before asking?

Answer 1

Two solutions:

A) Configure your connection pool with a timeout of several days. That will block the pending tasks until a connection is returned. Drawback: This won't work when a task hangs.

B) Use a thread pool and a work queue. The thread pool must have the same size as the connection pool (i.e. one connection per thread). Put all the work in the queue and have the tasks fetch work items from the queue until the queue is empty.

Pseudocode for solution B:

public class Setup
    connPool = createConnectionPool(100);
    queue = createWorkQueue();
    putAllWorkItemsInQueue(queue);
    for (int i=0; i<connPool.size(); i++) {
        t = new WorkerThread(queue)
        list.add(t);
        t.start();
    }
    while (queue.size() != 0) {
        Thread.sleep(1000);
    }
    for (thread in list) {
        thread.interrupt();
    }

public class WorkerThread
    run() {
        while (true) {
            try {
                workUnit = queue.get(); // This blocks
                process(workUnit);
            } catch (InterruptedException e) {
                break;
            }
        }
    }