Theory
ACID Properties
📋 Overview
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee database transactions are processed reliably. This framework solves the core problem of data corruption and inconsistency caused by system crashes or concurrent updates, making it the gold standard for mission-critical systems.
🏗️ Core Principles & Characteristics
- Atomicity: The "All or Nothing" principle. A transaction is treated as a single unit; if any part fails, the entire transaction is rolled back using Undo Logs.
- Consistency: Guarantees that a transaction moves the database from one valid state to another, ensuring all integrity constraints (Foreign Keys, Unique, etc.) are satisfied.
- Isolation: Ensures that concurrent transactions do not interfere with each other. The degree of isolation is defined by the "Isolation Level" (e.g., Read Committed, Serializable).
- Durability: Guarantees that once a transaction is committed, it will survive system failures. This is typically achieved through Write-Ahead Logging (WAL).
⚖️ Trade-offs: Pros & Cons
| Isolation Level | Advantages (Pros) | Disadvantages (Cons) |
|---|---|---|
| Serializable | Highest data integrity; prevents all anomalies (Dirty/Phantom reads). | Lowest performance; high risk of deadlocks and transaction timeouts. |
| Read Committed | Excellent performance and high concurrency; standard default for many DBs. | Vulnerable to "Non-repeatable reads" where data changes during a transaction. |
| WAL (Durability) | Fast sequential writes; ensures recovery after a crash. | Consumes extra disk space; requires periodic "checkpoints" to truncate logs. |
🌍 Real-World Implementation
- Tech Stack: PostgreSQL, MySQL (InnoDB), Oracle, SQL Server.
- Use Case: A banking transfer where money must be deducted from Account A and added to Account B simultaneously—failing one must fail both to prevent money from "vanishing."
💡 Interview "Gotchas" & Tips
- Default Isolation Levels: Be ready to state that MySQL (InnoDB) defaults to Repeatable Read, while PostgreSQL and Oracle default to Read Committed.
- ACID vs. BASE: In distributed systems, ACID is often traded for BASE (Basically Available, Soft-state, Eventual consistency) to achieve massive horizontal scale.
📐 Suggested Architecture Primitives
- Server, Service, SQL, NoSQL, Cache, Cloud, Auth, Note
Canvas