How do several processes share one SQLite database file without a server, and why does a network filesystem break that arrangement?
answer
- the file is the meeting point
- operating-system locks, not a coordinator
- fcntl() locking broken on many NFS
- WAL index lives in shared memory
basics
~20 sEach process runs its own copy of SQLite and coordinates through operating-system locks on the database file. On a network filesystem those locks and fsync() are often unreliable, so two machines can write at once and corrupt the file.
solid answer
~50 sThere is no coordinator. Every process opens the same file with its own copy of the library, and SQLite takes reader/writer locks on that file through the operating system: POSIX advisory locks on Unix, `LockFile()` and `LockFileEx()` on Windows. Many processes can read at once, but only one can write at a time; in the default rollback-journal mode a writer locks the whole file while it commits, and the others wait or get `SQLITE_BUSY`. `fcntl()` locking is broken or missing on many NFS implementations, and Windows network shares have a record of locking problems, so two machines can both believe they hold the write lock and corrupt the database; network `fsync()` can also be less robust than local sync. WAL mode adds a hard limit: its index lives in shared memory, so every process must be on the same host. Remote machines should go through a process on the file's host, or use a client-server database.
go deeper
Remember that every process opens the file itself and that the operating system's file locks are the only referee between them.
Explain which system calls SQLite relies on for locking and durability on Unix and Windows, and why WAL's shared-memory index ties every user to one host.
Diagnose the shared-drive story: intermittent corruption, no error at the time, early tests that passed. Prescribe one host owning the file, with remote machines going through a service.
Decide where the data lives before choosing SQLite: if several machines must write it directly, that alone moves the design to a client-server engine or a service in front of one file.
## No coordinator: the file is the meeting point A client-server engine has one server process that sees every request and decides who may touch what. SQLite has none. When three processes open `orders.db`, each one links its own copy of the SQLite library, each keeps its own page cache, and each reads and writes the file directly. The only thing they share is the file itself, so the only way they can agree on who is writing is through **locks the operating system keeps on that file**. SQLite's FAQ summarises the result: multiple processes can have the same database open, multiple processes can be running a `SELECT` at the same time, but only one process can be making changes at any moment, and, in the default rollback-journal mode, a writer locks the entire database file for the duration of its update (WAL mode lets readers continue beside the one writer). A process that finds the file locked gets `SQLITE_BUSY` by default, unless the application sets a busy handler or `sqlite3_busy_timeout()`. The exact lock states a writer passes through, and how to tune waiting, are a separate subject: SQLite's locking and write concurrency. ## The operating-system services SQLite assumes | Need | Unix | Windows | |---|---|---| | Reader/writer locks on the file | POSIX advisory locks (`fcntl()`) | `LockFile()`, `LockFileEx()`, `UnlockFile()` | | Make written data durable | `fsync()` | `FlushFileBuffers()` | | Shared index for WAL mode | Shared memory, by default a memory-mapped `-shm` file | The same, through the Windows VFS | SQLite's locking documentation is blunt: it **assumes these system calls work as advertised**, and if they do not, database corruption can result. On local disks they normally do. The trouble starts when the file lives somewhere the calls are only emulated. ## What a network filesystem breaks - **Locking.** POSIX advisory locking is known to be buggy or unimplemented on many NFS implementations, and there are reports of locking problems on Windows network shares. If two clients on different machines both acquire the write lock, they modify the same pages at once and corrupt the file. Because this is a filesystem bug, there is nothing SQLite can do to prevent it. - **Durability ordering.** SQLite's atomic commit depends on `fsync()` meaning "everything written so far is stored". SQLite's notes on network use warn that network filesystem sync can be less robust than local sync, and network writes can arrive out of order. - **WAL mode.** WAL needs a small shared-memory index, the wal-index, that every reader and writer maps. Processes on different machines cannot share memory, so SQLite's WAL documentation states that all processes using a WAL database must be on the same host and that WAL does not work over a network filesystem. - **Performance.** The link between an engine and its disk carries far more data than the link between an application and its engine. SQLite puts the engine inside the application, so a remote file pushes the heavy link across the network. Early tests on a network share often pass. The failures are intermittent, appear under concurrent use, and show up as a corrupted database rather than an error message, which is why SQLite's guidance says not to trust early success. ## Safe ways to serve several machines SQLite's own recommendations, when clients on several machines need simultaneous reads and writes: 1. **Use a client-server database engine**, which keeps the engine on the same machine as its files and sends only queries and results across the network. 2. **Keep the SQLite file on one host and run every reader and writer there**, for example a small service or proxy that remote machines send requests to. WAL mode is fine in this layout. 3. **Use rollback-journal mode and accept its limits**: many readers or one writer, never both at once. SQLite's notes say this reduces the exposure to network unreliability, but the library is not tested across a network, so the risk stays with you. ## Two related traps on a single machine - **Different locking schemes on one file.** Every process must reach the file through compatible locking. Processes using incompatible locking VFSes, such as dot-file locking in one process and POSIX locks in another, do not see each other's locks. - **Locks are advisory.** A program that writes the file without going through SQLite, such as a restore script or a file-sync client, ignores the locks entirely. How a damaged file is detected and the full list of corruption causes belong to the material on SQLite's limits and corruption; safe copying of a live database belongs to the material on SQLite's files and backup.
- A desktop app's users keep its SQLite database on a shared network drive used by several PCs. What do you change?Stop several machines opening the file directly. Either keep the database on one machine and have the other PCs talk to a small service running there, which is the only process that opens the file, or move shared data to a client-server database. If the drive must stay, limit it to one user at a time and keep rollback-journal mode, knowing SQLite does not test that setup.
- Why does WAL mode work for several processes on one machine but not across machines?WAL keeps an index of the write-ahead log, the wal-index, in shared memory that every connection maps, by default through the `-shm` file. Processes on one host can map the same memory; processes on different hosts cannot, so they could never agree on which log frames are valid. SQLite therefore requires every WAL user to be on the same host.
- A small SQLite server process on the file's machine coordinates the other processes.
- SQLite detects a network path when it opens a file and refuses to write.
- WAL mode makes a database on a network share safe for several machines.
- A network share only makes SQLite slower; the data stays consistent.
- Two processes can never have the same SQLite file open at once.