Hi Marc, Thanks for your comments! I just got confused that some SQLite webpages ( http://www.sqlite.org/transactional.html, http://www.sqlite.org/features.html) mention that transactions are durable after a power loss (the D in ACID); nowhere has it been mentioned that "immediate durability after a commit" is true only when the journal_mode is explicitly set to WAL. The documentation, however, was thorough regarding stuff like bad disks and bad OSes, and how they affect the integrity/corruption of the database.
My view had been fully biased: I was looking at some applications that use MySQL and such, acting as nodes in a network, and they require this "immediate durability" guarantee. I totally understand now that SQLite is aimed at an entirely different set of applications that don't require "immediate durability", but would rather that the database is fast. Thanks again, Thanu On Thu, May 23, 2013 at 8:00 AM, Marc L. Allen <[email protected]>wrote: > 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: [email protected] [mailto: > [email protected]] 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 <[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 > > > 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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
