22 / 32 · 5 min
Transactions and ACID
A transfer takes money from one account and adds it to another. If the machine dies between the two, the money has vanished. A transaction makes the pair inseparable.
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;ACID is four promises:
Atomicity. All of it or none. A crash after the first update rolls it back.
Consistency. Constraints hold before and after. A transaction that would leave a balance negative is refused.
Isolation. Concurrent transactions do not see each other's half-done work.
Durability. Once committed, it survives a power cut. This is what the write-ahead log is for.
Isolation is the one with degrees, because full isolation is expensive:
| Level | Allows |
|---|---|
| Read uncommitted | Dirty reads |
| Read committed | Non-repeatable reads |
| Repeatable read | Phantoms |
| Serializable | Nothing: as if run one at a time |
A dirty read sees data another transaction has not committed and may roll back. A non-repeatable read gets a different answer to the same query within one transaction. A phantom is a new row appearing in a repeated range query.
Most databases default to read committed: dirty reads are unacceptable, and serializable is too slow for most work.
Takeaway: a transaction turns several statements into one indivisible step. Isolation level is how much concurrency you trade for how much safety.