Just to throw in my $0.02 as a user.... Given the SQL stream of...
<misc SQL in transaction> COMMIT <power loss> Vs. <misc SQL in transaction> <power loss> <unexecuted COMMIT> Except in cases where, in the first example, I have time to inform someone about the COMMIT before the power loss, there's no functional difference between the two events. I would hate to think I would ever demand that SQLite guarantee the commit occurs regardless of how quickly the power loss happens after the commit statement. For a huge majority of the applications I've dealt with (and I say huge instead of all only in case there's one I've forgotten about), the timing difference doesn't matter. What matters is that when I come back up I have an intact database. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of thanumalayan mad Sent: Wednesday, May 22, 2013 8:31 AM To: Richard Hipp Cc: General Discussion of SQLite Database Subject: Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous 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 <d...@sqlite.org> wrote: > > > On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad <madth...@gmail.com>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 > d...@sqlite.org > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
