The Power Can Fail Between Your Two Statements

This course is about what happens when two transactions run at the same time,

which is the hard and interesting problem. This first lesson is about the part

everybody already half knows, done properly, because the mechanism underneath

it is the one the rest of the course keeps using.

A transaction is a boundary you draw around a group of changes. Inside the

boundary you make as many changes as you like. At the end you either commit,

and every change becomes visible at the same instant, or you roll back, and

none of them ever existed as far as anyone else is concerned.

That is the whole promise when nothing else is running. Not speed, not locking,

not consistency of your business rules. Simply that there is no observable

middle.

The canonical example earns its place. Moving fifty pounds between two accounts

is two writes: subtract from one, add to the other. There is a moment between

them when the money is nowhere. If nothing can observe that moment and nothing

can make it permanent, the pair of writes is a transfer. If either of those

fails, it is a way of losing money.

It is worth being specific about what can interrupt the pair of writes, because

people often picture only one of these.

The process can be killed. The machine can lose power. The disk can accept a

write into its own cache and lose it. The network can disappear between the

application and the database after the first statement. The application can

throw an exception on the line between them. The container can be evicted.

All of these produce the same situation: some of the changes reached storage

and some did not, and nothing in the stored data distinguishes that from a

legitimate state.

The mechanism is a log and a single rule.

Before any change is written to the page where the data lives, a record

describing that change is appended to a sequential log, and that log record is

made durable. This is the write-ahead rule, and the ordering is the whole of

it: log first, data afterwards.

Recovery then has a simple job. Read the log from the last checkpoint. Any

transaction with a commit record gets its changes reapplied, because they may

not have reached the data pages. Any transaction without one gets its changes

undone, because the caller was never told it succeeded. The presence or absence

of one small record decides which.

There is one physical cost in the whole mechanism, and it is at the flush.

A commit cannot be reported to the caller until the log is actually on the

device. Not in the operating system's cache, not in the drive's own cache, but

on durable media. That operation is a disk sync, and it is slow in a way that

is unrelated to the amount of data: a few hundred microseconds on good flash,

several milliseconds on a spinning disk.

Two practical consequences follow from that figure, and both show up in real

systems.

Many small transactions are far more expensive than one larger one covering the

same work, because each pays its own sync. A loop that commits once per row is

the most common cause of a load that takes hours instead of minutes, and the

fix is to commit once per few thousand rows.

And every database offers a setting that relaxes the sync, reporting a commit

once the log record is in the operating system's cache rather than on the

device. It is genuinely faster and it genuinely gives up durability: a power

failure loses the last fraction of a second of committed transactions. That is

an acceptable trade for some workloads and a catastrophe for others, and the

thing to avoid is having made the trade without knowing it.

The next lesson drops the assumption that nothing else is running, which is

where the subject actually begins.