Databases 101
Transactions, isolation levels, and indexes. The three things every later note assumes you already have in your hands.
Prerequisites: Basic SQL and a rough idea of what a table is.
After this: Pick an isolation level on purpose and predict the anomalies the weaker ones allow.
Suggested first pass: Read sections 1–5, answer each section in your own words, then use the remaining failure modes and exercises as the advanced pass.
ACID gets recited as a single idea. It is four separate promises, and they fail in different ways.
- Atomicity: a transaction happens completely or not at all. A transfer debits and credits together, or neither, even if the machine loses power mid-way.
- Consistency: the database's own rules stay true. Unique constraints, foreign keys, checks. The least interesting letter.
- Isolation: concurrent transactions do not corrupt each other. All the subtlety lives here.
- Durability: once the database says "committed", the data survives a crash.
The database does not rewrite your data files on every commit, which would be far too slow. It appends the change to a log and forces that to disk. This is the write-ahead log. After a crash, the database replays the log to reconstruct anything that had not reached the data files yet. Nearly every storage system you will meet does some version of this, which is why WAL shows up again in replication and in Kafka.