---
title: "SQLite WAL vs. rollback journal"
url: https://recursiveai.org/onelite/articles/sqlite-wal-vs-rollback-journal/
description: "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."
date: 2026-11-20
author: OneLite team
---

# 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.

November 20, 2026 · OneLite team

**OneLite** is a free, open-source SQLite-compatible .NET engine from Recursive AI, licensed [MIT or Apache-2.0](https://licenses.nuget.org/MIT%20OR%20Apache-2.0). [NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite/1.0.0) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp)

`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")}");
```

*Output · OneLite 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](https://www.sqlite.org/isolation.html), 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](https://www.sqlite.org/pragma.html#pragma_journal_mode) explains.

In [WAL mode](https://www.sqlite.org/wal.html#how_wal_works), 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](https://github.com/recursive-ai-oss/onelite-csharp/blob/06b0dbc3d58fedffc33938bbff6a901376b41a8a/docs/wal.md#L13-L17) 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](https://www.sqlite.org/wal.html#concurrency). 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`](https://github.com/recursive-ai-oss/onelite-csharp/blob/06b0dbc3d58fedffc33938bbff6a901376b41a8a/docs/concurrency.md#L3-L18) 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](https://github.com/recursive-ai-oss/onelite-csharp/blob/06b0dbc3d58fedffc33938bbff6a901376b41a8a/docs/wal.md#L32-L36) 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](https://www.sqlite.org/wal.html#the_wal_file) 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](https://www.sqlite.org/lang_attach.html). On native SQLite, WAL is [persistent](https://www.sqlite.org/wal.html#persistence_of_wal_mode): check the mode returned by `PRAGMA journal_mode=WAL` or `PRAGMA journal_mode=DELETE` rather than assuming the switch succeeded. OneLite’s [`Database.OpenDefault`](https://recursiveai.org/onelite/docs/happy-path/#opening) enables WAL by default, which is why the example starts in that mode.
