Database books describe transactions with four words. Their first letters spell ACID.
| Letter | Property | The promise | Example in the bank |
|---|---|---|---|
| A | Atomicity | All the changes, or none. | A transfer that fails halfway keeps no change. |
| C | Consistency | Each transaction goes from one valid state to another valid state. | The CHECK refuses a negative balance, so the transaction fails. |
| I | Isolation | Transactions that run at the same time do not see each other's unfinished work. | Liam's app does not see Ava's transfer until it commits. |
| D | Durability | After COMMIT, the change stays, even after a crash. | A power cut after COMMIT does not lose the transfer. |
Consistency comes from your rules
The database knows only the rules that you give it: primary keys, foreign keys, NOT NULL, UNIQUE and CHECK (Chapter 5). A transaction that breaks a rule fails, and atomicity undoes all of it.
A rule that the database does not know, it cannot protect. "The total money in the bank stays the same" is a rule only your transfers keep.
Isolation has levels
Full isolation makes transactions wait for each other. Most databases give less isolation by default, to be faster. Lessons 6 and 7 of this chapter show what can go wrong and how to choose a level.
Durability needs the disk
COMMIT returns only after the change is safe on disk. PostgreSQL first writes each change to the write-ahead log (WAL), a file on disk. After a crash, PostgreSQL reads the WAL and repeats every committed change.