CODEWITHSUNDEEP
c++mysqlcmultithreadingconnection-pooling
Isak Savo
Isak Savo

Reputation: 35894

How to access MySQL from multiple threads concurrently

We're doing a small benchmark of MySQL where we want to see how it performs for our data.

Part of that test is to see how it works when multiple concurrent threads hammers the server with various queries.

The MySQL documentation (5.0) isn't really clear about multi threaded clients. I should point out that I do link against the thread safe library (libmysqlclient_r.so)

I'm using prepared statements and do both read (SELECT) and write (UPDATE, INSERT, DELETE).

Upvotes: 23

Views: 25983

Answers (4)

chazomaticus
chazomaticus

Reputation: 15766

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.

Upvotes: 29

Elemental
Elemental

Reputation: 7521

Seems clear to me from the mySQL Docs that any specific MYSQL structure can be used in a thread without difficulty - using the same MYSQL structure in different threads simultaneously is clearly going to give you extremely unpredictable results as state is stored within the MYSQL connection.

Thus either create a connection per thread or used a pool of connections as suggested above and protect access to that pool (i.e. reserving or releasing a connection) using some kind of Mutex.

Upvotes: 1

Glen
Glen

Reputation: 22290

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

Upvotes: 6

Tiemen
Tiemen

Reputation: 509

MySQL Threaded Clients in C

It states that mysql_real_connect() is not thread safe by default. The client library needs to be compiled for threaded access.

Upvotes: -1

Related Questions