A library card catalogue with a night-time ledger
A school library has a card catalogue: a small cabinet that lets you find a book by author without walking every shelf. It also has a ledger where the librarian writes every change before touching the shelves, so if the lights go out mid-shift, tomorrow's librarian can finish the job.
A database is those two ideas, scaled up. Indexes are the catalogue. The write-ahead log is the ledger. Transactions are the rule that a change either happens completely or not at all. This guide opens the box so that when an interviewer asks "why is this query slow?" or "what happens if two users update at once?", you can answer from mechanism, not memory.
It builds on Foundations and data, which introduced indexes, isolation, and replication briefly. The next guide, Scaling data, takes these ideas across many machines.
1. ACID, one letter at a time
ACID is a checklist for a transaction: a group of reads and writes treated as one unit.
| Letter |
Plain meaning |
Mechanism usually behind it |
| Atomicity |
All of it happens, or none of it |
Undo information and the write-ahead log |
| Consistency |
Rules you declared (keys, checks, foreign keys) are never left broken |
Constraints checked at commit, plus your application logic |
| Isolation |
Concurrent transactions do not see each other's half-finished work |
Locks, or multiple row versions (MVCC) |
| Durability |
Once "committed" is reported, a crash will not erase it |
The log is forced to durable storage before the reply |
Two honest caveats. The "C" is partly your job: the database enforces the rules you wrote, but it cannot know a rule you never declared. And "isolation" is a dial, not a switch, which is the next section.
2. Isolation levels and the strange things that happen without them
If two customers buy the last copy of a book at the same moment, the outcome depends on how strictly the database keeps transactions apart. The SQL standard names four levels, defined by which anomalies they allow.
| Anomaly |
What goes wrong |
Example |
| Dirty read |
You read data another transaction has not committed |
You see a stock count that is later rolled back |
| Non-repeatable read |
Reading the same row twice gives different values |
Price is 100, then 90 inside one transaction |
| Phantom read |
Re-running a range query returns new rows |
"Count orders today" changes mid-report |
| Lost update |
Two read-modify-write cycles overwrite each other |
Both add 1 to a counter that reads 5; the result is 6, not 7 |
| Write skew |
Two transactions each check a rule, then write different rows, together breaking the rule |
Two doctors both go off-call because each saw the other still on call |
You've reached the free preview of this post.
Subscribe to the Blogs plan (₹99/mo) — or use any Algomi Suite, Career, Investor, or Trader plan — to keep reading.
See plans
