SQLiter `migrateIfNeeded()` and multiple processes

The PRAMGA user_version read for determining need of creation & migration is not part of the subsequent write transaction. It seems to be a plausible race condition, which multiple processes might coincidentially make the decision to run the same prime or migration schema simutaneously. Perhaps the write transaction should be applied to the whole migrateIfNeeded() scope?

https://github.com/touchlab/SQLiter/blob/cabde577c417dd5594e08ae9a98fcdc6f374ee0e/SQLiter/src/nativeCommonMain/kotlin/co/touchlab/sqliter/NativeDatabaseConnection.kt#L84-L95

Sep 28 '20 22:09 andersio

I reckon iOS File Coordination can be used on the database files to provide guarantees that I need, without needing any change in SQLiter. Thanks anyway.

Oct 02 '20 23:10 andersio

I took a quick look at this and want to dig a bit deeper, so reopening for now.

Oct 02 '20 23:10 kpgalligan

To add a bit of context on what I did: File Coordination is applied on the parent directory of the DB (in an App Group container), which makes sure no more than one process can run the create-or-migrate phase at the same time. This was done basically because we start developing app extensions, and they are spawned as throwaway processes by iOS.

The setup uses coordinateWritingItemAtURL with NSFileCoordinatorWritingForMerging, inspired by the recommended practices of a popular Swift SQLite library.

Feb 27 '21 17:02 andersio