SQLite and ext3 journalling mode

[Ted Ts'o]

On Wed, Dec 08, 2010 at 11:56:21AM -0500, Richard Hipp wrote:
> 
> Thanks, Ric.  Yes, we are numbered among the "annoying users".  Based on
> what you are telling us, we'll recommend that people use data=ordered,
> barrier=1 for maximum data reliability in the face of power loss.

Note that given that SQLite uses fsync() properly, data=ordered
shouldn't hurt or help --- with respect to the very narrow issue of
data integrity in the SQLite database.

What data=ordered protects against is the possibility that stale data
(from a deleted file, possibly from some other user's) might show up
as "garbage" in a file whose newly allocated blocks didn't get written
out to disk before a system crash or other unclean shutdown.

To the extent that SQLite properly uses fsync() and doesn't pay
attention to data in parts of the db file that hasn't been committed
yet, this won't matter.  From the perspective of users who care about
stale data showing up in a file being a potential security issue, they
should definitely use data=ordered in ext3.

However, if you have a specific use case where the only thing that
matters is SQLite, then assuming that fsync() is being used properly,
SQLite itself should be OK.

I hope this helps,

						- Ted