The author forgets to mention that you need to access the same SQLite file within the same process (possibly different threads) using 2 different SQLite library instances, in order to trigger the problem.
I would imagine in most cases a mobile application would use (1) the SQLite lib bundled with Android to access system-related SQLite databases, and (2) its own SQLite lib to access application-specific SQLite databases (where you need to use specific SQLite features or whatnot), in which case there is no problem at all doing that.
(a) Yes, the author (me) does forget to mention that. Oops.
(b) Yes, it's true that there are plenty of uses cases which won't hit the problem. But for those that do, the consequences are pretty severe. And the distinction between those two classes of use cases is not obvious.
Thanks, yes, the article was unclear about that - in its discussion of ownership/POSIX I was confused if it was talking about the individual database files or some kind of centralized tracking file.
And yes, clarification of what cases I could expect both my connection and the OS connection to hit the same file would be good too. Because I wouldn't expect the OS to hit my own private sqllite dbs, and I wouldn't expect to be writing code that directly hits the OS dbs (instead of going through OS APIs to query them).
Here's a question: how does your app know which SQLite it's using? Or how does it actively say "ok, use the OS SQLite instead of the bundled one?" Aren't the symbol names the same?
I imagine that it's mostly in the background via libraries. E.g.:
- Access system-level SQLite databases via system APIs using the OS-linked SQLite. For example, you want to access the system config settings, so you use the iOS/Android API for doing so.
- Access your app's SQLite libraries using (e.g.) SQLCipher. For example, you want to store your app's preferences in a SQLite db, so long as SQLCipher manages to use a single SQLite library internally, then you're golden.
An example of running into issues could be:
- Using an ORM to access your App's SQLite db to read/write changes locally.
- Using a separate library to sync your App's SQLite db to desktop/"the cloud"/whatever.
Those two libraries could be using separate SQLite libraries, and be accessing the same SQLite db file. Oops!
It could be possible to resolve said issue so long as you make sure that only one library is accessing the SQLite database at a time. Basically your own in-app DB locking mechanism. Yay!
I would imagine in most cases a mobile application would use (1) the SQLite lib bundled with Android to access system-related SQLite databases, and (2) its own SQLite lib to access application-specific SQLite databases (where you need to use specific SQLite features or whatnot), in which case there is no problem at all doing that.