Re: [sqlite] Opening and closing an SQLITE3 db in PHP

[Tim Streater]

On 19 Mar 2014 at 12:45, Simon Slavin  wrote: 

> On 19 Mar 2014, at 11:21am, Tim Streater  wrote:
>
>> So I could do:  $dbh = new sqlite3 ($dbname, SQLITE_OPEN_READONLY); ??
>
> Yes.  I think that's the way you're meant to do it if you really do want
> readonly.
>
>> 3) Is the lock always released if I do $dbh->close(); ?
>>
>>> No longer need to answer this, but it brings up the fact that, if you
>>> haven't
>>> already released the lock by closing all the transactions on the database,
>>> the
>>> ->close() will fail because you have unfinished work.  So that's another
>>> reason to be fussy about using transactions correctly.
>>
>> So suppose I have this sequence (ignoring any error handling for clarity):
>>
>> $dbh = new sqlite3 ($database);
>> $res = $dbh->query ('select ...');
>>
>> while  (true)
>> {
>> $reg = $res->fetchArray (SQLITE3_ASSOC);
>> if  ($reg===false)  break;
>> // Do stuff with this row of results - may take some minutes
>> $dbh->exec ('update ...');
>> // Do more stuff  - may take some minutes
>> $dbh->exec ('update ...');
>> }
>>
>> $res->finalize ();
>> $dbh->close ();
>>
>> then is the locking issue not so much the distance between the "new" and the
>> dbclose, as the distance between the query and its finalize() ? If this is
>> the case then I can simply gather the results I need from the select and do
>> the time-consuming loop as a separate step.
>
> Correct.  While your query is running (until you have finalized or reset it)
> you have a lock on the database.

Thanks - that's clarified it properly. Means I can fix it more simply, and 
without lots of opens/closes.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Opening and closing an SQLITE3 db in PHP

[Simon Slavin]

On 19 Mar 2014, at 11:21am, Tim Streater  wrote:

> So I could do:  $dbh = new sqlite3 ($dbname, SQLITE_OPEN_READONLY); ??

Yes.  I think that's the way you're meant to do it if you really do want 
readonly.

> 3) Is the lock always released if I do $dbh->close(); ?
> 
>> No longer need to answer this, but it brings up the fact that, if you haven't
>> already released the lock by closing all the transactions on the database, 
>> the
>> ->close() will fail because you have unfinished work.  So that's another
>> reason to be fussy about using transactions correctly.
> 
> So suppose I have this sequence (ignoring any error handling for clarity):
> 
> $dbh = new sqlite3 ($database);
> $res = $dbh->query ('select ...');
> 
> while  (true)
> {
> $reg = $res->fetchArray (SQLITE3_ASSOC);
> if  ($reg===false)  break;
> // Do stuff with this row of results - may take some minutes
> $dbh->exec ('update ...');
> // Do more stuff  - may take some minutes
> $dbh->exec ('update ...');
> }
> 
> $res->finalize ();
> $dbh->close ();
> 
> then is the locking issue not so much the distance between the "new" and the 
> dbclose, as the distance between the query and its finalize() ? If this is 
> the case then I can simply gather the results I need from the select and do 
> the time-consuming loop as a separate step.

Correct.  While your query is running (until you have finalized or reset it) 
you have a lock on the database.  In the above code, other processes will have 
no access from the ->query() to the ->finalize().  So you lock the database 
just once but it will last for a long time.

If you looped around ->fetchArray and read the result of the query into an 
array first, you could release the lock sooner and let other processes do 
things while you work out what updates you want to do.  This would mean your 
code required three locks on the database, but for a shorter total time.  It 
would also mean you would need enough memory free to store in all the rows from 
the query at once.  So if your query could return millions of rows perhaps you 
shouldn't do that.  Also it would let other processes make changes to the 
database before you executed your UPDATE commands, which might invalidate them. 
 But if you know your program would never do this, then that's okay.

PS: fetchArray loops can be done neater like this, which does the assign and 
the termination test in one:

while ($reg = $res->fetchArray (SQLITE3_ASSOC))
{
...
}

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Opening and closing an SQLITE3 db in PHP

[Tim Streater]

On 19 Mar 2014 at 00:53, Simon Slavin  wrote: 

>> 1) I start with $dbh = new sqlite3 ($dbname); Am I right in thinking that
>> this does not explicitly open the db (and hence acquire a lock), but that the
>> db is opened and the default lock (SQLITE_OPEN_READWRITE |
>> SQLITE_OPEN_CREATE) is acquired only when the first query is done?
>
> Having a database connection open will not cause a database lock.  Locks occur
> only when a transaction is open.  If you are using BEGIN and END/COMMIT
> correctly, you are doing what you can to minimise locks.

OK.

>> 2) If I want to use a different flag such as SQLITE_OPEN_READONLY do I need
>> to do an explicit open such as $dbh->open ($dbname, SQLITE_OPEN_READONLY); or
>> is there a way to specify the flag(s) with "new sqlite3" ?
>
> 'new' and 'open' are equivalent here, since the __construct() class for
> sqlite3 (which is what using 'new' does) calls open.  You can feed either with
> the same parameters:
>
> 
> 

So I could do:  $dbh = new sqlite3 ($dbname, SQLITE_OPEN_READONLY); ??

>> 3) Is the lock always released if I do $dbh->close(); ?
>
> No longer need to answer this, but it brings up the fact that, if you haven't
> already released the lock by closing all the transactions on the database, the
> ->close() will fail because you have unfinished work.  So that's another
> reason to be fussy about using transactions correctly.

So suppose I have this sequence (ignoring any error handling for clarity):

$dbh = new sqlite3 ($database);
$res = $dbh->query ('select ...');

while  (true)
 {
 $reg = $res->fetchArray (SQLITE3_ASSOC);
 if  ($reg===false)  break;
 // Do stuff with this row of results - may take some minutes
 $dbh->exec ('update ...');
 // Do more stuff  - may take some minutes
 $dbh->exec ('update ...');
 }

$res->finalize ();
$dbh->close ();

then is the locking issue not so much the distance between the "new" and the 
dbclose, as the distance between the query and its finalize() ? If this is the 
case then I can simply gather the results I need from the select and do the 
time-consuming loop as a separate step.

Thanks for the insights (and Dan, too).



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Opening and closing an SQLITE3 db in PHP

[Dan Kennedy]

On 03/19/2014 06:49 AM, Tim Streater wrote:

Part of my app will, at user request, read some data from an SQLite db and also 
some files from disk, and send it all out on the network. This may in some 
cases take several minutes, at the end of which the db gets updated here and 
there. While this is happening, the user may wish to do another action that 
will require read/write access to this db. Typically this will be another 
process.

As it happens I'd not tried exactly the above until yesterday, at which point I got 
"database locked" after my timeout period expired. Although I'm careful to 
finalize() and close() as appropriate, the way things are structured at present the 
database is left open while the slow processing (writing to the network) takes place.

I can rejig this without too much trouble, but for various reasons it's not just a simple 
case of "closing the db earlier", and so I'd like to be sure I understand how 
things work before starting. I'm using PHP's sqlite3 class rather than PDO, and I've done 
nothing explicit about the journal mode.

My questions are:

1) I start with $dbh = new sqlite3 ($dbname); Am I right in thinking that this 
does not explicitly open the db (and hence acquire a lock), but that the db is 
opened and the default lock (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE) is 
acquired only when the first query is done?


Calling sqlite3_open() does open the database file. But it does not read 
any data from the db (not even enough to check that the file really is a 
database file). So no lock is required.


Locks are only held while transactions are running. A transaction may be 
started and ended explicitly using "BEGIN" and "COMMIT" or "ROLLBACK". 
Or, if you don't start one explicitly, SQLite automatically opens and 
closes a separate transaction for each statement executed.





2) If I want to use a different flag such as SQLITE_OPEN_READONLY do I need to do an 
explicit open such as $dbh->open ($dbname, SQLITE_OPEN_READONLY); or is there a way to 
specify the flag(s) with "new sqlite3" ?


I don't think you need to do this.




3) Is the lock always released if I do $dbh->close(); ?


Yes, assuming the sqlite3_close() call succeeds. But all locks are also 
released when the current transaction is closed, which might be more 
convenient that closing the entire database.





4) Would it just be simpler to switch to WAL mode?


There's a good chance it will be, yes.

Dan.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Opening and closing an SQLITE3 db in PHP

[Simon Slavin]

On 18 Mar 2014, at 11:49pm, Tim Streater  wrote:

> I'm using PHP's sqlite3 class rather than PDO, and I've done nothing explicit 
> about the journal mode.

Good.  From PHP using sqlite3 is more efficient and leads to simpler 
programming than using the PDO.

> My questions are:
> 
> 1) I start with $dbh = new sqlite3 ($dbname); Am I right in thinking that 
> this does not explicitly open the db (and hence acquire a lock), but that the 
> db is opened and the default lock (SQLITE_OPEN_READWRITE | 
> SQLITE_OPEN_CREATE) is acquired only when the first query is done? 

Having a database connection open will not cause a database lock.  Locks occur 
only when a transaction is open.  If you are using BEGIN and END/COMMIT 
correctly, you are doing what you can to minimise locks.

In addition SQLite contains a strange optimization that the 'sqlite3_open()' 
call doesn't access the database file at all.  Use of the operating system's 
'open' command is deferred until the first operation that needs to read or 
write the file.  All 'sqlite3_open()' does it note things that will be used 
when SQLite3 really does need the data file.

> 2) If I want to use a different flag such as SQLITE_OPEN_READONLY do I need 
> to do an explicit open such as $dbh->open ($dbname, SQLITE_OPEN_READONLY); or 
> is there a way to specify the flag(s) with "new sqlite3" ?

'new' and 'open' are equivalent here, since the __construct() class for sqlite3 
(which is what using 'new' does) calls open.  You can feed either with the same 
parameters:




But you should really use 'new' every time since that's the only way to get 
back the connection handle that you will use to talk to the connection.

> 3) Is the lock always released if I do $dbh->close(); ?

No longer need to answer this, but it brings up the fact that, if you haven't 
already released the lock by closing all the transactions on the database, the 
->close() will fail because you have unfinished work.  So that's another reason 
to be fussy about using transactions correctly.

> 4) Would it just be simpler to switch to WAL mode?

There might be a speed improvement from this, but generally you need to solve 
any problems encountered with locking anyway just to ensure that your code is 
clean and you know what it does. 

Simon. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Opening and closing an SQLITE3 db in PHP

[mm.w]

Hello, you should ask that on the php stream, they would probably know
better about their wrapper implementation.

try catching up with Ilia.

Best Regards


On Tue, Mar 18, 2014 at 4:49 PM, Tim Streater  wrote:

> Part of my app will, at user request, read some data from an SQLite db and
> also some files from disk, and send it all out on the network. This may in
> some cases take several minutes, at the end of which the db gets updated
> here and there. While this is happening, the user may wish to do another
> action that will require read/write access to this db. Typically this will
> be another process.
>
> As it happens I'd not tried exactly the above until yesterday, at which
> point I got "database locked" after my timeout period expired. Although I'm
> careful to finalize() and close() as appropriate, the way things are
> structured at present the database is left open while the slow processing
> (writing to the network) takes place.
>
> I can rejig this without too much trouble, but for various reasons it's
> not just a simple case of "closing the db earlier", and so I'd like to be
> sure I understand how things work before starting. I'm using PHP's sqlite3
> class rather than PDO, and I've done nothing explicit about the journal
> mode.
>
> My questions are:
>
> 1) I start with $dbh = new sqlite3 ($dbname); Am I right in thinking that
> this does not explicitly open the db (and hence acquire a lock), but that
> the db is opened and the default lock (SQLITE_OPEN_READWRITE |
> SQLITE_OPEN_CREATE) is acquired only when the first query is done?
>
> 2) If I want to use a different flag such as SQLITE_OPEN_READONLY do I
> need to do an explicit open such as $dbh->open ($dbname,
> SQLITE_OPEN_READONLY); or is there a way to specify the flag(s) with "new
> sqlite3" ?
>
> 3) Is the lock always released if I do $dbh->close(); ?
>
> 4) Would it just be simpler to switch to WAL mode?
>
> My rejigging would involve closing db's when I'm about to do something
> potentially slow and then and re-opening them later but I don't think that
> would cause significant overhead.
>
>
>
> --
> Cheers  --  Tim
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Opening and closing an SQLITE3 db in PHP

[Tim Streater]

Part of my app will, at user request, read some data from an SQLite db and also 
some files from disk, and send it all out on the network. This may in some 
cases take several minutes, at the end of which the db gets updated here and 
there. While this is happening, the user may wish to do another action that 
will require read/write access to this db. Typically this will be another 
process.

As it happens I'd not tried exactly the above until yesterday, at which point I 
got "database locked" after my timeout period expired. Although I'm careful to 
finalize() and close() as appropriate, the way things are structured at present 
the database is left open while the slow processing (writing to the network) 
takes place.

I can rejig this without too much trouble, but for various reasons it's not 
just a simple case of "closing the db earlier", and so I'd like to be sure I 
understand how things work before starting. I'm using PHP's sqlite3 class 
rather than PDO, and I've done nothing explicit about the journal mode.

My questions are:

1) I start with $dbh = new sqlite3 ($dbname); Am I right in thinking that this 
does not explicitly open the db (and hence acquire a lock), but that the db is 
opened and the default lock (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE) is 
acquired only when the first query is done?

2) If I want to use a different flag such as SQLITE_OPEN_READONLY do I need to 
do an explicit open such as $dbh->open ($dbname, SQLITE_OPEN_READONLY); or is 
there a way to specify the flag(s) with "new sqlite3" ?

3) Is the lock always released if I do $dbh->close(); ?

4) Would it just be simpler to switch to WAL mode?

My rejigging would involve closing db's when I'm about to do something 
potentially slow and then and re-opening them later but I don't think that 
would cause significant overhead.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users