mg>hopefully quick comments
> Date: Sun, 7 Jun 2009 04:41:20 -0600
> Subject: Thread safe queries with multiple connections
> From: buf...@biffco.net
> To: mysql@lists.mysql.com
>
> 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.
> */
>
typedef struct st_mysql
{
NET net; /* Communication parameters */
unsigned char *connector_fd; /* ConnectorFd for SSL */
char *host,*user,*passwd,*unix_socket,*server_version,*host_info;
char *info, *db;
struct charset_info_st *charset;
MYSQL_FIELD *fields;
MEM_ROOT field_alloc;
my_ulonglong affected_rows;
my_ulonglong insert_id; /* id if insert on table with NEXTNR */
my_ulonglong extra_info; /* Not used */
unsigned long thread_id; /* Id for connection in server */
unsigned long packet_length;
unsigned int port;
unsigned long client_flag,server_capabilities;
unsigned int protocol_version;
unsigned int field_count;
unsigned int server_status;
unsigned int server_language;
unsigned int warning_count;
struct st_mysql_options options;
enum mysql_status status;
my_bool free_me; /* If free in mysql_close */
my_bool reconnect; /* set to 1 if automatic reconnect */
/* session-wide random string */
char scramble[SCRAMBLE_LENGTH+1];
/*
Set if this is the original connection, not a master or a slave we have
added though mysql_rpl_probe() or mysql_set_master()/ mysql_add_slave()
*/
my_bool rpl_pivot;
/*
Pointers to the master, and the next slave connections, points to
itself if lone connection.
*/
struct st_mysql* master, *next_slave;
struct st_mysql* last_used_slave; /* needed for round-robin slave pick */
/* needed for send/read/store/use result to work correctly with replication */
struct st_mysql* last_used_con;
LIST *stmts; /* list of all statements */
const struct st_mysql_methods *methods;
void *thd;
/*
Points to boolean flag in MYSQL_RES or MYSQL_STMT. We set this flag
from mysql_stmt_close if close had to cancel result set of this object.
*/
my_bool *unbuffered_fetch_owner;
/* needed for embedded server - no net buffer to store the 'info' */
char *info_buffer;
void *extension;
} MYSQL *mysql;
typedef struct tagMYCONNECTION
{
/* Note that pMySQL MUST be first here, so I can cast a PMYCONNECTION to a
MYSQL *. */
MYSQL *pMySQL;
/* Also note that this is a mock up to not have to run mysql_init() and
allocate the above. */
MYSQL MySQL;
unsigned int nPort;
unsigned long lFlags;
TCHAR szHostname[MYSQL_HOSTNAME_SIZE + 1];
TCHAR szUsername[MYSQL_USERNAME_SIZE + 1];
TCHAR szPassword[MYSQL_PASSWORD_SIZE + 1];
TCHAR *pConnectDatabase;
TCHAR *pCurrentDatabase;
TCHAR *pTables;
TCHAR *pColumns;
TCHAR *pRoutines;
} MYCONNECTION, *PMYCONNECTION;
typedef struct st_mysql_res {
my_ulonglong row_count;
MYSQL_FIELD *fields;
MYSQL_DATA *data;
MYSQL_ROWS *data_cursor;
unsigned long *lengths; /* column lengths of current row */
MYSQL *handle; /* for unbuffered reads */
const struct st_mysql_methods *methods;
MYSQL_ROW row; /* If unbuffered read */
MYSQL_ROW current_row; /* buffer to current row */
MEM_ROOT field_alloc;
unsigned int field_count, current_field;
my_bool eof; /* Used by mysql_fetch_row */
/* mysql_stmt_close() had to cancel this result */
my_bool unbuffered_fetch_cancelled;
void *extension;
} MYSQL_RES *result;
typedef char **MYSQL_ROW; /* return data as array of strings */
public MYSQL_ROW row;
public ConnectionPoolDataSource cpds;
public static String dbUrl = "jdbc:mysql:///dbName";
populate_MYSQL(mysql);
// mysql = pop_a_connection_from_the_stack();
MysqlConnectionPoolDataSource ds = new MysqlConnectionPoolDataSource();
ds.setURL(dbUrl);
PooledConnection conn=ds.getPooledConnection("user","password")
populate_MYCONNECTION(PMYCONNECTION);
java.sql.Statement stmt = conn.createStatement();
// mysql_query(mysql,query_string);
ResultSet result = stmt.executeQuery("SHOW COLLATION");
result = mysql_store_result(conn->pMySQL))
> result = mysql_store_result(&mysql);
populate_MYSQL_RES(result);
>
> 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.
MG>all of this can be accomodated by enabling these functions to be re-entrant
MG>http://en.wikipedia.org/wiki/Reentrant_(subroutine)
>
>
MG>other solutions?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
>
_________________________________________________________________
Hotmail® has ever-growing storage! Don’t worry about storage limits.
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009
