SQLite Concurrent Writes: WAL Mode and Lock Handling (2026)

SQLite’s default write locking mechanism is a performance bottleneck in concurrent environments because it locks the entire database file for writes, preventing any other write operations until the first one completes.

This is how it usually works:

-- Imagine two processes trying to write to the same database.
-- Process A starts a transaction.
BEGIN TRANSACTION;
INSERT INTO users (name) VALUES ('Alice');
-- SQLite acquires a write lock on the entire database file.
-- Process B tries to write.
BEGIN TRANSACTION;
INSERT INTO users (name) VALUES ('Bob');
-- Process B blocks. It waits for Process A to commit or rollback.
COMMIT; -- Process A finishes.
-- Now Process B can acquire the write lock and proceed.
COMMIT; -- Process B finishes.

The problem is that a single write lock on the whole database is a surprisingly coarse-grained approach for modern systems that often have many threads or processes needing to update data simultaneously. It’s like a single toll booth for an entire city’s traffic — everyone has to wait their turn, even if they’re going to different destinations.

The Solution: Write-Ahead Logging (WAL)

SQLite has a mode called Write-Ahead Logging (WAL) that dramatically improves concurrency for readers and writers. Instead of locking the entire database for every write, WAL allows readers and writers to operate more independently.

Here’s how WAL changes the game:

1. Writes go to a separate WAL file: When you write to a WAL-enabled database, the changes are first appended to a special log file (your_database.db-wal).
2. Readers read from the main database: Readers can still access the main database file (your_database.db) without being blocked by writers. They can also read from the WAL file to see the latest committed changes.
3. Checkpointing: Periodically, the changes from the WAL file are "checkpointed" back into the main database file. This process consolidates the log and keeps the WAL file size manageable.

Let’s see how that concurrent write example looks with WAL:

-- Process A starts a transaction in WAL mode.
BEGIN TRANSACTION;
INSERT INTO users (name) VALUES ('Alice');
-- Changes are written to your_database.db-wal.
-- A shared lock is acquired on the database, but not an exclusive write lock.
-- Process B tries to write.
BEGIN TRANSACTION;
INSERT INTO users (name) VALUES ('Bob');
-- Changes are written to your_database.db-wal, after Alice's.
-- Process B also acquires a shared lock.
-- Both readers and writers can proceed concurrently.
COMMIT; -- Process A finishes.
-- The changes are now visible to readers via the WAL file.
COMMIT; -- Process B finishes.
-- Both sets of changes are in the WAL file and will be checkpointed later.

Enabling WAL Mode

To enable WAL mode, you simply execute a PRAGMA command within your SQLite connection. You only need to do this once per database file, as the mode is persistent.

PRAGMA journal_mode=WAL;

After running this, your database will start using WAL. You’ll notice three files: your_database.db (the main database), your_database.db-shm (shared memory, used for coordinating access), and your_database.db-wal (the write-ahead log).

Understanding the Locks

Even with WAL, there are still locks, but they are much more granular and less restrictive.

• Readers: Acquire a shared lock on the database file. Multiple readers can hold shared locks simultaneously. They don’t block writers, but they might have to wait a moment if a writer is in the process of a checkpoint.
• Writers: Acquire a reserved lock on the database file. This prevents new readers from starting while a write transaction is active. However, readers that already hold a shared lock can continue reading. Writers also acquire an exclusive lock on the WAL file segment they are writing to.

The your_database.db-shm file is crucial here. It acts as a shared memory file that SQLite uses to communicate lock states and manage the WAL index. If this file becomes corrupted or inaccessible, WAL will fail.

When WAL Might Not Be Ideal

While WAL is generally superior for concurrency, there are a few scenarios where the default DELETE journal mode might be preferred:

• Very Low Concurrency or Single Writer: If you have a single process writing to the database and very few or no concurrent readers, the overhead of managing the WAL file and checkpointing might not be worth it.
• Limited Disk Space for WAL File: The WAL file grows with writes. While checkpointing keeps it in check, in extremely write-heavy scenarios with infrequent checkpoints, it could temporarily consume more disk space than a DELETE journal.
• Atomic Renames for Backups: Some backup strategies rely on atomically renaming the main database file. With WAL, the database is a collection of files, and backing up the live, active database requires copying all three files (.db, -shm, -wal) consistently.

Checking Your Journal Mode

You can easily check the current journal mode of your database:

PRAGMA journal_mode;

This will output wal, delete, truncate, memory, or off.

Checkpointing and WAL File Size

The WAL file (.db-wal) contains all the un-checkpointed changes. When it gets too large, or when a transaction commits, SQLite might perform a checkpoint. A checkpoint reads data from the WAL file and writes it back into the main database file (.db). This process cleans up the WAL file, reducing its size.

You can manually trigger a checkpoint:

PRAGMA wal_checkpoint;

This command attempts to move all committed transactions from the WAL file to the main database. It returns a status code indicating success or if it was busy.

The most surprising true thing about SQLite’s WAL mode is that it doesn’t actually require exclusive access to the database file for writes; instead, it uses a more sophisticated locking strategy involving the -shm and -wal files to allow readers and writers to coexist much more effectively.

The next concept you’ll likely run into is how to manage transactions and potential deadlocks in very high-concurrency WAL scenarios, especially when multiple clients might be trying to perform operations that, while not conflicting at the file level, could lead to logical deadlocks within the application.