ACID Properties

ACID is an acronym for four fundamental properties that guarantee reliable database transactions. Understanding ACID is essential for database interviews, as it forms the foundation of transaction processing and data integrity.

---

Atomicity

All or nothing: A transaction is treated as a single unit. Either all operations succeed, or all are rolled back.

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

If either UPDATE fails, both are rolled back. The account balance transfer is atomic—you can't have money disappear or appear from nowhere.

Implementation: Transaction logs and rollback mechanisms. The database maintains a log of all changes, allowing it to undo operations if the transaction fails.

Interview example: "What happens if the database crashes between the two UPDATE statements?" Answer: The entire transaction is rolled back on recovery, ensuring atomicity.

---

Consistency

Valid state transitions: A transaction brings the database from one valid state to another. All constraints, rules, and triggers are maintained.

-- Constraint: balance >= 0
BEGIN;
  UPDATE accounts SET balance = balance - 150 WHERE id = 1;  -- balance becomes -50
COMMIT;  -- ❌ Fails: violates consistency constraint

Implementation: Database constraints, foreign keys, check constraints, triggers. The database enforces business rules to maintain consistency.

Interview insight: Consistency is not just about database constraints—it also includes application-level business rules. For example, "total order amount must equal sum of line items."

---

Isolation

Concurrent transactions don't interfere: Transactions execute in isolation, as if they were the only transaction running.

Problem without isolation:

Transaction A: Read balance ($100)
Transaction B: Read balance ($100)
Transaction A: Write balance ($100 - $50 = $50)
Transaction B: Write balance ($100 - $30 = $70)
Result: $70 (should be $20)

This is a lost update problem. Isolation prevents this through locking mechanisms.

Implementation: Locking mechanisms and isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE). See the Isolation Levels topic for details.

Interview scenario: "Two users try to book the last hotel room simultaneously. How does ACID prevent double-booking?" Answer: Isolation ensures one transaction sees the other's changes, preventing both from succeeding.

---

Durability

Committed changes persist: Once a transaction is committed, its changes are permanent, even in the event of system failure.

BEGIN;
  INSERT INTO orders (id, total) VALUES (1, 100);
COMMIT;  -- Data is now durable

Even if the database crashes immediately after COMMIT, the order will be present after recovery.

Implementation: Write-ahead logging (WAL), transaction logs, disk persistence. Changes are written to durable storage (disk) before the transaction is considered committed.

Interview question: "How does a database ensure durability if the server crashes?" Answer: Write-ahead logging writes all changes to disk before commit. On recovery, the database replays the log to restore committed transactions.

---

Real-World Implications

Banking System

• Atomicity: Money transfer must complete fully or not at all—no partial transfers
• Consistency: Account balances must always sum correctly, no negative balances allowed
• Isolation: Concurrent transfers don't interfere—two transfers from the same account are serialized
• Durability: Completed transfers survive crashes—critical for financial data

E-Commerce

• Atomicity: Order creation and inventory deduction happen together—can't have orders without inventory updates
• Consistency: Stock levels can't go negative, order totals must match line items
• Isolation: Two customers can't buy the last item simultaneously
• Durability: Orders are never lost, even during system failures

Social Media

• Atomicity: Post creation and notification sending happen together
• Consistency: Follower counts must match actual relationships
• Isolation: Concurrent likes/comments don't interfere
• Durability: Posts persist across system restarts

---

Trade-offs and When to Relax ACID

NoSQL Databases

Many NoSQL databases sacrifice ACID for performance and scalability:

• MongoDB: Offers transactions, but not always ACID-compliant by default
• Cassandra: Eventually consistent, no transactions
• DynamoDB: Conditional writes, but not full ACID

When it's acceptable: High-throughput systems where eventual consistency is acceptable (social feeds, analytics, logs).

Distributed Systems

Full ACID is difficult across multiple nodes due to the CAP theorem:

• Consistency: All nodes see same data
• Availability: System remains operational
• Partition tolerance: System continues despite network failures

You can guarantee at most two of three. Distributed databases often choose AP (Availability + Partition tolerance) over C (Consistency).

Example: Amazon DynamoDB uses eventual consistency for better availability and performance.

Performance Overhead

ACID guarantees come with overhead:

• Locking: Can cause contention and deadlocks
• Logging: Write-ahead logs add I/O overhead
• Two-phase commit: In distributed systems, adds latency

When to relax: High-write-throughput systems, analytics workloads, caching layers.

---

Interview Questions

1. Beginner Question

Q: What does ACID stand for, and why is it important?

A: ACID stands for:

• Atomicity: All operations in a transaction succeed or all fail
• Consistency: Database remains in a valid state
• Isolation: Concurrent transactions don't interfere
• Durability: Committed changes persist

Why important: ACID ensures data integrity and reliability. Without it, you could lose data, have inconsistent states, or experience race conditions.

Example: A bank transfer without ACID could result in money disappearing (atomicity failure) or account balances not matching (consistency failure).

2. Intermediate Question

Q: Explain what happens if a database crashes in the middle of a transaction. How does ACID handle this?

A:

Atomicity: The transaction is rolled back. All changes made before the crash are undone using the transaction log.

Durability: Changes from committed transactions are preserved. The database uses write-ahead logging (WAL):

1. All changes are written to the log file on disk before commit
2. On recovery, the database replays committed transactions from the log
3. Uncommitted transactions are rolled back

Example:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Written to log
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Written to log
  -- CRASH occurs here

On recovery:

• If transaction was committed: Both updates are replayed from log
• If transaction was not committed: Both updates are rolled back

3. Senior-Level System Question

Q: Design a distributed payment system that processes 1 million transactions per second. How would you balance ACID guarantees with performance requirements?

A:

Challenge: Full ACID across distributed nodes is expensive and can limit throughput.

Architecture:

1. Shard by user/account:

   • Each shard handles a subset of accounts
   • Transactions within a shard can use full ACID
   • Cross-shard transactions use eventual consistency

2. Two-tier consistency:

   # Strong consistency for critical operations
   def transfer_money(from_account, to_account, amount):
       if same_shard(from_account, to_account):
           # Use ACID transaction
           with transaction():
               debit(from_account, amount)
               credit(to_account, amount)
       else:
           # Use saga pattern for cross-shard
           saga = Saga()
           saga.add_step(debit, from_account, amount)
           saga.add_step(credit, to_account, amount)
           saga.execute()  # Compensating transactions on failure
   

3. Event sourcing for audit:

   • Store all events (debit, credit) in event log
   • Rebuild state from events
   • Provides durability and audit trail

4. Optimistic concurrency:

   • Use version numbers instead of locks
   • Retry on conflicts
   • Reduces lock contention

5. Async processing:

   • Accept transaction immediately
   • Process asynchronously
   • Notify user on completion
   • Trade-off: Eventual consistency for higher throughput

6. Caching layer:

   • Cache account balances in Redis
   • Update cache after database commit
   • Reduces database load

Trade-offs:

• Consistency vs. Performance: Relax consistency for non-critical paths
• Latency vs. Throughput: Batch operations for higher throughput
• Complexity vs. Guarantees: Saga pattern is complex but provides cross-shard atomicity

Monitoring:

• Track transaction success rate
• Monitor for consistency violations
• Alert on high conflict rates

---

Key Takeaways

• Atomicity ensures all-or-nothing execution—critical for financial transactions and data integrity
• Consistency maintains valid database states through constraints and business rules
• Isolation prevents concurrent transactions from interfering with each other through locking
• Durability guarantees committed changes survive crashes via write-ahead logging
• ACID is not always necessary—eventual consistency is acceptable for many use cases (social feeds, analytics)
• Distributed systems often relax ACID for better availability and performance (CAP theorem trade-offs)
• Performance vs. guarantees—ACID adds overhead (locking, logging); consider relaxing for high-throughput systems
• Understand the trade-offs—when to use ACID (financial systems) vs. when to relax it (caching, analytics)
• Implementation matters—isolation levels, locking strategies, and logging mechanisms affect ACID guarantees
• Design for failure—ACID helps systems recover correctly from crashes and maintain data integrity