ACID vs BASE: Database Transaction Models Explained

ACID and BASE are two opposite philosophies for handling data changes in a database. ACID promises that every transaction is all-or-nothing, correct, isolated and permanent. BASE relaxes those promises so the system stays responsive and scales across many machines, accepting that copies of the data may disagree for a short time.

This tutorial explains each ACID property with a banking example, covers isolation levels and how databases implement them, breaks down what BASE really means, compares the two side by side, and shows Java code for both styles. It also covers sagas for transactions across microservices and gives clear guidance on when to choose each model.

1. Why Databases Need Transaction Guarantees

A transaction is a group of database operations that belong together as one unit of work. Transferring money is the classic example: subtract ₹1,000 from Account A and add ₹1,000 to Account B. Both steps must happen, or neither.

Many things can go wrong in between:

• The server crashes or loses power after the first step.
• Two customers try to buy the last item at exactly the same moment.
• A network error interrupts the application halfway through.
• A bug tries to save a negative balance or an order for a product that does not exist.

Without clear rules, these situations leave data broken: money disappears, items are sold twice, or reports show half-finished changes. ACID and BASE are two different answers to the question "what guarantees should the database give when things go wrong or happen at the same time?"

2. What Is ACID?

ACID is an acronym for four properties that together make transactions reliable:

• A – Atomicity: all operations in a transaction succeed, or none of them take effect.
• C – Consistency: a transaction moves the database from one valid state to another, never breaking defined rules.
• I – Isolation: transactions running at the same time do not interfere with each other.
• D – Durability: once a transaction is committed, it survives crashes and power failures.

Traditional relational databases such as PostgreSQL, MySQL (with the InnoDB engine), Oracle and SQL Server are built around ACID transactions.

3. The Four ACID Properties Explained

Atomicity: all or nothing

Atomicity means a transaction cannot be partly done. If any step fails, the database rolls back every change the transaction made, as if it never started.

Consistency: rules are never broken

Consistency means every committed transaction leaves the data valid according to the rules you defined: primary keys, foreign keys, NOT NULL, CHECK constraints, unique indexes and triggers. If a transaction would break a rule, for example by making a balance negative when a CHECK (balance >= 0) constraint exists, the database rejects it.

Note that the database can only enforce rules it knows about. Business rules that live only in application code are the application's responsibility.

Isolation: no stepping on each other's toes

Many transactions run at the same time. Isolation means each one behaves as if it were running alone, so one transaction never sees another's half-finished work. The strongest level, serializable, guarantees the result is the same as if the transactions had run one after another. Weaker levels trade some safety for speed, as the next section explains.

Durability: committed means permanent

Once the database says "committed", the change will not be lost, even if the server crashes a millisecond later. Databases achieve this by writing changes to a log on disk before confirming the commit, and often by copying them to replicas too.

4. Isolation Levels and Read Anomalies

Perfect isolation is expensive because it forces transactions to wait for each other. So the SQL standard defines four isolation levels, each allowing different anomalies:

• Dirty read: reading data another transaction has changed but not yet committed. If that transaction rolls back, you read something that never officially existed.
• Non-repeatable read: reading the same row twice in one transaction and getting different values, because someone else committed a change in between.
• Phantom read: running the same query twice and getting a different set of rows, because someone else inserted or deleted matching rows.

Defaults differ between databases. PostgreSQL, Oracle and SQL Server default to Read Committed. MySQL's InnoDB engine defaults to Repeatable Read, and its implementation also prevents most phantom reads. Always check your database's documentation, because real implementations often behave slightly differently from the standard.

5. How Databases Implement ACID

• Write-ahead log (WAL): before changing data files, the database appends every change to a sequential log on disk. On commit, it makes sure the log is safely flushed. After a crash, it replays committed changes and discards uncommitted ones. This provides atomicity and durability.
• Locks: a transaction can lock rows it is changing so others must wait. This is one way to provide isolation.
• MVCC (Multi-Version Concurrency Control): instead of making readers wait, the database keeps several versions of a row. Each transaction reads a consistent snapshot, while writers create new versions. PostgreSQL, MySQL InnoDB and Oracle all use MVCC, which lets reads and writes happen at the same time.
• Constraints and triggers: checked before a commit is accepted, providing consistency.
• Two-phase commit: when one transaction spans several databases or nodes, a coordinator first asks every participant to prepare, then tells all of them to commit. It is slow and blocks if the coordinator fails, so large distributed systems try to avoid it.

6. What Is BASE?

BASE is a looser model, popular in large distributed NoSQL databases. The acronym was chosen as a chemistry joke (acid versus base) and stands for:

• BA – Basically Available: the system responds to requests as much as possible, even during failures. Some answers may be stale or partial, but the system does not simply stop.
• S – Soft state: the data on each node may change over time even without new input, as updates from other replicas arrive. At any instant, replicas may hold different values.
• E – Eventually consistent: if no new updates are made, all replicas will converge to the same value after a short delay.

BASE accepts a small window of inconsistency in exchange for high availability, low latency and easy horizontal scaling. It is the practical result of choosing availability in the CAP theorem.

7. How BASE Works in Practice

Consider a "like" button on a social network with billions of users. When you tap it:

1. Your request reaches the nearest replica, which records the like and confirms immediately.
2. The replica sends the change to other replicas in the background (asynchronous replication).
3. For a few milliseconds or seconds, a user reading from a different replica may see the old count.
4. Once the update arrives everywhere, all replicas agree again.

The key design question in a BASE system is how to deal with the inconsistency window. Common techniques include read-your-own-writes (sending a user's reads to the replica that took their write), conflict resolution rules such as last-write-wins, and idempotent processing so that a repeated update does not count twice.

8. ACID vs BASE: Comparison Table

9. An ACID Transaction in Java

With plain JDBC, you control a transaction by turning off auto-commit, running your statements, and then calling commit() or rollback(). This example uses an in-memory H2 database (add the com.h2database:h2 dependency) so you can run it anywhere:

package com.javabytechie.acid;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BankTransferDemo {

    static void transfer(Connection conn, long fromId, long toId, BigDecimal amount) throws SQLException {
        conn.setAutoCommit(false);                                  // BEGIN
        try (PreparedStatement debit = conn.prepareStatement(
                     "UPDATE account SET balance = balance - ? WHERE id = ? AND balance >= ?");
             PreparedStatement credit = conn.prepareStatement(
                     "UPDATE account SET balance = balance + ? WHERE id = ?")) {

            debit.setBigDecimal(1, amount);
            debit.setLong(2, fromId);
            debit.setBigDecimal(3, amount);
            if (debit.executeUpdate() != 1) {
                throw new SQLException("Insufficient funds in account " + fromId);
            }

            credit.setBigDecimal(1, amount);
            credit.setLong(2, toId);
            if (credit.executeUpdate() != 1) {
                throw new SQLException("Account " + toId + " not found");
            }

            conn.commit();                                          // both changes become permanent
        } catch (SQLException e) {
            conn.rollback();                                        // undo everything in this transaction
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }

    static void printBalances(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT id, balance FROM account ORDER BY id")) {
            StringBuilder line = new StringBuilder("Balances:");
            while (rs.next()) {
                line.append(" #").append(rs.getLong("id")).append('=').append(rs.getBigDecimal("balance"));
            }
            System.out.println(line);
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:bank")) {
            try (Statement st = conn.createStatement()) {
                st.execute("CREATE TABLE account (id BIGINT PRIMARY KEY, balance DECIMAL(12,2) NOT NULL)");
                st.execute("INSERT INTO account VALUES (1, 5000.00), (2, 2000.00)");
            }
            printBalances(conn);

            transfer(conn, 1, 2, new BigDecimal("1000.00"));
            System.out.println("Transfer of 1000 committed");
            printBalances(conn);

            try {
                transfer(conn, 1, 2, new BigDecimal("9000.00"));
            } catch (SQLException e) {
                System.out.println("Transfer of 9000 rolled back: " + e.getMessage());
            }
            printBalances(conn);
        }
    }
}

Balances: #1=5000.00 #2=2000.00
Transfer of 1000 committed
Balances: #1=4000.00 #2=3000.00
Transfer of 9000 rolled back: Insufficient funds in account 1
Balances: #1=4000.00 #2=3000.00

The balance >= ? condition in the debit statement checks and updates in a single atomic step, so two concurrent withdrawals cannot both pass the check. In Spring applications you rarely write this plumbing yourself. Annotating a service method with @Transactional makes Spring begin a transaction before the method, commit after it returns, and roll back if it throws a runtime exception:

package com.javabytechie.acid;

import java.math.BigDecimal;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

@Service
public class TransferService {

    private final AccountRepository accounts;

    public TransferService(AccountRepository accounts) {
        this.accounts = accounts;
    }

    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void transfer(long fromId, long toId, BigDecimal amount) {
        Account from = accounts.findById(fromId).orElseThrow();
        Account to = accounts.findById(toId).orElseThrow();

        from.withdraw(amount);   // throws IllegalStateException if the balance is too low
        to.deposit(amount);
        // No explicit save needed: JPA flushes both changes when the transaction commits
    }
}

For real money movement with JPA, also protect against concurrent updates, for example with an @Version field for optimistic locking or a pessimistic row lock on the account rows.

10. Designing Code for a BASE System

In BASE systems, updates are often delivered as events through queues, and the same event may arrive more than once or out of order. The code that applies them must therefore be idempotent: processing an event twice must have the same effect as processing it once.

package com.javabytechie.base;

import java.util.Map;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;

public class LikeCounterProjection {

    private final Set<String> processedEventIds = ConcurrentHashMap.newKeySet();
    private final Map<Long, Long> likesByPost = new ConcurrentHashMap<>();

    public void onLikeAdded(String eventId, long postId) {
        if (!processedEventIds.add(eventId)) {               // seen before: skip it
            System.out.println("Duplicate " + eventId + " ignored");
            return;
        }
        long total = likesByPost.merge(postId, 1L, Long::sum);
        System.out.println(eventId + " applied: post " + postId + " now has " + total + " likes");
    }

    public static void main(String[] args) {
        LikeCounterProjection projection = new LikeCounterProjection();
        projection.onLikeAdded("evt-1", 42);
        projection.onLikeAdded("evt-2", 42);
        projection.onLikeAdded("evt-2", 42);                 // redelivered by the message broker
        projection.onLikeAdded("evt-3", 42);
    }
}

evt-1 applied: post 42 now has 1 likes
evt-2 applied: post 42 now has 2 likes
Duplicate evt-2 ignored
evt-3 applied: post 42 now has 3 likes

In production, the set of processed IDs would live in a durable store, and it would be updated in the same atomic step as the counter. The idea stays the same: BASE moves some responsibility for correctness from the database into your application design.

11. ACID Across Microservices: Sagas

When an order touches several services, each with its own database, a single ACID transaction is no longer possible without slow distributed locking. The common BASE-style alternative is a saga: a sequence of local ACID transactions, one per service. If a later step fails, the saga runs compensating actions that undo the earlier steps.

Sagas show that ACID and BASE are not enemies. Each service keeps full ACID guarantees for its own data, while the business process as a whole is eventually consistent. Read more in the Saga Pattern tutorial.

12. When to Choose ACID vs BASE

Most real systems use both: an ACID relational database for orders and payments, and BASE-style stores for caches, search indexes, analytics and activity feeds. This approach is often called polyglot persistence.

13. Which Databases Follow ACID or BASE?

The line has blurred over the years. Many NoSQL databases have added transactions, and new distributed SQL databases offer ACID at scale:

• Relational databases (PostgreSQL, MySQL InnoDB, Oracle, SQL Server): full ACID transactions.
• Distributed SQL (Google Spanner, CockroachDB, YugabyteDB): ACID transactions across many nodes and regions, using consensus protocols, at the cost of some extra write latency.
• MongoDB: single-document operations are atomic, and multi-document ACID transactions are supported on replica sets and sharded clusters in modern versions.
• Cassandra: BASE by design with tunable consistency; lightweight transactions offer compare-and-set within a single partition.
• Amazon DynamoDB: eventually consistent reads by default, optional strongly consistent reads, and transactions across a limited number of items.
• Redis: individual commands are atomic, and MULTI/EXEC groups commands, but there is no rollback if one command fails.

14. Common Myths

• "NoSQL means no transactions." Many NoSQL databases now support transactions in some form. Always check the specific product and version.
• "BASE means data is lost." No. BASE delays agreement between replicas; committed writes are still stored durably.
• "ACID cannot scale." Relational databases handle very large workloads with read replicas, partitioning and good indexing, and distributed SQL databases scale ACID horizontally.
• "Eventual consistency means minutes of delay." In a healthy system, replicas typically converge within milliseconds. Long delays happen mainly during failures or heavy load.
• "Consistency in ACID is the same as in CAP." ACID consistency is about rules and constraints. CAP consistency is about all replicas showing the latest write.

15. Best Practices

• Default to an ACID database for core business data unless you have a clear scale or availability reason not to.
• Keep transactions short: do not call slow external APIs or wait for user input while a transaction is open.
• Choose the lowest isolation level that is still correct for each operation, and understand your database's default.
• Put important rules in the database as constraints, not only in application code.
• In BASE systems, make every update idempotent and define a conflict-resolution strategy up front.
• Give users read-your-own-writes behaviour so they always see their own changes immediately.
• Use sagas or the transactional outbox pattern for workflows that span services, instead of distributed two-phase commit.
• Monitor replication lag in BASE systems and alert when it grows.

16. Summary

• ACID (Atomicity, Consistency, Isolation, Durability) guarantees that each transaction is all-or-nothing, valid, isolated and permanent.
• Isolation levels let you trade some protection against read anomalies for better performance.
• BASE (Basically Available, Soft state, Eventually consistent) favours availability and scale, accepting brief disagreement between replicas.
• ACID suits money, orders and inventory; BASE suits feeds, counters, logs and massive key-value workloads.
• Modern architectures combine both, using local ACID transactions plus sagas, events and idempotent processing across services.

The right question is never "ACID or BASE for the whole company?" but "which guarantee does this particular data need?" Open the FAQ tab for answers to common questions about ACID and BASE.

Frequently Asked Questions (FAQ)