Creating a thread-safe library to access a database

[Luc4]

Hi! I need to create a library that should provide API to get content from a database. I would like to create this library thread-safe, because it is possible that the users will try to invoke the methods from within many different threads.

To do this, I created a singleton class that accesses the database. That class have methods get*, set* etc... to respectively execute select, update etc... queries. To guarantee thread-safety, in each method I create the connection to the database, I set the connection options, I open the database etc... and, at the end of the method I close the connection to the database. This because the connection can only be used in the same thread that created it.

My question is: is this the correct way or the best way of doing it?

Thanks!

[Lesiok]

Creating and opening connection to DB is time consuming.

[squidge]

I would have a pool of DB connections instead and just use whichever is free at the time.

[Luc4]

I understand. This is very interesting! And how would you do this? I mean, if creating the connection is time consuming, when should I create each connection to add it to the pool?

[SixDegrees]

The database itself ought to provide substantial locking mechanisms, even when barraged by multiple simultaneous queries. There's no point, in terms of the database, in duplicating this capability on the client side. In fact, it will probably wind up hurting performance, since many queries can safely be handled in parallel otherwise, and bottlenecking them into a serial arrangement throws this capability away.

Your DB documentation almost certainly has a section discussing such things.

Squidge's suggestion is much better. By using a client-side thread pool, you can keep the number of threads constant and prevent saturating the local environment, while still allowing multiple requests simultaneously.

Although I still don't see why simply allowing each thread to make its own independent connections and queries would cause problems.