ACID vs BASE — Consistency Models Explained

Overview

ACID and BASE represent two philosophies for handling data consistency in databases. ACID guarantees strong consistency through transactions that are Atomic, Consistent, Isolated, and Durable. BASE prioritizes availability and partition tolerance, accepting that data may be temporarily inconsistent. Understanding when to use each model — and how to combine them — is essential for designing reliable distributed systems.

ACID Properties

Atomicity

All operations in a transaction complete successfully, or none do. There is no partial completion.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;  -- Both succeed, or ROLLBACK cancels both

Consistency

Transactions bring the database from one valid state to another, preserving all constraints and rules.

Isolation

Concurrent transactions do not interfere with each other. The result is as if transactions ran sequentially.

Durability

Once committed, changes survive system failures. Data is written to persistent storage.

Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Use Case
Read Uncommitted	Possible	Possible	Possible	Rare, analytics only
Read Committed	No	Possible	Possible	Default for most databases
Repeatable Read	No	No	Possible	Financial read operations
Serializable	No	No	No	Critical financial transactions

BASE Properties

Basically Available

The system guarantees availability. Every request receives a response, but that response may be stale.

Soft State

The state of the system may change over time, even without input, as data replicates and reconciles.

Eventual Consistency

If no new updates are made, eventually all nodes will converge to the same value.

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   Write     │────▶│  Replica A  │────▶│  Replica B  │
│   X = 42    │     │   X = 42    │     │   X = null  │
└─────────────┘     └─────────────┘     └─────────────┘
                          │                     │
                          └───────sync──────────┘
                                         │
                                    X = 42 (eventual)

ACID vs BASE Comparison

Aspect	ACID	BASE
Consistency	Strong (immediate)	Eventual (delayed)
Availability	May reject under load	Always responds
Partition Tolerance	Sacrificed if needed	Required
Best For	Financial, inventory, bookings	Social, analytics, caching
Complexity	Managed by database	Managed by application
Example	PostgreSQL, MySQL (InnoDB)	Cassandra, DynamoDB, Couchbase

CAP Theorem

The CAP theorem states that a distributed system can guarantee at most two of:

• Consistency: All nodes see the same data at the same time
• Availability: Every request receives a response
• Partition Tolerance: System continues despite network failures

In practice, partition tolerance is mandatory in distributed systems, so the real choice is CP (consistent) vs AP (available).

Choosing Between ACID and BASE

Choose ACID When

• Financial transactions (banking, payments, trading)
• Inventory management (prevent overselling)
• Booking systems (prevent double-booking)
• Regulatory compliance requires exact records
• The cost of inconsistency exceeds the cost of downtime

Choose BASE When

• Social media feeds (stale data is acceptable)
• Analytics and metrics (approximate is sufficient)
• Shopping carts (temporary inconsistency is tolerable)
• Content delivery (CDN caches are inherently stale)
• Systems where uptime is more critical than perfect accuracy

Hybrid Approaches

Modern systems often use both models in different parts:

┌─────────────────────────────────────────┐
│           Application Layer             │
└──────────────┬──────────────────────────┘
               │
      ┌────────┴────────┐
      │                 │
┌─────▼─────┐    ┌──────▼──────┐
│  ACID DB  │    │  BASE Store │
│PostgreSQL │    │  Cassandra  │
│  Orders   │    │  Analytics  │
│  Payments │    │  Sessions   │
└───────────┘    └─────────────┘

Implementing BASE with Sagas

When you need BASE semantics but ACID-like reliability, use sagas:

class OrderSaga {
  async execute(order: Order): Promise<void> {
    try {
      await this.inventoryService.reserve(order.items);
      await this.paymentService.charge(order.total);
      await this.shippingService.schedule(order);
    } catch (error) {
      await this.compensate(order);
    }
  }

  private async compensate(order: Order): Promise<void> {
    await this.inventoryService.release(order.items);
    await this.paymentService.refund(order.total);
  }
}

Common Mistakes

• Using ACID for everything — adds unnecessary latency and complexity to non-critical data
• Using BASE for financial data — eventual consistency can cause double-spending or overselling
• Ignoring the CAP choice — pretending you can have all three in a distributed system
• Not handling BASE read anomalies — reading stale data and making decisions on it

Troubleshooting

• Query is slow after an index change: check execution plans and cardinality estimates. Rebuild statistics and verify the index is being used.
• Replication lag grows: monitor network, disk I/O, and long transactions. Split large writes and consider parallel replication.
• Connections exhausted: review connection pool size, idle timeouts, and leaked connections. Use prepared statements and close connections in finally blocks.
• Backup takes too long: enable compression, incremental backups, and off-peak scheduling. Test restore times against RTO targets.
• Deadlocks in high concurrency: access tables and rows in a consistent order. Keep transactions short and retry deadlocked operations.

FAQ

Can a database support both ACID and BASE? Yes. PostgreSQL with read replicas provides ACID on the primary and BASE on replicas. Some databases (e.g., Cosmos DB) let you choose consistency per request.

How do I handle conflicts in BASE systems? Use vector clocks, last-write-wins with timestamps, or application-specific conflict resolution (e.g., merge shopping carts).

Is BASE faster than ACID? Generally yes, because it avoids coordination overhead (locks, two-phase commit). But the speed difference depends on workload and implementation.

How do I get started with this in an existing project?

Start with a small, isolated part of your codebase. Apply the concepts from this guide to one module or service. Measure the impact, then expand to other areas.

What tools do I need?

The tools mentioned throughout this guide are listed in each section. Most are open-source and widely adopted. Check the related resources for setup instructions.

How do I measure success after implementing this?

Define clear metrics before starting: performance benchmarks, error rates, or maintainability indicators. Compare before and after. Iterate based on the data, not on assumptions.

Advanced Topics

Scenario: Hybrid E-commerce ACID/BASE

System: 10M users, 500K orders/day
Model: ACID for payments/inventory, BASE for catalog/reviews

Architecture:
  | Service | Model | DB | Consistency |
  |---------|-------|-----|-------------|
  | Payments | ACID | PostgreSQL | Serializable |
  | Inventory | ACID | PostgreSQL | Repeatable Read |
  | Orders | ACID | PostgreSQL | Read Committed |
  | Catalog | BASE | MongoDB | Eventual |
  | Search | BASE | Elasticsearch | NRT |
  | Analytics | BASE | ClickHouse | Eventual |

Order flow (Saga):
  1. Reserve inventory (ACID)
     BEGIN; UPDATE inventory SET stock = stock - qty WHERE sku = ?;
     INSERT INTO reservations ...; COMMIT;
  2. Process payment (ACID)
     BEGIN; INSERT INTO payments ...; UPDATE accounts ...; COMMIT;
  3. Create order (ACID)
     INSERT INTO orders ...; COMMIT;
  4. Publish event (BASE, Kafka)
     Produce OrderCreated to Kafka

  Compensation on failure:
  - Payment fails: release inventory
  - Order fails: refund payment, release inventory

  TypeScript:
    class CheckoutSaga {
      async execute(cart, paymentMethod) {
        const reservation = await this.reserveInventory(cart.items);
        try {
          const payment = await this.processPayment(cart.total, paymentMethod);
          const order = await this.createOrder(cart, payment.id);
          await this.eventBus.publish(new OrderCreated(order));
          return order;
        } catch (error) {
          await this.releaseInventory(reservation);
          await this.refundPayment(payment?.id);
          throw error;
        }
      }
    }

Outbox pattern (guarantees publication):
  BEGIN; INSERT INTO orders ...;
  INSERT INTO outbox (event_type, payload) VALUES ("OrderCreated", ...);
  COMMIT;
  -- Separate process reads outbox and publishes to Kafka

Sync:
  Catalog -> ES: MongoDB Change Stream, latency 1-5s
  Orders -> Analytics: Kafka consumer -> ClickHouse, 30-60s

Inconsistency handling:
  | Scenario | Mitigation |
  |----------|------------|
  | Stale catalog | TTL cache + refresh |
  | Review not indexed | Scheduled reindex |
  | Analytics behind | Accept NRT |
  | Saga fails to compensate | Alert + reconciliation |

Monitoring:
  - Kafka lag: < 60s (alert > 300s)
  - Unprocessed outbox: > 100 (alert)
  - Reconciliation: daily cross-store job

Lessons:
  - ACID for money, BASE for everything else
  - Outbox solves dual-write
  - Sagas need idempotent compensation
  - Periodic reconciliation catches silent inconsistencies

What is tunable consistency?

Cassandra and DynamoDB let you adjust consistency per operation. ONE: reads one node (fast). QUORUM: reads majority (consistent). ALL: reads all (max consistency). Use QUORUM for critical operations and ONE for cache.

End of document. Review and update quarterly.

See Also

• CQRS + Event Sourcing — Combined Guide
• CAP Theorem and Database Trade-offs
• Complete Guide to RAG in Production
• Complete Guide to Vector Databases
• Data Lake vs Data Warehouse — Architecture Guide

Common Production Pitfalls

• Treating the guide as a checklist to complete once rather than a practice to evolve.
• Adopting every recommendation at once instead of starting with one measured change.
• Skipping the maturity assessment and forcing advanced practices on an unprepared team.
• Not updating runbooks and on-call expectations as new practices are introduced.
• Ignoring real incident data when prioritizing which parts of the guide to apply first.
• Failing to assign an owner who reviews decisions quarterly.
• Copying examples without adapting them to the team’s actual tooling and constraints.
• Forgetting to measure outcomes before adding the next improvement.