ACID vs BASE: Understanding Database Consistency Models

ACID vs BASE: Understanding Database Consistency Models

ACID and BASE represent two fundamentally different approaches to data consistency in databases. ACID prioritizes correctness and strong guarantees, while BASE prioritizes availability and performance at the cost of immediate consistency. Understanding this spectrum is essential for making the right database selection in system design.

ACID Properties Explained

ACID is the set of properties that guarantee reliable transaction processing in traditional SQL databases. Every major relational database — PostgreSQL, MySQL, Oracle, SQL Server — implements ACID.

Atomicity

Atomicity guarantees that a transaction is treated as a single, indivisible unit. Either all operations within the transaction succeed, or none of them take effect. There is no partial execution.

-- Bank transfer: Atomicity ensures both operations succeed or both fail
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500.00 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 500.00 WHERE account_id = 'B';

-- If either UPDATE fails (e.g., insufficient funds), BOTH are rolled back
COMMIT;

-- Without atomicity, a crash between the two UPDATEs could
-- deduct money from A without crediting B — money disappears!

Databases implement atomicity using a write-ahead log (WAL). Before modifying data, the database writes the intended changes to a log. If a crash occurs mid-transaction, the database reads the log on recovery and rolls back incomplete transactions.

Consistency

Consistency ensures that a transaction moves the database from one valid state to another valid state. All integrity constraints (primary keys, foreign keys, CHECK constraints, unique constraints) must be satisfied after the transaction completes.

-- Consistency: Constraints prevent invalid states
CREATE TABLE accounts (
    account_id VARCHAR(20) PRIMARY KEY,
    balance DECIMAL(12,2) CHECK (balance >= 0),  -- No negative balances
    owner_id INT REFERENCES users(id) NOT NULL     -- Must reference valid user
);

-- This transaction violates the CHECK constraint and will be rejected
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 10000.00 WHERE account_id = 'A';
-- ERROR: new row violates check constraint "accounts_balance_check"
ROLLBACK;

Isolation

Isolation determines how concurrent transactions interact with each other. It ensures that the intermediate state of one transaction is not visible to other transactions. SQL defines four isolation levels, each balancing concurrency against consistency.

Understanding Read Anomalies

Dirty Read: Transaction A reads data that Transaction B has modified but not yet committed. If B rolls back, A has read data that never existed.

-- Dirty Read Example
-- Transaction A                    -- Transaction B
BEGIN;                              BEGIN;
                                    UPDATE products SET price = 9.99
                                    WHERE id = 1; -- was 19.99
SELECT price FROM products
WHERE id = 1;
-- Returns 9.99 (uncommitted!)
                                    ROLLBACK; -- Price reverts to 19.99
-- Transaction A used price 9.99
-- which never actually existed

Non-Repeatable Read: Transaction A reads a row twice and gets different values because Transaction B modified and committed the row between the two reads.

Phantom Read: Transaction A runs a query twice and gets different sets of rows because Transaction B inserted or deleted rows that match the query condition.

-- Setting isolation level in PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;
-- All reads within this transaction see a consistent snapshot
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- ... do work ...
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Guaranteed to return the same count
COMMIT;

-- MySQL: Set default isolation level
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Durability

Durability guarantees that once a transaction is committed, it survives any subsequent failure — server crash, power outage, disk failure. The database achieves this by writing committed data to non-volatile storage (disk) before acknowledging the commit.

Databases use the WAL (Write-Ahead Log) to ensure durability. The WAL is written to disk before the actual data files are updated. On crash recovery, the database replays the WAL to restore committed transactions.

BASE Properties Explained

BASE stands for Basically Available, Soft state, Eventually consistent. It is the consistency model used by most NoSQL databases and distributed systems that prioritize availability over strong consistency.

Basically Available

The system guarantees availability — it will always respond to requests, even during network partitions or node failures. The response may not reflect the most recent write, but the system does not go down.

Soft State

The system's state may change over time even without new input. Replicas may not have the latest data, and the system is continuously propagating updates in the background.

Eventually Consistent

If no new updates are made, all replicas will eventually converge to the same value. The time to convergence varies — it could be milliseconds or seconds depending on the system and network conditions.

ACID vs BASE: The Consistency Spectrum

ACID and BASE are not binary choices — they represent ends of a consistency spectrum. Many modern databases offer tunable consistency levels between the two extremes.

Real Database Examples on the Spectrum

Strong ACID

PostgreSQL and MySQL InnoDB provide full ACID compliance with configurable isolation levels. They are the gold standard for transactional workloads.

ACID with Distribution

Google Spanner and CockroachDB provide ACID transactions across distributed nodes. They use consensus protocols (Paxos/Raft) to achieve this, trading some latency for strong consistency. See our NewSQL guide for details.

Tunable Consistency

Cassandra lets you choose consistency per query. A consistency level of QUORUM means a majority of replicas must respond, providing a balance between availability and consistency. ONE means any single replica can respond (fast but potentially stale).

-- Cassandra: Tunable consistency levels
-- Write with QUORUM consistency (majority of replicas must acknowledge)
INSERT INTO users (id, name, email)
VALUES (uuid(), 'Alice', 'alice@example.com')
USING CONSISTENCY QUORUM;

-- Read with ONE consistency (fastest, may return stale data)
SELECT * FROM users WHERE id = some_uuid
USING CONSISTENCY ONE;

-- DynamoDB: Choose per-request
-- Strong consistency: always returns latest committed value
-- Eventually consistent: may return slightly stale data (half the cost)

Eventually Consistent

DynamoDB (default reads), Cassandra (with ONE consistency), and Redis in cluster mode use eventual consistency. Data propagates to all nodes asynchronously, with convergence typically within milliseconds.

When to Choose ACID

• Financial transactions: Bank transfers, payments, billing — money must never be lost or duplicated
• Inventory management: Stock counts must be accurate to prevent overselling
• User authentication: Account creation and password changes must be immediately consistent
• Order processing: Order state transitions (placed → paid → shipped) must be reliable
• Any system where data correctness is more important than availability

When to Choose BASE

• Social media feeds: A slight delay in showing new posts is acceptable
• Analytics and counters: View counts, likes — approximate numbers are fine temporarily
• Search indexes: Slightly stale search results are acceptable
• Session storage: A user might need to log in again in rare cases
• Any system where availability and performance outweigh perfect consistency

The CAP Theorem Connection

The CAP theorem states that distributed systems can guarantee only two of three properties: Consistency, Availability, and Partition Tolerance. Since network partitions are inevitable, the real choice is between CP (ACID-like, sacrificing availability during partitions) and AP (BASE-like, sacrificing consistency during partitions).

ACID databases are typically CP systems: they become unavailable rather than return inconsistent data. BASE databases are typically AP systems: they remain available but may return stale data during network issues.

For deeper exploration of consistency in distributed systems, see our guides on database replication, distributed transactions, and distributed databases.

Frequently Asked Questions

Can a NoSQL database be ACID compliant?

Yes. MongoDB supports multi-document ACID transactions since version 4.0. DynamoDB supports transactions across multiple items. CockroachDB and TiDB are distributed databases with full ACID support. The line between SQL and NoSQL is blurring as databases adopt features from both paradigms.

What isolation level should I use?

Read Committed is the default in PostgreSQL and is sufficient for most applications. Use Repeatable Read when you need consistent reads within a transaction (financial reports). Use Serializable only when absolute correctness is required and you can tolerate lower throughput. Never use Read Uncommitted in production.

Is eventual consistency dangerous?

It depends on the use case. For a social media like count, eventual consistency is perfectly fine — showing 999 likes instead of 1000 for a few seconds causes no harm. For a bank balance, eventual consistency is unacceptable. The key is matching the consistency level to the business requirement of each operation.

How long does "eventually consistent" actually take?

In most modern systems, eventual consistency converges within milliseconds to a few seconds under normal conditions. DynamoDB eventually consistent reads are typically consistent within one second. Cassandra typically converges within the anti-entropy repair cycle. The guarantee is that the system WILL converge, not that it will do so instantly.

Can I use ACID and BASE in the same application?

Absolutely, and this is the recommended approach. Use ACID (PostgreSQL/MySQL) for your core transactional data (orders, payments, inventory) and BASE (Redis, Cassandra, DynamoDB) for non-critical data (caching, analytics, session storage). This is the polyglot persistence pattern described in our SQL vs NoSQL guide.