TopolearnWAL mode is the single biggest improvement for SQLite concurrency and write speed in most apps.
SQLite needs a journal to make transactions atomic. The default rollback journal (journal_mode = DELETE) copies original pages to a -journal file before changing the database. While a writer commits, it needs an exclusive lock, so readers and the writer block each other.
WAL mode (PRAGMA journal_mode = WAL) inverts this: changes are appended to a separate -wal file, and the main database file is updated later. Benefits:
- Readers don't block the writer and the writer doesn't block readers. Each reader sees a consistent snapshot as of when its read transaction began.
- Writes are usually faster: sequential appends and fewer fsyncs, especially with
PRAGMA synchronous = NORMAL, which in WAL mode can lose the most recent commits on power loss but never corrupts the database.
There is still only one writer at a time.
Periodically the WAL content is copied back into the database file by a checkpoint — automatically when the WAL reaches about 1000 pages (wal_autocheckpoint), or manually with PRAGMA wal_checkpoint(TRUNCATE). A reader that never finishes can prevent the checkpoint from completing, letting the WAL grow without bound.
Details to know: WAL mode is persistent (stored in the file), so set it once. It creates -wal and -shm files next to the database; copy them along with it, or use the backup API. All processes must be on the same machine: WAL relies on shared memory and doesn't work over network filesystems.
Example
import sqlite3, os, tempfile
path = os.path.join(tempfile.mkdtemp(), "app.db")
w = sqlite3.connect(path, isolation_level=None)
print(w.execute("PRAGMA journal_mode = WAL").fetchone()) # ('wal',)
w.execute("PRAGMA synchronous = NORMAL")
w.execute("CREATE TABLE log (id INTEGER PRIMARY KEY, msg TEXT)")
w.execute("INSERT INTO log (msg) VALUES ('first')")
r = sqlite3.connect(path, isolation_level=None)
r.execute("BEGIN") # reader snapshot
print(r.execute("SELECT count(*) FROM log").fetchone()) # (1,)
w.execute("BEGIN IMMEDIATE") # writer not blocked
w.execute("INSERT INTO log (msg) VALUES ('second')")
w.execute("COMMIT")
print(r.execute("SELECT count(*) FROM log").fetchone()) # (1,) same snapshot
r.execute("COMMIT")
print(r.execute("SELECT count(*) FROM log").fetchone()) # (2,)
print(w.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone()) # (0, 0, 0)
print(sorted(os.listdir(os.path.dirname(path)))) # ['app.db', 'app.db-shm', 'app.db-wal']Remember
- WAL: readers and the single writer don't block each other
- synchronous=NORMAL with WAL is fast and corruption-safe
- Checkpoints move WAL content into the database; long readers can stall them
Watch out for
- Copying only app.db while the app runs in WAL mode, leaving recent commits behind in app.db-wal.