Solving 'Database is Locked' Errors with SQLite WAL Mode

The Concurrency Wall

You are building an application with a read‑heavy workload. Everything works perfectly during development, but as soon as multiple users hit the system, your logs fill with SQLITE_BUSY: database is locked errors. This happens because, by default, SQLite uses a rollback journal. In this mode, when a process wants to write to the database, it acquires an exclusive lock. This lock blocks all other connections—even those just trying to read data—until the write transaction completes.

The solution is to switch the database to Write‑Ahead Logging (WAL) mode. Instead of locking the main database file for every update, WAL allows readers to continue accessing the original database while changes are appended to a separate side‑file. This effectively decouples reads from writes.

How WAL Changes the Locking Model

In standard DELETE mode (the default), a writer must wait for all readers to finish before it can start, and once the writer starts, no one else can read. WAL mode flips this logic. It uses a -wal file to store new transactions. Readers look at the main database file and the WAL file simultaneously to see the most recent committed state.

• Concurrent Access: Multiple readers can operate while a single writer is active.
• Write Performance: Writes are generally faster because they are sequential appends to the WAL file rather than overwriting pages in the main database.
• Persistence: The main database file remains untouched until a "checkpoint" occurs, which merges the WAL changes back into the primary file.

Implementing WAL Mode

Enabling WAL is a persistent database setting. You only need to run the command once per database file; SQLite remembers the mode across sessions.

-- Check current mode (likely returns 'delete')
PRAGMA journal_mode;

-- Enable Write‑Ahead Logging
PRAGMA journal_mode=WAL;

-- Verify the change (should return 'wal')
PRAGMA journal_mode;

Worked Example: Testing Non‑Blocking Reads

To verify that WAL mode is working, you can simulate a long‑running write transaction and attempt to read from a separate session.

1. Session A (The Writer): Open a connection and start a transaction that takes time to complete.

   BEGIN IMMEDIATE TRANSACTION;
   UPDATE users SET status = 'active' WHERE id = 1;
   -- Do not COMMIT yet; keep this session open.
   

2. Session B (The Reader): Open a second connection and execute a query.

   SELECT * FROM users WHERE id = 1;
   

In default mode, Session B would hang or return a database is locked error. In WAL mode, Session B will immediately return the previous committed version of the row, allowing the application to remain responsive while the writer finishes its work.

The Trade‑offs: Checkpoints and Filesystems

WAL mode is not a magic bullet; it introduces specific engineering constraints:

• The Checkpoint Overhead: Eventually, the -wal file grows too large. SQLite performs a "checkpoint" to move data back to the main .db file. If you have a read transaction that stays open for hours, it can prevent the checkpoint from completing, causing the WAL file to grow indefinitely and consume disk space.
• Network Filesystems: WAL requires shared memory (shm files). Because of how locking is handled in shared memory, WAL mode does not work on network drives (NFS, SMB/CIFS). If your database is hosted on a network share, you must stick to the rollback journal.
• Extra Files: You will see -wal and -shm files appearing next to your database. These are temporary but essential; do not delete them while the database is in use.

Verification and Rollback

To confirm the current state, run PRAGMA journal_mode;. If you find that WAL mode is causing disk space issues or is incompatible with your storage, you can revert to the default behavior:

PRAGMA journal_mode=DELETE;

Note that switching back to DELETE mode will trigger a final checkpoint to ensure all WAL data is merged into the main database before the WAL file is removed.