vendredi 17 juillet 2015

sqlite errors in multithread PHP script

I need to update single sqlite database from different separated PHP threads, all running at the same time. Below is the code for db connection

   $db = new SQLite3(__DIR__.'/proxy.db');
   $db->busyTimeout(5000000);
   $db->exec('PRAGMA journal_mode=WAL;');
   $db->exec('PRAGMA temp_store=2;');
   $db->exec('PRAGMA synchronous=0;');

And this is the different methods for updating...

   $db->query('BEGIN TRANSACTION;UPDATE proxy SET lastused='.time().' WHERE id='.$row['id'].';COMMIT;');
   $db->query('BEGIN DEFERRED;UPDATE proxy SET lastused='.time().' WHERE id='.$row['id'].';COMMIT;');
   $db->query('BEGIN IMMEDIATE;UPDATE proxy SET lastused='.time().' WHERE id='.$row['id'].';COMMIT;')

all of them sometime return error: "Unable to execute statement: SQL logic error or missing database". If remove begin-commit code, then I got SQLite3::query(): database is locked.

Can I somehow change the code to get rid of those errors (but still use separated multi-threaded processes)?

Aucun commentaire:

Enregistrer un commentaire