Recursive AI

SQLite WAL vs. rollback journal

See how SQLite’s write-ahead log (WAL) preserves read snapshots after a commit, what a checkpoint copies, and when to use a rollback journal.

OneLite is a free, open-source SQLite-compatible .NET engine from Recursive AI, licensed MIT or Apache-2.0. NuGet · GitHub

dotnet add package RecursiveAI.OneLite --version 1.0.0

A rollback journal saves old pages; a write-ahead log (WAL) stores new ones. Run this in a .NET 10 console project to watch a WAL commit in OneLite, not native SQLite:

Program.cs.NET 10
using RecursiveAI.OneLite;
 
var path = $"wal-{Guid.NewGuid():N}.db";
using var writer = Database.OpenDefault(path);
using var reader = Database.OpenDefault(path);
Console.WriteLine($"Mode: {writer.Scalar<string>($"PRAGMA journal_mode")}");
writer.ExecuteScript("CREATE TABLE counter(n); INSERT INTO counter VALUES (1);");
writer.ExecuteScript("PRAGMA wal_checkpoint(TRUNCATE); PRAGMA wal_autocheckpoint=0;");
var before = File.ReadAllBytes(path);
 
reader.ExecuteScript("BEGIN;");
Console.WriteLine($"Reader starts: {reader.Scalar<long>($"SELECT n FROM counter")}");
writer.ExecuteScript("UPDATE counter SET n=2;");
Console.WriteLine($"Reader after commit: {reader.Scalar<long>($"SELECT n FROM counter")}");
Console.WriteLine($"Main file unchanged: {before.SequenceEqual(File.ReadAllBytes(path))}");
reader.ExecuteScript("COMMIT;");
 
writer.ExecuteScript("PRAGMA wal_checkpoint(TRUNCATE);");
Console.WriteLine($"Main file changed: {!before.SequenceEqual(File.ReadAllBytes(path))}");
Console.WriteLine($"New read: {reader.Scalar<long>($"SELECT n FROM counter")}");
OutputOneLite 1.0.0
Mode: wal
Reader starts: 1
Reader after commit: 1
Main file unchanged: True
Main file changed: True
New read: 2

The update commits while the first read transaction is open, but that reader still sees 1. The main file doesn’t contain the update until it’s checkpointed. A new read sees 2.

What gets written in each mode

In native SQLite’s rollback mode, the writer records original pages in the rollback journal before writing changed pages to the database file. Readers must be out of the way before the writer overwrites those pages. The journal can restore the original pages if an incomplete write has to be undone. In DELETE mode, deleting the journal marks the commit; TRUNCATE and PERSIST invalidate it differently, as the journal-mode reference explains.

In WAL mode, each log frame holds a page image and metadata. The writer adds changed pages to -wal and marks the end of the transaction there. OneLite’s ordinary WAL commit publishes the new committed end to the WAL index. Neither engine needs to copy those pages back to the main file for the commit to become visible to new readers.

How a WAL reader keeps its snapshot

A reader holds its own cutoff at a committed position in the log, called the end mark in SQLite’s WAL documentation. For each page, it looks for the newest copy it can see at or before that cutoff. If there’s none, it reads the main file. The WAL index, normally backed by -shm, helps locate frames without scanning the log for each page; -wal and -shm are sidecar files next to the .db file. Appending newer frames doesn’t move an existing reader’s cutoff. Native SQLite WAL allows one writer at a time. OneLite does too by default: ordinary write transactions use a single WAL write lock. OneLite’s opt-in BEGIN CONCURRENT lets write transactions overlap when DatabaseOptions.ConcurrentWrites is enabled.

Checkpointing copies pages back; it doesn’t commit them

A checkpoint copies committed page images from the WAL into the main file. It can’t overwrite a page with a version that’s too new for an active reader. A long-lived reader can therefore delay checkpoint progress and keep the log growing. In the example, the first checkpoint gives the reader a clean baseline, and the final one runs after the reader releases its snapshot. OneLite’s checkpoint modes include PASSIVE, which does what it can without waiting, and TRUNCATE, which also shrinks the log to zero on success. A checkpoint doesn’t always shrink the WAL; it can leave space for reuse.

When to choose WAL or a rollback journal

Start with WAL when readers need to keep working while a writer commits, and the database lives on a local filesystem with support for SQLite’s shared-memory coordination. Plan for snapshots held open by long queries, and don’t copy just the live .db file as a backup: the WAL can hold committed data that isn’t in it yet.

Keep a rollback journal when reader/writer overlap isn’t needed and you want to avoid WAL sidecars, or when you need native SQLite’s cross-database atomic commit for qualifying attached databases. On native SQLite, WAL is persistent: check the mode returned by PRAGMA journal_mode=WAL or PRAGMA journal_mode=DELETE rather than assuming the switch succeeded. OneLite’s Database.OpenDefault enables WAL by default, which is why the example starts in that mode.