Explain ACID — and give a real example of a transaction that would break each property if violated

#ACID, one broken property at a time

ACID is Atomicity, Consistency, Isolation, and Durability — four guarantees a transactional database makes about every transaction. The fastest way to actually understand (and explain) each one is to describe the exact bug you'd get if the database silently dropped it, using the same running example: transferring $100 from account A to account B.

CREATE TABLE accounts (
    account_id    INTEGER PRIMARY KEY,
    balance_cents INTEGER NOT NULL CHECK (balance_cents >= 0)
);

#Atomicity — all steps happen, or none do

A transfer is really two writes: debit A, credit B. Atomicity guarantees the database treats them as one indivisible unit — either both happen or neither does, even if the process crashes between them.

BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 10000 WHERE account_id = 'A';
UPDATE accounts SET balance_cents = balance_cents + 10000 WHERE account_id = 'B';
COMMIT;

If atomicity were violated, the database (or a careless application) could execute the first UPDATE, crash before the second, and leave the money debited from A but never credited to B — $100 simply vanishes. This is exactly the bug you get from wrapping the two statements in separate auto-committed calls instead of one transaction: a crash between the two calls leaves the system in a state that was never a valid intermediate step of any correct transfer, because a correct transfer either doesn't happen or fully happens. Databases enforce atomicity with a write-ahead log (WAL): before changes are applied, the intended change is durably logged, so on crash recovery the database can either finish replaying a committed transaction or roll back an uncommitted one, but never leave it half-done.

#Consistency — every transaction leaves declared invariants intact

Consistency means a transaction can only move the database from one state that satisfies all declared rules (constraints, foreign keys, triggers) to another state that also satisfies them — it's the DBMS enforcing your invariants, not a guarantee that your business logic is bug-free.

-- The CHECK constraint above (balance_cents >= 0) is exactly this:
-- an invariant the database refuses to let any transaction violate.
BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 10000 WHERE account_id = 'A';
-- If A only had $50, this UPDATE now violates balance_cents >= 0
-- and the whole transaction is rejected — nothing commits.
COMMIT;

If consistency were violated, the database would let that transaction commit anyway, leaving account A with a negative balance — a state that should be structurally impossible given the constraint you declared. Note the distinction from atomicity: atomicity is about the transaction completing wholly or not at all; consistency is about the resulting state obeying the rules you told the database to enforce, whatever those rules are.

#Isolation — concurrent transactions don't corrupt each other's view

Isolation guarantees that concurrent transactions behave (to some configurable degree) as if they ran one after another, not interleaved in ways that produce impossible results.

-- Transaction 1                    Transaction 2
BEGIN;                              BEGIN;
SELECT balance_cents FROM accounts  SELECT balance_cents FROM accounts
  WHERE account_id = 'A';             WHERE account_id = 'A';
-- both read 10000 (i.e., $100.00)
-- T1 computes 10000 - 3000 = 7000  -- T2 computes 10000 - 5000 = 5000
UPDATE accounts SET balance_cents = 7000
  WHERE account_id = 'A';
COMMIT;
                                     UPDATE accounts SET balance_cents = 5000
                                       WHERE account_id = 'A';
                                     COMMIT;
-- Final balance: 5000 — T1's $30 withdrawal is silently lost.

This is the classic lost update anomaly: both transactions read the same starting balance, computed their own "correct" new balance in application code, and the second write clobbered the first — $30 vanished with no error raised anywhere. If isolation were fully absent, you'd also see dirty reads (reading another transaction's uncommitted changes) and other anomalies. The fix is either a stricter isolation level (SERIALIZABLE, which would force one of these transactions to fail and retry) or explicit locking:

BEGIN;
SELECT balance_cents FROM accounts WHERE account_id = 'A' FOR UPDATE;
-- FOR UPDATE locks this row; Transaction 2's SELECT ... FOR UPDATE
-- on the same row now blocks until Transaction 1 commits or rolls back.
UPDATE accounts SET balance_cents = balance_cents - 3000 WHERE account_id = 'A';
COMMIT;

#Durability — once committed, a crash can't undo it

Durability guarantees that once the database returns "commit successful" to the client, that transaction's effects survive any subsequent crash — power loss, process kill, disk controller reset.

If durability were violated — say, the database acknowledged the commit as soon as the write hit an in-memory buffer or OS page cache, without calling fsync to force it onto physical disk — then a crash a few seconds after the client received "success" could roll the database back to a point before the transfer, even though the client was told it succeeded and may have already told the customer their transfer went through. Databases enforce durability by forcing the write-ahead log record to persistent storage (via fsync or equivalent) before acknowledging commit, and production systems add synchronous replication to a second node so durability survives losing an entire machine, not just a process crash.

#Putting it together

#Quick check

🧠 Check your understanding
0/3 · 0/3 answered
1. Two concurrent transactions both read an account balance of $100, each independently subtract different amounts, and write back their result — with the second write overwriting the first's change entirely. Which ACID property was violated?
IsolationAtomicityDurabilityConsistency
2. A database acknowledges a commit to the client but had only written the change to an OS page cache, not fsynced to disk. The machine loses power a second later and the change is gone. Which property failed?
AtomicityDurabilityConsistencyIsolation
3. A CHECK constraint prevents an UPDATE from leaving an account balance negative, causing the whole transaction to roll back. Which property is being enforced?
ConsistencyDurabilityIsolationAtomicity