What are ACID properties? | Interview Question

Q: What are ACID properties?

**Target Audience:** Beginners who have heard "ACID" and want to know what each letter promises in practice, and candidates preparing for the follow-ups ("which letter is weakest by default?", "what does durable actually cost?"). **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The concepts apply to every transactional database (MySQL InnoDB, SQL Server, Oracle); engine differences are called out where they matter. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First You hand a bank clerk a slip: "move 30 from Alice to Bob". The clerk has two ledgers. She crosses out 30 in Alice

The problem

Question presented to candidate: "People say a relational database is ACID. What do the four letters actually mean, and can you show me one concrete thing the database does for each of them — say, for a bank transfer between two accounts?"

What a strong answer should cover:

Atomicity: a transaction is all or nothing; if any step fails or the client calls ROLLBACK, every change it made is undone (implemented with an undo log or old row versions)
Consistency: a committed transaction moves the database from one valid state to another; the database enforces declared rules (CHECK, NOT NULL, foreign keys, unique keys) and the application owns the business invariants
Isolation: concurrent transactions do not see each other half-done; how strictly is the isolation level, and most engines default to something weaker than serializable
Durability: once COMMIT returns, the change survives a crash, because it was written to a write-ahead log (WAL) and flushed to disk first
Engine details that trip people up: in SQLite a failed statement does not abort the transaction (PostgreSQL does), and durability knobs such as synchronous_commit trade safety for speed

Code / implementation expected: Optional — a transfer wrapped in BEGIN ... COMMIT / ROLLBACK and a CHECK constraint is enough to illustrate the four properties.

Schema used by the examples:

transactions
acid

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have heard "ACID" and want to know what each letter promises in practice, and candidates preparing for the follow-ups ("which letter is weakest by default?", "what does durable actually cost?"). Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The concepts apply to every transactional database (MySQL InnoDB, SQL Server, Oracle); engine differences are called out where they matter.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

You hand a ba

The solution is hidden so you can try first

16 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLAtomicity: a transfer inside BEGIN, then ROLLBACK undoes both updates

SQLConsistency: a committed transfer keeps the total and logs the transfer

PostgreSQL: the rules the database itself enforces on accounts

SELECT conname AS rule, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'accounts'::regclass
ORDER BY conname;

PostgreSQL: the settings that make COMMIT durable on this server

SELECT name, setting
FROM pg_settings
WHERE name IN ('fsync', 'synchronous_commit', 'wal_level', 'full_page_writes')
ORDER BY name;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.