Where did the 200 pounds go?
The coffee shop pays its bean supplier 200 pounds through its banking system.
The system takes 200 pounds from the shop's account. Then the server crashes before it adds the money to the supplier. The shop is 200 pounds poorer and the supplier has nothing. How do banks stop this happening?
By the end of this lesson
Note
I can explain why a transfer must be all or nothing.
Key words
| Word | What it means | Example |
|---|---|---|
| Transaction | a group of changes that succeed or fail together | |
| Commit | to save every change in the transaction | COMMIT; |
| Rollback | to undo every change since the start | ROLLBACK; |
| Atomic | done completely or not at all | |
| Balance | the money in an account |
The cells students fill in are left blank in this preview.
A transfer is two writes
Moving money is never one change. It is two: take from one account, add to the other.
Between the two writes, many things can go wrong. The server can crash. The network can drop. A check can fail. If only the first write is saved, money disappears. The two writes must be atomic: both happen, or neither does.
Worked example: a crash between the writes
| Moment | Shop | Supplier | Total money |
|---|---|---|---|
| Before | 500 | 100 | 600 |
| After taking 200 | 300 | 100 | 400 |
| Crash: nothing more happens | 300 | 100 | 400, so 200 is lost |
| With a transaction: rolled back | 500 | 100 | 600 |
The cells students fill in are left blank in this preview.
BEGIN starts it and COMMIT saves it
BEGIN starts a transaction. The writes after it are held, not yet final.
COMMIT saves every write in the transaction at once. Until the commit, no other user sees the half-done change. If the server crashes before COMMIT, the database throws the held writes away when it starts again.
Try it: a transfer that commits
-- Pay the supplier 200 pounds: two writes, one transaction.
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Coffee shop';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Bean supplier';
COMMIT;
-- Both changes were saved together.
SELECT * FROM accounts;
ROLLBACK undoes the lot
Sometimes the program finds a problem part way through. The supplier's account is closed. Or the shop does not have enough money.
Then the program says ROLLBACK. Every write since BEGIN is undone. The data is exactly as it was, as if the transfer was never tried.
Try it: a transfer that rolls back
-- Something goes wrong after the first write.
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Coffee shop';
-- The program finds a problem, so it undoes everything since BEGIN.
ROLLBACK;
-- The shop still has 500: nothing was half done.
SELECT * FROM accounts;
-- Change ROLLBACK to COMMIT and run it. What goes wrong?
A common mistake
Warning
Many people think that if the second write fails, the database undoes the first one by itself. It only does that inside a transaction. Without BEGIN, each write is saved the moment it runs.
The same rule in a program
Application code follows the same rule. Check everything that could fail before changing anything. Then make all the changes together.
In the task, transfer takes the money first and checks afterwards. Fix it so a refused transfer changes nothing at all.