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.
