On 18 Mar 2014, at 11:49pm, Tim Streater <t...@clothears.org.uk> 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: <http://www.php.net/manual/en/sqlite3.construct.php> <http://www.php.net/manual/en/sqlite3.open.php> 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
