[ACCEPTED]-How to access MySQL from multiple threads concurrently-connection-pooling

Accepted answer
Score: 29

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, from main()). Read the docs about use in multithreaded environments to see why it's necessary.
  • Make a new MYSQL structure using mysql_init() in each thread. This has the side effect of calling mysql_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 (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.

Score: 6

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.)

Score: 1

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