In this lesson, you will learn how to safely coordinate database access when multiple independent application processes attempt to read from or write to the same database file simultaneously.
The Challenge of Shared Access
When you use a lightweight database like SQLite, the database resides in a single file on the disk. In a multi-process environment—where your application might spawn background workers, separate UI processes, or CLI tools—these processes do not share memory. If two processes try to write to that file at the exact same millisecond, you risk database corruption or "Database is locked" errors.
File-Level Locking Mechanisms
To maintain data integrity, lightweight databases use file-level locking. Think of this like a library book checkout system: the database acts as the book, and the file system acts as the librarian.
When a process wants to write, it requests an "Exclusive Lock" on the file. If another process is already writing, the operating system denies the request, forcing the second process to wait or retry.
Most modern embedded databases implement this using a "journaling" or "Write-Ahead Log" (WAL) mode. In WAL mode, the system allows multiple readers to access the database simultaneously without blocking, while still funneling all writes through a single, coordinated process.
Practical Implementation
When your application architecture involves multiple processes, you should always configure your connection to handle these locks gracefully. For example, in SQLite, you set a "busy timeout":
-- This instructs the database to wait for a lock to clear
-- for up to 5000 milliseconds before throwing an error.
PRAGMA busy_timeout = 5000;
By setting a busy_timeout, you prevent your application from crashing instantly if a background sync process is currently writing a batch of data. Instead, your foreground process pauses momentarily, waits for the lock to be released, and then proceeds. This simple strategy allows multiple processes to cooperate safely without requiring a heavy, dedicated server process.
Key Takeaway: Multi-process access relies on file-level locks and timeouts to ensure that while many processes can read simultaneously, writes are serialized to maintain total data consistency.