Pattern for Accessing MySQL connection
- by Dipan Mehta
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:
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.
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.
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.
