How to access MySQL from multiple threads concurrently

Solution 1:

As maintainer of a fairly large C application that makes MySQL calls from multiple threads, I can say I've had no problems with simply making a new connection in each thread. Some 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 share MYSQL structs or anything created specific to that struct (i.e. MYSQL_STMTs) and it'll work as you expect.

This seems like less work than making a connection pool to me.

Solution 2:

You could create a connection pool. Each thread that needs a connection could request a free one from the pool. If there's no connection available then you either block, or grow the pool by adding a new connection to it.

There's an article here describing the pro's and cons of a connection pool (though it is java based)

Edit: Here's a SO question / answer about connection pools in C

Edit2: Here's a link to a sample Connection Pool for MySQL written in C++. (you should probably ignore the goto statements when you implement your own.)