Transactions (MVCC)

The board right after the one on the write-ahead log (Write-Ahead Log), inside the project lesson Build Your Own Database - From Scratch.

Before you start. This series builds a small database, a program that stores rows on disk and answers queries the way Postgres or SQLite do. So far it writes every change to a log file before it touches the row itself. That means a crash halfway through does not lose or corrupt anything. It replays that log to put itself back together after a restart. Every one of those changes has, until now, been the only thing happening at a time.

The idea in 30 seconds

The old fix for two programs touching the same row was a lock: one waits for the other, and one slow reader can freeze every writer.
The fix, called MVCC (multi-version concurrency control: many versions, so many can work at once), is to never overwrite a row: append a new version, and keep the old ones around.
Every version carries the number of the transaction that made it and the one that ended it, if any.
A transaction takes one fixed picture, its snapshot, the moment it begins, and never updates it.
Committing a transaction is one word changed from “in progress” to “done.” Aborting is the same flip to “never happened,” and nothing has to be undone.
Old versions pile up, and something has to clean them out later - that part of the story is the second half.

Read the story · Skip to the numbers