sqlite-single-writer

Use a separate lock file with OS-level exclusive locking (flock on Unix, LockFileEx on Windows) to serialize writes across processes. Enable WAL mode so reads remain concurrent while writes queue through the lock.

Use fcntl.flock with LOCK_EX and LOCK_NB on Unix, or msvcrt.locking on Windows, against a dedicated db.lock file. Retry with exponential backoff starting at 5ms and raise a timeout error with holder info after 500ms.

Yes, WAL mode lets readers proceed while a single writer holds the database. Enable it with PRAGMA journal_mode=WAL along with busy_timeout=500 and synchronous=NORMAL on every connection.

Both flock and LockFileEx are automatically released by the operating system when the holding process exits or crashes. The lock file content may be stale, but the lock itself is never held by a dead process.

No, flock is unreliable on NFS and SMB network filesystems. This pattern only works on local filesystems; for network storage you need a different coordination mechanism or a client-server database.

SQLite manages its own internal locks on the database file, and external locking can interfere with WAL and shared-memory files. A separate db.lock file coordinates writers without conflicting with SQLite's locking.