Sqlite3, SQLSTATE[HY000]: General error: 5 database is locked

Question

I have this little test script:

session_start();
session_write_close();
error_reporting(-1);
register_shutdown_function(function() {
    //echo 'shutdown';
});

$MAX = 120;
set_time_limit($MAX);
echo date('Y-m-d H:i:s').'<br>';
$m = microtime(true);
$file_db = new PDO('sqlite:'.dirname(__FILE__).'/test.sqlite3');
$file_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$file_db->exec("CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, message TEXT, time INTEGER)");

$d = date('U');
do
{
    $file_db->exec ('INSERT INTO messages VALUES (null, "titleee'.rand(1,9).'", "MESSAGEEEE'.rand(1,99).'", "'.rand(1,999).'")');
    if (date('U') - $d > $MAX/2)
    {
        break;
    }
} while (true);
$file_db = null;
echo 'ok: '.(microtime(true)-$m);

if this is run in browser in multiple instance, sooner or later it drops "SQLSTATE[HY000]: General error: 5 database is locked" exception. How to dodge it?

Answer 1

I had this error because I had an unsaved record on SQLiteBrowser then SQLite wouldn't write on it. My script resumed to work properly once I saved the record. So, every time this error occurs, check your if SQLiteBrowser (or any other editor) is on and does have any unsaved changes.

Answer 2

Add: sleep(2) after $file_db->exec Too many processes are trying to insert too rapidly into the database which is locking the table. You are welcome to try: $file_db->query("SET LOCK MODE TO WAIT 120") immediately after you instantiate $file_db. That should make the script wait up to two minutes for the table to unlock...

Answer 3

I tracked a locking issue down to a Virtual Machine network file sharing issue: https://www.sqlite.org/lockingv3.html#how_to_corrupt

One should note that POSIX advisory locking is known to be buggy or even unimplemented on many NFS implementations (including recent versions of Mac OS X) and that there are reports of locking problems for network filesystems under Windows. Your best defense is to not use SQLite for files on a network filesystem.

I was able to resolve the issue by moving the SQLite database file to a guest OS (Linux) directory that was not mounted/shared with the host OS (Windows).

In my case I moved the SQLite database file to /tmp on the Linux guest OS.