Reads are concurrent, writes block reads (and other writes) while they write. (There's also something called WAL mode that enables reads during a write, which uses multiple files for each database.)
Edit: looks like you can set up blocking on the client using pragma busy_timeout [1] so the above is only true in default configuration
so things like mutexes (one process, multiple threads) or mutexes over shared memory (multiple processes, multiple threads) ?
sounds complicated (and error-prone).
but at the end of the day i guess that kind of juggling has to be done somewhere, either a database server does it for you or you do it yourself in the database client.
i wouldn't mind sqlite on the server, but i'm not sure I would want to trade zero-downtime-deployments (spin up new version, move load from old to new, spin down old version) for single-file ease of operations.
It's handled by the file lock. You don't need to track anything.
Yes there is a timeout value that will return an error if the database file hasn't unlocked in time. In Python the default timeout is 5 seconds, which is plenty if your queries execute on the order of milliseconds or tens of milliseconds.
PRAGMA journal_mode = WAL; PRAGMA foreign_keys = 1; PRAGMA mmap_size = 1099511627776;
Unless you know a reason that you can’t (like NFS or using a 32-bit cpu).
interesting, why there's that limitation when nfs and a 32-bit cpu are involved?
mmap_size = 1TiB requires 1TiB of address space per connection. This is fine on 64-bit, unless you have millions of connections. Doesn’t work with 32-bit (4GiB) address space.
You want mmap_size to be greater than the size of your database. If you know your database will be small then you could set it to a smaller number, but you could still become address space constrained with many connections.