ACID Properties

Property	Guarantee	Implementation	Failure Mode
Atomicity	All-or-nothing	Undo logs, rollback segments	Partial writes on crash
Consistency	Valid state transitions only	Constraints, triggers	Constraint violations
Isolation	Concurrent txns don’t interfere	Locks, MVCC	Read anomalies, lost updates
Durability	Committed = permanent	WAL, fsync	Data loss on crash

---

Atomicity

All operations in a transaction succeed together or roll back entirely. Implemented via undo logs that store pre-modification values.

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

Crash recovery: If crash occurs mid-transaction, recovery process reads undo log and reverts uncommitted changes.

Savepoints

Use SAVEPOINT for partial rollbacks within a transaction—useful for retry logic without aborting the entire txn.

SAVEPOINT before_risky_op;
-- attempt operation
ROLLBACK TO before_risky_op;  -- partial undo if needed

---

Consistency

Transactions move the database between valid states. The DBMS rejects any write violating constraints.

Constraint Type	Checked When	Cost
NOT NULL, CHECK	Per-row insert/update	Low
UNIQUE, PRIMARY KEY	Per-row (index lookup)	Medium
FOREIGN KEY	Per-row (parent lookup)	Medium-High
DEFERRABLE constraints	At commit	Batch-friendly

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
DEFERRABLE INITIALLY DEFERRED;  -- checked at COMMIT, not per-row

ACID vs. CAP Consistency

Different concepts:

• ACID Consistency: Schema integrity (constraints hold)
• CAP Consistency: Linearizability (all nodes see same data)

---

Isolation

Concurrent transactions must produce results equivalent to some serial execution order.

Read Anomalies

Anomaly	Description	Example
Dirty Read	Read uncommitted data	T1 writes, T2 reads, T1 rollbacks → T2 has garbage
Non-Repeatable Read	Same query, different results	T1 reads X=5, T2 updates X=10 and commits, T1 reads X=10
Phantom Read	New rows appear in range query	T1 counts rows WHERE x>5, T2 inserts matching row, T1 recounts
Lost Update	Concurrent writes overwrite	T1 and T2 both read X=5, both write X+1, result is 6 not 7

Isolation Levels

Level	Dirty	Non-Repeatable	Phantom	Implementation
Read Uncommitted	✗	✗	✗	No read locks
Read Committed	✓	✗	✗	Lock-based or snapshot per statement
Repeatable Read	✓	✓	✗*	Snapshot at txn start (MVCC)
Serializable	✓	✓	✓	SSI or strict 2PL

*MySQL’s Repeatable Read prevents phantoms via gap locks; Postgres doesn’t.

Practical Guidance

• Read Committed: Safe default for most OLTP workloads
• Repeatable Read: Use when read-consistency within a txn matters
• Serializable: Financial/inventory systems—expect retries, design for idempotency

---

Durability

Committed transactions survive crashes. Implemented via Write-Ahead Logging (WAL).

WAL Protocol

1. Write change to WAL buffer
2. Flush WAL to disk (fsync)
3. Return "COMMIT OK" to client
4. Apply changes to data pages (async, checkpointing)

Recovery: On crash, replay WAL from last checkpoint. Redo committed txns, undo uncommitted.

---

See Distributed Transactions for 2PC and Saga patterns.