C++ – Pattern for Accessing MySQL connection

cdesign-patternsMySQL

We have an application which is C++ trying to access MySQL database.

There are several (about 5 or so) threads in the application (with Boost library for threading) and in each thread has a few objects, each of which is trying to access Database for its' own purpose. It has a simple ORM kind of model but that really is not an important factor here.

There are three potential access patterns i can think of:

  1. There could be single connection object per application or thread and is shared between all (or group). The object needs to be thread safe and there will be contentions but MySQL will not be fired with too many connections.

  2. Every object could initiate connection on its own. The database needs to take care of concurrency (which i think MySQL can) and the design could be much simpler. There could be two possibilities here.
    a. either object keeps a persistent connection for its life OR
    b. object initiate connection as and when needed.

  3. To simplify the contention as in case of 1 and not to create too many sockets as in case of 2, we can have group/set based connections. So there could be there could be more than one connection (say N), each of this connection could be shared connection across M objects.

Naturally, each of the pattern has different resource cost and would work under different constraints and objectives.

What criteria should i use to choose the pattern of this for my own application?

What are some of the advantages and disadvantages of each of these pattern over the other?

Are there any other pattern which is better?

PS: I have been through these questions:
mysql, one connection vs multiple
and
MySQL with mutiple threads and processes
But they don't quite answer exactly what i am trying to ask.

Best Answer

  1. There could be single connection object per application or thread and is shared between all (or group). The object needs to be thread safe and there will be contentions but MySQL will not be fired with too many connections.

This is essentially doing what the server is already doing, on client side. Unless the client side may fire a crazy number of connections and you don't have control on it, better just create connections and let server handles the synchronization.

Your second option does not necessarily incur a lot of connections. Take a look at Dynamic Scoping. It's easy to implement dynamic scoping in C++ so you basic have one connection per thread.

The 3rd option is more or less as opposite to option 1 - if you stick with one connection per thread approach, why not simply restrict the number of threads?

Related Topic