The earlier TCL lesson covered the commands — BEGIN, COMMIT, ROLLBACK, SAVEPOINT. This lesson covers what those commands are actually guaranteeing, and why a database offers several different levels of that guarantee.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Both updates succeed together, or neither does — this money-transfer example is the classic illustration of why transactions exist.
| Guarantee | What it means |
|---|---|
| Atomicity | A transaction's changes all happen, or none of them do — no partial transfer where money leaves one account but never arrives at the other |
| Consistency | A transaction can only move the database from one valid state to another — every constraint (from the earlier lesson) still holds afterward |
| Isolation | Concurrent transactions don't see each other's uncommitted changes — covered in depth below |
| Durability | Once committed, a change survives — even a server crash immediately after doesn't lose it |
Multiple transactions often run at the same time. Without isolation, one transaction could read another's half-finished changes — a "dirty read" — and make a decision based on data that's about to be rolled back.
| Level | Prevents | Trade-off |
|---|---|---|
| Read Uncommitted | Nothing — dirty reads are possible | Fastest, least safe |
| Read Committed | Dirty reads | The default in most databases (e.g. PostgreSQL, SQL Server) |
| Repeatable Read | Dirty reads + non-repeatable reads (a row changing mid-transaction) | MySQL's default |
| Serializable | Every concurrency issue — transactions behave as if run one at a time | Safest, slowest |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... queries here run with the strictest isolation ...
COMMIT;Common Mistake
A higher isolation level isn't automatically the right choice — Serializable can cause more transactions to fail and need retrying under heavy concurrent load. Most applications are well-served by the database's default.