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
