DON'T Bypass the SQLite API
SQLite is ACID provided that the programmer adheres strictly to the official SQLite API. Accessing an SQLite database with anything other than a single instance of the SQLite library can result in corrupted data.
Some examples:
- Literally just opening an SQLite database with anything other than the offical API can corrupt it. More accurately, calling the POSIX
close()function (which generally follows anopen()) on a file messes with any advisory locks on the file. And SQLite relies on advisory locks for multiprocess access.
- Archiving an SQLite database using
cpcan produce a corrupt backup. SQLite uses a seperate journal file for durability. And it is not possible to atomically move both the data and the journal file. Use the SQLite Online Backup library for archiving, instead.
- Using multiple instances of the SQLite library to concurrently access a database file can result in data corruption. This is not entirely unlikely, especially in polyglot codebases.
DO Use an In-Memory Database
Sharing mutable data across threads is hard.
On Linux, you can also park the database file in /dev/shm/ if you want to share in-memory data across different processes.