Pager Lifecycle Functions: Pinning Pages, Running Transactions, and Making Them Stick in SQLite

Source: Dev.to

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—important because sqlite3PagerWrite may already have started an implicit write transaction, and SQLite never duplicates work unnecessarily.

The pager may also 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:

1. Increments the file‑change‑counter in the database header
2. Syncs the rollback journal to disk
3. Writes all dirty pages from the cache to the database file
4. Syncs the database file itself

After this phase the database file contains the new data while the journal still exists, so 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—deleting, truncating, or invalidating 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 requests 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

• 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 Rollback

The same function performs two very different actions, depending on the request.

Savepoint Release

• Destroys the savepoint handler
• Keeps all changes made since the savepoint

Savepoint Rollback

• Restores the 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 the pager to do so.

Wrapping Up the Pager Arc

Across the last few posts we’ve moved from:

1. Journals
2. Transactions
3. Locks
4. Savepoints
5. The pager functions that glue everything together

My experiments and hands‑on executions related to SQLite live here:
lovestaco/sqlite – sqlite‑examples

References

• SQLite Database System: Design and Implementation – Sibsankar Haldar.
• FreeDevTools – A collection of free developer tools.

👉 Check out:

Any feedback or contributions are welcome!
It’s online, open‑source, and ready for anyone to use.

⭐ Star it on GitHub: