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).
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.
Changes only become visible to other connections after they have been committed.
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.
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.
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: https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t... - search for SQLITE_BUSY.
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.
Changes only become visible to other connections after they have been committed.
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.
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.
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: https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t... - search for SQLITE_BUSY.