>> Using the standard defaults (which avoid WAL), is there any >> possibility whatsoever of that last SQL transaction being lost? > > I have an unusual answer: Yes, and it doesn't matter.
While I agree in principle, your answer depends on some assumptions that need not hold. > Let's suppose, as you did, that the application got back "Commit > Successful" followed quickly by a power failure. You want to know, > could the transaction be lost anyway? But I ask you, what action could > the application possibly take, in that subsecond interval, that it > matters? Under the QNX OS using a QNX6 filesystem with default configuration, that ?subsecond interval? is actually up to 10s. For any non-journalling filesystem (SD cards, anyone?) mounted without immediate write-through (for efficiency) on Linux the interval can be, IIRC, up to 30s. So how much can happen in this period is very sensitive to details not necessarily under control of or even available to the SQLite user. The application could for example write to some non-SQLite storage (other file system, raw flash, physical journal printout, ?) and try to guarantee global consistency by waiting for the SQLite transaction to complete. Like the user reading ?saving OK? and throwing away the Post-It with the original information. Or (what we did) it could shut off device power. > There is no God's-eye view of application state. The important service > provided by the DBMS is not "what's committed is definitely saved", but > rather that "what's committed is definitely *consistent*". So when your application requires consistency of some broader scope, you need the DBMS give you enough rope^h^h^h^h^h^h^h^h^h^h^hthe tools to implement that yourself. Without a durability guarantee you're screwed. The more frequent simpler usecases of course are not susceptible to that and then indeed it doesn't matter. S.M. -- Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer Am Wolfsmantel 46, 91058 Tennenlohe, Germany Tel: +49-8458-3332-531 stefan.meinlschmidt at esolutions.de Fax: +49-8458-3332-20-531
