[ACCEPTED]-How to access MySQL from multiple threads concurrently-connection-pooling
As maintainer of a fairly large C application 8 that makes MySQL calls from multiple threads, I 7 can say I've had no problems with simply 6 making a new connection in each thread. Some 5 caveats that I've come across:
- Edit: it seems this bullet only applies to versions < 5.5; see this page for your appropriate version: Like you say you're already doing, link against
main()). Read the docs about use in multithreaded environments to see why it's necessary.
- Make a new
mysql_init()in each thread. This has the side effect of calling
mysql_real_connect()as usual inside each thread, with its thread-specific MYSQL struct.
- If you're creating/destroying lots of threads, you'll want to use
mysql_thread_end()at the end of each thread (and
mysql_library_end()at the end of
main()). It's good practice anyway.
Basically, don't 4 share
MYSQL structs or anything created specific 3 to that struct (i.e.
MYSQL_STMTs) and it'll work as 2 you expect.
This seems like less work than 1 making a connection pool to me.
You could create a connection pool. Each 11 thread that needs a connection could request 10 a free one from the pool. If there's no 9 connection available then you either block, or 8 grow the pool by adding a new connection 7 to it.
There's an article here describing the 6 pro's and cons of a connection pool (though 5 it is java based)
Edit: Here's a SO question 4 / answer about connection pools in C
Edit2: Here's a link to a 3 sample Connection Pool for MySQL written in C++. (you should probably 2 ignore the goto statements when you implement 1 your own.)
Seems clear to me from the mySQL Docs that 10 any specific MYSQL structure can be used 9 in a thread without difficulty - using the 8 same MYSQL structure in different threads simultaneously 7 is clearly going to give you extremely unpredictable 6 results as state is stored within the MYSQL 5 connection.
Thus either create a connection 4 per thread or used a pool of connections 3 as suggested above and protect access to 2 that pool (i.e. reserving or releasing a 1 connection) using some kind of Mutex.
More Related questions