SQLite Atomic Commit
SQLite's central correctness claim is atomic commit: after a successful
COMMIT with synchronous=FULL, the transaction is durably persisted;
after any failure (power loss, kernel crash, process crash), recovery converges to either
the pre-commit state or the post-commit state — never any in-between. SQLite achieves this
with two protocols: the original rollback journal (a separate file that
holds copies of pages before they're overwritten) and the modern WAL (a
separate file that holds the new pages until checkpointed). This page walks the nine
precisely-ordered steps of the rollback journal commit, explains why each fsync barrier is
placed exactly where it is, and contrasts with WAL.
The 9 Steps of Rollback-Journal Commit
Key Numbers
Why Two fsyncs
The 9 Steps in Detail
Each step has a specific role in the recovery argument.
1. Acquire RESERVED lock. SQLite's locking protocol has 5 levels: UNLOCKED, SHARED, RESERVED, PENDING, EXCLUSIVE. RESERVED means "I intend to write." Other readers may continue reading; no other writer can begin. This is the gate that ensures only one writer at a time.
2. Read original pages into memory. The pages that will be modified are read into the page cache. They're now in memory, dirty in the sense that they'll be modified, but unchanged on disk.
3. Write originals to the rollback journal. The page cache writes the
original (unmodified) page contents to foo.db-journal, prefixed with a header
identifying which page they came from and a checksum. The journal grows with each modified
page.
4. Modify pages in memory. The transaction's actual changes are applied to the in-memory page cache. The database file on disk is still unchanged.
5. fsync the journal file. The first crucial fence. After this returns successfully, the journal is durable on disk. If we crash now, recovery can read the journal, find original page contents, and restore the database to the pre-transaction state.
6. Write modified pages to the database file. The dirty pages from the page
cache are written to foo.db. The database file now contains the new state, but
it's not yet fsync'd — and the journal still exists on disk for rollback.
7. Acquire EXCLUSIVE lock. Block all readers. Required because the next step will fsync, and after that the journal will be deleted; we don't want any reader to see the pre-fsync database state.
8. fsync the database file. The second crucial fence. After this, the new page contents are durable on disk. The journal still exists, but its job is done.
9. Delete the journal (or zero its header). This is the canonical "transaction committed" event. Future opens will see no journal and proceed normally. The delete is atomic from POSIX — either the directory entry exists or it doesn't.
Recovery: What Happens Without a Clean Shutdown
Open a database with a journal still present.
SQLite opens database.db
→ notices database.db-journal exists
→ reads journal header
→ if header is zeroed or invalid: discard, no recovery needed
→ if header is valid: enter recovery
for each page in journal:
restore that page in database.db using journal data
fsync database.db
delete journal Recovery is idempotent. If the host crashes mid-recovery, the next open notices the journal again and replays. Eventually it succeeds and the journal is removed.
Why is replay safe even when some pages may already match the journal's "original" content? Because replay just writes the journal's pages to the database. A page that already matches gets re-written with the same bytes. No corruption. The cost is wasted I/O proportional to the journal size, but correctness is preserved.
The Journal File Format
Self-describing, with checksums, designed for crash recovery.
offset size field 0 8 Magic: 0xd9d505f9 0x05ff0d97 (8 bytes) 8 4 Number of pages in journal (or 0xffffffff if unknown) 12 4 Random nonce for checksum 16 4 Initial database size in pages (for truncation) 20 4 Sector size (for sub-sector recovery) 24 4 Journal page size then for each page in the journal: +0 4 Page number being saved +4 SZ Original page contents (database page size) +SZ 4 Page checksum (with the random nonce mixed in)
The random nonce in the header is mixed into each page's checksum. If the journal is partially written and then overwritten by an unrelated writer (e.g. a stale FS block), the checksums won't match the new nonce and recovery rejects the journal. This is the same defense that WAL uses with its salts.
WAL: The Modern Alternative
Same atomic-commit guarantee, different mechanism.
WAL inverts the rollback journal's design. Instead of saving original pages to a side file
so they can be restored on rollback, WAL keeps the originals in the main database file
untouched and writes new pages to a side file (foo.db-wal).
Readers continue to see the original database; writers append to the WAL. A periodic
checkpoint folds the WAL back into the main database.
Atomic commit in WAL: a frame in the WAL is marked as a "commit frame" by setting its commit flag to the new total page count. After fsync of the WAL up to and including the commit frame, the transaction is durable. Readers that arrive after this see the new state; readers that started before still see the original.
WAL is the recommended journal mode in modern SQLite. It allows concurrent reads with a
single writer (the rollback journal serializes everyone), and the fsync cost is one per
transaction (the WAL append) rather than two. The rollback journal remains the default for
backward compatibility on disk; new applications should use PRAGMA journal_mode=WAL.
FAQ
What is the actual atomic-commit guarantee?
After fsync, either all of a transaction's writes are durably visible or none of them are. There is no in-between state on disk that a future read can observe. SQLite achieves this with the rollback journal protocol or the WAL protocol, both of which use fsync at exactly the right moments to ensure that a power-cut at any instant leaves a recoverable state. The atomicity is per-database; with ATTACH'd multiple databases, each commits atomically but cross-database commit is two-phase.
Why does the rollback journal copy old pages instead of new ones?
Because the database file's pages can be modified in place. To roll back, you need the original pages — which the journal preserves before they're overwritten. WAL inverts this: original pages stay untouched in the database, and new pages go to the WAL. Both approaches achieve atomicity; the rollback journal optimizes for the no-concurrent-readers case (writer can take exclusive lock and overwrite pages directly), while WAL optimizes for many-readers-one-writer.
What guarantees does fsync give you?
On a healthy filesystem, fsync flushes the file's in-flight writes to durable storage and returns success only when they're truly persisted. Catches: (1) Some SSDs lie — they ack the fsync before data is on flash, betting on capacitor-backed buffers to finish the write on power loss. Quality enterprise SSDs are honest. (2) Some filesystems (ext4 with data=writeback) only fsync data, not the journal entries describing where data lives — a metadata loss can lose the file even though data was 'flushed.' SQLite documents these caveats. (3) Network filesystems (NFS) frequently lie about fsync.
What are the 9 steps for real?
From SQLite's docs, in rollback-journal mode: (1) acquire RESERVED lock; (2) read original pages into memory; (3) write originals to journal file; (4) modify pages in memory; (5) fsync journal file (atomic-commit fence #1); (6) write modified pages to database file; (7) acquire EXCLUSIVE lock; (8) fsync database file; (9) delete journal (or zero its header). The fsync at step 5 is the commitment point — after this, recovery can replay or roll back deterministically.
Why is journal deletion (step 9) so important?
Because the presence of a journal file is the recovery indicator. If SQLite opens a database and finds a non-trivial journal, it knows a transaction was in flight when the previous instance was killed and replays the journal to roll back. If the journal is missing or its header is zeroed, no recovery needed. The 'commit' visible to readers is the moment the journal is unlinked (or its header is overwritten with zeros) — atomically, that's when the new state becomes the official state.
Can power loss between steps corrupt the database?
By design, no. Power loss at any of the 9 steps leaves a recoverable state. Before step 5, the journal is incomplete (or doesn't exist), and the database file is untouched. Recovery sees an incomplete journal and discards it. After step 5 but before step 9, the journal has all original pages and the database may have partial new pages; recovery replays the journal to roll back to the pre-transaction state. After step 9, the new state is the canonical one; the journal is gone. The 'all-or-nothing' property holds at every instant.