[sqlite] Wierd Locking problem

[Luc Andre]

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

[Eduardo Morras]

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] Wierd Locking problem

[Simon Slavin]

On 24 Aug 2015, at 3:45pm, Luc Andre  wrote:

> I'm sure no process open the file using the SQLite API in write mode.

You are, but the software has to check for it, and occasionally two of your 
threads do the same check at the same time.

I'm afraid that if you have already set a timeout I don't know why you are 
seeing problems.  I hope someone else can help.

Simon.

[sqlite] Wierd Locking problem

[Luc Andre]

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

[Luc Andre]

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

[sqlite] Wierd Locking problem

[Simon Slavin]

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.

> Any hint / fix ?

For every connection to the database set a timeout using this function:



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.

Simon.