I’m seeing conflicting information about SQLite’s use of F_FULLFSYNC on macOS when committing transactions. This is making me nervous about durability and the possibility of database corruption.
The SQLite docs for PRAGMA fullfsync (https://www.sqlite.org/pragma.html#pragma_fullfsync <https://www.sqlite.org/pragma.html#pragma_fullfsync>) say: > This flag determines whether or not the F_FULLFSYNC syncing method is used > on systems that support it. The default value of the fullfsync flag is off. > Only Mac OS X supports F_FULLFSYNC. I verified in the built-in sqlite3 tool on macOS 10.12.1 that the result of `pragma fullfsync` is 0. My understanding is that issuing a F_FULLFSYNC ioctl call is important for ensuring that all writes have been flushed to physical storage, since disk controllers may lie and ignore a regular sync instruction, leaving some writes in their volatile cache memory. (I was working at Apple, on a project using SQLite, during the development of OS X 10.4 circa 2006; there were a number of databases that got corrupted by kernel panics or forced power-off, until Apple had the F_FULLFSYNC call added to SQLite.) Apple’s documentation for Core Data (which uses SQLite) says: (https://developer.apple.com/library/content/documentation/Cocoa/Conceptual/CoreData/PersistentStoreFeatures.html <https://developer.apple.com/library/content/documentation/Cocoa/Conceptual/CoreData/PersistentStoreFeatures.html>) > In OS X the fsync command does not guarantee that bytes are written, so > SQLite sends a F_FULLFSYNC request to the kernel to ensure that the bytes are > actually written through to the drive platter. This request causes the kernel > to flush all buffers to the drives and causes the drives to flush their track > caches. Without this, there is a significantly large window of time within > which data will reside in volatile memory. If system failure occurs you risk > data corruption. This contradicts the SQLite docs, but it does match my understanding, and until today I thought that SQLite on macOS enabled fullfsync by default. So which of these is true? • F_FULLFSYNC is no longer necessary for full durability (due to some change in the way SQLite commits?) or • SQLite by default sacrifices durability and data integrity for performance by skipping the [slow] F_FULLFSYNC call This is disturbing because we do have one developer using our framework who has been reporting occasional database corruption. He says it’s happened to users, and he claims to be able to make it happen by forcibly powering-down his laptop (though we haven’t been able to reproduce it.) —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
