Missing from this, but very important: you can connect to the same SQLite database file multiple times inside the same process (e.g. in different threads) but you can also connect to the same SQLite database file from multiple different processes on the same machine (with the same filesystem - NFS will lead to all kinds of nasty problems).<p>Easy way to prove this to yourself: run two different terminals, run "sqlite3 data.db" in each one, run create table / insert / update queries in one and watch the results become visible to selects in the other.<p>Changes only become visible to other connections after they have been committed.<p>In default mode any writes will hold an exclusive lock and will cause any reads to be blocked waiting for that lock. In WAL mode reads can continue even while writes are taking place.<p>Writes can only run one at a time, but can queue waiting for each other to finish - and since most writes take microseconds to run this is very rarely a problem.<p>The most complicated thing to understand is the need to use BEGIN IMMEDIATE TRANSACTION in concurrent environments to avoid the occasional SQLITE_BUSY exception. The best explanation I've seen of this is here: <a href="https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/" rel="nofollow">https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t...</a> - search for SQLITE_BUSY.
I just dealt with a lot of sqlite concurrency stuff recently. When I first got started, a lot of recommendations were that apps should create a connection and hold that connection its the duration. This works great but I feel like it's worth noting that this can get dangerous when you're working in a multi-threaded context.<p>You can have one thread in the middle of stepping through the results of a SELECT prepared statement and if another thread resets the statement and runs it again, that original thread will start stepping through the results from the other thread's call. Or if both threads begin a transaction, you'll get nested transaction errors.<p>Things get really confusing, especially because you might not notice the problems until the app is running on a slower machine.
There are benefits to having a single writer connection (and multiple readers).<p>One is that you can get fairer and more efficient locking. The (interruptible) file lock APIs that SQLite uses lead to a lot of sleeping and retrying that wastes time, and is not very fair.<p><a href="https://fractaledmind.github.io/2024/07/19/sqlite-in-ruby-backoff-busy-handler-problems/" rel="nofollow">https://fractaledmind.github.io/2024/07/19/sqlite-in-ruby-ba...</a><p>Another some APIs, like the backup API, work better if they can track writes, and (e.g.) backing up from the single writer connection helps there.
> IMMEDIATE TRANSACTION<p>It has unfair properties, there is no lock queue and workers could starve forever trying to get lock in high contention context. It's very easy to get busy timeout. You have to use external proper lock if you want to use IMMEDIATE mode. A peculiar thing: with external write lock (flock) you could get a true serializability without errors.<p>IMPLICIT (default) transaction mode in other hand could be tricky to manage, because you could get integrity error on commit.