ACID Properties

ACID Properties

ACID is an acronym for the four properties that guarantee database transactions are processed reliably. Every serious database interview starts here.

A — Atomicity

A transaction is treated as a single unit. Either all operations succeed, or none of them are applied. If a failure occurs mid-transaction, the database rolls back to its previous state.

-- Transfer $100 from Alice to Bob
BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
  UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT; -- Both succeed, or neither applies

If the system crashes after the first UPDATE but before the second, the rollback ensures Alice's money is returned.

C — Consistency

A transaction moves the database from one valid state to another. All integrity constraints (foreign keys, unique constraints, check constraints) must hold before and after the transaction.

-- Consistency: balance cannot go negative
ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);

If Alice only has $50 and you try to debit $100, the constraint prevents the transaction from completing — consistency is maintained.

I — Isolation

Concurrently executing transactions behave as if they were executed serially. The intermediate state of a transaction is invisible to others.

Isolation levels (weakest to strongest):

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
  SELECT balance FROM accounts WHERE name = 'Alice'; -- reads 500
  -- Another transaction cannot change Alice's balance until this commits
  SELECT balance FROM accounts WHERE name = 'Alice'; -- still reads 500
COMMIT;

D — Durability

Once a transaction is committed, it persists permanently — even if the system crashes immediately after. Databases achieve this through write-ahead logging (WAL): changes are written to a durable log before being applied to data files.

How Databases Implement ACID

• Atomicity → undo logs / rollback segments
• Consistency → constraint checks, triggers
• Isolation → locks, MVCC (Multi-Version Concurrency Control)
• Durability → write-ahead log (WAL), checkpointing

MVCC — How Modern Databases Achieve Isolation Without Blocking

PostgreSQL, MySQL InnoDB, and MongoDB all use MVCC. Instead of locking rows for reads, they keep multiple versions of each row. Readers see a consistent snapshot; writers create new versions.

Transaction T1 (reads at time=10):         Transaction T2 (writes at time=12):
SELECT balance → sees version@t=10 (500)   UPDATE balance SET balance=400
                                            → creates new version@t=12 (400)
SELECT balance → still sees @t=10 (500)    COMMIT
                ← T1 is unaffected!
COMMIT

BASE — the NoSQL-leaning counterpart to ACID

Where ACID prioritizes strict consistency, BASE (Basically Available, Soft state, Eventually consistent) is the tradeoff many NoSQL/distributed systems make deliberately: Basically Available — the system responds to every request, even under partial failure, rather than blocking for strict correctness. Soft state — data may change over time even without new input, as replicas converge. Eventually consistent — given enough time without new writes, all replicas converge to the same value, but at any given instant, different replicas might disagree.

This isn't a lesser version of ACID — it's a different point on the same consistency-availability spectrum (see CAP theorem in HLD Fundamentals Refresher), chosen deliberately when horizontal scale and availability under partition matter more than every read reflecting the absolute latest write. A social media like-count that's occasionally a few seconds stale is a reasonable BASE tradeoff; a bank balance almost never is — which is exactly why the right choice depends on the actual business invariant being protected, not a blanket preference for one model.

Interview Tips

1. Don't confuse Consistency here with CAP Consistency — CAP's C is about distributed systems agreement; ACID's C is about data validity constraints.
2. Isolation is the most nuanced — be ready to explain the four levels and the anomalies each prevents.
3. Durability trade-off — fsync=off in PostgreSQL is faster but violates durability. Mention this trade-off.
4. Classic question: "What happens if the database crashes after COMMIT but before writing to disk?" Answer: WAL ensures the committed data is recoverable.