On 24 February 2016 at 23:46, Igor Tandetnik <igor at tandetnik.org> wrote:
> On 2/24/2016 10:08 AM, Stephen Chrzanowski wrote: > >> IMO, all that, plus the fact that you have an easy roll back mechanism. >> Anything that needs to be put in the database is external to the pristine >> database. Lock the database with a transaction, fill up the journal, the >> power goes out, your pristine database isn't touched. >> > > Rollback journal works the other way round. New data is written to > database file; previous content of overwritten pages is copied to journal > file. 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*. > Committing a transaction is fast - simply delete the journal. So this is not quite true. It's only[1] at commit time that the pages modified in memory are paged back to disk, and this concentration of I/O makes commit relatively expensive. But there is a reason for this - it improves concurrency. As you point out, the database file is not "pristine" after the first modified page is written to disk, until the journal file is deleted (which marks the end of the commit phase and finalises the transaction). Clearly other processes must be locked out of the database for this phase. By deferring modification on disk as long as possible, sqlite maximises the amount of time other processes can continue to read the database. [1] or if its memory cache is exceeded, sqlite will obtain the EXCLUSIVE lock and modify the database before commit time. See pragmas cache_size and cache_spill. -Rowan
