ACID properties in SQL — explained with examples

ACID = the four guarantees a relational DB makes about transactions. Atomicity, Consistency, Isolation, Durability.

A — Atomicity ("all or nothing")

Either every statement in a transaction succeeds, or none of them are applied.

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
-- If the second UPDATE fails, the first is rolled back
COMMIT;

Without atomicity, money could disappear into the gap between the two statements.

C — Consistency ("rules are enforced")

The database moves from one valid state to another. Constraints (FOREIGN KEY, CHECK, UNIQUE, NOT NULL) are honored.

ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;  -- balance now -100
-- ERROR: check constraint violated; whole txn rolled back

I — Isolation ("concurrent transactions don't see each other's mess")

Multiple transactions run concurrently as if they ran one after the other. SQL defines isolation levels that trade strictness for performance.

-- Strictest: SERIALIZABLE — transactions appear fully sequential
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts;
-- Another txn cannot insert/update accounts during this
COMMIT;

The four standard levels, weakest to strongest:

Level	Phenomena allowed
READ UNCOMMITTED	Dirty reads, non-repeatable reads, phantoms
READ COMMITTED (default in Postgres)	Non-repeatable reads, phantoms
REPEATABLE READ	Phantoms (Postgres prevents these too)
SERIALIZABLE	None

D — Durability ("committed means permanent")

Once a transaction commits, the changes survive a crash, restart, or power loss. Implemented via write-ahead logging (WAL) flushed to disk.

BEGIN;
INSERT INTO orders (...) VALUES (...);
COMMIT;
-- At this point even if the DB crashes, the order is on disk

Real-world example — money transfer

BEGIN;
  UPDATE accounts SET balance = balance - 5000 WHERE id = 1
    AND balance >= 5000   -- A: check + update atomically
  RETURNING id;
  -- If no rows returned, balance was insufficient — fail the txn
  UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
  INSERT INTO transfers (from_id, to_id, amount, at)
    VALUES (1, 2, 5000, NOW());
COMMIT;

Atomicity (both updates or neither), Consistency (no negative balances thanks to the conditional update), Isolation (no concurrent transfer sees the half-state), Durability (committed transfer survives a crash).

Cost — why not always use SERIALIZABLE?

Higher isolation = more locking or more retries. Most apps run at READ COMMITTED and only escalate specific high-stakes transactions (transfers, inventory holds) to SERIALIZABLE.