02 · ACID Properties — Transactional Guarantees

Foundations · Topic 2 of 4

---

What is ACID?

ACID defines the four guarantees that make database transactions reliable:

Property	Guarantee
Atomicity	All-or-nothing: the transaction fully commits or fully rolls back
Consistency	The DB moves from one valid state to another; constraints are never violated
Isolation	Concurrent transactions don't interfere with each other
Durability	Committed data survives crashes (persisted to disk / WAL)

---

Atomicity

A transaction is a unit. If any step fails, all changes are rolled back.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- both succeed or neither does

Cloud implementations

• PostgreSQL: native multi-statement transactions
• Spanner: global atomic transactions across regions
• DynamoDB: TransactWriteItems (up to 100 items, single-region)
• MongoDB: multi-document transactions (4.0+, replica sets)
• Cassandra: lightweight transactions (LWT) via Paxos — limited, avoid for complex workflows

---

Consistency

The DB enforces constraints (foreign keys, uniqueness, check constraints) across every transaction.

-- Constraint prevents invalid state
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

Note

Consistency in ACID is different from the C in CAP theorem.

---

Isolation

Isolation controls what a transaction can see from other in-flight transactions. See Isolation Levels for full coverage.

---

Durability

Writes are persisted before the commit is acknowledged using the Write-Ahead Log (WAL).

sequenceDiagram
    Client->>DB: COMMIT
    DB->>WAL: Append log record
    WAL->>Disk: fsync
    DB->>Client: OK (committed)

---

ACID vs BASE

	ACID	BASE
Consistency	Strong, immediate	Eventual
Availability	May sacrifice during partition	Prioritized
Databases	PostgreSQL, Spanner, MySQL	Cassandra, DynamoDB (default), MongoDB (default)

---