SQL Interview Prep · Lesson

ACID Properties Explained

Atomicity, consistency, isolation, and durability with concrete examples.

ACID Properties Explained is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Interviewers Ask About ACID

When a senior interviewer says "walk me through ACID", they are not testing whether you memorized four words. They want to know if you understand what a database guarantees when many users hit it at once.

ACID stands for Atomicity, Consistency, Isolation, and Durability. Every transactional database (Postgres, MySQL/InnoDB, SQL Server, Oracle) promises these four properties for work wrapped in a transaction.

The strongest answers tie each letter to a concrete failure it prevents. Over the next scenes we will do exactly that.

What Is a Transaction?

A transaction is a unit of work that the database treats as a single, indivisible step. You open it with BEGIN, do one or more statements, then either COMMIT to make it permanent or ROLLBACK to discard everything.

The classic example is a bank transfer: debit one account, credit another. Both must happen or neither. A transaction is the wrapper that lets you make that promise.

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

A = Atomicity

Atomicity means a transaction is all-or-nothing. If any statement fails, or the server crashes mid-way, every change in that transaction is rolled back as if it never started.

In the transfer example, if the credit fails after the debit succeeds, atomicity guarantees the debit is undone too. You never lose money to a half-finished operation.

Interview phrasing: "Atomicity ensures the transaction commits as a whole or not at all, leaving no partial state."

Atomicity in Action

Here the second update violates a check constraint (balance cannot go negative). The database aborts the transaction, and the ROLLBACK undoes the first update too.

The key point for interviews: after the rollback, account 1 still has its original balance. There is no partial write sitting in the table.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- next line fails: balance would go below 0
UPDATE accounts SET balance = balance - 999999 WHERE id = 2;
ROLLBACK;  -- account 1 is restored to its original balance

C = Consistency

Consistency means a transaction moves the database from one valid state to another valid state. Every committed transaction must respect all defined rules: constraints, foreign keys, triggers, and cascades.

If a transaction would leave a constraint violated (an orphaned foreign key, a duplicate primary key, a failed CHECK), the database refuses to commit it.

Nuance to mention: the database enforces declared rules, but application-level invariants ("a user can have at most 3 active sessions") are your job unless modeled as constraints.

Consistency Enforced by Constraints

Consistency is mostly the sum of your schema rules. Below, a foreign key guarantees you can never insert an order for a customer that does not exist.

If the insert references a missing customer, the transaction fails, atomicity rolls it back, and consistency is preserved. This is why interviewers say atomicity and consistency work together.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  amount NUMERIC(10,2) CHECK (amount > 0)
);

-- This fails if customer 999 does not exist, keeping data consistent
INSERT INTO orders (id, customer_id, amount) VALUES (1, 999, 50.00);

I = Isolation

Isolation means concurrent transactions do not step on each other. Ideally, each transaction behaves as if it ran alone, even when dozens run at the same time.

In practice databases offer different isolation levels that trade strictness for performance. Weaker levels allow certain anomalies (dirty reads, phantoms) in exchange for higher concurrency.

This is the richest part of any ACID interview, so the next two lessons cover isolation levels and read anomalies in depth.

Why Isolation Is a Spectrum

Perfect isolation (SERIALIZABLE) is expensive because it must prevent every possible interleaving conflict, often by locking or aborting transactions.

So the SQL standard defines four levels. A common interview trap: candidates assume isolation is binary. It is not. You explicitly choose how much you want.

READ UNCOMMITTED weakest
READ COMMITTED
REPEATABLE READ
SERIALIZABLE strongest

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts;
-- ... business logic ...
COMMIT;

D = Durability

Durability means once a transaction commits, its changes survive crashes, power loss, and restarts. A committed transaction is on stable storage.

Databases achieve this with a write-ahead log (WAL): changes are written to a durable log before the data files are updated. On restart, the engine replays the log to recover any committed work that had not yet hit the main files.

Interview phrasing: "Durability is guaranteed by the WAL; commit returns only after the log record is flushed to disk."

Putting the Bank Transfer Together

One transfer exercises all four properties at once:

Atomicity: both updates apply or neither does.
Consistency: a CHECK keeps balances non-negative.
Isolation: a concurrent reader does not see a half-done transfer.
Durability: after COMMIT, the new balances survive a crash.

Being able to map a single example to all four letters is exactly what scores points in the interview.

BEGIN;
UPDATE accounts SET balance = balance - 100
  WHERE id = 1 AND balance >= 100;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Common Follow-Up Questions

Interviewers often probe the edges. Be ready for:

"Which property does a foreign key enforce?" Consistency.
"What protects you from a server crash mid-transaction?" Atomicity (rollback of uncommitted) plus durability (replay of committed).
"Is isolation free?" No, stronger isolation costs concurrency.
"Do NoSQL stores give ACID?" Many trade it for availability (BASE); say it depends on the engine.

Quick Check

Test your grasp of which ACID property does what.

Recap: ACID in One Breath

You can now answer the question cleanly:

Atomicity - all-or-nothing; partial work is rolled back.
Consistency - every commit respects constraints and leaves a valid state.
Isolation - concurrent transactions do not corrupt each other; strictness is tunable.
Durability - committed data survives crashes, backed by the write-ahead log.

Anchor each letter to a concrete failure it prevents and you will sail through the opening of any concurrency interview.

Frequently asked questions

Is the “ACID Properties Explained” lesson free?

Yes — the full text of “ACID Properties Explained” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “ACID Properties Explained”?

Atomicity, consistency, isolation, and durability with concrete examples. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “ACID Properties Explained” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep