SQLite works well with threads as long as a single thread has its own database connection, or so they say in the documentation. I wanted to make a lot of database read only select calls on a table from multiple threads and I coded my C++ program according to the guidelines. But I found out some of the threads will get a "database is locked" exception after a while, and any subsequent calls to the database will raise a "library routine called out of sequence" exception. Changing the SQL connection to other types such as SQLITE_OPEN_FULLMUTEX, SQLITE_OPEN_NOMUTEX, and or permutations did not solve my problem.
Eventually I decided on a workaround to give each thread an in-memory copy of the database. The following code snippets show a simple example. The example uses the light weight C++ SQLite wrapper from https://github.com/aminroosta/sqlite_modern_cpp
The runThread function creates a copy of the original source database example.sqlite and puts it in memory with a unique connection string. Subsequently, it performs some read operations on the in-memory database.
Finally, the main function that creates multiple threads and waits for them to complete.
An screenshot of the program in execution. Since each thread has a copy of the database, the database is never locked by other threads and I will never encounter the "database is locked" exception.
Note that this workaround will not work if read and write operations are required.