Can MySQL support concurrent queries per connection?

If I have 10 queries, an开发者_高级运维d each query is updating a particular table (i.e., 10 different tables).

Can I open one mySQL connection, spawn 10 threads, each thread handles 1 query such that they can run concurrently instead of executing one by one.

Thanks!

No you can't:

MySQL client library (at least native C one) is not thread safe to use same connection from different threads. You need to use a connection per thread.

If you just need update/insert queries running in parallel (asynchronously in terms of MySQL API) - you can use INSERT DELAYED and UPDATE LOW_PRIORITY queries.

Because of the way the MySQL protocol works, these will get sent to the server one-by-one if you only have one connection open.

There is no file "log.conn.txt" created, so there is no conflict between concurrent queries to the single mysql client connection:

<?
declare(ticks=1);
pcntl_signal(SIGUSR1, create_function('$signo', 'sleep(1);while (($pid=pcntl_wait(@$status, WNOHANG))>0) {}'));//protect against zombie children
$pdo=new PDO('mysql:host=192.168.0.2;port=3306;dbname=baseinfo', 'dev', 'dev',
             array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
                   PDO::MYSQL_ATTR_INIT_COMMAND=>'set names utf8'
                  )
            );
for ($i=0; $i<20; ++$i)
    {if (($pid=pcntl_fork())===-1)
        {//...
         continue;
        }
     else if ($pid)
          {$pids[]=$pid;
           pcntl_wait($status, WNOHANG); //protect against zombie children, one wait vs one child
          }
     else if ($pid===0)
          {ob_start();//prevent output to main process
           register_shutdown_function(create_function('$pars', 'ob_end_clean();posix_kill(posix_getppid(), SIGUSR1);posix_kill(getmypid(), SIGKILL);'), array());//to kill self before exit();, or else the resource shared with parent will be closed
           for ($j=0; $j<200; ++$j)
               {try
                   {file_put_contents('log.'.$i.'.txt', $pdo->query('select partner_login from base_account where id=100')->fetch(PDO::FETCH_COLUMN, 0)."\t".time().substr(microtime(),2,6)."\n", FILE_APPEND);
                   }
                catch (Exception $e)
                      {if ($pdo->getAttribute(PDO::ATTR_SERVER_INFO)==='MySQL server has gone away')
                          {file_put_contents('log.conn.txt', time().substr(microtime(),2,6).":{$i}:{$j} lost\n", FILE_APPEND);
                           $pdo=&new PDO('mysql:host=192.168.0.2;port=3306;dbname=baseinfo', 'dev', 'dev',
                                        array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
                                              PDO::MYSQL_ATTR_INIT_COMMAND=>'set names utf8'
                                             )
                                       );
                          }
                      }
                usleep(50000);
               }
           exit();//avoid foreach loop in child process
          }
    }
//wait all child to end, avoid close db connection before all children self killed
foreach ($pids as $p)
        {pcntl_waitpid($p, $status);
        }
?>