On 19 Mar 2014, at 11:21am, Tim Streater <t...@clothears.org.uk> 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
