How Databases Lock Your Data (ACID)

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:

1. Read Uncommitted — you can see uncommitted data. Almost nobody uses this.
2. Read Committed — only see committed data, but values can change between reads.
3. Repeatable Read — same row always returns the same value, but new rows can appear (phantom reads).
4. 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.