Enabling and Using SQLite Write‑Ahead Logging for Concurrent Access

Why WAL Matters

SQLite’s default rollback journal serializes all writes: a single writer holds an exclusive lock and blocks other writers and readers until the transaction commits. For many applications, this lock contention hurts throughput and causes SQLITE_BUSY errors under high concurrency.

Write‑Ahead Logging (WAL) changes the transaction model. Instead of writing changes directly into the database file, SQLite appends them to a separate .wal file. Readers keep accessing the original .db file while writers add new pages to .wal. This reduces lock contention, improves read scalability, and can increase write throughput on fast storage.

Enabling WAL

Switch a database into WAL mode with a single SQL statement or connection‑string parameter. The change is persistent for that file, so you only need to run it once.

-- Run in the SQLite CLI or any client that supports PRAGMA
PRAGMA journal_mode = WAL;

Or, if you open the database from code, add journal_mode=wal to the URI:

sqlite3://localhost:5432/mydb?journal_mode=wal

After the command, the database will create a mydb.wal file next to mydb. Verify the mode:

PRAGMA journal_mode;
-- Expected output: wal

Sample Code – Concurrent Readers and Writers

Below is a minimal Python example using sqlite3 that demonstrates concurrent access without SQLITE_BUSY errors. The code is illustrative and should be adapted to your environment.

import sqlite3, threading, time

DB_PATH = 'example.db'

# Ensure WAL is enabled
conn = sqlite3.connect(DB_PATH)
conn.execute('PRAGMA journal_mode = WAL;')
conn.commit()
conn.close()

# Writer thread
def writer():
    conn = sqlite3.connect(DB_PATH, timeout=10)
    cur = conn.cursor()
    cur.execute('CREATE TABLE IF NOT EXISTS t(id INTEGER PRIMARY KEY, val TEXT)')
    for i in range(100):
        cur.execute('INSERT INTO t(val) VALUES (?)', (f'value {i}',))
        conn.commit()
        time.sleep(0.01)
    conn.close()

# Reader thread
def reader():
    conn = sqlite3.connect(DB_PATH, timeout=10)
    cur = conn.cursor()
    for _ in range(200):
        cur.execute('SELECT COUNT(*) FROM t')
        print('Rows:', cur.fetchone()[0])
        time.sleep(0.005)
    conn.close()

threads = [threading.Thread(target=writer), threading.Thread(target=reader)]
for t in threads: t.start()
for t in threads: t.join()

In WAL mode, the writer holds only a shared lock while the reader holds a shared lock too. Because readers do not block writers (and vice versa), the code runs to completion without encountering SQLITE_BUSY.

Managing the .wal File

WAL mode keeps writes in the .wal file until a checkpoint copies them back into the main database. If checkpoints never run, the .wal file can grow indefinitely. Two common approaches:

• Automatic checkpoints – Use PRAGMA wal_autocheckpoint = N; where N is the maximum number of pages. After every N pages of writes, SQLite performs a checkpoint automatically.
• Manual checkpoints – Call PRAGMA wal_checkpoint; or use the sqlite3_wal_checkpoint API. This forces a checkpoint immediately.

Example of setting an automatic checkpoint after 100 pages:

PRAGMA wal_autocheckpoint = 100;

After a burst of writes, run a manual checkpoint to free up space:

PRAGMA wal_checkpoint;

When backing up a WAL‑enabled database, copy both .db and .wal files or use the sqlite3_backup API, which automatically copies the log.

Common Pitfalls

• Running on unsupported filesystems: WAL requires atomic writes and rename operations. Network shares (e.g., SMB on Windows) or filesystems that lack these guarantees can cause silent failures or locks. Verify by performing a write and checking for SQLITE_BUSY or by testing PRAGMA journal_mode; again – it may revert to delete.
• Neglecting checkpoints: A .wal file that never checkpoints can grow to several gigabytes, consuming disk space and slowing down the system.
• Ignoring backup semantics: Copying only the .db file loses uncheckpointed changes. Always copy the .wal file or run the backup API.
• Small databases: The overhead of maintaining a separate .wal file can outweigh the concurrency benefits for tiny databases (a few megabytes).
• Older SQLite builds: SQLite <3.7.0 does not support WAL. On embedded platforms, check the build flags; attempting to enable WAL will silently fall back to the rollback journal.
• Thread‑local connections: SQLite connections are not thread‑safe unless check_same_thread=False is set (Python) or the appropriate flag is used in other languages. Using a single connection across threads can still cause SQLITE_BUSY if not handled correctly.

Verification Checklist

1. Run PRAGMA journal_mode; – output should be wal.
2. Check the directory – a *.wal file must exist.
3. Perform concurrent reads/writes – no SQLITE_BUSY errors should appear.
4. Run PRAGMA wal_autocheckpoint = 10; then perform 200 writes; after a short delay, the .wal file size should shrink.
5. Backup the database using the API – verify that both .db and .wal files are present in the backup location.

Following these steps ensures that WAL mode delivers its intended concurrency and performance benefits while avoiding the most common pitfalls.