PDO与SQLLite错误（代码：HY000 errorInfo中：HY000，5，数据库被锁定）

Question

我使用此代码时，你得到错误Code: HY000 errorInfo: HY000, 5, database is locked：

function saveCity($dbname, $city) { 
     $SQLLogger = ''; 
     $city_id = ''; 

     try { 
      // connect to SQLite database 
      $dbh = new PDO("sqlite:".$dbname); 

      // put city in the database 
      $sql = "INSERT INTO Orte (ORT) VALUES (:city);"; 
      $sth = $dbh->prepare($sql); 
      $sth->bindValue(":city", $city, PDO::PARAM_STR); 
      if(!$sth->execute()){ 
       // error handling 
       echo "saveCity"; 
       echo "Could not execute statement.\n"; 
       echo "errorCode: " . $sth->errorCode() . "\n"; 
       echo "errorInfo: " . implode(", ", $sth->errorInfo()) . "\n"; 
      } else { 
       // log SQL statements 
       $sql = str_replace(":city", $city, $sql); 
       $SQLLogger .= $sql; 
      } 

      // get id for new created city 
      $sql = "SELECT _id FROM Orte WHERE ORT='$city';"; 
      $sth = $dbh->prepare($sql); 
      $sth->execute(); 
      $res = $sth->fetch(); 
      $city_id = $res['_id']; 

      // close the database connection 
      $dbh = null; 
      echo "closed"; 
     } catch(PDOException $e) { 
      echo $e->getMessage(); 
     } 

     // write SQL statements into log file 
     if(!empty($SQLLogger)) { 
      $logfile = "logs/SQLLogger.txt"; 
      $new_line = chr(10) . chr(13); //ASCI-character for \r\n 
      file_put_contents($logfile, $new_line . $SQLLogger, FILE_APPEND); 
     } 

     return $city_id; 
    } 

    function saveZIP($dbname, $zip, $city_id) { 
     $SQLLogger = ''; 
     $zip_id = ''; 

     try { 
      // connect to SQLite database 
      $dbh = new PDO("sqlite:".$dbname); 

      // save zip code in database 
      $sql = "INSERT INTO Plzn (Postleitzahl, Ort) VALUES (:zip, :city_id);"; 
      $sth = $dbh->prepare($sql); 
      $sth->bindValue(":zip", $zip, PDO::PARAM_STR); 
      $sth->bindValue(":city_id", $city_id, PDO::PARAM_INT); 
      if(!$sth->execute()){ 
       // error handling 
       echo "saveZIP"; 
       echo "Could not execute statement.\n"; 
       echo "errorCode: " . $sth->errorCode() . "\n"; 
       echo "errorInfo: " . implode(", ", $sth->errorInfo()) . "\n"; 
      } else { 
       // log SQL statements 
       $sql = str_replace(":zip", $zip, $sql); 
       $sql = str_replace(":city_id", $city_id, $sql); 
       $SQLLogger .= $sql; 
      } 

      // get id for new created zip code 
      $sql = "SELECT _id FROM Plzn WHERE Postleitzahl='$zip';"; 
      $sth = $dbh->prepare($sql); 
      $sth->execute(); 
      $res = $sth->fetch(); 
      $zip_id = $res['_id']; 

      // close the database connection 
      $dbh = null;  
     } catch(PDOException $e) { 
      echo $e->getMessage(); 
     } 

     // write SQL statements into log file 
     if(!empty($SQLLogger)) { 
      $logfile = "logs/SQLLogger.txt"; 
      $new_line = chr(10) . chr(13); //ASCI-character for \r\n 
      file_put_contents($logfile, $new_line . $SQLLogger, FILE_APPEND); 
     } 

     return $zip_id; 
    } 

    function saveZIPCity($dbname, $zip, $city) { 
     $SQLLogger = ''; 
     $zip_id = ''; 

     try { 
      // connect to SQLite database 
      $dbh = new PDO("sqlite:".$dbname); 

      // check if zip code is already in database 
      $zip = $_SESSION['Plzn']['Postleitzahl']; 
      $sql = "SELECT * FROM Plzn WHERE Postleitzahl='$zip';"; 
      $sth = $dbh->prepare($sql); 
      $sth->execute(); 
      $result = $sth->fetch(); 
      if (!empty($result)){ 
       // zip code is already there so lets take it 
       $zip_id = $result['_id']; 
      // zip code isn't in the database, perhaps the city name is? 
      } else { 
       // check if city is already in database 
       $city = $_SESSION['Orte']['ORT']; 
       $sql = "SELECT _id FROM Orte WHERE ORT='$city';"; 
       $sth = $dbh->prepare($sql); 
       $sth->execute(); 
       $result = $sth->fetch(); 
       if (!empty($result)){ 
        // city is in database, lets take it 
        $city_id = $result['_id']; 
        $zip_id = saveZIP($dbname, $zip, $city_id); 
        echo "city exists. save new zip"; 
       // city is not in database 
       } else { 
        $city_id = saveCity($dbname, $city); 
        $zip_id = saveZIP($dbname, $zip, $city_id); 
       } 
      } 
      // close the database connection 
      $dbh = null;  
     } catch(PDOException $e) { 
      echo $e->getMessage(); 
     } 

     // write SQL statements into log file 
     if(!empty($SQLLogger)) { 
      $logfile = "logs/SQLLogger.txt"; 
      $new_line = chr(10) . chr(13); //ASCI-character for \r\n 
      file_put_contents($logfile, $new_line . $SQLLogger, FILE_APPEND); 
     } 

     return $zip_id; 
    } 

在这段代码中我使用的是独立的功能，似乎有一个并发问题。如果我把所有功能都放在这里：

function saveZIPCity($dbname, $zip, $city) { 
    $SQLLogger = ''; 
    $zip_id = ''; 

    try { 
     // connect to SQLite database 
     $dbh = new PDO("sqlite:".$dbname); 

     // check if zip code is already in database 
     $zip = $_SESSION['Plzn']['Postleitzahl']; 
     $sql = "SELECT * FROM Plzn WHERE Postleitzahl='$zip';"; 
     $sth = $dbh->prepare($sql); 
     $sth->execute(); 
     $result = $sth->fetch(); 
     if (!empty($result)){ 
      // zip code is already there so lets take it 
      $zip_id = $result['_id']; 
     // zip code isn't in the database, perhaps the city name is? 
     } else { 
      // check if city is already in database 
      $city = $_SESSION['Orte']['ORT']; 
      $sql = "SELECT _id FROM Orte WHERE ORT='$city';"; 
      $sth = $dbh->prepare($sql); 
      $sth->execute(); 
      $result = $sth->fetch(); 
      if (!empty($result)){ 
       // city is in database, lets take it 
       $city_id = $result['_id']; 
       // save zip code in database 
       $sql = "INSERT INTO Plzn (Postleitzahl, Ort) VALUES (:zip, :city_id);"; 
       $sth = $dbh->prepare($sql); 
       $sth->bindValue(":zip", $zip, PDO::PARAM_STR); 
       $sth->bindValue(":city_id", $city_id, PDO::PARAM_INT); 
       if(!$sth->execute()){ 
        // error handling 
        echo "Could not execute statement.\n"; 
        echo "errorCode: " . $sth->errorCode() . "\n"; 
        echo "errorInfo: " . implode(", ", $sth->errorInfo()) . "\n"; 
       } else { 
        // log SQL statements 
        $sql = str_replace(":zip", $zip, $sql); 
        $sql = str_replace(":city_id", $city_id, $sql); 
        $SQLLogger .= $sql; 
       } 

       // get id for new created zip code 
       $sql = "SELECT _id FROM Plzn WHERE Postleitzahl='$zip';"; 
       $sth = $dbh->prepare($sql); 
       $sth->execute(); 
       $res = $sth->fetch(); 
       $zip_id = $res['_id']; 
      // city is not in database 
      } else { 
       // put city in the database 
       $sql = "INSERT INTO Orte (ORT) VALUES (:city);"; 
       $sth = $dbh->prepare($sql); 
       $sth->bindValue(":city", $city, PDO::PARAM_STR); 
       if(!$sth->execute()){ 
        // error handling 
        echo "Could not execute statement.\n"; 
        echo "errorCode: " . $sth->errorCode() . "\n"; 
        echo "errorInfo: " . implode(", ", $sth->errorInfo()) . "\n"; 
       } else { 
        // log SQL statements 
        $sql = str_replace(":city", $city, $sql); 
        $SQLLogger .= $sql; 
       } 

       // get id for new created city 
       $sql = "SELECT _id FROM Orte WHERE ORT='$city';"; 
       $sth = $dbh->prepare($sql); 
       $sth->execute(); 
       $res = $sth->fetch(); 
       $city_id = $res['_id']; 

       // save zip code in database 
       $sql = "INSERT INTO Plzn (Postleitzahl, Ort) VALUES (:zip, :city_id);"; 
       $sth = $dbh->prepare($sql); 
       $sth->bindValue(":zip", $zip, PDO::PARAM_STR); 
       $sth->bindValue(":city_id", $city_id, PDO::PARAM_INT); 
       if(!$sth->execute()){ 
        // error handling 
        echo "Could not execute statement.\n"; 
        echo "errorCode: " . $sth->errorCode() . "\n"; 
        echo "errorInfo: " . implode(", ", $sth->errorInfo()) . "\n"; 
       } else { 
        // log SQL statements 
        $sql = str_replace(":zip", $zip, $sql); 
        $sql = str_replace(":city_id", $city_id, $sql); 
        $SQLLogger .= $sql; 
       } 

       // get id for new created zip code 
       $sql = "SELECT _id FROM Plzn WHERE Postleitzahl='$zip';"; 
       $sth = $dbh->prepare($sql); 
       $sth->execute(); 
       $res = $sth->fetch(); 
       $zip_id = $res['_id']; 
      } 
     } 
     // close the database connection 
     $dbh = null;  
    } catch(PDOException $e) { 
     echo $e->getMessage(); 
    } 

    // write SQL statements into log file 
    if(!empty($SQLLogger)) { 
     $logfile = "logs/SQLLogger.txt"; 
     $new_line = chr(10) . chr(13); //ASCI-character for \r\n 
     file_put_contents($logfile, $new_line . $SQLLogger, FILE_APPEND); 
    } 

    return $zip_id; 
} 

它可以正常工作。哪里有问题？

Solution： 使用一个类进行查询并避免同时打开数据库连接。

Answer 1

我相信，因为这条线

$city_id = saveCity($dbname, $city); 
$zip_id = saveZIP($dbname, $zip, $city_id); 

在每个函数调用的。你初始化一个新的数据库连接。在这种情况下，您使用的sqlite只有在您只调用一次的情况下才有效。因为它需要一个写锁（这就是为什么你有这个错误 - 你称它为两次）