ACID vs BASE

When choosing a database, you are fundamentally choosing a consistency model. This choice dictates how your system behaves when things go wrong (crashes, power failures, network partitions).

ACID (Strict Consistency)

ACID is the standard for Relational Databases (SQL). It prioritizes data integrity above all else.

1. Atomicity

"All or nothing." A transaction is treated as a single unit. If any part of the transaction fails, the entire transaction fails and the database is left unchanged.

• Example: Transferring $100 from Alice to Bob involving two steps (Debit Alice, Credit Bob). Both must happen, or neither.

2. Consistency

Data must meet all validation rules. The database moves from one valid state to another valid state.

• Example: If a database rule says "Balance cannot be negative", a transaction that results in a negative balance will be rejected.

3. Isolation

Transactions occur independently. Concurrency control ensuring that multiple transactions executing at once do not interfere with each other.

• Example: If Alice reads her balance while Bob is sending her money, she sees either the "before" or "after" state, never a messy "during" state.

Isolation Levels (From Weakest to Strongest)

Different isolation levels offer different trade-offs between consistency and performance:

1. Read Uncommitted (No Isolation)

• Transactions can read data written by uncommitted transactions
• Anomaly: Dirty Read (reading data that may be rolled back)

# Transaction A
UPDATE accounts SET balance = 1000 WHERE id = 1; # Not committed yet

# Transaction B (running concurrently)
SELECT balance FROM accounts WHERE id = 1; # Reads 1000 (dirty!)

# Transaction A
ROLLBACK; # Oops! B read data that never existed

2. Read Committed (Default in PostgreSQL, Oracle)

• Can only read committed data
• Anomaly: Non-Repeatable Read (same query returns different results)

# Transaction A
SELECT balance FROM accounts WHERE id = 1; # Returns 500

# Transaction B commits in between
UPDATE accounts SET balance = 1000 WHERE id = 1;
COMMIT;

# Transaction A (still running)
SELECT balance FROM accounts WHERE id = 1; # Returns 1000 (!= 500)

3. Repeatable Read (Default in MySQL)

• Same query always returns same result within a transaction
• Anomaly: Phantom Read (new rows appear)

# Transaction A
SELECT COUNT(*) FROM orders WHERE customer_id = 123; # Returns 5

# Transaction B commits
INSERT INTO orders (customer_id) VALUES (123);
COMMIT;

# Transaction A
SELECT COUNT(*) FROM orders WHERE customer_id = 123; # Returns 6 (phantom row!)

4. Serializable (Strongest)

• Transactions execute as if they ran one-by-one
• No Anomalies: Fully isolated
• Cost: Slower performance (locks/retries)

4. Durability

Once a transaction is committed, it remains committed even in the case of a system failure (power outage).

• Example: Writing data to non-volatile storage (disk) rather than just memory.

BASE (Eventual Consistency)

BASE is the standard for NoSQL databases dealing with massive scale. It prioritizes availability and partition tolerance (CAP Theorem).

1. Basically Available

The system guarantees availability. There will be a response to any request, but that response might be "failure" or stale data. It does not guarantee strict consistency.

2. Soft State

The state of the system may change over time, even without input. This is due to the eventual consistency model where data propagates to replicas in the background.

3. Eventual Consistency

The system will eventually become consistent once it stops receiving inputs. The data will propagate to every node... eventually.

• Example: You post a comment on Facebook. Your friend might not see it for 2 seconds. Eventually, all 50 database nodes will have your comment.

Comparison Table

Real-World Examples

ACID: Banking System

# Transfer $100 from Alice to Bob
BEGIN TRANSACTION;
  
  # Debit Alice
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';
  
  # Check constraint
  SELECT balance FROM accounts WHERE user_id = 'alice';
  # If balance < 0, ROLLBACK
  
  # Credit Bob
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 'bob';
  
COMMIT;
# Both updates happen atomically or neither happens

Why ACID?

• Money cannot disappear or be duplicated
• Regulatory compliance requires audit trail
• Correctness > Availability

BASE: Social Media Feed

// User posts a tweet
const tweet = await db.tweets.insert({
  id: generateId(),
  user_id: 'alice',
  content: 'Hello World',
  timestamp: Date.now()
});

// Followers see it... eventually
// Timeline Service replicates asynchronously
await timelineService.fanOut(tweet);
// Some followers see it in 100ms, others in 2 seconds

Why BASE?

• 500M users need instant response
• Temporary inconsistency is acceptable
• Availability > Strict Consistency

The Convergence: Hybrid Approaches

Modern databases are blurring the lines:

NewSQL: ACID at Scale

Google Spanner

• Globally distributed
• Full ACID transactions
• Uses atomic clocks (TrueTime) for global ordering
• Powers Google Ads (billions of $ in transactions)

CockroachDB

• PostgreSQL-compatible
• ACID transactions across regions
• Uses hybrid logical clocks

How they do it:

-- Transaction spans US, Europe, Asia data centers
BEGIN TRANSACTION;
  UPDATE inventory SET stock = stock - 1 WHERE region = 'US';
  INSERT INTO orders (region, user_id) VALUES ('US', 123);
COMMIT; -- Uses consensus protocol (Raft/Paxos) for atomicity

Tunable Consistency in NoSQL

Cassandra antml:parameter>

# Eventual consistency (fast)
session.execute(query, consistency_level=ConsistencyLevel.ONE)

# Strong consistency (slow but accurate)
session.execute(query, consistency_level=ConsistencyLevel.QUORUM)

# ACID-like (slowest)
session.execute(query, consistency_level=ConsistencyLevel.ALL)

DynamoDB

// Eventual consistency
const params = { TableName: 'Users', Key: { id: '123' } };
await dynamodb.getItem(params);

// Strong consistency
const params = { 
  TableName: 'Users', 
  Key: { id: '123' },
  ConsistentRead: true // Pays extra latency for accuracy
};

Decision Framework: When to Use What?

Choose ACID when:

• Financial transactions (payments, transfers, refunds)
• Inventory management (stock levels must be accurate)
• Healthcare records (consistency is legally required)
• Booking systems (double-booking is unacceptable)

Choose BASE when:

• Social media feeds (eventual visibility is fine)
• Analytics/logging (approximate counts acceptable)
• Content delivery (slight staleness tolerable)
• Recommendation engines (eventual updates work)

Use Hybrid when:

• E-commerce: ACID for checkout, BASE for product catalog
• Ride-sharing: ACID for driver assignment, BASE for location tracking
• Banking: ACID for transactions, BASE for notification delivery

Interview Tips 💡

When discussing ACID vs BASE in system design interviews:

1. Understand the domain: "For a payment system, we need ACID because..."
2. Explain trade-offs: "We'll use eventual consistency for the news feed to handle 1M users, accepting that followers see posts with 1-2 second delay"
3. Mention CAP theorem: "Since we need partition tolerance, we choose between consistency (ACID) and availability (BASE)"
4. Hybrid approach: "Critical path uses ACID, everything else uses BASE"
5. Specific examples: Reference Spanner, Cassandra quorum reads, DynamoDB eventual vs strong

Common Misconceptions

❌ "NoSQL = No Transactions": False. MongoDB, DynamoDB support transactions now.

❌ "ACID = Can't Scale": False. Spanner, CockroachDB prove ACID scales globally.

❌ "BASE = Data Loss": False. BASE means eventual consistency, not data loss.

Related Concepts

• CAP Theorem — Fundamental trade-offs in distributed systems
• Distributed Transactions — SAGA and 2PC patterns
• Database Replication — Achieving consistency across replicas
• Isolation Levels — Deep dive into transaction isolation
• Eventual Consistency — Understanding BASE in practice