MYSQL access + 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 thread pt[1,2,3]_func then does 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 implement 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 tried to implement 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 such as 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