What is ACID and what is BASE, and when do you use each model?

• ACID – Atomicity (all-or-nothing), Consistency (valid state transitions), Isolation (concurrent transactions do not corrupt each other), and Durability (committed data survives crashes). The model used by transactional relational databases.
• BASE – Basically Available (system always responds), Soft state (state can change over time as replicas sync), Eventual consistency (all nodes converge if writes stop). The relaxed model used by many distributed NoSQL systems.
• Trade-off – ACID favours correctness at the cost of scale; BASE favours availability and partition tolerance at the cost of instant consistency.
• Polyglot persistence – mature systems use both: ledgers on ACID, feeds and catalogs on BASE.

What is ACID?

ACID describes the four guarantees of a reliable database transaction.

Atomicity means a transaction is all-or-nothing. If Rajesh's transfer debits his account but the credit to Priya fails, the database rolls back the debit so the system never shows money vanished.

BEGIN;
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 'SAV2001';
UPDATE accounts SET balance = balance + 50000 WHERE account_id = 'CUR3001';
COMMIT;
-- If anything fails between BEGIN and COMMIT, both updates are rolled back.

Consistency means every transaction moves the database from one valid state to another, respecting all constraints and foreign keys. If an account cannot go negative, a transaction that would break that rule is rejected.

Isolation means concurrent transactions do not see each other's half-done work. If Rajesh and Priya both transact at the same instant, isolation levels control what each sees, preventing dirty reads and lost updates.

Durability means once the database says COMMIT succeeded, the data survives a power loss, because it was written to a write-ahead log (WAL) on disk before the acknowledgement.

What is BASE?

BASE comes from the CAP theorem reality: in a distributed system, a network partition forces you to choose between consistency and availability. BASE systems choose availability.

• Basically Available – the system always responds, even if with slightly stale data.
• Soft state – the system's state can change over time even without new input, as replicas synchronise.
• Eventual consistency – if writes stop, all replicas will converge to the same value, but for a short window different nodes may differ.

ACID:                               BASE:
┌─────────────────────┐             ┌─────────────────────────┐
│ Strong consistency   │             │ Eventual consistency     │
│ All-or-nothing       │             │ Always available          │
│ Pessimistic locking  │             │ Optimistic / async        │
│ Vertical scale       │             │ Horizontal scale          │
└─────────────────────┘             └─────────────────────────┘

How do you decide which model to use?

The decision is driven by the cost of staleness or partial completion.

| Workload | Model | Why |
|----------|-------|-----|
| Fund transfer | ACID | Half-done transfer loses money |
| Account balance | ACID | Stale balance causes panic |
| Product catalog | BASE | A second of staleness is harmless |
| Social feed | BASE | Availability matters more than consistency |
| View counter | BASE | Approximate count is fine |
| Audit log | ACID | Compliance requires exact records |

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;

public class TransferService {

    private final Connection connection;

    public TransferService(Connection connection) {
        this.connection = connection;
    }

    /**
     * ACID transfer: both legs in one transaction.
     */
    public void transfer(String fromAccount, String toAccount, BigDecimal amount)
            throws SQLException {
        connection.setAutoCommit(false);
        try {
            try (PreparedStatement debit = connection.prepareStatement(
                    "UPDATE accounts SET balance = balance - ? WHERE account_id = ?")) {
                debit.setBigDecimal(1, amount);
                debit.setString(2, fromAccount);
                debit.executeUpdate();
            }
            try (PreparedStatement credit = connection.prepareStatement(
                    "UPDATE accounts SET balance = balance + ? WHERE account_id = ?")) {
                credit.setBigDecimal(1, amount);
                credit.setString(2, toAccount);
                credit.executeUpdate();
            }
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(true);
        }
    }
}

What is polyglot persistence?

Mature systems use both ACID and BASE in the same architecture. This is called polyglot persistence. The authoritative ledger lives on an ACID relational database (PostgreSQL, MySQL). Read models, feeds, and catalogs live on BASE stores (Cassandra, DynamoDB, Redis).

┌─────────────────────────────────────┐
│ Transfer Service                     │
│ (writes to ACID PostgreSQL)          │
└───────────┬─────────────────────────┘
            │ event
            ▼
┌─────────────────────────────────────┐
│ Feed Service                         │
│ (writes to BASE Cassandra)           │
└─────────────────────────────────────┘

How does ACID work internally?

Databases implement ACID through several mechanisms:

• Atomicity – undo log (rollback segment) records old values so a rollback can restore them.
• Isolation – MVCC (multi-version concurrency control) gives each transaction a consistent snapshot; readers do not block writers.
• Durability – WAL (write-ahead log) is flushed to disk before the commit is acknowledged.

-- PostgreSQL: see the WAL position
SELECT pg_current_wal_lsn();

What is the difference between Consistency in ACID and Consistency in CAP?

Consistency in ACID means a transaction moves the database between valid states respecting constraints (foreign keys, checks, NOT NULL). Consistency in CAP means all nodes in a distributed system see the same data at the same time. These are different concepts sharing the same word.

What are common mistakes?

The most common mistake is treating ACID and BASE as a strict binary. They are a spectrum. You can use ACID for critical paths and BASE for non-critical reads within the same system.

Wrong thinking: "We are a NoSQL shop, so we use BASE everywhere."
Right thinking: "Money is ACID. Feeds are BASE. We use both."

Another mistake is assuming BASE means "no guarantees." BASE systems provide well-defined eventual consistency semantics, not chaos.

What should a senior engineer know?

A senior engineer should know that the choice is not about technology but about the cost of incorrectness. For a balance, a brief inconsistency costs customer trust and potentially money. For a feed, a brief inconsistency costs nothing. The engineer picks the model per data domain, not per system.

What are common interview follow-up questions?

• "Can you have ACID on a distributed database?" Yes, distributed databases like CockroachDB and Google Spanner provide distributed ACID transactions, at the cost of cross-region latency.
• "What about the Saga pattern?" Sagas provide eventual consistency across microservices by chaining local transactions with compensating actions, which is a BASE-like approach for multi-service workflows.
• "Is Redis ACID?" Redis supports transactions with MULTI/EXEC but not true isolation or durability by default. It is closer to BASE.