On 27 February 2016 at 00:02, Igor Tandetnik <igor at tandetnik.org> wrote:
> On 2/26/2016 4:01 AM, Rowan Worth wrote: > >> In principle this is correct, but actually the database *file* is not >> immediately modified in rollback mode. Instead when a page is modified the >> original contents are saved to the rollback journal, and the page is >> updated *in memory*. >> > > ... until such time as the cache needs to be spilled - then it's updated > in the database file. In fact, I'm pretty sure the rollback journal is not > created as long as all the changes are entirely in RAM. > The rollback journal is always created before the main database file is modified. This must happen for durability's sake - if the changes were only held in RAM then sqlite would have no way to recover a partially modified database caused by a power failure mid-transaction. I'm not sure whether modified pages are written to the journal file immediately or whether that also happens at COMMIT/cache-spill time. I simplified to make the main point stand out: it is not true that the > database file remains "pristine" while the transaction is in progress, and > changes are written only to the journal file. Instead, to the first > approximation, the opposite is true. > I agree it is not true in general, but it is mostly accurate for small transactions (where "small" means less than sqlite's memory cache). The default cache size is 2,000 pages, I'd imagine it depends a lot on the workload involved as to what percentage of transactions fit in the cache. -Rowan
