Using plain DatabaseQueue's when sharing a Database - any risks?

[Jasperav]

I was following the tutorial here: https://github.com/groue/GRDB.swift/blob/master/Documentation/SharingADatabase.md. I need to share my database because I want to access it in my app extension for push notifications.

I was introduced with some concepts I never encountered before (NSFileCoordinator, coordinate method, etc). It looked pretty intense just to open a database connection. I created an App group and I read it directly from it with a DatabaseQueue. It seems to work for both my main app and extension.

I was wondering why the complex setup is needed. 2 problems are tackled in the tutorial though. In my main app I already used a DatabaseQueue and for the method in SharingADatabase I need a DatabasePool for the WAL-mode. I was curious if I can use a DispatchQueue in my main app and a DatabasePool for the extension, if my current setup is bad.

So in short: I am using DispatchQueue's instead of the Pool. It looks like it is working. Is this ok or bad? If I really need a DatabasePool to open a read-only connection in my extension, is it ok to use a DispatchQueue in my main app?

[groue]

Hello @Jasperav,

When sharing a database between several processes, putting the database in the WAL journaling mode can be useful. It reduces the odds that one process fails accessing the database because another process holds a lock on the database file. The WAL mode may also play some role in the prevention of the 0xdead10cc exception, but I'm not quite sure.

Still, reducing the odds of errors is the reason why the WAL mode is recommended.

The GRDB apis kind of conflate the mode of the database, and the DatabaseQueue and DatabasePool classes. But what really happens is that DatabaseQueue does not modify the journaling mode, while DatabasePool forces the WAL mode (except read-only). That's because DatabasePool needs the WAL mode in order to provide the concurrent database accesses it promises.

You can put the database in the WAL mode with a DatabaseQueue, with the PRAGMA journal_mode=WAL SQL statement.

If you share the database between multiple processes, you may not follow the WAL mode advice (but raise the odds of errors). If you follow the WAL mode advice, you can still use a DatabaseQueue. That would be totally OK. But you'd lose the ability to perform parallel reads provided by DatabasePool.

[groue]

And the NSFileCoordinator makes sure that while database setup (migrations, any custom code) is performed by one process, other processes can't access the database. Without file coordination, processes may perform setup twice, or access the database in an unspecified state, with unclear (usually bad) consequences.

[Jasperav]

How can this happen? If reads and writes are inside transactions, it doesn't matter how many connections there are to the sqlite db right?

[groue]

Once the schema is settled, you're totally right: file coordinators aren't needed in general SQLite usage. But DatabaseMigrator is not a type that is able to run while another migrator is running. Even if it would, migrations contain user code, and user code may deal with anything, including something which is not the database schema (other files for example). Such user code is maybe not able to deal with other processes. So making DatabaseMigrator able to deal with cross-process concurrency, and concurrency in general, is not a goal. NSFileCoordinator is the ready-made tool for that job. It allows the user to specify the exact extent, in the file system, of what "database setup" means.

[groue]

The reason why sharing a database is hard is that there are many opportunities for failures, and those are excruciatingly difficult to reproduce and test (because process synchronization is hard, because some system events are impossible to simulate, etc). Most of the "Sharing a Database" guide is about error prevention, based on some experience and documentation reading. When one "naively" shares a database, everything usually "works", until it doesn't (usually on the device of some disappointed users).