Database connections in a multithreaded application

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ncook
Posts: 4
Joined: Sat 16 Nov 2013 04:39

Database connections in a multithreaded application

Post by ncook » Sat 16 Nov 2013 05:56

Sorry in advance for the long question, but I need to make the context for the question very clear, to make sure that I get an accurate answer that I can use to decide if I want to purchase UniDAC for my team.

We are writing a multi-threaded, server style application, and we are in the process of deciding if we should buy UniDAC to replace our current database access components. I have a very specific set of questions that need to be answered to help make that decision, but when searching this forum (and the web in general) I am getting conflicting answers, so I would like to have a definitive answer from the Devart team please.

First some background: Due to the required architecture of the application, it must open a new thread for each new request. The thread life time will be short (one request) and the application will potentially be responding to many requests per second, so performance optimisation is important.

One of the most significant bottle necks in responding to each request is the process of connecting to the database, as this can be quite slow in some situations. So reducing the frequency that the application needs to do that is important. It has been spelled out very clearly, many times in many places, that sharing a single connection object across multiple threads in NOT a good idea, but obviously opening a brand new connection as each different thread is created could be very slow.

* So what is the "RIGHT way" to structure this kind of application when using UniDAC, to overcome this performance problem?

It's been stated many times in various answers on this forum that a separate connection object should be used in each thread, but almost as often in other answers it has been stated that sharing a connection object is ok in some cases, without any clear indication of when it is ok and when it is not. I could not find any answer (or article, or blog post, or example, etc.) that takes the next step to explain how you can safely use a separate connection in each thread, without killing the application performance when threads are being dynamically created and destroyed many times each second.

* Is connection pooling the right way to solve this dilemma?

That may seem like a silly question to some people, but the answer is not obvious to me because:
(a) the connection pooling options seem to be set on the connection object itself, so it is not clear to me that different connection objects, created and destroyed in different threads, would share the same connection pool, and
(b) there have been statements made in the past that this would not be a safe thing to do (e.g. "Please note that it is safer not to use pooling in a multithreaded application." from here http://forums.devart.com/viewtopic.php?t=22415).

* Is the connection pool shared across threads?
* And if it is, then is it safe to use it that way?
* And if the answer to that is "sometimes", then when is it safe, and when is it not safe?

It would be helpful if there were any published articles, or blog posts that explain all this, or examples that demonstrate the right way to structure the database access in this kind of application, but a definitive answer from the Devart team would at least be a start.

AndreyZ

Re: Database connections in a multithreaded application

Post by AndreyZ » Thu 21 Nov 2013 09:46

We have fixed several problems with connection pooling in the past. Now, connection pooling is thread-safe. It means that you can use connection pooling in multi-threaded application. The only restriction is that you must have one connection (TUniConnection) in each thread.
So, there are the answers on your questions:
Is connection pooling the right way to solve this dilemma?
Yes.
Is the connection pool shared across threads?
Yes.
And if it is, then is it safe to use it that way?
Yes.
And if the answer to that is "sometimes", then when is it safe, and when is it not safe?
It is always safe.

ncook
Posts: 4
Joined: Sat 16 Nov 2013 04:39

Re: Database connections in a multithreaded application

Post by ncook » Thu 21 Nov 2013 21:57

Thanks Andrey. That's great.

I was hoping (and expecting) that those were the answers, but it helps to have it clearly articulated, in a definitive manner, from an authoritative source.

We can now move on with confidence that our application design is solid.

AndreyZ

Re: Database connections in a multithreaded application

Post by AndreyZ » Fri 22 Nov 2013 08:51

You are welcome. If any other questions come up, please contact us.

Post Reply