Hello, I'm Maneshwar. I'm working on FreeDevTools online currently building **one place for all dev tools, cheat codes, and TLDRs* — a free, open-source hub where developers can quickly find and use tools without any hassle of searching all over the internet.*
Yesterday we built intuition around rollback journals: before-images, segments, headers, and why SQLite can survive crashes without ever corrupting the db file.
Today’s learning completes the legacy journaling picture by covering two lesser-known but crucial pieces the statement journal, and the master journal for multi db transactions.
Together, these explain how SQLite achieves correctness not just across crashes, but also across statement failures and multi db commits.
This closes the storage + journaling chapter cleanly.
Tomorrow, we move up a layer into transaction management and locking.
Statement Journal: Rolling Back a Single Statement
A statement journal exists for one very specific reason:
To undo the partial effects of a single SQL statement that fails midway.
This happens when an INSERT, UPDATE, or DELETE touches multiple rows, and a constraint violation or trigger exception occurs partway through execution.
At this point, the user transaction must remain active, but the statement must be rolled back.
That is where the statement journal comes in.
What the Statement Journal Is (and Is Not)
- It is a separate rollback journal file
- Stored as a temporary file (random
etilqs_*name) - Lives in the system temp directory (e.g.
/tmp) - Exists only during statement execution
- Deleted immediately after the statement completes
it:
- is not used for crash recovery
- does not have segment headers
- does not store checksums
- does not persist across crashes
All metadata like number of records (nRec), db size at statement start are kept in memory, not on disk.
Statement Journal Lifecycle
The key idea:
SQLite can roll back a single statement without aborting the user transaction.
SAVEPOINT Interaction
SAVEPOINTs complicate things slightly.
To support nested savepoints:
- SQLite retains statement journals
- Journals are deleted only when:
- the savepoint is released, or
- the outer transaction commits
This is how SQLite achieves fine grained rollback semantics without exposing complexity to the user.
Multi-db Transactions and the Problem of Atomicity
SQLite allows multiple databases to be attached to a single library connection:
ATTACH 'db2.sqlite' AS db2;
A single transaction can now modify:
maindb2- any number of attached databases
Each db:
- has its own rollback journal
- commits independently
Left alone, this would break atomicity.
More about multi-db here:
The Master Journal: Coordinating Child Journals
To make a multi-db transaction globally atomic, SQLite introduces the master journal.
Important properties:
- Created only when committing
- Deleted once commit finishes
- Never created for transaction aborts
- Contains no page images
- Stores names of child rollback journals
Each rollback journal participating in the transaction becomes a child journal.
Master Journal
- Lives in the same directory as the main db
- Name format:
<main-db-name>-mj<random-hex>
- The random suffix avoids collisions
What the Master Journal Contains
The master journal stores:
- Full UTF-8 paths of all child rollback journals
- Names separated by
\0(null characters)
Nothing more.
No pages.
No before-images.
Just coordination metadata.
How Child Journals Reference the Master Journal
At commit time:
- Each child rollback journal appends a master journal record
- This record is written at a sector boundary
- It contains:
- length of master journal name
- checksum of the name
- the name itself (UTF-8)
- forbidden page number (lock-byte page)
This two-way linkage guarantees correctness during recovery.
Why the Master Journal Exists
If SQLite crashes during commit:
- Recovery checks child journals
- Child journals reference the master journal
- Master journal confirms which journals belong together
Only when all child journals are safely committed is the transaction considered complete.
This preserves global atomicity across databases.
Forbidden Page Number (Lock-Byte Page)
Each master journal record includes a forbidden page number.
This refers to the lock-byte page, which:
- is reserved
- is never written
- exists to handle Windows vs POSIX locking differences
SQLite avoids touching this page entirely.
Big Picture: Legacy Journaling Architecture
Each journal has a sharply defined role:
| Journal | Purpose |
|---|---|
| Rollback | Crash recovery |
| Statement | Single statement rollback |
| Master | Multi db atomicity |
Summary
- SQLite stores data and metadata in a single file
- Files are structured as fixed size called pages
- Page 1 anchors the db and contains metadata
- Free pages are tracked explicitly using a freelist
- Legacy journaling uses three journal types:
- rollback
- statement
- master
- Rollback journals ensure crash safety
- Statement journals ensure statement level correctness
- Master journals ensure global atomicity across databases
This completes the storage and journaling story.
Tomorrow we leave files behind and move into runtime behavior:
- Transaction types
- Locking modes
- Shared vs reserved vs exclusive locks
From bytes on disk → to concurrency in memory.
My experiments and hands-on executions related to SQLite will live here: lovestaco/sqlite
References:
SQLite Database System: Design and Implementation. N.p.: Sibsankar Haldar, (n.d.).
👉 Check out: FreeDevTools
Any feedback or contributors are welcome!
It’s online, open-source, and ready for anyone to use.
⭐ Star it on GitHub: freedevtools
Top comments (0)