On Wed, 8 Feb 2006, Xavier Noria wrote: >On Feb 8, 2006, at 17:10, Doug Nebeker wrote: > >> When you don't wrap everything in a transaction, each statement becomes >> it's own transaction. And the database file is opened, updated, and >> closed on each transaction. So your first case had roughly 50 times >> the amount of file I/O and transaction startup/commit overhead as the >> second case. > >I see. Let me ask a few more questions to help me get the picture. > >A transaction is stored in memory until committed? As a rule of thumb >can I imagine that one transaction equals roughly to a single open/ >edit/close? Is the file opened and locked right when the transaction >begins?
It is held in memory until it is committed, or the page cache is splilled. At that point, the database file is locked for writing, a rollback journal created, old page values written to the rollback journal and new page values written to the database file. It is this creation of files that must be done synchronously for durability, and is done for each statement level transaction in the original example (and once for the large transaction.) So no, the file is not opened and locked right away, unless you explicitly tell SQLite to using "BEGIN EXCLUSIVE;". If you're interested in the details, check out: http://www.sqlite.org/lockingv3.html http://www.sqlite.org/lang_transaction.html > >-- fxn > > > -- /"\ \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
