On 22 Oct 2013, at 5:31am, romtek <rom...@gmail.com> wrote: > I started writing this message and then read > http://www.sqlite.org/lang_transaction.html, and I think I've gotten my > answer, but I will ask the question to be absolutely certain, particularly > when using SQLite with PHP (using PDO).
If you are writing code specifically intended for use with SQLite (like your library was meant to be) then please use PHP's sqlite3 library rather than the PDO. The advantage of using PDO is that if you transfer from one DBMS to another, you only need to make minimal changes to your code. But the disadvantage is that it doesn't understand how SQLite works very well, and cannot take advantage of the things it does well or return the results it produces well. The PDO library is useful if you are producing one set of code that must work with lots of different SQL engines but because it's not native to any of them it is its own layer that needs separate understanding and debugging. PHP's sqlite3 library, on the other hand, contains just enough code to let you handle sqlite3 databases using conventional PHP calls and objects, rather than having to write C code. It doesn't need much documentation because you just look up the equivalent functions in the SQLite3 documentation. It can do everything that SQLite3 does, and provide all the information that SQLite3 does. [Exaggeration, but that's the idea.] > Probably based on flawed understanding of how SQLite worked, I've spent > (wasted, I see now) my time to create a library for working with my DBs > with an intention to minimize the time a script has an open connection to a > DB file. And I've tried to make sure that I close connections as soon as > possible. The document linked to above says that having a connection or > executing a regular BEGIN statement doesn't create a lock. > > So, if my new > understanding is correct, I should only be concerned with shortening > execution of code within transaction blocks and not at all with how long a > connection is held by a script. Is this correct? This is correct. Having a connection open to the database doesn't lock the database. Starting a transaction (with BEGIN) doesn't lock the database unless you use BEGIN IMMEDIATE or BEGIN EXCLUSIVE. A database lock lasts only from the first command within the transaction to the COMMIT. In most handling from PHP all you need to do is open your database, set a timeout of, perhaps, 10 seconds: <http://www.php.net/manual/en/sqlite3.busytimeout.php> then proceed with whatever you want to do. $DBhandle = new SQLite3('path/to/file.ext'); $DBhandle -> busyTimeout(10000); // 10 seconds ... do stuff with $DBhandle here $DBhandle -> close(); I am concerned that you may be worrying unduly about the time your database is locked. It sounds like premature optimisation. It's often better to just write elegant code that does things when your program wants them done and worry about locking only if it turns out to be a problem. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
