Concurrent access to database with metadatabase enabled causes "database is locked" exception

[davidrothera]

Description

In projects which have the metadatabase attached as per the recommended method inside prepareDatabase along with multi-threaded usage of that database it can result in issues where the meta db is locked.

SQLite error 5: database is locked - while executing `BEGIN IMMEDIATE TRANSACTION`

I believe this is because in the attach method we create a short lived pool and that is not shared between prepare calls so cant properly pool/queue the requests.

Checklist

• I have determined whether this bug is also reproducible in a vanilla SwiftUI project.
• I have determined whether this bug is also reproducible in a vanilla GRDB project.
• If possible, I've reproduced the issue using the main branch of this package.
• This issue hasn't been addressed in an existing GitHub issue or discussion.

Expected behavior

No issue

Actual behavior

Intermittent issues

Reproducing project

https://github.com/davidrothera/LockedDatabaseRepro

SQLiteData version information

1.5.1

Sharing version information

No response

GRDB version information

7.9.0

Destination operating system

No response

Xcode version information

No response

Swift Compiler version information

[davidrothera]

Changing this line to a read connection appears to fix the issue.

sqlite-data/Sources/SQLiteData/CloudKit/SyncEngine.swift

Line 2244 in 3b57618

_ = try database.write { db in

I'm assuming that the only reason that select statement is there is to test the DB connection and if so I'm assuming a read connection would be fine here? With WAL/SHM mode at least multiple instances can refer to the same database without issues then.

[mbrandonw]

Hi @davidrothera, thanks for looking into this! The reason we have the "SELECT 1" is that we found SQLite would not necessarily create the database from just opening a connection. One would have to actually execute a query to create the database, which was important for first run of the app.

However, I am not actually able to reproduce that now. So, I would say that we will definitely accept a PR to change this write to a read in order to alleviate the locking error, and then we will investigate a bit more and see about just removing the "SELECT 1" entirely.

How does that sound?

[davidrothera]

Sounds good to me, I'll get it up shortly.