SQLite on macOS: Not ACID compliant with the bundled version (2022)

bonsaidb.io

74 points·tempodox··7 comments

From the linked thread, the Apple port is already doing the right thing in WAL mode which is what pretty much every SQLite client should be using these days [1]. Not sure why you’d be using delete journal mode in 2024 when WAL has existed for more than a decade now.

[1] https://twitter.com/numist/status/1536830264214638593

How do you make sure you are using WAL mode? Is it a configuration when you new up a client or something?

You issue `pragma journal_mode=wal` once after creating the database. The change is persistent.

https://www.sqlite.org/wal.html#persistence_of_wal_mode

It's not the default and in most use cases there's no pressing reason to change it. (Unless you know that Apple has broken the default mode data consistency guarantees...)

Secondarily there's also a list of disadvantages listed for the WAL mode in the SQLite docs, and some others besides, eg infinitely growing WAL files if there's always readers around.

I don’t remember if the system default was changed but the default for all databases created by Apple apps was switched to WAL mode in the iOS 4 timeframe.

The infinitely growing WAL problem is a real problem though. Often that is caused by devs accidentally leaking or abandoning a never closed read transaction. I think there’s a new wal2 mode in development to address that issue. Obviously the best solution for now is to not have such long lived readers in the first place.

And this is one reason I am going to ship SQLite as a vendored dependency for my stuff.

Given that most macs have built-in battery, the risk is not that big, I guess.

Reply on news.ycombinator.com