Keeping data safe: transactions

Emma sends $50 to Lucas. For the database, that's two steps: take $50 out of Emma's account, then add $50 to Lucas's.

Now imagine the power goes out right after step one. Emma's money is gone, and Lucas never received it. $50 has disappeared.

Databases stop this with a transaction: a group of steps that must all succeed together or not happen at all. It's like a sealed envelope. Either the whole envelope is delivered, or it's returned to sender unopened. Half a letter never arrives.

A transaction makes four promises, known as ACID:

• Atomic: all or nothing. Both steps happen, or neither does.
• Consistent: the rules always hold. For example, a balance can never go below zero if that's a rule.
• Isolated: if two people make changes at the same time, they don't trip over each other. It's as if they took turns.
• Durable: once the database says "saved", it stays saved, even if the power fails a second later.

How does it keep that last promise? Before changing anything, the database writes a note in a diary called the log: "about to move $50 from Emma to Lucas". If something crashes, it reads the diary when it restarts and either finishes the job or cleanly undoes it.

Later in the course you'll control transactions yourself with BEGIN, COMMIT and ROLLBACK.