ACID Properties — SQL Tutorial with Examples

What You'll Learn

ACID guarantees reliable processing of database transactions. Atomicity: all operations succeed or all are rolled back — no partial state. Consistency: data moves from one valid state to another, all rules (constraints, triggers) always hold. Isolation: concurrent transactions execute as if sequential. Durability: once committed, data survives crashes (written to disk/WAL log).

Syntax

-- ACID is enforced by the database engine
-- Atomicity: BEGIN + COMMIT/ROLLBACK
-- Consistency: constraints, triggers, foreign keys
-- Isolation: transaction isolation levels
-- Durability: WAL (Write-Ahead Log), fsync

Example

-- Atomicity: debit AND credit must both succeed
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- only NOW are changes permanent

-- Consistency: foreign key constraint prevents orphans
INSERT INTO orders (customer_id, total)
VALUES (999, 50.00);
-- Fails if customer 999 doesn't exist in customers table

-- Isolation: READ COMMITTED default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Beyond the Basics

Data in play — reference tables for this topic

-- employees (from Topic 1): Edsger's NULL department_id is ALLOWED
--    by design — a NULLable column is a consistency decision (Topic 7)
--
-- customers (from Topic 6): ids 1-5
-- orders (from Topic 7):
-- id | customer_id | status    | total
-- 1  | 1           | shipped   | 240.00
-- 2  | 2           | shipped   | 89.90
-- 3  | 1           | pending   | 430.00
-- 4  | 3           | shipped   | 59.50
-- 5  | 4           | cancelled | 120.00
--
-- The FK on orders.customer_id is what makes the example INSERT
-- of customer 999 fail — Consistency, enforced by the engine

Atomicity: Topic 23's two-UPDATE rule, stated as a guarantee

Topic 23 showed the reassignment-plus-raise transaction. ACID is the promise that the engine keeps that guarantee under the worst conditions — power loss, crash, kill -9 — not just when your code paths behave. 'All or nothing' is not a library feature you call; it is the default behavior of a committed transaction.

BEGIN;
UPDATE employees SET department_id = 1    WHERE id = 4;
UPDATE employees SET salary   = 85800     WHERE id = 4;
COMMIT;   -- server crash a microsecond before this line?
          -- On restart: NEITHER change exists. Not one. That's Atomicity

Atomicity means the intermediate state never survives — not 'usually doesn't'. The engine's WAL/undo log exists precisely to keep that promise across crashes.

Consistency: constraints are promises the engine keeps for you

The example INSERT fails because orders.customer_id is a foreign key into customers (Topic 15) — customer 999 does not exist, and the engine refuses to create an orphan order. Consistency is not just FKs: every constraint from Topic 1 (PRIMARY KEY, NOT NULL, UNIQUE, CHECK) is a rule the data can never violate, no matter which application writes it.

INSERT INTO orders (customer_id, total)
VALUES (999, 50.00);
-- ERROR: foreign key violation — customers has no id 999

-- Consistency is also what Edsger's NULL means:
-- department_id is NULLable BY DECISION — 'no department yet' is a
-- valid state; 'customer 999' never is. Constraints encode the difference

Every rule you can express as a constraint is a bug class deleted forever — app code can forget; the engine cannot.

Isolation: what two sessions see while you work

Session A runs Topic 23's salary UPDATE but has not committed. Session B reads Edsger's row. Does B see 85800 (a dirty read), the old 78000, or block? The answer is the isolation level. READ COMMITTED (PostgreSQL, SQL Server default) shows only committed data; REPEATABLE READ (MySQL default) freezes B's snapshot for the whole transaction; SERIALIZABLE makes concurrent runs behave as if sequential.

-- Session A:
BEGIN;
UPDATE employees SET salary = 85800 WHERE id = 4;  -- not committed

-- Session B (READ COMMITTED):
SELECT salary FROM employees WHERE id = 4;   -- 78000 — old value
-- Session B (READ UNCOMMITTED):
SELECT salary FROM employees WHERE id = 4;   -- 85800 — dirty read!

-- The isolation level decides which answer B gets

Higher isolation = fewer anomalies (dirty/non-repeatable/phantom reads) = more locking or version retention. Know your engine's default and what it hides from you.

Common Mistakes

Assuming NoSQL databases are ACID compliant — many prioritize availability over strict consistency (CAP theorem). Also: ACID doesn't mean 'no bugs' — application logic bugs still cause inconsistency.

Interview Tips

This is a classic senior-level question. Give a real-world banking example for each property. Isolation levels are frequently asked: explain dirty reads, non-repeatable reads, and phantom reads at each level.

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

1. Atomicity means…?

2. INSERT INTO orders (customer_id, total) VALUES (999, 50.00) fails. Which ACID property is enforcing what?

3. Session A has an uncommitted salary UPDATE. Session B reads the row. Under READ COMMITTED, B sees…?

4. Which engine defaults to REPEATABLE READ?

5. Why is Edsger's NULL department_id NOT a Consistency violation?

6. Durability means…?

Practice

Run a consistency audit: return every account holder with their transaction count, using a LEFT JOIN so accounts with zero transactions still appear.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Do NoSQL databases break ACID?

Many relax Isolation and Consistency (in the CAP-theorem sense) to gain availability and partition tolerance across distributed nodes — though the landscape has matured: MongoDB gained multi-document transactions, and many 'NewSQL' stores are fully ACID across nodes. The interview answer is about trade-offs, not a yes/no.

What isolation level is the default in each major engine?

PostgreSQL, SQL Server, and Oracle: READ COMMITTED. MySQL/InnoDB: REPEATABLE READ. SQLite: SERIALIZABLE (single-writer, so it can afford it). Know your default — most concurrency bugs are assumptions about a level the engine does not actually provide.