Thread safe queries with multiple connections

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.





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Thread safe queries with multiple connections

--_a0e45a6e-109a-4ad2-8f80-ccb08cdc6713_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


mg>hopefully quick comments

> Date: Sun=2C 7 Jun 2009 04:41:20 -0600
> Subject: Thread safe queries with multiple connections
> From: buford@biffco.net
> To: mysql@lists.mysql.com
>=20
> 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=20
> connections are share. The application my have multiple connections
> structures defined in a pool=2C but each individual connection will be us=
ed
> 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.
>=20
> That is=2C since mysql_store_result() "...reads the entire result of a qu=
ery
> to the client=2C allocates a MYSQL_RES structure=2C and places the result=
into
> this structure"=2C is it then safe to use query results simultaneously us=
ing
> more than one connection?
>=20
> Like in the following scenario using the C API functions. (Note code is
> not actual=2C compiled code=2C just a quick sketch that may contain error=
s=2C
> but expresses the idea):
>=20
> In thread A (error result code checking removed for conciseness=2C but
> assume it is there):
>=20
> void some_data_processing_function(char* query_string)
> {
> /* Note that this function declares separate
> * result=2C and row data structures
> * each time it is invoked=2C and has exclusive
> * use of a connection to run query and store
> * result.
> */
>=20
typedef struct st_mysql
{
NET net=3B /* Communication parameters */
unsigned char *connector_fd=3B /* ConnectorFd for SSL */
char *host=2C*user=2C*passwd=2C*unix_socket=2C*server_version=2C* h=
ost_info=3B
char *info=2C *db=3B
struct charset_info_st *charset=3B
MYSQL_FIELD *fields=3B
MEM_ROOT field_alloc=3B
my_ulonglong affected_rows=3B
my_ulonglong insert_id=3B /* id if insert on table with NEXTNR */
my_ulonglong extra_info=3B /* Not used */
unsigned long thread_id=3B /* Id for connection in server */
unsigned long packet_length=3B
unsigned int port=3B
unsigned long client_flag=2Cserver_capabilities=3B
unsigned int protocol_version=3B
unsigned int field_count=3B
unsigned int server_status=3B
unsigned int server_language=3B
unsigned int warning_count=3B
struct st_mysql_options options=3B
enum mysql_status status=3B
my_bool free_me=3B /* If free in mysql_close */
my_bool reconnect=3B /* set to 1 if automatic reconnect */
/* session-wide random string */
char scramble[SCRAMBLE_LENGTH+1]=3B
/*
Set if this is the original connection=2C not a master or a slave we hav=
e
added though mysql_rpl_probe() or mysql_set_master()/ mysql_add_slave()
*/
my_bool rpl_pivot=3B
/*
Pointers to the master=2C and the next slave connections=2C points to
itself if lone connection.
*/
struct st_mysql* master=2C *next_slave=3B
struct st_mysql* last_used_slave=3B /* needed for round-robin slave pick =
*/
/* needed for send/read/store/use result to work correctly with replicatio=
n */
struct st_mysql* last_used_con=3B
LIST *stmts=3B /* list of all statements */
const struct st_mysql_methods *methods=3B
void *thd=3B
/*
Points to boolean flag in MYSQL_RES or MYSQL_STMT. We set this flag=20
from mysql_stmt_close if close had to cancel result set of this object.
*/
my_bool *unbuffered_fetch_owner=3B
/* needed for embedded server - no net buffer to store the 'info' */
char *info_buffer=3B
void *extension=3B
} MYSQL *mysql=3B

typedef struct tagMYCONNECTION
{
/* Note that pMySQL MUST be first here=2C so I can cast a PMYCONNECTION to =
a MYSQL *. */
MYSQL *pMySQL=3B
/* Also note that this is a mock up to not have to run mysql_init() and all=
ocate the above. */
MYSQL MySQL=3B
unsigned int nPort=3B
unsigned long lFlags=3B
TCHAR szHostname[MYSQL_HOSTNAME_SIZE + 1]=3B
TCHAR szUsername[MYSQL_USERNAME_SIZE + 1]=3B
TCHAR szPassword[MYSQL_PASSWORD_SIZE + 1]=3B
TCHAR *pConnectDatabase=3B
TCHAR *pCurrentDatabase=3B
TCHAR *pTables=3B
TCHAR *pColumns=3B
TCHAR *pRoutines=3B
} MYCONNECTION=2C *PMYCONNECTION=3B

typedef struct st_mysql_res {
my_ulonglong row_count=3B
MYSQL_FIELD *fields=3B
MYSQL_DATA *data=3B
MYSQL_ROWS *data_cursor=3B
unsigned long *lengths=3B /* column lengths of current row */
MYSQL *handle=3B /* for unbuffered reads */
const struct st_mysql_methods *methods=3B
MYSQL_ROW row=3B /* If unbuffered read */
MYSQL_ROW current_row=3B /* buffer to current row */
MEM_ROOT field_alloc=3B
unsigned int field_count=2C current_field=3B
my_bool eof=3B /* Used by mysql_fetch_row */
/* mysql_stmt_close() had to cancel this result */
my_bool unbuffered_fetch_cancelled=3B =20
void *extension=3B
} MYSQL_RES *result=3B

typedef char **MYSQL_ROW=3B /* return data as array of strings */
public MYSQL_ROW row=3B

public ConnectionPoolDataSource cpds=3B
public static String dbUrl =3D "jdbc:mysql:///dbName"=3B

populate_MYSQL(mysql)=3B

// mysql =3D pop_a_connection_from_the_stack()=3B
MysqlConnectionPoolDataSource ds =3D new MysqlConnectionPoolDataSource(=
)=3B
ds.setURL(dbUrl)=3B

PooledConnection conn=3Dds.getPooledConnection("user"=2C"password")
populate_MYCONNECTION(PMYCONNECTION)=3B
java.sql.Statement stmt =3D conn.createStatement()=3B

// mysql_query(mysql=2Cquery_string)=3B
ResultSet result =3D stmt.executeQuery("SHOW COLLATION")=3B

result =3D mysql_store_result(conn->pMySQL))=20
> result =3D mysql_store_result(&mysql)=3B
populate_MYSQL_RES(result)=3B
>=20
> push_connection_back_onto_stack(mysql)=3B
>=20
> while ((row =3D mysql_fetch_row(result)))
> {
>=20
> /* Proceed to step through and process rows here */
>=20
> }
>=20
> /* Clean up data structures */
>=20
> return=3B
> }
>=20
> Then=2C while thread A is processing the result set=2C thread B invokes t=
he
> same function with a different query_string. Note that new=2C local resul=
t
> and row data structures are invoked with each call to the function=2C but=
if
> thread A has not pushed its connection back on the stack=2C then B will =
pop
> a different connection. On the other hand=2C if A has made it to the whil=
e
> loop and is processing the rows of the result set=2C then B may pop the s=
ame
> or a different connection structure off the stack of connections.
MG>all of this can be accomodated by enabling these functions to be re-entr=
ant
MG>http://en.wikipedia.org/wiki/Reentrant_(subroutine)
>=20
>=20

MG>other solutions?

>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.c=
om
>=20

____________________________________________________________ _____
Hotmail=AE has ever-growing storage! Don=92t worry about storage limits.=20
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=3DTXT_T AGLM_WL_HM_Tuto=
rial_Storage_062009=

--_a0e45a6e-109a-4ad2-8f80-ccb08cdc6713_--