This fact is quite an unpleasant thing for a database with a lot of writes and relative seldom reads like, for example, a small packet capturing tool I write for my router.
The classical mechanics of SQLite locking is described here, but if you face the same problem as me, you probably would want to enable WAL – Write-ahead logging feature, which can solve the issue of SQLite DB locking:
The traditional rollback journal works by writing a copy of the original unchanged database content into a separate rollback journal file and then writing changes directly into the database file. In the event of a crash or ROLLBACK, the original content contained in the rollback journal is played back into the database file to revert the database file to its original state. The COMMIT occurs when the rollback journal is deleted.
The WAL approach inverts this. The original content is preserved in the database file and the changes are appended into a separate WAL file. A COMMIT occurs when a special record indicating a commit is appended to the WAL. Thus a COMMIT can happen without ever writing to the original database, which allows readers to continue operating from the original unaltered database while changes are simultaneously being committed into the WAL. Multiple transactions can be appended to the end of a single WAL file.
To enable WAL, execute:
sqlite> PRAGMA journal_mode=WAL;
Good news, it’s persistent and required to be run once.
Also additional tuning can be done, like setting: PRAGMA journal_size_limit=N, PRAGMA wal_checkpoint(<MODE>) or PRAGMA synchronous=FLAG, but it’s strongly advised to read documentation before changing something.
Finally, as always, remember to call sqlite3_finalize() after the prepared statement is no longer needed, or you risk locking the database which in the case of WAL leads to the checkpoint starvation and uncontrolled grows of the WAL file.
