使用以下代码时，我得到错误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 rn
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 rn
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 rn
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 rn
file_put_contents($logfile, $new_line . $SQLLogger, FILE_APPEND);
}
return $zip_id;
}
它工作起来没有问题。问题出在哪里？
解决方案：使用类进行查询，避免同时打开数据库连接。
我相信是因为这条线
$city_id = saveCity($dbname, $city);
$zip_id = saveZIP($dbname, $zip, $city_id);
在您的每个函数调用中。初始化一个新的数据库连接。在这种情况下，您使用的是sqlite，只有当您只调用它一次时，它才起作用。因为它需要一个写锁（这就是为什么你会出现这个错误——你调用了两次）