From: Martin Gainty Date: June 7 2009 1:12pm Subject: RE: Thread safe queries with multiple connections List-Archive: http://lists.mysql.com/mysql/217814 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_a0e45a6e-109a-4ad2-8f80-ccb08cdc6713_" --_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@stripped > To: mysql@stripped >=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@stripped= om >=20 _________________________________________________________________ Hotmail=AE has ever-growing storage! Don=92t worry about storage limits.=20 http://windowslive.com/Tutorial/Hotmail/Storage?ocid=3DTXT_TAGLM_WL_HM_Tuto= rial_Storage_062009= --_a0e45a6e-109a-4ad2-8f80-ccb08cdc6713_--