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.
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.)