Monday, September 12, 2016

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.


No comments: