Pinning Pages

Why this stage exists

In the io-counters stage the pool got a size, and pages started leaving it. That opened a hole you may already have fallen into. An INSERT finds its leaf and starts to change it, then needs the parent to add a separator, and then a fresh page for the split. With a small pool, each of those requests can evict a page to make room, and nothing stops it from evicting the leaf the INSERT is in the middle of changing. The leaf's frame gets the new page's bytes. The INSERT writes its cell into what it thinks is the leaf and is really someone else's page. That is why SQL only ran with the default pool until now.

The fix every database uses is a pin: a count on each page in the pool of how many pieces of code are using it right now. A page with a count above 0 stays where it is. Requesting a page pins it; releasing it unpins it. Eviction only considers pages nobody is using.

Pins are simple, and the bug they bring is just as simple: forgetting to release one. A page that is never unpinned never leaves. With 1000 frames you would never notice. With 16, a few leaked pins and the pool is full of pages nobody is using and nothing can be evicted. Every later stage in this part, and the transactions that come after it, rely on pins being exactly right.

From this stage the tests run SQL with --cache-pages 16 too: a full scan of a table with far more leaves than frames, and a mix of INSERT, UPDATE and DELETE whose file must come out byte for byte the same as with 1000 frames. Every dirty page that leaves the small pool is written as it leaves, so the two files match.

What changes: every page has a count

Each page in the pool now carries a pin count next to its dirty flag. A request adds one, a release takes one away, and a page with a count of 0 is the only kind that can be evicted:

Pins add up. A tree walk that holds a parent while it reads a child has both pinned at once; pager pin twice on the same page needs two pager unpin. The count tells the pool how many releases are still owed.

When a page must come in, the pool is full and every page in it is pinned, there is no victim. The request fails with 01201, and the command prints that line and nothing else. Nothing changes: the page does not come in, pager alloc allocates nothing, and every count stays as it was. With --cache-pages 2 and two pages pinned by hand, you can see it on purpose. With 16 frames and correct SQL you never should.

In PostgreSQL a backend pins a buffer before reading it and drops the pin when it is done, and pins left at the end of a transaction are reported as a bug. SQLite calls the same idea a page reference, and counts them for the same reason.

What changes: no pin outlives its command

Inside a command, pins come and go. Between commands, the only pins left are the ones you made on purpose with pager pin. After any command, successful or not, pager frames shows pin=0 on every other page.

The hard part is not the successful path. It is every way a command can stop early: a duplicate key found half way down the tree, an UPDATE that tries to change the key, a value too long for its column, a page that fails its checksum in the middle of a scan. Each of those leaves the code somewhere in the middle of its work, holding pages. Every page it requested must still be released. Whatever your language gives you for "run this however the block ends" is what this stage is about.

Output contract for this stage

The full definition is on the output contract page, section 24.

pager pin is a request like pager get: a hit or a miss in pager stats. pager unpin and pager frames request nothing. dirty is exact after pager commands; after SQL it only has to be 1 on a page whose bytes changed.

What you should see when done

A file of five pages and a pool of two frames:

$droid --db notes.db --cache-pages 2
Welcome to droid!
>pager frames
[PAGER] Frames: 1 of 2
[PAGER]   Page 0: pin=0 dirty=0
>pager pin 0
[PAGER] Pin page 0: pin=1
>pager get 1
[PAGER] Get page 1: CACHE_MISS (loaded from disk)
>pager get 2
[PAGER] Get page 2: CACHE_MISS (loaded from disk)
>pager get 0
[PAGER] Get page 0: CACHE_HIT
>pager pin 2
[PAGER] Pin page 2: pin=1
>pager get 3
[ERROR:01201] Every page in the buffer pool is pinned
>pager unpin 2
[PAGER] Unpin page 2: pin=0
>pager get 3
[PAGER] Get page 3: CACHE_MISS (loaded from disk)
>pager frames
[PAGER] Frames: 2 of 2
[PAGER]   Page 0: pin=1 dirty=0
[PAGER]   Page 3: pin=0 dirty=0
>pager unpin 2
[ERROR:01202] Page 2 is not pinned
>.exit
exiting.. good bye!

Things to notice. Page 2 pushed page 1 out, not page 0: page 0 was pinned. Once both frames were pinned, page 3 could not come in at all, and after one unpin it could. The last unpin was refused because page 2's count was already 0, even though page 2 was no longer in the pool.

Then run a few statements on a table big enough to have several levels, including some that fail, and look at pager frames after each one: every pin= should be 0.

Common bugs in this stage

• Leaking on the error path. The classic. The release is written at the end of the function, and the duplicate-key check returns before it. Test every error a statement can hit and look at pager frames after it.
• Releasing too early. Unpinning a page and then still writing to its bytes works with a big pool and corrupts another page with a small one. Release after the last use, not after the last read you remember.
• Unpinning twice. A count that goes below 0, or a release for a page you never requested, hides a leak somewhere else. Treat it as a bug, not as a no-op.
• Pinning in pager get. get, read and write pin their page for the command and release it before the next one. Only pager pin leaves a pin behind.
• Failing half way through a 01201. Check for a victim before you change anything: the command that hits 01201 must leave every count, every frame and the page total as they were.
• Writing a pinned dirty page on eviction. It cannot be evicted, so it is not written. Only pager flush and the end of the session write it.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: no destructors, so every early return is a chance to leak. A single exit path per function, with goto cleanup releasing what was requested, is the usual C answer.
• C++: a small guard object whose destructor unpins makes the error paths correct by construction, exceptions included. Make it movable and not copyable, or two guards will release one pin.
• Rust: a guard that unpins in Drop does the same, and early returns with ? then release for free. It also gives the borrow checker something to hold: the page's bytes can live exactly as long as the guard.
• Zig: a defer that releases the pin, right after each request, and errdefer for anything that must only be undone on failure. Put the defer on the line after the request so the pairs are impossible to miss.