Part 2 of 5 · 1 min read
The write does not go straight to the data file
A write first changes an in-memory copy of a page in the buffer pool.
Think of correcting a typo on a printed page. First mark the correction on a sticky note stuck over it, before the printer is ever asked to reprint the final page.
In plain words: a write statement, such as an UPDATE, does not modify the data file on disk directly. It first locates the relevant page in the buffer pool, pulling it in from disk if it is not already cached, and changes the in-memory copy of that page. The page is now dirty, as covered on the buffer pool page, and the actual data file on disk still holds the old value for a while longer.
A worked example. Updating one book's price in the bookstore database finds that book's page already cached in the buffer pool, flips the in-memory price value, and marks the page dirty, all without touching the data file on disk yet.
The key lines.
UPDATE books SET price = 24.99 WHERE book_id = 501;
-- the buffer pool's in-memory copy of this page changes immediately;
-- the on-disk data file still holds the old price for now
In-place update (buffer pool)
An in-place update in the buffer pool changes a page's values directly in memory, without immediately writing those changes to the data file on disk.
Example: the sticky-note correction sits on the in-memory page, not yet reprinted onto the permanent data file.