On 19 Mar 2014 at 00:53, Simon Slavin <slav...@bigfraud.org> 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: > > <http://www.php.net/manual/en/sqlite3.construct.php> > <http://www.php.net/manual/en/sqlite3.open.php> 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
