PDO(mysql驱动)查询超时设置方法


官方关于ATTR_TIMEOUT的说明,需要注意这个参数配置在不同驱动下的实际效果可能不一致。

PDO::ATTR_TIMEOUT: Specifies the timeout duration in seconds. Not all drivers support this option, and its meaning may differ from driver to driver. For example, sqlite will wait for up to this time value before giving up on obtaining an writable lock, but other drivers may interpret this as a connect or a read timeout interval. Requires int.

误区

  • 初始化之后再设置ATTR_TIMEOUT
$opts = array(
    \PDO::ATTR_AUTOCOMMIT => true,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_TIMEOUT => 1,
);

$dsn = 'mysql:dbname=root;host=128.0.0.1;port=3306';

$startTime = microtime(true);
try {
    $db = new \PDO($dsn, 'root', 'root', $opts);
    $db->setAttribute(\PDO::ATTR_TIMEOUT,1);
} catch (\PDOException $ex) {
    $this->exception = $ex;
    $message = "MySQL connection failed (" . $dsn . ';password)' . $ex->getCode() . '=>' . $ex->getMessage();
    print_r($message);
}
print_r(round((microtime(true) - $startTime) * 1000, 2) . 'ms';);

// output about n(default 30)s

说明:PDO初始化时已经开始创建连接,之后再设置ATTR_TIMEOUT无效,此时连接使用默认配置时间

  • 使用default_socket_timeout
set_ini('default_socket_timeout',3);

$opts = array(
    \PDO::ATTR_AUTOCOMMIT => true,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
);

$dsn = 'mysql:dbname=root;host=128.0.0.1;port=3306';

$startTime = microtime(true);
try {
    $db = new \PDO($dsn, 'root', 'root', $opts);
} catch (\PDOException $ex) {
    $this->exception = $ex;
    $message = "MySQL connection failed (" . $dsn . ';password)' . $ex->getCode() . '=>' . $ex->getMessage();
    print_r($message);
}
print_r(round((microtime(true) - $startTime) * 1000, 2) . 'ms';);

// output about n(default 30)s

说明:这种方法来自stackoverflow但是在实测中未生效,检查源码(php-5.6.29)发现PDO在创建连接时已设置有默认的30秒超时时间,所以在实际的socket连接中覆盖了default_socket_timeout?

long connect_timeout = pdo_attr_lval(driver_options, PDO_ATTR_TIMEOUT, 30 TSRMLS_CC);

  • 初始化设置的ATTR_TIMEOUT可以控制query的超时时间
$opts = array(
    \PDO::ATTR_AUTOCOMMIT => true,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_TIMEOUT => 1,
);

$dsn = 'mysql:dbname=root;host=127.0.0.1;port=3306';

$startTime = microtime(true);
try {
    $db = new \PDO($dsn, 'root', 'root', $opts);
    $db->query('select sleep(5)');
} catch (\PDOException $ex) {
    $this->exception = $ex;
    $message = "MySQL connection failed (" . $dsn . ';password)' . $ex->getCode() . '=>' . $ex->getMessage();
    print_r($message);
}
print_r(round((microtime(true) - $startTime) * 1000, 2) . 'ms';);
// print about 5s

说明:源码中的注释,ATTR_TIMEOUT只是连接超时的配置项

PDO_ATTR_TIMEOUT, /* connection timeout in seconds */

正解

  • 配置mysqlnd.net_read_timeout(推荐方式)

修改php.ini,设置mysqlnd.net_read_timeout=n(单位秒,扩展原设置31536000秒)

$opts = array(
    \PDO::ATTR_AUTOCOMMIT => true,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_TIMEOUT => 1,
);

$dsn = 'mysql:dbname=root;host=127.0.0.1;port=3306';

$startTime = microtime(true);
try {
    $db = new \PDO($dsn, 'root', 'root', $opts);
    $db->query('select sleep(5)');
} catch (\PDOException $ex) {
    $this->exception = $ex;
    $message = "MySQL connection failed (" . $dsn . ';password)' . $ex->getCode() . '=>' . $ex->getMessage();
    print_r($message);
}
print_r(round((microtime(true) - $startTime) * 1000, 2) . 'ms';);
// print about n s
  • 使用PGSQL_CONNECT_ASYNC

大概思路,使用非堵塞的方式执行查询,然后循环获取结果,如果超时时间内仍获取不到结果则kill query process后抛出异常


发表评论