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.
• Backup takes too long: enable compression, incremental backups, and off-peak scheduling.
• Deadlocks in high concurrency: access tables and rows in a consistent order.

Further Reading

• Official documentation: check the current reference for the framework or tool used.
• Related guides: explore the acid and databases guides for deeper coverage.
• Complementary patterns: review design patterns applicable to your technology stack.
• Public postmortems: study real incidents from teams that faced similar production issues.

Production Notes

• Deploy gradually using canary or blue-green to catch regressions early.
• Configure alerts for error rate, p99 latency, and failure rate before enabling in production.
• Document the rollback in the runbook; test the procedure in staging at least once per quarter.
• Review structured logs with correlation IDs to trace requests end-to-end during incidents.

Key Takeaways

• Apply acid vs base — consistency models explained when you need a practical solution for your use case.
• Monitor performance after implementation; measure latency, errors, and resource usage before and after.
• Check the Troubleshooting section for common failures; most have documented root causes with fixes.
• Keep dependencies updated and run tests in CI to prevent production regressions.

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.