ACID Properties

Why This Matters

When you're tested on database systems, understanding ACID properties isn't just about memorizing an acronym—it's about grasping why databases can be trusted with critical operations like processing payments or booking flights. Every time you hear about a bank transfer completing "all or nothing" or a reservation system preventing double-bookings, you're seeing ACID in action. These four properties work together to guarantee transaction reliability, and exam questions will test whether you understand how each property contributes to data integrity and what happens when one fails.

The real exam skill here is recognizing trade-offs. ACID guarantees come with performance costs, and modern distributed systems sometimes relax these guarantees (hello, BASE model) to achieve scalability. You'll need to know when strict ACID compliance matters, when it can be relaxed, and how different isolation levels balance consistency against concurrency. Don't just memorize definitions—know what problem each property solves and what breaks if it's violated.

---

The Four Guarantees

These four properties form an interdependent system. Each addresses a specific failure mode that could corrupt your data.

Atomicity

• All-or-nothing execution—a transaction either completes entirely or has no effect whatsoever, treating multiple operations as a single indivisible unit
• Rollback on failure ensures partial changes never persist; if step 3 of 5 fails, steps 1-2 are undone automatically
• Prevents partial updates that would leave data in an inconsistent state, critical for multi-step operations like fund transfers

Consistency

• Valid state transitions only—every transaction moves the database from one valid state to another, never violating defined rules
• Constraint enforcement means all integrity constraints, triggers, and cascades must be satisfied before a transaction commits
• Application-level guarantees depend on this property; if your schema says account balances can't go negative, consistency ensures that rule holds

Isolation

• Concurrent transaction independence—transactions execute as if they were running alone, even when multiple users act simultaneously
• Isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) let you tune the trade-off between strict correctness and performance
• Prevents interference like dirty reads, non-repeatable reads, and phantom reads depending on the isolation level configured

Durability

• Permanent once committed—after a transaction commits, its changes survive any subsequent system crash, power failure, or hardware malfunction
• Write-ahead logging (WAL) is the primary mechanism; changes are recorded in transaction logs before being applied to the database
• Recovery guarantees mean the system can reconstruct committed state from logs, making backups and replication possible

---

Real-World Applications

Understanding where ACID matters helps you reason about system design questions and recognize appropriate use cases.

Banking Transactions

• Fund transfers require atomicity—debiting Account A and crediting Account B must both succeed or both fail; a crash between operations can't lose money
• Consistency enforces business rules like minimum balances, overdraft limits, and account status checks before any transfer completes
• Isolation prevents race conditions where two withdrawals might both read the same balance and overdraw the account

E-Commerce Systems

• Inventory management needs isolation—when two customers try to buy the last item simultaneously, only one transaction should succeed
• Payment processing demands durability—once a charge is confirmed, that record must survive any system failure
• Order completion requires atomicity—charging the card, reducing inventory, and creating the order record must all succeed together

Reservation Systems

• Double-booking prevention through isolation—two users selecting the same seat at the same moment must be serialized
• Consistency maintains capacity constraints—a flight can't have more passengers than seats, enforced at the database level
• Atomicity groups related bookings—a round-trip reservation either books both legs or neither

---

Trade-offs and Alternatives

ACID guarantees aren't free—understanding the costs helps you answer design and comparison questions.

Performance Implications

• Locking overhead increases with isolation—Serializable isolation requires more locks held longer, reducing concurrent throughput
• Logging for durability consumes I/O—every transaction must write to the log before committing, adding latency
• Strict consistency limits horizontal scaling—distributed systems struggle to maintain ACID across multiple nodes efficiently

The BASE Alternative

• Basically Available, Soft state, Eventually consistent—a model that relaxes ACID guarantees for distributed system scalability
• Eventual consistency accepts temporary inconsistencies—replicas may briefly disagree, but will converge over time
• Availability prioritized over immediate consistency—the system remains responsive even during network partitions, unlike strict ACID systems that might block

Choosing Between Models

• ACID preferred for financial systems—where incorrect data has serious legal and monetary consequences
• BASE suits social media and analytics—where slightly stale data is acceptable and availability matters more
• Hybrid approaches exist—many systems use ACID for critical transactions and BASE for read-heavy, less sensitive operations

---

Quick Reference Table

---

Self-Check Questions

1. A transaction debits one account and credits another, but the system crashes after the debit. Which ACID property ensures the debit is reversed, and which property would have protected the credit if the transaction had completed before the crash?

2. Compare Read Committed and Serializable isolation levels—what anomalies does each prevent, and when would you choose one over the other?

3. An e-commerce site allows two customers to simultaneously purchase the last item in stock, resulting in overselling. Which ACID property was violated, and how would you fix it?

4. Why might a social media platform choose BASE over ACID for displaying post likes, but use ACID for processing payments? Explain the trade-off in each case.

5. A database uses write-ahead logging (WAL) for crash recovery. Which ACID property does this primarily support, and how does it work to guarantee that property?