SQL Interview Question: What Are ACID Properties?

Short answer: ACID describes the guarantees a database transaction gives: Atomicity, Consistency, Isolation and Durability.

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

Atomicity: all or nothing

If the second update fails (or the server crashes in between), the first is rolled back. Money never disappears halfway. Implemented with undo/redo logs.

Consistency: rules always hold

A transaction moves the database from one valid state to another: constraints (CHECK (balance >= 0), foreign keys, unique keys) are enforced, or the transaction fails.

Isolation: concurrent transactions don't interfere

Two transfers running at the same time behave as if they ran in some order. How strictly depends on the isolation level:

• Read Committed - no dirty reads (you never see uncommitted data).
• Repeatable Read - rows you read don't change under you within the transaction.
• Serializable - full isolation, as if transactions ran one at a time.

Implemented with locks and/or multi-version concurrency control (MVCC), where readers see a snapshot and don't block writers.

Durability: committed means saved

Once COMMIT returns, the change survives crashes and power loss. Implemented with a write-ahead log flushed to disk before the commit is acknowledged; replicas add protection against losing a machine.

Bonus: ACID vs BASE

Some distributed NoSQL systems favour availability with "Basically Available, Soft state, Eventually consistent" (BASE). Many modern databases, including MongoDB and distributed SQL systems, now offer ACID transactions too, so the line has blurred.