List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:June 7 2009 1:12pm
Subject:RE: Thread safe queries with multiple connections
View as plain text  
mg>hopefully quick comments

> Date: Sun, 7 Jun 2009 04:41:20 -0600
> Subject: Thread safe queries with multiple connections
> From: buford@stripped
> To: mysql@stripped
> 
> 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=1
> 

_________________________________________________________________
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
Thread
Thread safe queries with multiple connectionsbuford7 Jun
  • RE: Thread safe queries with multiple connectionsMartin Gainty7 Jun