C++ example code for reading an Sqlite database from multiple threads

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

int runThread(int threadNumber) { try { cout << "[" << threadNumber << "]" << "Running" << endl; stringstream ss; // Form the connection string to the in-memory database ss << "file:memory" << threadNumber << "?mode=memory"; string inMemoryDbConnectString = ss.str(); ss.str(""); // Open a database connection to the in-memory database database inMemoryDb(inMemoryDbConnectString); // Open a database connection to the original source database database sourceDb("example.sqlite"); // Copy the source database to memory auto sourceConnection = sourceDb.connection(); auto state = std::unique_ptr<sqlite3_backup, decltype(&sqlite3_backup_finish)>( sqlite3_backup_init(inMemoryDb.connection().get(), "main", sourceConnection.get(), "main"), sqlite3_backup_finish ); if (state) { int rc; // Each iteration of this loop copies 500 database pages from database db to the backup database. do { rc = sqlite3_backup_step(state.get(), 500); //std::cout << "Remaining " << sqlite3_backup_remaining(state.get()) << "/" << sqlite3_backup_pagecount(state.get()) << "\n"; } while (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED); // Do some reading from the in-memory database table for (int i = 0; i < 10000; i++) { double seconds = 452220.0 + (double)threadNumber + (double)i; double lower = seconds - 1; double upper = seconds + 1; ss << "SELECT seconds, x, y, z FROM attendance WHERE seconds >=" << lower << " AND seconds <=" << upper << " ORDER BY seconds ASC"; inMemoryDb << ss.str() >> [&]( double seconds, double x, double y, double z) { // do something with the returned records }; ss.str(""); } } } catch (sqlite_exception &e) { cout << "[" << threadNumber << "]" << "Error:" << e.what() << endl; } cout << "[" << threadNumber << "]" << "End" << endl; return 0; }

Finally, the main function that creates multiple threads and waits for them to complete.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

int main(int argc, char **argv) { // Create threads and put them into an array thread threads[3]; for (int i = 0; i < 3; i++) { threads[i] = thread(runThread, i); } // Wait for the threads to complete for (int i = 0; i < 3; i++) { threads[i].join(); } return 0; }

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.