[sqlite] Wierd Locking problem
We use straight SQLITE3 PHP Extension sot we don\ not have that much customization level. On Mon, Aug 24, 2015 at 5:44 PM, Eduardo Morras wrote: > On Mon, 24 Aug 2015 16:03:24 +0200 > Luc Andre wrote: > > > Hi All, > > > > Our web servers use a daily updated sqlite database file. > > > > The file is opened by multiple (apache/PHP) threads but always in > > read only mode: > > > > $db = new SQLite3(DB_FILE, SQLITE3_OPEN_READONLY); > > > > The file itself has no write access. > > > > -rw-r--r-- 1 root root 225759232 Aug 24 13:43 db.sqlite > > > > But sometimes we get PHP warnings: > > > > PHP Notice: SQLite3Stmt::execute(): Unable to execute statement: > > database is locked > > SQLite3::prepare(): Unable to prepare statement: 5, database is locked > > SQLite3::querySingle(): Unable to execute statement: database is > > locked > > > > We can not understand how a read only file can get locked. > > Some hints (some of them from documentation): > > a) If you use wal mode then you can't open it in read only mode, it' will > be on read-write mode. > b) If you have some triggers that modifies, inserts or delete data, you'll > get a db locked if any attempts to fire when another is running. > c) If you use wal mode and wal checkpoint isn't on passive mode, you'll > get a db busy. > d) Check performance on serialize threading mode (2), instead of > multi-thread mode (1). > e) If a journal size limit is set, whichever journal mode, a db lock > happens. > f) Some pragmas lock db, update user_version, cache_spill=on, > > > > Any hint / fix ? > > > > Regards, > > > > Luc > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > --- --- > Eduardo Morras > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Wierd Locking problem
On Mon, Aug 24, 2015 at 4:38 PM, Simon Slavin wrote: > > On 24 Aug 2015, at 3:03pm, Luc Andre wrote: > > > PHP Notice: SQLite3Stmt::execute(): Unable to execute statement: database > > is locked > > SQLite3::prepare(): Unable to prepare statement: 5, database is locked > > SQLite3::querySingle(): Unable to execute statement: database is locked > > > > We can not understand how a read only file can get locked. > > Although the connection you're using is readonly, another program may have > the same database file open for writing. So your readonly thread has to > check to see that nothing is making changes to the file. Occasionally two > of your threads make this check at the same time and argue with one-another > about who goes first. > > I'm sure no process open the file using the SQLite API in write mode. If you suspect a cron job that open open the file (as a regular binary file) in rw mode, I doubt it, but the SQLIte opening should have failed, and the opening is always fine. > > Any hint / fix ? > > For every connection to the database set a timeout using this function: > > <http://php.net/manual/en/sqlite3.busytimeout.php> > > which will look something like > > $dbConnection->busyTimeout(30); > > I'm using 5 minutes just to give an unattended program time to survive a > network problem. I don't expect any access to every really take 5 minutes. > > We already did a $dbConnection->busyTimeout(500); (500ms is already huge for our web server latency) with no luck. > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Wierd Locking problem
Hi All, Our web servers use a daily updated sqlite database file. The file is opened by multiple (apache/PHP) threads but always in read only mode: $db = new SQLite3(DB_FILE, SQLITE3_OPEN_READONLY); The file itself has no write access. -rw-r--r-- 1 root root 225759232 Aug 24 13:43 db.sqlite But sometimes we get PHP warnings: PHP Notice: SQLite3Stmt::execute(): Unable to execute statement: database is locked SQLite3::prepare(): Unable to prepare statement: 5, database is locked SQLite3::querySingle(): Unable to execute statement: database is locked We can not understand how a read only file can get locked. Any hint / fix ? Regards, Luc
