尝试从PHP脚本访问时数据库已锁定 [英] Database locked while trying to access from PHP script

问题描述

我正在编写一个与PHP后端通信的Android应用程序。后端数据库是SQLite3。问题是，我间歇性地收到此错误 PHP警告：SQLite3 :: prepare（）：无法准备声明：5，数据库已锁定 。我在每个PHP文件中打开与数据库的连接，并在脚本完成时关闭它。我认为问题在于，一个脚本在写入数据库文件时锁定了数据库文件，而第二个脚本试图访问数据库文件，但失败了。避免这种情况的一种方法是在所有php脚本之间共享一个连接。我想知道是否还有其他方法可以避免这种情况？

I am writing an Android app which communicates with a PHP backend. The backend db is SQLite 3. The problem is, I am getting this error intermittently PHP Warning: SQLite3::prepare(): Unable to prepare statement: 5, database is locked. I am opening a connection to the database in each PHP file and closing it when the script finishes. I think the problem is that one script locked the database file while writing to it and the second script was trying to access it, which failed. One way of avoiding this would be to share a connection between all of the php scripts. I was wondering if there is any other way of avoiding this?

编辑：
这是第一个文件：

This is the first file:

<?php
$first = SQLite3::escapeString($_GET['first']);
$last = SQLite3::escapeString($_GET['last']);
$user = SQLite3::escapeString($_GET['user']);
$db = new SQLite3("database.db");
$insert = $db->prepare('INSERT INTO users VALUES(NULL,:user,:first,:last, 0 ,datetime())');
$insert->bindParam(':user', $user, SQLITE3_TEXT);
$insert->bindParam(':first', $first, SQLITE3_TEXT);
$insert->bindParam(':last', $last, SQLITE3_TEXT);
$insert->execute();
?>

这是第二个文件：

<?php
$user = SQLite3::escapeString($_GET['user']);
$db = new SQLite3("database.db");
$checkquery = $db->prepare('SELECT allowed FROM users WHERE username=:user');
$checkquery->bindParam(':user', $user, SQLITE3_TEXT);
$results = $checkquery->execute();
$row = $results->fetchArray(SQLITE3_ASSOC);
print(json_encode($row['allowed']));
?>

推荐答案

首先，完成资源处理后，您应该总是关闭它。从理论上讲，将在垃圾回收时将其关闭，但是您不能依靠PHP马上进行操作。我已经看到一些数据库（以及与此相关的其他类型的库）由于没有明确释放资源而被锁定。

First, when you are done with a resource you should always close it. In theory it will be closed when it is garbage-collected, but you can't depend on PHP doing that right away. I've seen a few databases (and other kinds of libraries for that matter) get locked up because I didn't explicitly release resources.

$db->close();
unset($db);

第二，Sqlite3给您一个繁忙的超时时间。我不确定默认值是什么，但是如果您愿意等待几秒钟以在执行查询时清除锁定，则可以这样说。

Second, Sqlite3 gives you a busy timeout. I'm not sure what the default is, but if you're willing to wait a few seconds for the lock to clear when you execute queries, you can say so. The timeout is in milliseconds.

$db->busyTimeout(5000);

这篇关于尝试从PHP脚本访问时数据库已锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！