PDO General error: 2014 Cannot execute queries while other unbuffered queries are active when trying to LOCK TABLES

Question

I am updating someone's old old code from using mysql() to using PDO. In one place they have some LOCK TABLES commands to prevent two users from accessing the same data at the same time. When running LOCK TABLES, PDO throws "General Error: 2014 Cannot execute queries while other unbuffered queries are active".

I made some test code to eliminate other variables. The system runs on Ubuntu 18 / PHP 7.2 / MySQL 5.7.27:

try {
    $_DB=new PDO("mysql:host=".DBHOST.";dbname=".DBNAME.";charset=utf8", DBUSER, DBPASS,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::ATTR_TIMEOUT => "5",  PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_DIRECT_QUERY=>false,PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>true ));      

    // This gives the 2014 error. Any queries following this get the 2014 error.
    // Any queries before this work fine.
    $_DB->query("LOCK TABLES Inspections WRITE");

    $_DB->query("UNLOCK TABLES");
}
catch (Exception $e) {
    logError("Error : ".$e->getMessage());           
    // per @dharman's comment 
    //exit();
    throw($e);
}

I also tried adding a $_DB->beginTransaction before the LOCK TABLES and $_DB->commit after the UNLOCK TABLES, but still I get the same error.

I have tried various combination of ATTR_EMULATE_PREPARES and MYSQL_ATTR_USE_BUFFERED_QUERY, but nothing seems to make any difference.

Answer 1

You should use exec() instead query(). exec() does not expect any return values, which is exactly what LOCK TABLES needs.

$pdo->exec("LOCK TABLES Inspections WRITE");