Transactions: All-or-Nothing Units of Work

A transaction groups multiple operations into one logical unit that either fully succeeds or fully fails. The classic example is a bank transfer: UPDATE accounts SET balance = balance - 100 WHERE id = 1; then UPDATE accounts SET balance = balance + 100 WHERE id = 2;. If the second statement fails, the first must not be visible to anyone — or money evaporates.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK;

The ACID Guarantees

• Atomicity — all statements commit, or none do. No half-states.
• Consistency — the database moves from one valid state to another; constraints (CHECK, FK) hold at commit.
• Isolation — concurrent transactions don’t see each other’s partial work. How strictly? That’s the isolation level, below.
• Durability — once committed, the change survives crashes (via the write-ahead log, see Storage Engines).

The interesting engineering is that Atomicity + Isolation are in tension with throughput. The strictest behavior would serialize everything — the database’s real job is to give you strong guarantees without serializing every transaction.

Isolation Levels and Read Phenomena

Databases offer a spectrum of isolation. Each weaker level allows specific read phenomena:

• Dirty read — seeing another transaction’s uncommitted data (which may roll back).
• Non-repeatable read — reading the same row twice in one transaction and getting different values (because another transaction committed an update in between).
• Phantom read — running the same range query twice and seeing different sets of rows (rows inserted/committed in between).

READ COMMITTED (the default in PostgreSQL, SQL Server, Oracle) prevents dirty reads: each statement sees only committed data. REPEATABLE READ (the default in MySQL/InnoDB) also pins the rows you’ve already read. SERIALIZABLE is the strictest — it behaves as if transactions ran one after another, eliminating phantoms too.

MVCC: How Databases Escape Locking

The trick that makes high concurrency possible is Multi-Version Concurrency Control (MVCC). Instead of locking a row while it’s being read, the database keeps multiple versions of each row. A reader sees the version that was current when its transaction started (or at the statement start, for READ COMMITTED); a writer creates a new version rather than overwriting the visible one.

The cost is version garbage: old row versions must be cleaned up when no transaction can still see them (VACUUM in PostgreSQL). MVCC is why “snapshot isolation” is both extremely concurrent and slightly subtle — long-running transactions can pin old versions and bloat the table.

Deadlocks

When two transactions each hold a resource the other needs, the database detects the cycle and aborts one of them (the victim) so the other can proceed. This is not a bug to fear but a condition to design for: keep transactions short, and always acquire locks in a consistent order (e.g., lock rows by primary key) so cycles are rare. Your application must be able to retry a transaction that got aborted.

The Visualizer

Use the isolation visualizer above to step through two concurrent transactions under each isolation level. Watch how a dirty write at READ UNCOMMITTED becomes a blocked write at READ COMMITTED, how a repeatable-read transaction’s snapshot keeps its second read identical, and how SERIALIZABLE prevents phantom rows by locking the range.

Practice Trajectory

1. Open two database sessions against a test database and run a dirty-read scenario at READ UNCOMMITTED vs READ COMMITTED.
2. Demonstrate a non-repeatable read: update a row in session B while session A re-reads it under READ COMMITTED.
3. Show a phantom read with a range query, then switch to SERIALIZABLE and repeat.
4. Construct a two-transaction deadlock (A locks 1→2, B locks 2→1) and observe which one gets rolled back.
5. Explain what MVCC’s garbage versions are, and why long transactions cause table bloat.

When It’s the Right Tool