Chapter 11: Transactions & ACID

---

Metadata Card

Dimension	Value
Difficulty	(Intermediate)
Prerequisites	Chapter 1 (SQL), basic concurrency concepts
Keywords	ACID, atomicity, consistency, isolation, durability, transaction, commit, rollback, WAL
Code Language	SQL, Python

---

Your Progress

"Two administrators try to check in the same batch of goods at the same time. Master Chen says — set rules."

What is a Transaction?

A transaction is a sequence of database operations treated as a single logical unit of work. It either commits (all changes saved) or aborts (all changes rolled back).

ACID Properties

Property	Meaning	What It Prevents
Atomicity	All or nothing	Partial updates
Consistency	Transaction preserves database invariants	Corrupted data
Isolation	Concurrent transactions don't interfere	Dirty reads, lost updates
Durability	Committed changes survive crashes	Data loss

Atomicity — The All-or-Nothing Principle

A transfer: account_a -= 100; account_b += 100. If the system crashes after the first update, atomicity ensures rollback.

Implementation: Undo log (before-image of data). On abort, apply undo log entries to restore original state.

Consistency — Rules Before and After

Consistency is about maintaining database invariants:

• CHECK (balance >= 0)
• Foreign key constraints
• Application-level rules (total supply = constant)

The database enforces some (constraints, triggers) but the application is responsible for business-logic consistency.

Isolation — Pretend You're Alone

The simplest approach: run transactions serially (one at a time). But that destroys throughput.

Implementation: Concurrency control — locking, MVCC, timestamps.

Durability — Surviving Crashes

A committed transaction's changes must persist even if the power goes out immediately after.

Implementation: Write-Ahead Log (WAL) — write the log entry to disk BEFORE applying the actual data page change. On recovery, replay the WAL.

Transaction States

Active → Partially Committed → Committed
   ↘              ↘
    → Failed → Aborted

Transaction Implementation

BEGIN:
  Write transaction start record to WAL
  Assign transaction ID (XID)
  Snapshot current state (for MVCC)

EXECUTE:
  For each operation:
    1. Write UNDO info to log (for rollback)
    2. Write REDO info to WAL (for durability)
    3. Modify in-memory data

COMMIT:
  1. Write COMMIT record to WAL → fsync
  2. Release locks
  3. Notify waiting transactions

ABORT:
  1. Apply UNDO log in reverse order
  2. Write ABORT record to WAL

---

Traveler's Notes

ACID transactions are the foundation of reliable data systems. They define a contract between the database and its users: "I will handle your data correctly, even in the presence of failures and concurrency." Understanding ACID helps you choose between systems (SQL with ACID vs NoSQL without), design better applications, and debug transactional conflicts.