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?

Add: sleep(2) after `$file_db->exec` Too many processes are trying to insert too rapidly into the database which is locking the table. — Justin E, Jan 12 '15 at 13:16
Never used this, but 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... — Justin E, Jan 12 '15 at 13:29
I have suggested that you check if you have unsaved changes, on the answer right below. If that solves the problem and if it is as precise as needed, please select it as the answer for this problem. — Luis Milanese, May 18 '15 at 19:01
Simply saving unsaved changes to the database file solved this issue for me. — nightrain, May 01 '23 at 18:37

score 10 · Answer 1 · answered Apr 21 '15 at 04:07

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.

score 2 · Accepted Answer · answered Jan 12 '15 at 13:54

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...

answered Jan 12 '15 at 13:54

Justin E

1,252
16
30

I added "$file_db->setAttribute(PDO::ATTR_TIMEOUT, 0);" and I catch the exception. If that exception take place, I simply run again that query + I do a "usleep" - this works theoreticaly, doesnt it? – John Smith Jan 12 '15 at 13:57
1

Yes, in theory that will work just fine although you may wish to start with the usleep to kind-of offset the attempts a bit... – Justin E Jan 12 '15 at 14:00
1

[Sorry, but this is the best I can do...](http://stackoverflow.com/a/880310/987016) – Justin E Jan 12 '15 at 14:08
1

Switching to mysql will allow you to use the delayed keyword like so: `INSERT DELAYED INTO ...` which provides for concurrent database inserts. – Justin E Jan 12 '15 at 14:08
1

Close the cursor after use: $stmt->closeCursor(); – edercortes Mar 26 '15 at 18:35

score 1 · Answer 3 · answered Jan 19 '18 at 03:28

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.

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

3 Answers3

Linked