On Wed, May 02, 2012 at 01:16:41PM +0200, Lars Hansen scratched on the wall: > Hello, > > I'm sorry I have to bother you readers but I haven't understood > http://sqlite.org/lockingv3.html chapter 7. > > 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?
When the last active statement finishes execution. Prepared statements don't count, only active ones. Many programs only have one active statement at a time, but it can get more complex. Basically, SQLite keeps a reference count of how many statements are actually running. Any time that reference count hits zero, the database state goes through a commit. > 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? Yes. Any time there is not an explicit transaction open, autocommit is enabled. > Do I have to disable AutoCommit again after a commit to > have it be disabled? BEGIN. Start a new explicit transaction and autocommit will be disabled. > 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. Having and open transaction (e.g. a BEGIN) and having autocommit disabled are the same thing. You can't have one without the other. And yes, if COMMIT fails, the transaction is (usually) still open and if you want to proceed you have to manually retry. > 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)? Again, if you're inside an explicit transaction (BEGIN/COMMIT) then autocommit is always disabled. SQLite can, to a degree, retry COMMIT statements. But since there is no server to coordinate or queue requests, it isn't fully automatic, nor does it always work. See http://sqlite.org/c3ref/busy_handler.html > 4. "By default, SQLite version 3 operates in autocommit mode." > > Is SQLite transactional by default? Yes. Every command is run in the context of a transaction-- either an explicit one using the BEGIN/COMMIT commands, or an implicit, automatic autocommit transaction. > Do I have to use transactions? Explicit BEGIN/END transactions? No. If you don't, SQLite will wrap your commands in an automatic transaction. > I'd like to. Anything besides begin and commit I have to consider? Locking. Read: http://sqlite.org/lockingv3.html > 5. Does a "SELECT …" require a transaction begin and commit to transfer data? SELECT requires a transaction (for locking) but not an explicit BEGIN/COMMIT transaction. If you do not wrap SELECT in a BEGIN/COMMIT block, SQLite will-- as with any command-- wrap it in an automatic transaction. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
