SQLite on macOS Not ACID
Jonathan Johnson (2022):
From my investigation, Apple’s version of SQLite instead replaces
PRAGMA fullfsync = on’s implementation to useF_BARRIERFSYNC.SQLite users who are expecting
PRAGMA fullfsyncto provide durability guarantees in the event of power failures or kernel panics can override xSync via a custom VFS or build SQLite from source.[…]
Apple’s documentation clearly states that for any guarantees about data loss due to power loss or kernel panic, you must use the
fcntl()API with theF_FULLFSYNCcommand.[…]
For most consumer applications,
F_BARRIERFSYNCwill be enough to provide reasonable durability with the benefit of performing much more quickly. However, there are some situations where true ACID compliance is desired.[…]
It’s very confusing when a feature that’s documented to be specific to macOS doesn’t behave as documented on macOS.
Although, SQLite itself is open-source, as are many parts of macOS and iOS, he says that the source for Apple’s SQLite is not available.
Unfortunately, SQLite’s documentation about its durability properties is far from clear. I cannot tell whether SQLite is durable by default, and if not, what are the minimal settings you need to use to ensure durability.
[…]
A Hacker News commenter who agrees with my reading of the documentation asked Hipp how his comment is consistent with the documentation, but received no reply.
Hipp also says that WAL mode used to be durable by default, but it was changed after people complained about poor performance. This surprised me, since I had the impression that SQLite cared deeply about backwards compatibility, and weakening the default durability setting is a nasty breaking change for any application which needs durability.
[…]
My takeaway is that if you need durability, you’d better set the
synchronousoption explicitly because who knows what the default is, or what it will be in the future. With WAL mode, FULL seems to suffice. As for DELETE mode, who knows if FULL is enough, so you’d better go with EXTRA to be safe. And if your application might be used on macOS, enablefullfsync.
Previously:
1 Comment RSS · Twitter · Mastodon
Nitpick — SQLite isn’t just “open source”, it’s public domain, meaning it literally belongs to everybody and there is no restriction at all on forking it or changing it in any way.
https://sqlite.org/copyright.html
Which means that Apple isn’t violating the terms of the SQLite license by keeping their implementation proprietary, because using a public domain license doesn’t preclude anybody from doing whatever they want with it, including publishing closed-source derivatives.
(That said, if Apple’s fork isn’t ACID compliant, but the upstream version is, this is a curious decision on their part, and arguably a bug that should be fixed.)
