beginner~2h

Consistency

The most misunderstood letter in ACID — mostly because the word "consistency" means something completely different in the CAP theorem. This chapter draws that line clearly.

Level	Definition
Beginner	A transaction can only move the database from one "valid" state to another "valid" state — it can never leave the data in a state that breaks the rules you've defined.
Technical	Consistency guarantees that every transaction, if it commits, results in a database state that satisfies all defined constraints — primary keys, foreign keys, unique constraints, check constraints, and application-level invariants enforced within the transaction.
Interview-grade	ACID consistency is actually a consequence of atomicity, isolation, and the constraints you define — the database doesn't have a separate "consistency engine"; it enforces consistency by rejecting (rolling back) any transaction that would violate a declared constraint, combined with atomicity ensuring no partial, rule-breaking state is ever persisted.

◆ Under the hood — consistency is the "output," not a separate mechanism

Unlike Atomicity (undo logs), Isolation (locking/MVCC), and Durability (write-ahead logs), Consistency has no dedicated internal mechanism of its own — it's the emergent result of the other three properties working correctly plus you defining correct constraints. A database with perfect atomicity, isolation, and durability, but no foreign key or check constraints defined, can still let you commit logically nonsensical data (e.g. an order referencing a customer ID that doesn't exist) — consistency is a joint responsibility between the database engine and the schema/business rules you actually declare.

◆ The problem

The word "consistency" is reused for two genuinely different ideas across database literature, and conflating them is a very common interview mistake.

	ACID Consistency	CAP Consistency
What it means	Data satisfies defined constraints/invariants after every committed transaction	Every read sees the most recent write, across a distributed system's nodes
Scope	Within a single database's rules and constraints	Across replicas/nodes in a distributed system
Example violation	A row with a foreign key pointing to a non-existent parent row	Reading a replica that hasn't yet received a write that another replica already has

▲ Common mistake

Saying "MongoDB isn't consistent" or "this NoSQL database sacrifices consistency" without specifying which consistency is a genuine red flag in a senior interview — most NoSQL databases have perfectly enforceable ACID-style consistency within a single document or transaction; what they typically trade off is CAP consistency across replicas for availability, which is an entirely different axis.

◆ Story

A warehouse database has a rule: stock_count can never go negative — it's a physical impossibility to have -3 units of a product sitting on a shelf. Suppose a transaction tries to sell 5 units when only 3 remain. Atomicity alone wouldn't stop this — it would happily apply "subtract 5" as one atomic operation. It's a CHECK constraint (stock_count >= 0) that makes the database itself reject and roll back this transaction, refusing to ever let stock_count become -2 — that rejection, and the guarantee that invalid states can never be committed, is consistency in action.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    stock_count INT NOT NULL CHECK (stock_count >= 0), -- physical-world rule, enforced by the DB itself
    warehouse_id INT REFERENCES warehouses(id) -- foreign key: can't reference a warehouse that doesn't exist
);

BEGIN;
UPDATE products SET stock_count = stock_count - 5 WHERE id = 101; -- current stock_count is 3
-- ERROR: new row for relation "products" violates check constraint
-- the entire transaction is automatically rolled back — the invalid state never commits

@Transactional
public void sellStock(Long productId, int quantity) {
    Product product = productRepository.findByIdForUpdate(productId); // locked read — see Chapter 07
    if (product.getStockCount() < quantity) {
        throw new InsufficientStockException(productId); // application-level invariant, enforced before the DB constraint even runs
    }
    product.setStockCount(product.getStockCount() - quantity);
}

Notice both layers matter: the database CHECK constraint is the final, unbypassable guarantee (protects against bugs in any code path, including ones you forgot to add a check to); the application-level check gives a clean, specific exception instead of a raw constraint-violation error bubbling up to the user.

💻 Code example

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    stock_count INT NOT NULL CHECK (stock_count >= 0), -- physical-world rule, enforced by the DB itself
    warehouse_id INT REFERENCES warehouses(id) -- foreign key: can't reference a warehouse that doesn't exist
);

Want a visual for this concept?

Generate a diagram tailored to “Consistency” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Isolation & Anomalies →← Back to all Transaction Mastery chapters