Pinning Pages

Give every page in the pool a pin count. A request pins the page, the command unpins it when it is done, and only a page with a count of 0 can be evicted. Add pager pin, pager unpin and pager frames, refuse a request with 01201 when every page in a full pool is pinned, and make sure no pin outlives its command, errors included.

Not run yet · 11 tests

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:

Drawing the diagram…

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.

The algorithm in words

Request and release

Every request now comes with a matching release.

1.A request finds or brings in the page as before, then adds 1 to its pin count.
2.When the code that asked is done with the page's bytes, it releases the page: 1 comes off the count.
3.A page may be pinned several times at once, by a parent and a child in a tree walk or by pager pin twice. It can leave only when every pin is gone.
4.Invariant: between commands, the only pages with a count above 0 are those pager pin pinned and pager unpin has not yet unpinned.

Choose a victim among unpinned pages

Eviction is unchanged except for one rule.

1.When a page must come into a full pool, only a page whose count is 0 may leave. Which one is still your choice; the clock-eviction stage decides.
2.When every page in the pool is pinned, nothing can leave: the command fails with 01201. It prints that line, the page does not come in, nothing is allocated, and every count stays as it was.
3.A pinned page that is dirty stays dirty: evictions around it never write it. pager flush still does, and leaves it pinned.

Release on every path

A pin that is not released is a frame lost for the rest of the session.

1.Release each page in the same place in the code that requested it, so the pairs are easy to see.
2.When a command stops on an error, a duplicate key, a bad page, anything, every page it requested is still released before the error is printed.
3.After every command, whether it succeeded or failed, pager frames shows pin=0 on every page that pager pin did not pin.

Pin and unpin by hand

The debug commands make the counts visible.

1.pager pin requests the page, which counts as a hit or a miss, and leaves it pinned when the command ends. It stays pinned until as many pager unpin, or the end of the session.
2.pager unpin of a page whose count is 0, in the pool or not, is 01202 and changes nothing. A page never allocated is 00601 for both.
3.pager frames prints how many pages the pool holds out of how many frames, then each page, ascending, with its count and whether it is dirty.

Output contract for this stage

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

Input	Output
`pager pin <n>`	`[PAGER] Pin page <n>: pin=<k>`, `k` the count after it
`pager unpin <n>`	`[PAGER] Unpin page <n>: pin=<k>`
`pager frames`	`[PAGER] Frames: <used> of <frames>`, then for each page in the pool, ascending: `[PAGER] Page <p>: pin=<k> dirty=<0 or 1>`
a page must come in, the pool is full and every page in it is pinned	`[ERROR:01201] <message>`, and nothing changes
`pager unpin` of a page whose count is 0	`[ERROR:01202] <message>`
`pager pin` or `pager unpin` of a page never allocated	`[ERROR:00601] <message>`

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:

shell

$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.

Loading starter code…

No run yet

11 tests wait for your first run

Run tests builds your code, drives the binary through every case for this stage, and shows expected and received output side by side here. Failing cases come first.