SQLite concurrency

I seem to have a problem with a local sqlite-database, occurring occasionally .
Right after creating the database connection object, I start a thread which gathers some information from a webservice in order to add the results to the database. Simultaneously I start populating the database in the main thread. Most of the time everything works well, but occasionally I get an unspecified database error, which can only be solved by disconnecting and reconnecting.
Of course I can do the populating before I start the thread, but I am asking myself why I should have to do so.

(xojo 2016R3 on Win10)

Have you tried using WAL mode?

{YourDatabaseObject}.SQLExecute("PRAGMA journal_mode=WAL;")

I had a deadlock-style problem in my iOS app when accessing the same database from multiple threads and this seems to have solved it for me. But I also added transaction blocks — being sure not to nest them as there’s a problem in Xojo not handling the timeout exception — so it could also have been that.

Me too.

The WAL journaling mode uses a write-ahead log instead of a rollback journal to implement transactions. The WAL journaling mode is persistent; after being set it stays in effect across multiple database connections and after closing and reopening the database. A database in WAL journaling mode can only be accessed by SQLite version 3.7.0 (2010-07-21) or later.

Will add this pragma and keep an eye on it. Thanks Jason.

In addition to using Write-Ahead Logging as Jason suggested, the Thread should have its own SQLiteDatabase class instance and connection. Each concurrent process should have its database operations executed independently from the others, just as though they were different users. Perhaps that’s why the Xojo property name for setting the SQLIte Write-Ahead Logging Pragma is MultiUser. :wink:

Edited with more detail. Hope that helps.