Rollback Journals and Atomic Commit from the Pager’s Point of View

Learning outcomes

A transaction can be logically correct yet still need protection from process crashes, operating-system crashes, and power loss. SQLite’s pager layer makes the SQL transaction appear atomic even though storage devices write pages over time. This lesson follows the classic rollback-journal path; WAL uses a different mechanism and is reserved for Chapter 9.

Why direct overwrites are dangerous

Imagine one transaction must change database pages 5, 12, and 40. If SQLite overwrote page 5 and power failed before pages 12 and 40, the file could contain a mixture of old and new transaction state. Atomicity requires a way to recover to one coherent side of the commit boundary.

Before transaction         During logical change
+------------------+       +------------------+
| DB page 5 : old  |       | memory: page 5'  |
| DB page 12: old  |  -->  | memory: page 12' |
| DB page 40: old  |       | memory: page 40' |
+------------------+       +------------------+

Unsafe idea: overwrite each DB page and hope the machine stays up.
SQLite rollback-mode idea: save originals first, then make commit recoverable.

The pager and rollback journal

The pager is SQLite’s subsystem that manages database pages, cache state, locking, journaling, and the transition between in-memory changes and persistent storage. In rollback mode, before changed database pages are overwritten, SQLite records the original page content needed for rollback in a separate journal file.

fieldnotes.db                  fieldnotes.db-journal
+----------------------+       +---------------------------+
| page 1               |       | journal header            |
| page 2  (old state)  | ----> | original page 2           |
| ...                  |       | original page 17          |
| page 17 (old state)  |       | original DB size metadata |
+----------------------+       +---------------------------+

The journal is recovery information for the in-progress transaction.
It is not a backup and should not be copied/edited as one.

High-level atomic commit sequence in DELETE journal mode

The official atomic-commit description contains detailed VFS and lock steps. For application developers, the useful high-level sequence is:

Crash before or after the commit boundary

If a crash leaves a valid hot journal, SQLite recognizes that an earlier write did not complete cleanly. On a later open, SQLite can use the journal’s original pages to restore the database to a sane pre-transaction state before normal access continues.

Crash while journal says rollback is needed
          |
          v
Open database later
          |
          +--> detect hot journal
          |
          +--> restore original pages
          |
          +--> clean journal and continue

Result: transaction appears all-old rather than half-old/half-new.

Journal modes: know the names, do not tune yet

PRAGMA journal_mode can query or select modes including DELETE, TRUNCATE, PERSIST, MEMORY, WAL, and OFF. The first three are rollback-journal variants that differ mainly in how the journal is finalized/reused. MEMORY keeps journal information in memory and weakens crash recovery. OFF disables the rollback journal and makes ROLLBACK behavior undefined; it is not a production “speed trick” for this course.

PRAGMA journal_mode;PRAGMA synchronous;-- For this disposable lesson lab only, use classic rollback-journal mode:PRAGMA journal_mode=DELETE;-- Do not change synchronous just to make the lab faster.

In-memory databases have special journal-mode limits. Also, journal mode cannot always be changed while a transaction is active. Treat the returned mode as the truth: requesting a mode does not justify assuming the change succeeded.

Atomicity and durability are related but not identical

Atomicity asks whether a transaction is observed as all-or-nothing. Durability asks whether a transaction that reported commit survives later failures such as power loss. The synchronous setting influences how aggressively SQLite asks the VFS/storage stack to synchronize journal/database/WAL content. The exact durability guarantee also depends on the filesystem, hardware, and VFS implementation.

Safe process-interruption lab on a disposable database

Do not kill a process that owns valuable data. Instead create a dedicated file, commit a baseline, then launch a tiny child process that begins a transaction, changes a row, and exits abruptly without COMMIT. Reopening should expose the committed baseline, not the child’s uncommitted value.

import osimport sqlite3import syspath = sys.argv[1]con = sqlite3.connect(path, isolation_level=None)con.execute("PRAGMA journal_mode=DELETE")con.execute("BEGIN IMMEDIATE")con.execute("UPDATE probe SET value='uncommitted-child' WHERE id=1")# Simulate abrupt process loss. Disposable lab file only.os._exit(17)

# Run this setup once in Python or equivalent application code:import sqlite3, subprocess, sys, pathlibp = pathlib.Path("txn-crash-lab.db")if p.exists(): p.unlink()con = sqlite3.connect(p, isolation_level=None)con.execute("PRAGMA journal_mode=DELETE")con.execute("CREATE TABLE probe(id INTEGER PRIMARY KEY, value TEXT NOT NULL)")con.execute("INSERT INTO probe VALUES(1,'committed-baseline')")con.close()subprocess.run([sys.executable, "crash_lab.py", str(p)])con = sqlite3.connect(p)print(con.execute("SELECT value FROM probe WHERE id=1").fetchone()[0])# expected: committed-baselineprint(con.execute("PRAGMA integrity_check").fetchone()[0])# expected: okcon.close()

Depending on exact timing and platform, you may briefly observe a journal file before recovery/cleanup. The durable lesson is the database state after reopen, not whether a temporary file happened to remain visible at one instant.

Failure diagnosis: do not “clean up” journal files by hand

Checkpoint: reason about crash points

Production judgment and bridge

Application code normally should not manipulate journals. Its job is to use correct transaction boundaries, handle errors, and avoid unsafe file operations while SQLite’s pager executes the storage protocol. Lesson 4 returns to SQL and introduces savepoints: a way to create partial rollback boundaries without pretending SQLite supports nested BEGIN transactions.

Authoritative references

• SQLite — Atomic Commit In SQLite.
• SQLite — PRAGMA journal_mode and synchronous.
• SQLite — Temporary Files Used By SQLite.
• SQLite — File Locking And Concurrency.