On Tue, Sep 11, 2012 at 03:11:57PM +1200, Keith Chew scratched on the wall: > Hi Jay > > > http://www.sqlite.org/pragma.html#pragma_synchronous > > > > When synchronous is FULL (2), the SQLite database engine will use > > the xSync method of the VFS to ensure that all content is safely > > written to the disk surface prior to continuing. This ensures > > that an operating system crash or power failure will not corrupt > > the database. FULL synchronous is very safe, but it is also > > slower. When synchronous is NORMAL (1), the SQLite database > > engine will still sync at the most critical moments, but less > > often than in FULL mode. There is a very small (though non-zero) > > chance that a power failure at just the wrong time could corrupt > > the database in NORMAL mode. But in practice, you are more likely > > to suffer a catastrophic disk failure or some other unrecoverable > > hardware fault. > > > > >From what I have read so far, my understanding is consistent with your > explanation (except that I didn't realise corruption can happen in > NORMAL, only lost of data).
In the case of both NORMAL and FULL (in the case of non-WAL mode), if the commit is successful, there should never be data loss and the changes described by the transaction should become part of the database state. If the commit did not finish, the changes described in the transaction were never part of the database state to start with and are not considered "lost". In no case should NORMAL or FULL allow a commit to succeed, but not have the transaction be durable. ...except if the disks lie. Which, as Simon has pointed out, is most of the time. > Regardless, I would really like to hear > from a developer that the above paragraph also applies to the WAL > journal mode, and not just the older journal modes, since WAL was > introduced later in 3.7 onwards. It does not. The transaction model in WAL mode is totally different, so the journal modes are as well. > Because of the architecture change in WAL, I was hoping that the > durability can still be preserved while using NORMAL. WAL plays by a slightly different set of rules. The docs describe this fairly well, but from the sound of it you need FULL for durability. On the other hand, WAL requires fewer write to commit a transaction, so (if I'm reading this correctly) FULL in WAL mode is much faster than FULL in non-WAL mode. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
