https://www.youtube.com/watch?v=wIa-zbRqqIg
Two bank transfers hit at the same millisecond. Both read your balance as $1,000. Both subtract $500. You should have $0 left. But the database says $500.
Your bank just created money out of thin air. This is the lost update problem, and it's the reason every serious database needs transaction safety.
The Fix: ACID
Four rules that every transaction must follow:
- Atomicity — the whole transaction succeeds, or the whole thing rolls back. No half-finished writes.
- Consistency — the database moves from one valid state to another. Break a rule? Transaction rejected.
- Isolation — two transactions running concurrently can't interfere with each other.
- Durability — once committed, it's permanent. Even if the server crashes one millisecond later.
These four properties turn a dumb file into a real database. But the hardest one to get right is Isolation.
Locks: The Simple Approach
When a transaction wants to modify a row, it grabs a lock — like a padlock. Any other transaction touching the same row has to wait.
Transaction A locks the balance, reads $1,000, writes $500, releases. Now Transaction B grabs the lock, reads $500, writes $0. Correct answer. No lost update.
But if every transaction waits in line, your database crawls under heavy load.
Deadlocks: When Locks Go Wrong
Transaction A locks Row 1 and needs Row 2. Transaction B locked Row 2 and needs Row 1. Neither can proceed. They're stuck forever.
Databases detect this by building a wait-for graph. If the graph has a cycle, someone gets killed — the database picks a victim, rolls it back, and lets the other through.
MVCC: The Real Solution
Instead of locking rows, the database keeps multiple versions of each row. Think of it like timeline branches.
Transaction A sees the world as of timestamp 10. When it writes a new balance, it creates a new version — it doesn't overwrite the old one. Transaction B still sees the original. No locks needed.
Readers never block writers. Writers never block readers.
This is how PostgreSQL, MySQL's InnoDB, and Oracle actually work under the hood.
Isolation Levels: The Tradeoff Slider
SQL defines four levels, from chaos to perfect safety:
- Read Uncommitted — you can see uncommitted data. Almost nobody uses this.
- Read Committed — only see committed data, but values can change between reads.
- Repeatable Read — same row always returns the same value, but new rows can appear (phantom reads).
- Serializable — the gold standard. Every transaction behaves as if it ran alone. Safest, but slowest.
Most databases default to Read Committed — the sweet spot between safety and speed.
The higher you go on this slider, the safer your data, but the more you pay in performance. Choose wisely.
Top comments (0)