Re: [sqlite] Avoiding holding a lock for too long

[Tim Streater]

On 08 Feb 2014 at 17:48, Simon Slavin  wrote: 

> On 8 Feb 2014, at 4:58pm, Tim Streater  wrote:

>> I had a look at the PHP sqlite3 interface code, and it looks like ->query
>> does prepare, step, and reset (if there are no errors).
>
> If ->query() is doing 'step()' then the PHP code does not work the same way
> SQLite3 does internally.  SQLite would do 'step()' as part of ->fetchArray().

->query is only doing the one step(). If it gets SQLITE_ROW or SQLITE_DONE from 
that, then it does the reset, otherwise it returns an error. ->fetchArray() 
certainly does one step() each time it is called.

>>  Which of these obtains the lock on the db?
>
> The first 'step()'.  Before then all PHP needs to know is the structure of the
> database, not about the data in it.  The database needs to be locked from the
> first 'step()' to the last 'step()', though it can predict that it is finished
> if 'step()' returns 'no more rows'.

OK.

> So putting the above together you are still expected to use ->finalize() on
> the result set: it is the ->close() for that class and is the official way to
> release the handle.  Don't set it to null manually, use ->finalize() on it. 
> And, of course, eventually use ->close() on the database handle.
>
> Your solution may work for your test case, and it may work for this version of
> PHP using this version of SQLite, but I would recommend you use the API as
> documented.

OK - thanks, that's clearer now.



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

Re: [sqlite] Avoiding holding a lock for too long

[Simon Slavin]

On 8 Feb 2014, at 4:58pm, Tim Streater  wrote:

> On 07 Feb 2014 at 23:04, Simon Slavin  wrote: 
> 
>> You should not be manually setting any handle to null.  Try calling
>> ->finalize() on the statement and eventually ->close() on the database, which
>> do more than just setting their value.  If you are setting anything to null
>> manually, this may be causing the problem you report.
> 
> I made myself a simple testbed in PHP based on the code I posted last time. I 
> used a sleep call to make each turn round the while loop take a couple of 
> seconds, so the code took 20 secs to complete. In another Terminal window, I 
> ran a second script that tried to update the same database (with a 2000msec 
> timeout). Having established that this latter script got "database is 
> locked", I then rejigged the first script to fetch all the result rows before 
> entering the slow loop. This sufficed to allow the second script to run 
> without getting "database is locked" (i.e I didn't use ->finalize or ->close 
> in the first script).
> 
> I had a look at the PHP sqlite3 interface code, and it looks like ->query 
> does prepare, step, and reset (if there are no errors).

If ->query() is doing 'step()' then the PHP code does not work the same way 
SQLite3 does internally.  SQLite would do 'step()' as part of ->fetchArray().

>  Which of these obtains the lock on the db?

The first 'step()'.  Before then all PHP needs to know is the structure of the 
database, not about the data in it.  The database needs to be locked from the 
first 'step()' to the last 'step()', though it can predict that it is finished 
if 'step()' returns 'no more rows'.

So putting the above together you are still expected to use ->finalize() on the 
result set: it is the ->close() for that class and is the official way to 
release the handle.  Don't set it to null manually, use ->finalize() on it.  
And, of course, eventually use ->close() on the database handle.

Your solution may work for your test case, and it may work for this version of 
PHP using this version of SQLite, but I would recommend you use the API as 
documented.

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

Re: [sqlite] Avoiding holding a lock for too long

[Tim Streater]

On 07 Feb 2014 at 23:04, Simon Slavin  wrote: 

> You should not be manually setting any handle to null.  Try calling
> ->finalize() on the statement and eventually ->close() on the database, which
> do more than just setting their value.  If you are setting anything to null
> manually, this may be causing the problem you report.

I made myself a simple testbed in PHP based on the code I posted last time. I 
used a sleep call to make each turn round the while loop take a couple of 
seconds, so the code took 20 secs to complete. In another Terminal window, I 
ran a second script that tried to update the same database (with a 2000msec 
timeout). Having established that this latter script got "database is locked", 
I then rejigged the first script to fetch all the result rows before entering 
the slow loop. This sufficed to allow the second script to run without getting 
"database is locked" (i.e I didn't use ->finalize or ->close in the first 
script).

I had a look at the PHP sqlite3 interface code, and it looks like ->query does 
prepare, step, and reset (if there are no errors). Which of these obtains the 
lock on the db? I also looked at PHP's ->finalize but in the case where there 
are no more rows to return, it didn't seem to do much of anything except return 
FALSE. Where will the lock have been dropped in the first script?

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

Re: [sqlite] Avoiding holding a lock for too long

[Simon Slavin]

On 7 Feb 2014, at 10:44pm, Tim Streater  wrote:

> Now I'm finding that sometimes the "processing results here" can take a 
> minute or so, as it involves reading a file from disk and sending it to a 
> remote host. Instead of twiddling my thumbs waiting for this, I'm initiating 
> another action which inter alia involves doing an insert into mytable in 
> mydb, and yes, it gets "database is locked".
> 
> A simple way to fix this is to read the results into arrays in my loop above 
> (should be quick, rarely more than 10 rows), drop the database, and then have 
> a second loop to use the results, i.e. move "process results here" into the 
> second loop.

You don't seem to be calling ->finalize() on your result.

> But can I instead move setting the handle to null right after the query? Or 
> does that lose me the result set?

You should not be manually setting any handle to null.  Try calling 
->finalize() on the statement and eventually ->close() on the database, which 
do more than just setting their value.  If you are setting anything to null 
manually, this may be causing the problem you report.

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

[sqlite] Avoiding holding a lock for too long

[Tim Streater]

I have some PHP code, roughly as follows (ignoring error handling):

$dbh = new SQLite3 ('/path/to/mydb');
$res = $dbh->query ('select a,b,c from mytable where d=23');  // for example

while  (true)
 {

 $reg = $res->fetchArray (SQLITE3_ASSOC);
 if  ($reg===false)  break;

 // process results here (for each row)

 }

$dbh = null;


Now I'm finding that sometimes the "processing results here" can take a minute 
or so, as it involves reading a file from disk and sending it to a remote host. 
Instead of twiddling my thumbs waiting for this, I'm initiating another action 
which inter alia involves doing an insert into mytable in mydb, and yes, it 
gets "database is locked".

A simple way to fix this is to read the results into arrays in my loop above 
(should be quick, rarely more than 10 rows), drop the database, and then have a 
second loop to use the results, i.e. move "process results here" into the 
second loop.

But can I instead move setting the handle to null right after the query? Or 
does that lose me the result set?

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