On 2 May 2012, at 12:16pm, Lars Hansen <la...@me.com> wrote: > I'm sorry I have to bother you readers but I haven't understood > http://sqlite.org/lockingv3.html chapter 7.
I've answered your questions in order, but they're all interrelated, so please read them all before worrying about the first answer. For instance, the clearest and most useful answer is to your question 4. > 1. "In autocommit mode, all changes to the database are committed as soon as > all operations associated with the current database connection complete." > > How does SQLite know when ALL operations … complete? In PERL DBI context, I > may prepare and execute as many statements as I like. When does SQLite commit? I feel that that text is poorly worded. It seems to mean that the changes are committed when the connection to the database is closed, i.e. when _close() is used. This is not what happens. What it actually means is that the changes are committed when SQLite returns to autocommit mode. > 2. "The SQL command "COMMIT" does not actually commit the changes to disk. It > just turns autocommit back on." > > Is AutoCommit enabled after every commit? Do I have to disable AutoCommit > again after a commit to have it be disabled? Autocommit is usually on. You turn it off by explicitly opening a transaction yourself. Commmitting the transaction doesn't actually do any of the hard work, it just turns autocommit back on. autocommit immediately notices that there are pending operations to commit, and does the associated work. > 3. "If the SQL COMMIT command turns autocommit on and the autocommit logic > then tries to commit change but fails because some other process is holding a > SHARED lock, then autocommit is turned back off automatically. This allows > the user to retry the COMMIT at a later time after the SHARED lock has had an > opportunity to clear." > > If I had AutoCommit disabled and the commit fails, I have to retry to commit > myself. Is it possible (AutoCommit enabled or disabled) to have commits get > queued and executed when possible by SQLite so that I don't have to write > code myself but can rely on the order of commits (per connection)? No. Because if your commit is going to fail, which it might do after a long delay, your application will need to know that it has failed before it is able to proceed with other operations. In other words, if commits got queued up and executed later, you might have to undo any number of subsequent transactions. And that would require your application to keep track of them all, which would be difficult and annoying to program. However this normally does all come out in the wash. If you think about which database changes you want in the same transaction, you will find that your natural programming style leads to you wanting to know, when you issue your "COMMIT" to close the transaction, whether the commit succeeded or failed. > 4. "By default, SQLite version 3 operates in autocommit mode." > > Is SQLite transactional by default? Do I have to use transactions? I'd like > to. Anything besides begin and commit I have to consider? You should generally be using transactions with any dependent set of changes. However, if you have not declared your own transaction, and do an UPDATE or INSERT or DELETE, SQLite automatically wraps your operation in a 'BEGIN … op … COMMIT' for you. If the COMMIT it inserts after your operation results in an error, it returns that error for the operation rather than looking for a COMMIT you never issued. > 5. Does a "SELECT …" require a transaction begin and commit to transfer data? Although SELECT makes no changes to the database, in respect of database locking SELECT operates as described in my answer to question 4. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
