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

Pinning Pages: sqlite3PagerRef and sqlite3PagerUnref ## sqlite3PagerRef: Pinning a Page ## sqlite3PagerUnref: Letting Go ## Starting a Write Transaction: sqlite3PagerBegin ## Commit Phase One: Making Data Durable ## sqlite3PagerCommitPhaseOne ## Commit Phase Two: Declaring Victory ## sqlite3PagerCommitPhaseTwo ## Rollback: Undo Without Failure ## sqlite3PagerRollback ## Savepoints: Nested Safety Nets ## sqlite3PagerOpenSavepoint ## sqlite3PagerSavepoint: Release or Roll Back ## Savepoint Release ## Savepoint Rollback ## The Bigger Picture ## Wrapping Up the Pager Arc ## References: 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. 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. This function increments the page’s reference count and marks the page as pinned Pinned pages are untouchable: As long as the reference count is non-zero, the pager guarantees the page’s memory stays valid. 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: This is how SQLite avoids holding locks longer than necessary, without explicit lock calls from higher layers. This function marks the beginning of an explicit write transaction. If the database is already reserved for writing this function becomes a no-op That’s important, because: 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. SQLite commits in two distinct phases. The first is handled by: This is the durability phase. 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. The second phase completes the commit: This function finalizes the journal file, deletes, truncates, or invalidates it This split-phase design is what allows SQLite to survive crashes at any point in the commit sequence. When something goes wrong or when the application asks for it the pager takes a different path. Two important guarantees here: No matter how messy things got during execution, rollback always succeeds. This is one of SQLite’s strongest correctness promises. 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. Savepoints stack, multiple savepoints can coexist. This function does two very different things, depending on the request. 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. If you zoom out now, a pattern should be unmistakable. Higher layers simply request operations. They never enforce correctness, they rely on it. Across the last few posts, we’ve gone from: My experiments and hands-on executions related to SQLite will live here: lovestaco/sqlite 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 Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse - They won’t be evicted - They won’t be recycled - They won’t be overwritten - The pager releases the shared lock on the database file - The Pager object returns to a neutral, idle state - Acquires a reserved lock on the database file - Opens the rollback journal (unless the database is temporary) - Transitions the pager into write mode - sqlite3PagerWrite may already have started an implicit write transaction - SQLite never duplicates work unnecessarily - 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 - Recovery is no longer needed - The transaction is officially complete - Locks can be safely downgraded - Restores original page contents from the rollback journal - Reverts all in-memory pages - Finalizes the journal - Downgrades exclusive locks back to shared locks - Rollback cannot fail - The database is left in a consistent state - Creates a new savepoint handler - Records the current rollback journal position - Captures the database state at that moment - Destroys the savepoint handler - Keeps all changes made since the savepoint - Restores database state to the savepoint - Undoes all changes made after it - Deletes the rolled-back savepoint and all newer ones - Owns page lifetimes - Owns transaction boundaries - Owns journaling - Owns durability - Owns recovery - Owns rollback - To transactions - To savepoints - And finally to the pager functions that glue it all together