CAP vs ACID vs BASE?!

Have you ever studied or worked with databases, come across abbreviations such as ACID, BASE, and CAP, and wondered what they mean? I have, and this post is the result of my research into them.

Is CAP a type of headwear?⌗

CAP stands for Consistency, Availability, Partition-tolerance. Eric Brewer introduced it in a talk in 2000. He argued that when a partition disrupts network communication between nodes in a distributed system, the system must choose between consistency and availability. It cannot guarantee both. What are these properties, and why can we not have both?

C stands for consistency⌗

In computer science, especially when discussing data, the word consistency is heavily overloaded. ACID, CAP, and BASE all use it, often with different meanings, which can lead to confusion. In CAP, a strongly consistent system holds the same version of data on every node at every point in time. If you successfully write new data to one node, that data must also exist on all other nodes. This differs from the eventual consistency mentioned in BASE, which allows nodes to hold different versions temporarily before converging on a single version.

Consistency is highly desirable in many systems. Imagine depositing 1,000,000 USD at a bank (whoa!), then checking your account and finding no trace of the deposit because of latency or a temporary partition. What a disaster!

Choosing availability: a slow sync leaves Node 2 behind, so the client is told its own deposit does not exist

A stands for availability⌗

According to Seth Gilbert and Nancy Lynch, a distributed system is considered highly available only if every request to a non-failing node receives a valid response. In plain terms, the system must have 100% uptime to meet this definition.

That definition is difficult to meet in practice. Many Google Cloud products, for example, have an SLA of 99.99% uptime, equivalent to around 52 minutes of downtime per year. They do not meet the definition above but are still considered highly available. Even so, I find it easier to understand than Brewer’s definition: “Data is considered highly available if a given consumer of the data can always reach some replica.”

Availability is another desirable property of a distributed system. Modern systems are expected to be ready at any time. An unavailable e-commerce system can lose customers and orders to competitors. In healthcare, an outage can cause chaos and delay treatment.

P stands for partition-tolerance⌗

A system is partition-tolerant if it continues to operate normally when a partition occurs, for example because of a network slowdown or crashed nodes.

Why can we not choose all three?⌗

Consider a system with two nodes, A and B. During a partition, A cannot communicate with B. The system is partition-tolerant, so it continues operating, and users can still interact with it, satisfying availability. But the nodes now operate in isolation. Data written to A does not immediately exist on B, and vice versa, so the system is inconsistent. This is an AP system.

Now consider another system with the same setup. When the nodes discover they cannot communicate, they stop accepting client requests and try to reconnect. This keeps the system consistent, but users cannot interact with it, so it is not available. This is a CP system.

Is ACID a corrosive substance?⌗

ACID is a set of properties often associated with relational databases: Atomicity, Consistency, Isolation, Durability. Nearly every database is expected to support these properties in some form, and they are often used to indicate how reliable a database is.

A stands for Atomicity⌗

Atomicity means the smallest unit of work in a database is a transaction, not a query. A transaction can contain one or more queries, and it succeeds only if all of them succeed.

This matters because business transactions rarely consist of a single query. A bank transfer, for example, requires deducting money from the sender’s account and adding it to the recipient’s account. If either step fails, the whole transfer is considered unsuccessful. With atomicity, we can manage the transaction as a whole without manually handling the state of each query.

C stands for Consistency⌗

Consistency in ACID differs from strong consistency and eventual consistency in CAP and BASE. Here, it means the database guarantees that its data conforms to the predefined schema and constraints. These include data types, primary keys, unique keys, foreign keys, triggers, procedures, and the number of columns.

I stands for Isolation⌗

Isolation describes how a database isolates transactions from one another. It is a form of concurrency control intended to prevent data races when multiple transactions try to modify the same data, such as a page, table, record, or index. Databases usually provide isolation by locking data accessed by DML queries (such as INSERT, UPDATE, and DELETE) or creating snapshots for DQL queries (such as SELECT). There are four main isolation levels. Higher levels reduce the likelihood of data races but come at a performance cost.

Read-committed⌗

Read-committed is the second isolation level, but I think it is worth understanding first because it helps explain the others. At this level, other transactions cannot see changes made by the current transaction until it commits successfully. This ensures that you only read committed data.

-- Start a transaction
BEGIN;

-- Insert a new record into the 'example' table in the 'acid' database
INSERT INTO 'acid'.'example'('id', 'name')
VALUES 
    -- Other transactions can't see this record until this transaction is committed successfully
    (2, "Chuồn chuồn bay cao thì nắng, bay vừa thì râm");

-- Commit transaction
COMMIT;

Repeatable-read⌗

Repeatable read is the third isolation level. It provides the guarantees of read-committed and also ensures that other transactions cannot modify records used or read by the current transaction during its lifetime.

-- Start a transaction
BEGIN;

-- This record is locked. Other transactions can't modify it until this transaction commits.
SELECT 'name' FROM 'acid'.'example' WHERE 'id' = 1;

-- Other queries
-- etc.

-- The returned record is unchanged
SELECT 'name' FROM 'acid'.'example' WHERE 'id' = 1;

-- Commit the transaction
COMMIT;

Serializable⌗

Serializable is the highest isolation level. In addition to the guarantees of repeatable-read, it prevents other transactions from adding or removing records from the result sets used by the current transaction during its lifetime. In other words, it prevents phantom reads.

-- Start a transaction
BEGIN;

-- First query. This will lock the table, so other transactions
-- cannot add or delete records with an id less than 9999
SELECT 'name' FROM 'acid'.'example' WHERE 'id' < 9999;

-- Other queries
-- etc.

-- The number of results is unchanged
SELECT 'name' FROM 'acid'.'example' WHERE 'id' < 9999;

-- Commit transaction
COMMIT;

Dirty-read⌗

Dirty-read is the lowest isolation level. At this level, there is no isolation between transactions.

D stands for Durability⌗

Durability: MySQL acknowledges the COMMIT once the binlog is on disk, so a crash before the page write loses nothing and recovery replays the binlog

Durability describes how reliably a database preserves data when faults occur. If a transaction commits successfully, its data persists even if the database crashes immediately afterward, provided the hard drive is not damaged. Techniques such as two-phase commit, backups, and replication are used to provide this guarantee. In MySQL, each commit writes data to the binlog before writing it to pages on disk, allowing recovery from the binlog after a failure. The binlog also supports replication between nodes. MongoDB has a similar log called the oplog.

BASE!?⌗

BASE is often associated with distributed databases. It emphasizes availability, sacrificing strong consistency in favor of eventual consistency.

BA stands for Basically Available⌗

This property guarantees that the system is always available and that every request receives a valid response.

S stands for Soft-State⌗

Soft-state means the system’s state can change even without user interaction. This can result from eventual consistency: after a user writes data, the state does not change everywhere immediately. The change takes time to propagate and converge.

E stands for Eventual Consistency⌗

Unlike strong consistency, eventual consistency allows data to differ between nodes temporarily. Given enough time, all nodes converge on a single version of the data.