ACID - consistency

General Questions~5 min read

ACID consistency - a database guarantee that every transaction moves data from one valid state to another, enforcing all defined rules and constraints along the way.

Theory

TL;DR

Think of it as a bank vault rule: either both accounts update fully during a transfer, or nothing changes at all.
The database checks all constraints at commit time. Any violation triggers an automatic rollback.
Consistency is about your business rules (balance >= 0). Isolation is a separate concept about hiding concurrent changes.
Push rules to DB constraints and triggers first. Use app logic only for complex multi-database cases.

Quick example

sql

-- PostgreSQL: enforce balance >= 0 at DB level
CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  owner TEXT NOT NULL,
  balance NUMERIC(12,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (owner, balance) VALUES ('Alice', 100);

BEGIN;
UPDATE accounts SET balance = balance - 150 WHERE owner = 'Alice'; -- tries -50
COMMIT; -- FAILS: CHECK constraint violation, auto-rollback
SELECT balance FROM accounts WHERE owner = 'Alice'; -- still 100

Alice's balance stays 100. The transaction rolled back automatically because -50 violates the CHECK constraint. No partial state, no negative balance.

ACID consistency differs from regular data validation because it ties enforcement atomically to the transaction. App-level checks can race or fail between check and write (this is the TOCTOU problem: time-of-check-to-time-of-update). DB-level constraints block the commit until all rules hold across the entire transaction. This eliminates "zombie" states where partial updates leave your invariants broken, even under concurrency.

When to use

Balance >= 0, NOT NULL, field formats - DB CHECK constraints (fast, concurrent-safe)
Cross-table rules (total debt <= credit limit) - triggers or stored procedures
Multiple databases or external systems - application sagas, 2PC
High-scale NoSQL - app logic + eventual consistency (BASE trade-off)

How PostgreSQL enforces this

PostgreSQL validates consistency during transaction commit. It acquires locks on affected rows, re-checks all constraints and triggers against the final write set, and only writes to WAL if everything passes. If any CHECK, FK, or UNIQUE constraint fails, the entire transaction aborts via MVCC snapshot rollback.

The constraint checker runs as a deferred validation phase: post-write but pre-commit. Deferred constraints (SET CONSTRAINTS ALL DEFERRED) wait until COMMIT to validate, which matters for circular FK references or multi-step transfers where interim states are temporarily invalid.

After a crash, WAL replay re-applies committed transactions in commit order and rolls back incomplete ones. No partial state survives recovery.

Common mistakes

Validating only in app code

sql

-- WRONG: app checks balance, then updates separately
IF (SELECT balance FROM accounts WHERE id = 1) > 100 THEN
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
END IF;
-- A concurrent tx can drain the account between the check and the UPDATE

Two transactions can pass the check simultaneously and both deduct. The DB CHECK constraint closes this gap by evaluating at commit, under proper locking.

Using READ COMMITTED when you need strict consistency

READ COMMITTED allows non-repeatable reads. A transaction can read the same row twice and see different values if another transaction committed between the two reads. For invariants that span multiple reads (like checking email uniqueness before insert), use SERIALIZABLE isolation.

Immediate constraints on multi-step operations

sql

-- WRONG for a two-leg transfer:
SET CONSTRAINTS ALL IMMEDIATE;
-- The debit fires CHECK before the credit happens. Fails on the interim state.

-- RIGHT:
SET CONSTRAINTS ALL DEFERRED;
-- CHECK evaluates only at COMMIT, after all writes are complete.

Triggers without BEFORE context

RAISE EXCEPTION in an AFTER trigger fires too late in some scenarios. Use BEFORE triggers for constraint logic so the rollback happens before any row is actually written.

Real-world usage

PostgreSQL + Rails: ActiveRecord wraps models in transactions and uses CHECK/FK constraints. Shopify runs this pattern at scale.
CockroachDB: distributed SQL enforces serializable consistency via total order broadcast.
Google Spanner: TrueTime API for external consistency in geo-distributed transactions.
MySQL InnoDB: gap locks prevent phantom reads in REPEATABLE READ mode.
NoSQL trade-off: DynamoDB and Cassandra favor availability (BASE). Use ACID for money and inventory, eventual consistency for logs and social feeds.

Follow-up questions

Q: What is the difference between Consistency and Isolation in ACID?
A: Consistency enforces your business rules (invariants). Isolation hides concurrent changes from your transaction view. Consistency fails on bad data, Isolation fails on races.

Q: How does consistency work in a distributed system like Google Spanner?
A: Spanner uses TrueTime for external consistency. Commits get timestamps such that if Tx1 commits before Tx2 starts, Tx2 is guaranteed to see Tx1's effects. No 2PC required.

Q: Why might a transaction pass all constraints locally but still fail at commit?
A: Deferred constraints and triggers evaluate after all writes but before commit. A concurrent transaction can delete a FK target or invalidate a UNIQUE constraint in the window between your writes and your commit.

Q: Walk through how WAL replay ensures consistency after a PostgreSQL crash.
A: WAL stores full transaction images. On recovery, replay applies committed transactions in commit order and rolls back any that were incomplete. No partial state can survive this process.

Q: What is the TOCTOU race and why does it break app-level consistency?
A: TOCTOU is the gap between reading a value and writing based on it. A concurrent transaction can change the value in that gap. DB constraints eliminate this because they check at commit, under proper locking.

Examples

Basic: constraint blocking an invalid transaction

sql

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  owner TEXT NOT NULL,
  balance NUMERIC(12,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (owner, balance) VALUES ('Alice', 100), ('Bob', 10);

BEGIN;
UPDATE accounts SET balance = balance - 150 WHERE owner = 'Alice'; -- -50
COMMIT;
-- ERROR: new row violates check constraint "accounts_balance_check"
-- Alice's balance is still 100

The transaction never committed. PostgreSQL detected the constraint violation and rolled back everything. No application code needed.

Intermediate: atomic money transfer

sql

BEGIN;

UPDATE accounts
  SET balance = balance - 100
  WHERE owner = 'Alice'
  RETURNING balance; -- 0 after this

UPDATE accounts
  SET balance = balance + 100
  WHERE owner = 'Bob'
  RETURNING balance; -- 110 after this

COMMIT;
-- Result: Alice = 0, Bob = 110
-- If either update fails (Bob's row missing, CHECK hit), both roll back

This is the pattern Stripe uses for balance adjustments, combined with idempotency keys to handle retries safely. Both updates land or neither does. There is no state where Alice lost money but Bob did not receive it.

Advanced: layered consistency with triggers and deferred constraints

sql

-- Optimistic locking with versioning
CREATE TABLE wallets (
  id SERIAL PRIMARY KEY,
  balance NUMERIC CHECK (balance >= 0),
  version INT DEFAULT 0
);

CREATE FUNCTION check_version() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.version != OLD.version THEN
    RAISE EXCEPTION 'Concurrent update detected';
  END IF;
  NEW.version := OLD.version + 1;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER version_check
  BEFORE UPDATE ON wallets
  FOR EACH ROW EXECUTE FUNCTION check_version();

If two transactions read version 0 and both try to update, one fails the version check. If the other tries to withdraw more than the balance, CHECK catches it too. Two independent layers: the trigger blocks concurrent writes, CHECK blocks invalid state. I have seen engineers skip the CHECK thinking the trigger is enough. It is not. The trigger handles races, CHECK handles bad data. You need both.

Short Answer

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.

Finished reading?