List:General Discussion« Previous MessageNext Message »
From:buford Date:June 7 2009 10:41am
Subject:Thread safe queries with multiple connections
View as plain text  
I think my question is whether the data in MYSQL_RES and MYSQL_ROW data
structures are sufficiently independent from that of other instances of
those data structures in a multi-threaded situation when a pool of 
connections are share. The application my have multiple connections
structures defined in a pool, but each individual connection will be used
exclusively from the point of running the query until after
mysql_store_result is called. After that the connection is pushed back
into the pool for re-use.

That is, since mysql_store_result() "...reads the entire result of a query
to the client, allocates a MYSQL_RES structure, and places the result into
this structure", is it then safe to use query results simultaneously using
more than one connection?

Like in the following scenario using the C API functions. (Note code is
not actual, compiled code, just a quick sketch that may contain errors,
but expresses the idea):

In thread A (error result code checking removed for conciseness, but
assume it is there):

void some_data_processing_function(char* query_string)
{
/* Note that this function declares separate
 * result, and row data structures
 * each time it is  invoked, and has exclusive
 * use of a connection to run query and store
 * result.
 */

MYSQL* mysql;
MYSQL_RES* result;
MYSQL_ROW row;

mysql = pop_a_connection_from_the_stack();

mysql_query(mysql,query_string);
result = mysql_store_result(&mysql);

push_connection_back_onto_stack(mysql);

while ((row = mysql_fetch_row(result)))
{

/* Proceed to step through and process rows here */

}

/* Clean up data structures */

return;
}

Then, while thread A is processing the result set, thread B invokes the
same function with a different query_string. Note that new, local result
and row data structures are invoked with each call to the function, but if
thread  A has not pushed its connection back on the stack, then B will pop
a different connection. On the other hand, if A has made it to the while
loop and is processing the rows of the result set, then B may pop the same
or a different connection structure off the stack of connections.




Thread
Thread safe queries with multiple connectionsbuford7 Jun
  • RE: Thread safe queries with multiple connectionsMartin Gainty7 Jun