I do not observe any loss in durability in WAL mode: it works totally fine.
As for the documentation, http://www.sqlite.org/transactional.html and http://www.sqlite.org/features.html claim that SQLite is durable during power failures; and DELETE is the default journal_mode. Also, other pages, http://www.sqlite.org/pragma.html#pragma_synchronous, http://www.sqlite.org/atomiccommit.html, and http://www.sqlite.org/faq.html, made me think that rollback journaling ensures durability (given a honest disk and a "honest" OS), although those pages do not "legally" say that; I'm not sure if others would understand it this way though. The usual opinion in blogosphere (and forums) also seems to be that SQLite is, by default, durable across power failures, though they might be meaning only about a "5 second eventual durability". Finally, (I hope I do not come across here as being authoritative), a quick fix might be explicitly mentioning somewhere in the documentation that DELETE and TRUNCATE modes do not ensure immediate durability after a power loss; this would combat any wrongly-understood claims in the rest of the documentation. Also, not to spam, but it would be great if you could answer these questions for my research (you might send me a reply directly without going through the mailing list): [a] Was it always understood that unlink() and ftruncate() are not synchronous, and that SQLite transactions in DELETE mode are not immediately-durable in Linux; or had you initially misunderstood the semantics of those calls, or left-off the fsync() because of a typo error? [b] While designing the crash-tests, were the semantics of the calls in Unix understood? What if ftruncate() not being synchronous did lead to a consistency-loss? Was it reasoned-out that the non-synchronous ftruncate would not produce corruption? [c] How much of a loss in durability (what other than 5 seconds) would be "good enough" in most cases? ....... Again, sorry for the spam; my research is trying to make sense of the flushing-mess in the entire storage stack, and feedback would be extremely useful. PS: @Richard Hipp and other developers - thanks for SQLite! Amazing piece of software. -- Thanumalayan Sankaranarayana Pillai (Graduate student at the University of Wisconsin-Madison) On Wed, May 22, 2013 at 5:49 AM, Richard Hipp <[email protected]> wrote: > > > On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad <[email protected]>wrote: > >> >> Expected result: You always find that the transaction had been executed. >> Observed result: You sometimes find that the transaction did not execute. >> > > The core team has discussed this. In order to avoid a substantial > performance hit against transaction COMMIT, we have chosen to not do fsyncs > on the directory when a file is unlinked, and thus to allow loss of > durability following a power loss event. ACI without the D is still > guaranteed. But not the D. The overwhelming majority of applications care > not one wit about durability following power loss. For most applications, > it is sufficient that the file is uncorrupted. If recovery gives you a > snapshot of the file as it existed 5 seconds prior to the power loss, > that's fine. > > WAL-mode transactions should be durable across power-loss events. So if > durability is vitally important to you, you can always set PRAGMA > journal_mode=WAL. Are you observing loss of durability following power > loss in WAL mode? > > Is there any place in the documentation that we have overlooked where > SQLite claims to be durable across a power loss in rollback mode? > > > > -- > D. Richard Hipp > [email protected] > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
