On Tue, Sep 11, 2012 at 01:58:23AM +0100, Simon Slavin scratched on the wall: > On 11 Sep 2012, at 12:55am, Keith Chew <keith.c...@gmail.com> wrote:
> > and I know FULL (1) will provide that. The question is why > > NORMAL (1) cannot provide the same. > > Because NORMAL doesn't flush changes to disk after every single > transaction. It queues a few of them up and flushes them all in one go. That's not quite true. Committing a transaction takes more than one disk write. As I understand it, in FULL mode, the disk is sync'ed after each and every write. In NORMAL mode, all of the writes required to commit a transaction are made in quick succession, but the disk is only sync'ed after the last write. In most practical situations, my guess is that the sync takes longer than the writes (since the writes are mostly to the OS file-system buffers anyways). That means, for a very, very short time during the final commit process (microseconds, most likely), there are pending buffered writes. If the system were to lose power between one of these writes and the final sync, there is a very-small-but-non-zero chance the database could become corrupt. But the disk is still fully sync'ed, to the best of SQLite's ability, after each and every commit. In NORMAL mode, commits are still fully durable. > NORMAL) Faster but if you lose power more transactions are lost, but > the database is still not corrupt. > FULL) Every COMMIT will take more time, but a power-failure can > lose a maximum of one transaction. That's also incorrect. In both cases, if a transaction fully commits, you're golden. Transactions are fully durable. If COMMIT returned success, you should not lose the transaction. FULL mode, to the best of the SQLite developers' ability, protects against all corruption, but at a very high performance cost. NORMAL mode opens up an extremely small chance of corruption for a significant performance increase. The PRAGMA docs explain much of this: 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. -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
