Board index » delphi » TechTips: Handling database connections for multiple threads

TechTips: Handling database connections for multiple threads

Although it's really not the best way to handle multiple requests, many
programs nonetheless /do/ take the "flaming arrow approach" of creating a
new thread for every request, and shooting it up in the air to fend for

Where this wasteful strategy really comes home to roost is with database
connections.  The web-site goes online, then goes down in flames just as it
begins to be popular.  Let's say that the app "is already written this way
and we can't change it."  What can we do?

Try adjusting the application to introduce the notion of a pool of database
connections -- which are created (perhaps by the main-thread) when the app
is started.  The connection pool is controlled by a counting semaphore, and
there are only as many connections out there as the database system can
actually handle.  If more than 'n' threads attempt to do database work at
the same time, some of them are obliged to wait.

You can sometimes further refine this technique, if the scope of work that
the threads may be asked to do is predictable.  You can leave certain
queries open all the time and allow them to be re-used.  (A thread can also
be dedicated to closing queries that have been open-and-idle too long.)

Each thread must be carefully designed, e.g. with try..finally clauses, so
that it always cleans up.  

Fast automatic table repair at a click of a mouse!


Re:TechTips: Handling database connections for multiple threads

My... it doesn't take long to get a response!  "Why," they ask, "should I
intentionally limit my threads in this way, by putting a limit on the
number of connections... making them wait in line?  Isn't that

No...  it's not inefficient.  My point is that your applications are
/already/ exhibiting this fundamental limit (on how many requests they can
actually perform simultaneously), but they are crashing-and-burning because
they do not explicitly acknowledge that this limit exists or that they are,
in fact, bound by it.

It is infinitely better for a server to understand how much work it can do,
and to avoid overloading itself by means of a practical governor-mechanism,
than to overload itself and, in so doing, fail utterly.

Furthermore, all systems exhibit an "elbow in the curve" performance-wise.  
They degrade linearally up to a point, then they "hit the wall" beyond that
point.  (The performance-graphs takes a right-angle turn straight into the
floor, the so-called elbow.)  If the system voluntarily refuses to attempt
to do all that work "all at once," thus staying well back from the elbow,
it can handle much higher workloads .. dependably(!) .. while each request
experiences only a slight delay.

Re:TechTips: Handling database connections for multiple threads

Registered User wrote:
> A multi-threaded server with db connection pool is reasonable but can
> be very complicated. Why not use a pool of single-threaded servers
> instead? Any db contention will still need to be resolved but each
> server object and its members will obviously be thread-safe. ISAPI
> dlls etc. are multi-threaded by nature, let those objects handle as
> many multi-threading details as possible.

I entirely agree.  Servers /should/ be designed with a pool of workers that
pluck a request off a queue, service it, and go back to sleep waiting for
another request to arrive on the queue.  ("Units of work" and "the threads
which service them" are distinct.)  

But my starting-point in this scenario was that the application /wasn't/
designed this way initially, and so it's been deployed and now it's failing
in the field, and "what can we do about it now?"

> In between the web-server and the server pool is a server-server. When
> the server-server is created it creates a pool of servers. Each new
> thread enters a server-server critical section to get a server from
> the pool. After the thread exits the critical section it has its own
> server. Once it  is done with the server, re-enters the critical
> section and the server is returned to the pool. If no servers are
> available, the server-server returns a 502 error. The code subject to
> multiple threads becomes very, very thin.

Well, yes, a critical-section would be used to control the resource-pool
table, but before you're allowed to grab that critical-section you must
first obtain a semaphore.  If there are 10 servers in the pool, the
semaphore is initialized to the value 10.  As soon as the 11th request
tries to come in, it's blocked in a fair, FIFO queue waiting for the chance
to acquire a pool entry.  Once it has the semaphore, it should never then
find that there are no pool-entries to be had.

This imposes "fairness" that a simple critical-section alone would not
provide.  It also provides for timed waiting.  If after 'n' milliseconds
the requestor has not succeeded in obtaining the semaphore, the request
might be politely declined.

>>Each thread must be carefully designed, e.g. with try..finally clauses, so
>>that it always cleans up.

> Now it essentially becomes a matter of writing a single threaded db
> app in a manner that allows multiple instances to function without
> problem.

> Either way the design is always important.


Fast automatic table repair at a click of a mouse!

Other Threads