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.
In the last post, we walked through the pager’s entry points: opening databases, fetching pages, declaring write intent, and preparing journaling.
Today, we move into the control functions, the ones that manage page lifetimes, transaction boundaries, and commit vs rollback.
This is where the pager stops being a cache manager and fully reveals itself as SQLite’s transaction engine.
Pinning Pages: sqlite3PagerRef and sqlite3PagerUnref
Once a page is handed to the tree module, the pager must make sure that page doesn’t disappear underneath it.
That’s where reference counting comes in.
sqlite3PagerRef: Pinning a Page
This function increments the page’s reference count and marks the page as pinned
Pinned pages are untouchable:
- They won’t be evicted
- They won’t be recycled
- They won’t be overwritten
As long as the reference count is non-zero, the pager guarantees the page’s memory stays valid.
sqlite3PagerUnref: Letting Go
This function decrements the reference count.
When the count reaches zero the page becomes unpinned, eligible for reuse it may be placed on the cache freelist
A subtle but critical behavior happens here:
When all pages become unpinned:
- The pager releases the shared lock on the database file
- The Pager object returns to a neutral, idle state
This is how SQLite avoids holding locks longer than necessary, without explicit lock calls from higher layers.
Starting a Write Transaction: sqlite3PagerBegin
This function marks the beginning of an explicit write transaction.
What it does:
- Acquires a reserved lock on the database file
- Opens the rollback journal (unless the database is temporary)
- Transitions the pager into write mode
If the database is already reserved for writing this function becomes a no-op
That’s important, because:
-
sqlite3PagerWritemay already have started an implicit write transaction - SQLite never duplicates work unnecessarily
There’s also an important option here the pager may acquire an exclusive lock immediately instead of waiting until actual writes begin
This choice affects concurrency and latency, and it’s entirely managed by the pager.
Commit Phase One: Making Data Durable
SQLite commits in two distinct phases.
The first is handled by:
sqlite3PagerCommitPhaseOne
This is the durability phase.
The pager:
- Increments the file-change-counter in the database header
- Syncs the rollback journal to disk
- Writes all dirty pages from the cache to the database file
- Syncs the database file itself
After this phase the database file contains the new data while journal still exists and recovery is still possible
At this point, the transaction is crash-safe, but not yet finalized.
Commit Phase Two: Declaring Victory
The second phase completes the commit:
sqlite3PagerCommitPhaseTwo
This function finalizes the journal file, deletes, truncates, or invalidates it
Once this happens:
- Recovery is no longer needed
- The transaction is officially complete
- Locks can be safely downgraded
This split-phase design is what allows SQLite to survive crashes at any point in the commit sequence.
Rollback: Undo Without Failure
When something goes wrong or when the application asks for it the pager takes a different path.
sqlite3PagerRollback
This function:
- Restores original page contents from the rollback journal
- Reverts all in-memory pages
- Finalizes the journal
- Downgrades exclusive locks back to shared locks
Two important guarantees here:
- Rollback cannot fail
- The database is left in a consistent state
No matter how messy things got during execution, rollback always succeeds.
This is one of SQLite’s strongest correctness promises.
Savepoints: Nested Safety Nets
SQLite doesn’t treat transactions as flat structures.
Every SQL statement runs inside a savepoint, and applications can define their own as well.
The pager exposes two functions for this.
sqlite3PagerOpenSavepoint
This function:
- Creates a new savepoint handler
- Records the current rollback journal position
- Captures the database state at that moment
Savepoints stack, multiple savepoints can coexist.
sqlite3PagerSavepoint: Release or Roll Back
This function does two very different things, depending on the request.
Savepoint Release
- Destroys the savepoint handler
- Keeps all changes made since the savepoint
Savepoint Rollback
- Restores database state to the savepoint
- Undoes all changes made after it
- Deletes the rolled-back savepoint and all newer ones
This mechanism allows SQLite to roll back a single statement without aborting the entire transaction and without reopening files or resetting locks
Again, all handled by the pager.
The Bigger Picture
If you zoom out now, a pattern should be unmistakable.
The pager:
- Owns page lifetimes
- Owns transaction boundaries
- Owns journaling
- Owns durability
- Owns recovery
- Owns rollback
Higher layers simply request operations.
They never enforce correctness, they rely on it.
Wrapping Up the Pager Arc
Across the last few posts, we’ve gone from:
- Journals
- To transactions
- To locks
- To savepoints
- And finally to the pager functions that glue it all together
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
