Please can I formally propose that, for Linux: 1. A Pragma or other compile-time option is created such that SQLITE_OK is not issued on file writes/modifications/deletes until the hardware indicates that all associated Directory syncs, etc., are done. 2. Since the absence of 1. appears to break the 'D' in ACID, the option is set on by default, but the docs make the point that it could produce a substantial performance hit. The docs could also make the point that if the hardware returns incorrect status to the OS, then, even if the option is on, 'D' may still not be true (but not Sqlite's fault).
Best regards On Mon, Jan 25, 2016 at 3:47 PM, Richard Hipp <drh at sqlite.org> wrote: > On 1/25/16, Matthias-Christian Ott <ott at mirix.org> wrote: > > > > Does this mean that if I use SQLite SQLITE_EXTRA_DURABLE=0, PRAGMA > > journal_mode=DELETE and PRAGMA synchronous=FULL, SQLite could loose a > > transaction that it said to be committed depending on the VFS? > > Sort of. This appears to be true if you are running on QNX and you > lose power (or do a hard reset) shortly after the transaction commits. > It might be the case on other OSes/filesystems but it has never before > been observed. > > This is not new behavior. This is apparently what SQLite has been > doing for 15 years, across quadrillions of transactions on many > billions of devices, and it has never before caused any issues, until > just recently when Mr. Meinlschmidt upgraded to a newer version of > QNX. > > > > > If so, why isn't SQLITE_EXTRA_DURABLE=1 the default? Should correctness > > be more important than performance, except when the constraints are such > > that correctness has to be sacrificed for performance? > > > > The trade-off that is described in the description of SQLite > > SQLITE_EXTRA_DURABLE reads like an excerpt from the MySQL manual when > > MyISAM was still widely used. Perhaps I'm also too irritated by > > discussions with advocates of MySQL who would argue against the fact > > that proper transactions were necessary because the DBMS would be faster > > without them. That is not to say that the ACID properties and > > transactions solve every concurrency or correctness problem but they > > help significantly. > > > > As you point out, it is an engineering tradeoff. > > The feedback I receive is that most users of SQLite would much rather > avoid the extra directory syncs, even if it means having the last > transaction rollback following a power loss. Most developers do not > care that much about durability, at least not enough to want to take > the performance hit of syncing the directory after every unlink. > Non-durable commits on power-loss have long been the default in WAL > mode (run-time fixable by setting PRAGMA synchronous=FULL) and nobody > has before ever complained. Most people consider this a feature. In > fact, if I recall correctly, we first made synchronous=NORMAL the > default in WAL mode by popular request. WAL mode used to default to > power-loss durable but people requested the change for performance > reasons. > > Note especially that this is about durability, not consistency. > SQLite guarantees consistency regardless. People care about > consistency. Durability, not so much. I'm not a MySQL expert, but I > think the historical complaints about MyISAM had more to do with > consistency than with durability, did they not? > > -- > D. Richard Hipp > drh at sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
