Loading…
Loading…
SQL tutorial · Advanced
⏱️ 12 mins read
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).
-- 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-- 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;| 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
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 AtomicityThe 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 differenceSession 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 getsAssuming 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.
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.
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…?
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 →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.
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.