Database locked while trying to access from PHP script

Question

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?

Edit: 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();
?>

Here is the second file:

<?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']));
?>

Answer 1

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);

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);

Answer 2

I was getting "database locked" all the time until I found out some features of sqlite3 must be set by using SQL special instructions (i.e. using PRAGMA keyword). For instance, what apparently solved my problem with "database locked" was to set journal_mode to 'wal' (it is defaulting to 'delete', as stated here: https://www.sqlite.org/wal.html (see Activating And Configuring WAL Mode)).

So basically what I had to do was creating a connection to the database and setting journal_mode with the SQL statement. Example:

<?php
  $db = new SQLite3('/my/sqlite/file.sqlite3');
  $db->busyTimeout(5000);
  // WAL mode has better control over concurrency.
  // Source: https://www.sqlite.org/wal.html
  $db->exec('PRAGMA journal_mode = wal;');
?>

Hope that helps.