[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
libmysqlclient_r. - Call
mysql_library_init()(once, frommain()). Read the docs about use in multithreaded environments to see why it's necessary. - Make a new
MYSQLstructure usingmysql_init()in each thread. This has the side effect of callingmysql_thread_init()for you.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 (andmysql_library_end()at the end ofmain()). 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
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.