Money that vanishes
Sam sends £20 to Priya. The system takes £20 from Sam. Then the power fails.
Priya never got the money. Sam has lost it. Four simple promises, called ACID, stop this from ever happening.
Key words
| Word | What it means |
|---|---|
| Transaction | a group of changes that must all happen or none happen |
| Commit | make the changes of a transaction permanent |
| Rollback | undo the changes of a transaction |
| Atomic | all or nothing: the group can not be left half done |
| Consistent | the data always follows its rules |
| Isolated | two users at once do not spoil each other's changes |
| Durable | once saved, a change survives a crash |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can explain each ACID promise, show it with a transaction and say when a design needs it.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
This is practice
Important
The real task comes from the exam board. This lesson uses an invented organisation and its own made-up data. Your teacher tells you how the real tasks are set and what you may bring.
Worked example: ACID with a money transfer
| Letter | Promise | In the transfer |
|---|---|---|
| A: Atomic | All or nothing | Both the take and the give happen, or neither does |
| C: Consistent | The rules always hold | A balance never goes below zero |
| I: Isolated | Users do not spoil each other | Two transfers at once do not mix up the balances |
| D: Durable | Saved means saved | After the done message, a crash does not lose the transfer |
The cells students fill in are left blank in this preview.
Run it: a transfer that completes
BEGIN;
UPDATE account SET balance_pence = balance_pence - 2000 WHERE account_id = 1;
UPDATE account SET balance_pence = balance_pence + 2000 WHERE account_id = 2;
COMMIT;
SELECT owner, balance_pence FROM account ORDER BY account_id;Run it: a transfer that is undone
BEGIN;
UPDATE account SET balance_pence = balance_pence - 2000 WHERE account_id = 1;
ROLLBACK;
SELECT owner, balance_pence FROM account ORDER BY account_id;What those runs show
In the first run both changes were wrapped in one transaction and committed. Sam went from 5000 pence to 3000, and Priya from 2000 to 4000.
In the second, the first change was undone. Both balances are as they were. That is atomic: no half done state is kept.