MYSQL + POSIX Threads

[laimaretto]

Hi all,

I've succesfully implemented a MYSQL access using mysql.h. I'm only doing SQL INSERT kind-of queries and they are being inserted nicely in the MYSQL engine.

Latter on I've realized that I needed to implement some threads in my code (actually 3), where each one of those should be inserting records into the MYSQL engine. I've done it this way...

// Global variables
MYSQL *conn;
pthread_mutex_t mysql_mutex=PTHREAD_MUTEX_INITIALIZER;

int main() {
	conn = mysql_init(NULL);
	if (!mysql_real_connect(conn, server.c_str(), user.c_str(),
             password.c_str(), "", 0, NULL, CLIENT_MULTI_STATEMENTS)) {
	} else {
	    // blablabla
	}

        // We start each thread...
	if ( pthread_create( &pt1, NULL, pt1_func, NULL ) ) {
		return -1;
	}

	if ( pthread_create( &pt2, NULL, pt2_func, NULL ) ) {
		return -1;
	}

	if ( pthread_create( &pt3, NULL, pt3_func, NULL ) ) {
		return -1;
	}
}

Now, each of the threads, pt1_func, pt2_func or pt3_func, do something like this ...

int mysql_conn_status;
mysql_conn_status = mysql_query(conn, query_string);

So far so good. Nevertheless, I tried to "stress" my code and realized that, whenever any thread tried to run the mysql_query function at the same time, connection to the DB would be lost.

I then implemented some MUTEX around it, such as this ...

int mysql_conn_status;
pthread_mutex_lock(&mysql_mutex);
mysql_conn_status = mysql_query(conn, query_string);
pthread_mutex_unlock(&mysql_mutex);

... and apparently this solved the issue. My guess is that, since the connection
handle is only one (conn) then this was the issue.

Question is: since there is no "modification" of any variable, why did the MUTEX solved the issue? I mean, the 3 threads are using the same connection handle to the DB (conn), but, as far as I know, they do not "change" it ...

On the other hand, I implemented 3 independent connection handles to the DB (one for each thread), such as this ...

// Global variables
MYSQL *conn_1;
MYSQL *conn_2;
MYSQL *conn_3;
pthread_mutex_t mysql_mutex=PTHREAD_MUTEX_INITIALIZER;

... where each thread would use, later on, something like this ...

        // inside thread_1, but the same being implemented inside
        // threads 2 and 3
	conn_1 = mysql_init(NULL);
	if (!mysql_real_connect(conn_1, server.c_str(), user.c_str(),
             password.c_str(), "", 0, NULL, CLIENT_MULTI_STATEMENTS)) {
	} else {
	    // blablabla
	}

        mysql_conn_status = mysql_query(conn_1, query_string);

... but this didn't work at all. Something curious happened when I tried to use this approach: I could get only one thread to be connected to the DB; the other two wouldn't succeed at it ... any hint here?

So, bottom line: the problem is solved using one connection handle but limiting the simultaneous use of it by implementing proper MUTEX. Any other ideas on how to do it or is this the only one?

Many thanks!!

Best regards,

Lucas

[Elkvis]

mysql really doesn't like it when you try to access the same connection simultaneously with multiple threads. if you want multiple threads talking to the database, create multiple connections. the mutex forced the program to serialize (as opposed to parallel) access to the mysql connection, which effectively makes it behave like a single-threaded application, at least with respect to the mysql connection.

[sean]

Don't think of the problem as being "modifying a variable", think of the problem as being "modifying the state". Your handle represents a TCP connection and a protocol that expects a very specific ordering of messages. If the software isn't written to assuming concurrent access, you can potentially mess up this ordering (or have it mess up what you're trying to do). So that's why a single handle won't work...

As for multiple handles, I would suggest that either you've made an error and what you think are 3 separate handles are actually all the same handle (maybe threads aren't as isolated from each other as you think, maybe there's a pointer error somewhere...), or it's also possible that the client library is programmed to give you an existing connection if there is one - but still doesn't intend it to be used concurrently - that's your job to manage.

edit: I just looked up PHP's documentation for the corresponding function and my memory serves me correctly - that function CAN reuse connections.

[Codeplug]

MySQL :: MySQL 5.6 Reference Manual :: 21.9.17.2 How to Write a Threaded Client

gg