Durability

Recall the buffer pool from the previous chapter: it is the database’s own cache, where it keeps pages of data in memory. I explained how it can speed up reads if what is queried is already in the buffer pool. Writes work the same way: they are made in memory first too. When you update a row, the database loads that row’s page into the buffer pool if it is not already there, and changes it in memory. The change is not written to disk right away. It is written later, in the background, in batches. The database does this to make writes faster.

Now consider a purchase, which is two writes in one transaction. When the two writes run, the database changes the rows in memory. When it commits, the change is still only in memory. If the machine loses power or the database process crashes, the change is lost. This is going to be a problem for the Dining Dollars app, because the app tells the student “purchase confirmed” when the database commits. The student takes the lunch, and if the change is lost, the student gets something they were not charged for. The app has to be able to trust that a confirmed purchase is permanent.

To close the gap, the database must not say “committed” until the change is safe on disk. Writing the whole page to disk on every commit would be expensive: a page is large, and each one sits at a different place on the disk.

So the database does something smaller. Before it says “committed,” the database writes short log records for the transaction’s changes to the end of a log file on disk. Writing those records together is cheaper than writing the changed data pages to their separate places on disk. Several transactions committing at about the same time can have their log records written together. Once the records for a transaction are safely on disk, the database says “committed.” The page itself is still in memory and still goes to disk later, in the background, as before.

If the machine crashes after the commit and before the page is written, the database reads the log when it starts again. It finds the log record of the committed change, sees that the page on disk does not have it yet, and applies the change. The change survives.

This guarantee is called durability: once the database says a write is committed, the write survives a crash or a power failure.

Relational databases offer this by default. Some let you turn it off. If so, the database says “committed” as soon as the log record is in memory and writes it to disk later. Commits get faster but at the cost of durability: after a crash, the most recent commits can be lost. Some systems accept that trade-off for data they can afford to lose. For a purchase that a student was told went through, that is not acceptable.