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
