ACID Properties

ACID Properties

Why This Matters

ACID is like a safety net for your data. It guarantees that when you transfer money from one account to another, either both operations succeed or both fail. You can't have money disappear or appear from nowhere.

This matters because databases are shared resources. Multiple transactions can run simultaneously, and without ACID guarantees, you'd have race conditions, data corruption, and lost updates. ACID ensures that transactions are reliable, even when things go wrong.

In interviews, when someone asks "How would you design a payment system?", they're testing whether you understand ACID. Do you know when to use it, and when to relax it? Most engineers don't. They either use ACID for everything (slow) or nothing (unreliable).

What Engineers Usually Get Wrong

Most engineers think ACID is "just a checklist"—either your database has it or it doesn't. But ACID is a set of guarantees that databases provide to varying degrees. Atomicity means "all or nothing," but what does "all" mean? Isolation means "transactions don't interfere," but at what level? Serializable isolation prevents all interference but is slow. Read committed allows more concurrency but can cause anomalies.

Engineers also think "ACID is always necessary." But ACID has overhead. Locking can cause contention. Logging adds I/O overhead. For high-throughput systems, this overhead can be a bottleneck. Many systems relax ACID for better performance (eventual consistency).

How This Breaks Systems in the Real World

An e-commerce system was processing orders. The process was: (1) create order record, (2) charge credit card, (3) update inventory. This was done in a transaction with serializable isolation. Under normal load, this worked. But during a sale (high traffic), many transactions tried to update the same inventory row simultaneously. Serializable isolation requires transactions to run one at a time for conflicting rows. Transactions started queuing. Response times went from 100ms to 10 seconds.

The fix? Use a lower isolation level (read committed) and handle race conditions in application code (optimistic locking). The lesson? Isolation levels are trade-offs. Higher isolation is safer but slower.

Another story: A social media service was storing likes and comments. It used ACID transactions for everything. During a viral post, thousands of users tried to like the post simultaneously. All these transactions tried to update the same row. The database became a bottleneck. Response times spiked. The fix? Use eventual consistency. Accept likes asynchronously, update counters in batches. This gave the system much higher throughput.

Atomicity

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

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

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.

1-- Constraint: balance >= 0
2BEGIN;
3  UPDATE accounts SET balance = balance - 150 WHERE id = 1;  -- balance becomes -50
4COMMIT;  -- ❌ 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.

1BEGIN;
2  INSERT INTO orders (id, total) VALUES (1, 100);
3COMMIT;  -- 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.

Failure Stories You'll Recognize

The Lost Update: Two transactions tried to update the same account balance simultaneously. Both read the balance as $100. Both calculated the new balance. One wrote $50, the other wrote $70. The final balance was $70, but it should have been $20. The fix? Use proper isolation levels or optimistic locking.

The Deadlock: Two transactions were trying to update the same rows in different orders. Transaction A locked row 1, then tried to lock row 2. Transaction B locked row 2, then tried to lock row 1. Deadlock. The database detected it and killed one transaction. But if many transactions were doing this, deadlocks became frequent. The fix? Always acquire locks in the same order.

The Uncommitted Read: A transaction read data that another transaction was updating but hadn't committed yet. The first transaction saw inconsistent data. Then the second transaction rolled back. The first transaction had seen data that never actually existed. The fix? Use proper isolation levels (read committed or higher).

What Interviewers Are Really Testing

They want to hear you talk about isolation levels, race conditions, and when to use ACID. Junior engineers say "just use transactions." Senior engineers say "ACID provides guarantees, but isolation levels are trade-offs. Choose the right level for your use case."

When they ask "How would you design a high-throughput system?", they're testing:

• Do you understand that ACID has overhead?
• Do you know when to relax ACID for performance?
• Can you design a system that balances consistency and performance?

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:

1BEGIN;
2  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Written to log
3  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Written to log
4  -- 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:

   Use strong consistency for same-shard transactions, saga pattern for cross-shard:

1// Strong consistency for critical operations
2async function transferMoney(fromAccount: string, toAccount: string, amount: number) {
3  if (sameShard(fromAccount, toAccount)) {
4    // Use ACID transaction
5    await db.transaction(async (trx) => {
6      await debit(trx, fromAccount, amount);
7      await credit(trx, toAccount, amount);
8    });
9  } else {
10    // Use saga pattern for cross-shard

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

• 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

How InterviewCrafted Will Teach This

We'll teach this through production failures, not definitions. Instead of memorizing "ACID stands for Atomicity, Consistency, Isolation, Durability," you'll learn through scenarios like "what happens when 1000 transactions try to update the same row?"

You'll see how ACID guarantees affect performance, reliability, and system design. When an interviewer asks "how would you design a payment system?", you'll think about isolation levels, race conditions, and when to relax ACID—not just "use transactions."

• Transactions - Learn how transactions group operations into atomic units
• Isolation Levels - Understand different isolation levels and their trade-offs
• Data Replication - Explore how ACID properties work in distributed systems
• Query Optimization - Learn how ACID overhead affects query performance