ACID vs BASE

April 2026 · 16 min read

Every database makes a fundamental choice about how it handles data integrity. ACID (Atomicity, Consistency, Isolation, Durability) prioritises correctness at all costs, while BASE (Basically Available, Soft-state, Eventually consistent) trades strict correctness for availability and performance. Understanding these two philosophies — and the rich spectrum between them — is essential for choosing the right database and designing resilient distributed systems.

In this post we will dissect every ACID and BASE property with concrete examples, explore SQL isolation levels and the anomalies they prevent, examine distributed transaction patterns like 2PC and Saga, and build interactive animations that make these abstract concepts tangible.

ACID Properties

ACID is the gold standard for relational databases. Every property addresses a specific failure mode that can corrupt your data.

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';   -- debit
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';   -- credit
COMMIT;

ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 1500 WHERE id = 'A';  -- A has $1000
  -- CHECK constraint violated! balance would be -500
ROLLBACK;  -- transaction cannot commit

-- T1: Transfer $100 from A to B
-- T2: Calculate total balance across all accounts

-- If T2 reads A after T1's debit but before T1's credit:
-- T2 sees $100 "missing" from the system!
-- With proper isolation, T2 either sees the state before T1 or after T1, never in between.

Isolation Levels

The SQL standard defines four isolation levels, each progressively stricter. The level you choose determines which anomalies your transactions may encounter.

*MySQL/InnoDB’s Repeatable Read prevents phantoms via gap locks. PostgreSQL’s does not fully prevent them.

Read Uncommitted

The weakest level. A transaction can read data written by another transaction that has not yet committed. This is called a dirty read. If that other transaction rolls back, you have read data that never actually existed.

-- Session 1                           -- Session 2
SET TRANSACTION ISOLATION LEVEL        SET TRANSACTION ISOLATION LEVEL
  READ UNCOMMITTED;                      READ UNCOMMITTED;
BEGIN;                                 BEGIN;
UPDATE products SET price = 0          
  WHERE id = 42;                       
                                       SELECT price FROM products
                                         WHERE id = 42;
                                       -- Returns 0! (uncommitted)
ROLLBACK;                              
                                       -- Session 2 used price=0 for
                                       -- a calculation, but it was
                                       -- never real data.

Almost no production system uses this level. PostgreSQL doesn’t even implement it — READ UNCOMMITTED silently behaves as READ COMMITTED.

Read Committed

The default level in PostgreSQL and Oracle. Each statement within a transaction sees only data committed before that statement began. You never see uncommitted data, but the same query run twice might return different results if another transaction committed in between.

-- Session 1                           -- Session 2
BEGIN;                                 BEGIN;
SELECT balance FROM accounts           
  WHERE id = 'A';  -- returns 1000     
                                       UPDATE accounts SET balance = 500
                                         WHERE id = 'A';
                                       COMMIT;
SELECT balance FROM accounts           
  WHERE id = 'A';  -- returns 500!     
-- Same query, different result        
-- (non-repeatable read)               
COMMIT;

Repeatable Read

The default level in MySQL/InnoDB. The transaction sees a consistent snapshot taken at the start of the first read. All subsequent reads return data from that snapshot, regardless of concurrent commits.

-- Session 1 (Repeatable Read)         -- Session 2
BEGIN;                                 BEGIN;
SELECT balance FROM accounts           
  WHERE id = 'A';  -- returns 1000     
                                       UPDATE accounts SET balance = 500
                                         WHERE id = 'A';
                                       COMMIT;
SELECT balance FROM accounts           
  WHERE id = 'A';  -- STILL 1000!     
COMMIT;

MySQL vs PostgreSQL difference: MySQL/InnoDB uses gap locks and next-key locks to prevent phantom reads at this level. PostgreSQL uses MVCC snapshots, which prevents phantoms for SELECT but not for write-write conflicts involving new rows.

Serializable

The strictest level. Transactions execute as if they ran one after another (serial order). All anomalies are prevented, but throughput drops because the database must detect and abort conflicting transactions.

-- PostgreSQL's Serializable Snapshot Isolation (SSI)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT sum(balance) FROM accounts;  -- snapshot read
-- ... some logic ...
UPDATE accounts SET balance = balance + interest WHERE ...;
COMMIT;  -- may fail with "could not serialize access"

PostgreSQL implements Serializable Snapshot Isolation (SSI), which detects dangerous read-write dependency cycles and aborts one of the conflicting transactions. MySQL uses two-phase locking (2PL) with shared and exclusive locks.

Transaction Anomalies

Each anomaly represents a specific way that concurrent transactions can produce incorrect results. Understanding them is key to choosing the right isolation level.

-- T1                                  -- T2
BEGIN;                                 BEGIN;
SELECT count(*) FROM doctors           SELECT count(*) FROM doctors
  WHERE on_call = true;  -- 2            WHERE on_call = true;  -- 2
-- "Safe to remove one"               -- "Safe to remove one"
UPDATE doctors SET on_call = false     UPDATE doctors SET on_call = false
  WHERE name = 'Alice';                  WHERE name = 'Bob';
COMMIT;                                COMMIT;
-- Result: 0 doctors on call!

-- Prevention: use atomic operations
UPDATE counters SET value = value + 1 WHERE id = 42;
-- Or use SELECT ... FOR UPDATE to acquire a row-level lock

BASE Properties

BASE is the philosophical opposite of ACID. It was popularised by the NoSQL movement and is rooted in the CAP theorem — if you need high availability in a partitioned network, you must relax consistency.

Why NoSQL Chose BASE

Traditional RDBMS systems were designed for single-node or tightly-coupled clusters. When web-scale applications needed to serve millions of users across continents, they hit fundamental limits:

• Latency: Coordinating distributed transactions across data centers introduces 100ms+ of latency per round-trip. ACID’s synchronous coordination is too slow for global services.
• Availability: ACID requires all participants to agree before committing. If any node is unreachable, the transaction blocks. In a large distributed system, something is always failing.
• Throughput: Locking and serialization bottleneck write throughput. BASE systems can accept writes on any node without coordination, enabling linear write scaling.

Google’s Bigtable, Amazon’s DynamoDB, Apache Cassandra, and MongoDB (before v4.0) all adopted BASE semantics to achieve the availability and horizontal scalability that ACID could not easily provide at massive scale.

ACID vs BASE Comparison

When to Use ACID

• Financial transactions — money must never be created or destroyed
• Inventory management — you can’t sell the last item to two customers
• Booking/reservation systems — double-booking a hotel room is unacceptable
• Healthcare records — medical data integrity is a regulatory requirement
• Any domain where incorrect data is worse than slow or unavailable data

When to Use BASE

• Social media feeds — a few seconds of stale data is perfectly fine
• Analytics / event logging — approximate counts are acceptable
• Content delivery / caching — serving slightly stale content is better than serving nothing
• IoT sensor data — millions of writes per second, eventual aggregation
• Any domain where availability and speed matter more than immediate correctness

The Spectrum: Tunable Consistency

The ACID-vs-BASE choice is not binary. Many modern databases let you tune the trade-off per query:

Even Google’s Spanner proves that globally-distributed ACID is possible — using GPS-synchronised atomic clocks (TrueTime) to achieve external consistency. The trade-off is higher latency (commit waits for clock uncertainty to elapse).

Implementing Transactions in Distributed Systems

When data spans multiple services or databases, maintaining transactional guarantees becomes dramatically harder. Here are the most common patterns:

// Order Saga
Step 1: Create Order           → Compensate: Cancel Order
Step 2: Reserve Inventory      → Compensate: Release Inventory
Step 3: Charge Payment         → Compensate: Refund Payment
Step 4: Ship Order             → Compensate: Cancel Shipment

// If Step 3 fails:
Compensate Step 2: Release Inventory
Compensate Step 1: Cancel Order

-- Within a single ACID transaction:
BEGIN;
  UPDATE orders SET status = 'confirmed' WHERE id = 123;
  INSERT INTO outbox (aggregate_id, event_type, payload)
    VALUES (123, 'OrderConfirmed', '{"orderId": 123}');
COMMIT;

-- A separate process (CDC or poller) reads the outbox
-- table and publishes events to Kafka/RabbitMQ.

MVCC (Multi-Version Concurrency Control)

MVCC is the secret behind modern databases’ ability to handle thousands of concurrent transactions without locking everything. Instead of blocking readers when a writer modifies a row, the database keeps multiple versions of each row and gives each transaction its own consistent “snapshot” of the data.

How MVCC Works

When a transaction modifies a row, the database doesn’t overwrite the old version. Instead, it creates a new version of the row, tagged with the writing transaction’s ID. The old version remains available for other transactions that started before the write.

PostgreSQL’s MVCC Implementation

PostgreSQL stores all row versions directly in the heap (the main table files). Each row version (called a “tuple”) has two hidden columns:

• xmin — the transaction ID that created this version (via INSERT or UPDATE)
• xmax — the transaction ID that deleted or replaced this version (0 if still live)

-- You can actually see these hidden columns:
SELECT xmin, xmax, * FROM accounts WHERE id = 'A';
-- xmin: 1000, xmax: 0  → created by txn 1000, still live

-- After an UPDATE in txn 1050:
-- Old version: xmin=1000, xmax=1050  (dead, replaced by 1050)
-- New version: xmin=1050, xmax=0     (current live version)

A transaction with ID 1025 (started between 1000 and 1050) will see the old version because xmin=1000 ≤ 1025 and xmax=1050 > 1025 (txn 1050 hadn’t committed when 1025’s snapshot was taken).

VACUUM: Dead tuples accumulate over time. PostgreSQL’s VACUUM process reclaims space by removing tuples that are no longer visible to any active transaction. Autovacuum runs this automatically, but high-update tables may need tuning.

MySQL/InnoDB’s MVCC Implementation

InnoDB takes a different approach. The clustered index (primary key B-tree) always stores the latest version. Older versions are stored in a separate undo log (rollback segment), linked via a “roll pointer” in each row.

• To read an old version, InnoDB follows the roll pointer chain backwards through the undo log until it finds a version visible to the reading transaction.
• The undo log is also used for rollback (ACID Atomicity) — dual purpose.
• Purge thread: InnoDB’s purge thread removes undo log entries that are no longer needed (equivalent to PostgreSQL’s VACUUM).

Why MVCC Enables High Concurrency

This is why PostgreSQL and MySQL can handle thousands of concurrent connections with a mix of reads and writes. Long-running analytical queries don’t block short OLTP transactions, and vice versa.

The cost of MVCC: Storage overhead (multiple row versions), CPU overhead (visibility checks for every row), and the need for background cleanup (VACUUM/purge). In write-heavy workloads, dead tuple bloat can become a serious issue if autovacuum can’t keep up.

Summary

• ACID guarantees correctness through Atomicity (all-or-nothing), Consistency (invariants hold), Isolation (concurrent txns don’t interfere), and Durability (committed data survives crashes).
• Isolation levels trade anomaly prevention for performance: Read Uncommitted < Read Committed < Repeatable Read < Serializable.
• Transaction anomalies include dirty reads, non-repeatable reads, phantoms, write skew, and lost updates — each is a concrete failure mode to guard against.
• BASE trades consistency for availability: the system always responds, state is soft, and consistency is eventual.
• Modern databases offer a spectrum between ACID and BASE, with tunable consistency (Cassandra), optional transactions (MongoDB), and globally-distributed ACID (Spanner).
• Distributed transaction patterns (2PC, Saga, TCC, Outbox) each make different trade-offs between consistency, availability, and complexity.
• MVCC is what makes high-concurrency ACID practical — readers never block writers and vice versa, at the cost of storage and cleanup overhead.