(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.
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).
- 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!
"A close() operation on one connection might unknowingly clear the locks on a different database connection, leading to database corruption."
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.