Take the 2-minute tour ×

I'm building a multi-threaded service application in Delphi XE2. Each thread serves its own purpose apart from the other ones. The main service thread is only responsible for keeping the other threads going and saving a log file, etc. Each of these threads reports back to the main service thread through synchronized event triggers. These threads are created when the service starts and destroyed when the service ends.

I'd like to introduce a separate thread as a centralized database connection to avoid having to create many instances of TADOConnection. My service code can call standard functions such as UserListDataSet := DBThread.GetUserList(SomeUserListDataSet); or it would also be nice if I could send direct SQL statements like SomeDataSet := DBThread.Get(MySqlText);. I'd also like to avoid too many occasions of CoInitialize() etc.

The job threads will need to use this db thread. I need to figure out how to "ask" it for certain data, "wait" for a response, and "acquire" that response back in the thread which requested it. I'm sure there are many approaches to this, but I need to know which one is best suited for my scenario. Windows messages? Events? Should I have some sort of queue? Should it send data sets or something else? Is there already something that can do this? I need to figure out how to structure this DB thread in a way that it can be re-used from other threads.

The structure looks like this:

+ SvcThread
  + DBThread
    + TADOConnection
  + Thread1
  + Thread2
  + Thread3

I need threads 1 2 and 3 to send requests to the DBThread. When a thread sends any request to it, it needs to wait until it gets a response. Once there's a response, the DB Thread needs to notify the asking thread. Each of the threads might send a request to this DB Thread at the same time too.

A good tutorial on how to accomplish this would be perfect - it just needs to be a suitable fit for my scenario. I don't need to know just "how to make two threads talk together" but rather "how to make many threads talk to a centralized database thread". These job threads are created as children of the main service thread, and are not owned by the db thread. The db thread has no knowledge of the job threads.

share|improve this question
Essentially, since I cannot use ADO components across threads, I'm looking for a way to wrap all DB calls into its own thread. It would also be great if I could send any SQL statement into this thread and get some data set back out of it which can be pulled into another thread. –  Jerry Dodge Feb 13 '12 at 23:43
I've revised the question to ask how to centralize the database connection(s) for multiple threads to use. –  Jerry Dodge Feb 14 '12 at 2:18
Based on the answers and comments, I think I might actually build a database request thread pool of my own... The system it's designed for is actually really large and the database is used by at least 12+ applications. –  Jerry Dodge Feb 14 '12 at 2:23
Speaking of which, that calls for a more modular based system where the EXE's use DLL's which wrap common functionality in the overall system. This is a separate matter, but this question has a lot to do with making a large system work on a modular basis. –  Jerry Dodge Feb 14 '12 at 2:27
I have an entire development framework that handles this "straight out of the box". Each thread is actually a threaded message queue and all inter-thread communication is message-based (no synchronisation methods). Request/response patterns are implemented in the framework by the the calling thread blocking on a return callback, although from the application developer's perspective it just looks like a simple method call. –  Misha Feb 14 '12 at 10:37
add comment

3 Answers

up vote 6 down vote accepted

Normally, you'd have a request queue where all the requests are stored. Your database thread reads a request from the queue, handles it, then invokes a callback routine specified by the requester to handle the result. Not sure how this maps to Delphi paradigms, but the basics should be the same.

share|improve this answer
Should this queue be kept in the main service thread or inside the database thread? –  Jerry Dodge Feb 13 '12 at 23:46
I mean, I know it should go inside the DB thread, but any advantages of it being outside? –  Jerry Dodge Feb 13 '12 at 23:48
I'd put it in the main service thread, so if your database thread dies with an error, you still have the queue for post-mortem analysis. –  TMN Feb 13 '12 at 23:49
@TMN - +1, it maps almost directly to Delphi paradigms. The request/result class would have an 'OnCompleted' property of type 'TNotifyEvent' - this is essentially a method callback. It takes one 'sender' parameter of type TObject - this parameter would normally be the request/result instance. I agree about the queue - the queue should belong to the service thread. If nothing else, this makes it easy to create multiple DB threads with multiple connections, should that be necessary/desirable. –  Martin James Feb 14 '12 at 0:18
add comment

Do any of the "requesting" threads have anything profitable that they could be doing while they are waiting for a response to be obtained from the database? If the answer is "no," as I suspect that it is quite likely to be, then perhaps you can simplify your situation quite a bit by eliminating the need for "a DB thread" completely. Perhaps all of the threads can simply share a single database-connection in turn, employing a mutual-exclusion object to cause them to "wait their turn."

Under this scenario, there would be one database-connection, and any thread which needed to use it would do so. But they would be obliged to obtain a mutex object first, hold on to the mutex during the time they were doing database queries, and then release the mutex so that the next thread could have its turn.

If you decide that it is somehow advantageous (or a necessity...) to dedicate a thread to managing the connection, then perhaps you could achieve the result using (a) a mutex to serialize the requests, as before; and (b) one event-object to signal the DB-thread that a new request has been posted, and (c) another event-object to signal the requester that the request has been completed.

In either case, if you have indeed determined that the requester threads have nothing useful that they could be doing in the meantime, you have the threads "simply sleeping" until their turn comes up. Then, they do their business, either directly or indirectly. There are no "queues," no complicated shared data-structures, simply because you have (say...) determined that there is no need for them.

share|improve this answer
Good point, and the mutex idea is still new to me and I have to give it a try. I'm just worried that 10 threads creating 10 database connections would be overkill with "talking" to the SQL server. My overall goal is to eliminate the need to create multiple TADOConnection objects, but as you point out, it's actually already an advantage of having separate ones, and practically defeats the purpose of multi-threading if they all need to wait their turn. –  Jerry Dodge Feb 13 '12 at 23:56
Now on the other hand, I could make this "DB Thread" spawn a new dynamic thread for every request, but that's also getting way too complex and I don't see a reason to take it that far. –  Jerry Dodge Feb 14 '12 at 0:08
'I could make this "DB Thread" spawn a new dynamic thread for every request' - don't do that. 'I don't see a reason to take it that far' - try hard not to find one! –  Martin James Feb 14 '12 at 0:22
@MartinJames Nowhere close to thinking about going that route, just an idea that popped. I'd only do that if I was building some big library which was meant to do that, which I'm not, so I won't. –  Jerry Dodge Feb 14 '12 at 0:53
add comment

I think using a DB connection pool would be a better fit for your problem. This would also allow you to scale your application later on without having to then create additional DB thread and then having to manage "load balancing" for those DB threads.

Since you are mentioning using TADOConnection please have a look at this implementation made by Cary Jensen http://cc.embarcadero.com/item/19975. I am successfully using this DB connection pool in several applications. I have modified it in several ways, including using an ini file to control: maximum number of connections, cleanup time, timeout times etc.

Cary has written several articles that serves as documentation for it. One is here http://edn.embarcadero.com/article/30027.

share|improve this answer
This looks like a great solution, and it's definitely worth looking into it. Thanks. –  Jerry Dodge Feb 14 '12 at 15:20
add comment

Your Answer


