🗄 SQL & Databases · Chapter 10

Transactions and isolation levels

A transaction is a promise that several statements happen as one. Isolation levels decide how much of other people's half-finished work you're allowed to see.

Study this lesson free in the app →

The basics

A transaction is a group of database statements that succeed or fail as one unit — either all of them take effect, or none do.

The problem it solves: moving ₹500 between wallets is two updates: subtract from one, add to the other. If the server crashes between them, money vanishes. And when thousands of users act at once — two people withdrawing from one joint account at the same second — each statement can be correct on its own while the combination is wrong. Transactions give you a guarantee for both: crashes and concurrency.

The mental model: a bank locker with a two-key rule. Nothing inside changes until you turn the final key (COMMIT); walk away midway and everything snaps back (ROLLBACK). Isolation levels decide how much of other people's half-finished work you can see while your locker is open — and the cheaper defaults let you see more than you'd expect.

Explained

BEGIN;
  UPDATE wallets SET balance = balance - 500 WHERE user_id = 'U1';
  UPDATE wallets SET balance = balance + 500 WHERE user_id = 'U2';
COMMIT;

Without the transaction, a crash between those two statements destroys ₹500. The money leaves one wallet and never arrives.

With it, either both happen or neither does. That's atomicity, and it's the reason databases exist rather than files.

Two people at one ATM account

A joint account holds ₹1,000. Two card holders withdraw ₹800 at the same moment from different machines.

Both machines check the balance: ₹1,000. Both see enough. Both dispense.

The account is now ₹600 overdrawn, and neither machine did anything wrong — each read a balance that was true when it read it.

The bug lives in the gap between reading and writing. Isolation levels are precisely the rules about what happens in that gap: how much another transaction's in-flight work you can see, and whether the database stops you or lets you race.

Every concurrency bug you will ever meet is a version of these two machines.

ACID, and which letter actually bites

Atomicity — all statements commit or none do. The one people know.

Consistency — the database moves from one valid state to another; constraints hold.

Isolation — how concurrent transactions affect each other. This is the one that causes production bugs, and the only one with a dial.

Durability — once committed, it survives a crash.

Three classic anomalies, in increasing subtlety:

| Anomaly | What happens | | --- | --- | | Dirty read | You read another transaction's uncommitted change, which then rolls back | | Non-repeatable read | You read a row twice in one transaction and get different values | | Phantom read | You run the same query twice and new rows have appeared |

And the one that isn't in the textbook list but causes the most real incidents:

Lost update — two transactions read the same value, both compute from it, and the second write silently overwrites the first. The ATM story.

The four isolation levels

| Level | Dirty read | Non-repeatable | Phantom | | --- | --- | --- | --- | | Read Uncommitted | possible | possible | possible | | Read Committed | no | possible | possible | | Repeatable Read | no | no | possible* | | Serializable | no | no | no |

Each step up costs concurrency — more locking, or more transactions aborted and retried.

What you'll actually meet:

• Postgres, Oracle, SQL Server default: Read Committed. Each statement sees a fresh snapshot of committed data. Two statements in one transaction can see different worlds
• MySQL InnoDB default: Repeatable Read. The whole transaction sees one snapshot
• Serializable — behaves as if transactions ran one after another. In Postgres this is optimistic: it lets them run and aborts one with a serialization failure, so your application must retry

*Postgres's Repeatable Read uses snapshot isolation, which already prevents most phantoms — one of many reasons the standard's names map imperfectly onto real engines.

The practical takeaway: know your engine's default, and know that Read Committed does not protect you from lost updates.

Watch ₹500 disappear

Both transactions were valid. The database did what it was asked. The bug is in the read-then-write gap — and closing it is the application's job.

Three fixes, one right answer

You must stop the lost update above. Three proposals:

A — raise the isolation level to Serializable.

B — do the arithmetic in the database:

UPDATE wallets SET balance = balance - 800
 WHERE id = 'U1' AND balance >= 800;

C — lock the row when reading:

SELECT balance FROM wallets WHERE id='U1' FOR UPDATE;

Where transactions meet pipelines

Most of a data engineer's transaction work isn't wallets — it's making a load atomic.

The problem: a job rewrites a partition. Halfway through, it fails. Now the table holds half of yesterday and half of today, and every dashboard reading it shows garbage — with no error anywhere.

Fix 1 — wrap it. Both statements or neither:

BEGIN;
  DELETE FROM daily_revenue WHERE ride_date = '2026-07-31';
  INSERT INTO daily_revenue SELECT ... WHERE ride_date = '2026-07-31';
COMMIT;

Readers see the old partition until the instant of commit, then the new one. Never a mixture.

Fix 2 — atomic swap, which avoids holding a long write lock:

CREATE TABLE daily_revenue_new AS SELECT ...;
BEGIN;
  ALTER TABLE daily_revenue     RENAME TO daily_revenue_old;
  ALTER TABLE daily_revenue_new RENAME TO daily_revenue;
COMMIT;

In a warehouse or lakehouse, the same guarantee comes from the table format rather than from you: Delta Lake, Iceberg and Snowflake give atomic commits on writes. A failed Spark job leaves no partial table — readers continue seeing the previous snapshot. That is what people mean by "ACID on object storage", and it's a genuinely important property, because the alternative is exactly the half-written-partition problem above.

What none of them give you is a transaction spanning your Kafka consumer and your warehouse. That boundary is where idempotency takes over.

Isolation is the letter that bites. Know your engine's default — Read Committed does not prevent lost updates.

Never write a value you computed from a stale read. balance = balance - 800, not balance = 200.

Serializable means you must implement retry.

Words to know

In two minutes

• A transaction makes several statements atomic: COMMIT applies all, ROLLBACK (or a crash) applies none.
• Isolation is the ACID letter that causes production bugs, and the only one with a dial.
• Read Committed (Postgres default) gives each statement a fresh snapshot: no dirty reads, but lost updates and non-repeatable reads remain.
• The cleanest race fix removes the read-then-write gap: UPDATE … SET balance = balance - 800 WHERE balance >= 800.
• Invariants like 'unique email' belong in constraints, not application checks.
• Serializable prevents all anomalies, including write skew, but you must retry serialization failures.
• In pipelines, transactions make loads atomic: delete+insert in one transaction, or build-and-swap.

Test yourself

There are 9 questions, 3 puzzles and 7 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.