Contents
Introduction
SQLite is a nice self-contained database suitable for a wide variety of applications. A consideration to use databases is that multiple connections can access them concurrently. In many cases e.g. PostgreSQL would be used but that is not always feasible - especially when deploying a piece of software on embedded hardware/edge devices with tight resource constraints. SQLite offers a certain degree of currency that often is enough but also not entirely intuitive.
SQLite-3 Concurrency Explained
SQLite (version 3) standard requires to lock a database exclusively when writing to it. Reading can be done using shared locks. Using Write-Ahead Logging is possible as well. Often it is enough to simply let a commit wait for a certain time until a timeout is reached. This can be done using the PRAGMA busy_timeout=milliseconds.
In practice that means that after a successful connect to a SQLite database was established, there is a sql command to be executed to set this value for the database module/library/class we’re using:
db_conn.execute("PRAGMA busy_timeout = 30000")
int sqlite3_ret = 0;
std::string sql_pragma = "PRAGMA busy_timeout=30000";
sqlite3_ret = sqlite3_exec(DB, sql_pragma.c_str(),
callback, 0, &db_message_error);
Benchmark Results
Let’s have a look if it this really works in practice and open multiple connections to a single data base and commit random data. The code is available on github.
The time out seems to work as there is a clear slow down in per-thread time per commit visible:
Time per commit (total run time divided by number of rows inserted into the SQLite3 database):
NB!: The C++ version runs at <15% CPU utilization whereas the Python version runs at 100 %. It might be possible that Python’s sqlite3 library is a lot more optimized than the rather straight forward C++ approached for the benchmark.