ACID and isolation levels in the SA interview

Why ACID shows up in SA interviews

A systems analyst writes the spec that tells engineers what the system must guarantee when two users hit the same row at once. Two cashiers debit the same account for $1,000 while it holds $1,500 — what should happen? "Debit twice and go negative" is a spec bug, not an implementation bug. The interviewer will ask which isolation level you pick and why; "the default" is the wrong answer.

The real pain without ACID literacy is sloppy phrasing in tickets. An analyst writes "on payment, debit the wallet and create an order" as one sentence. The engineer translates it into two separate statements, a network blip lands between them, money leaves the wallet but no order row exists. Two months later support finds fifty silent ghost charges, and the post-mortem ends on the line "the spec did not require atomicity."

ACID is the contract that classical relational databases offer. Isolation levels are the trade-off dial between correctness and throughput. The SA's job is to specify which guarantee each operation needs.

ACID: the four properties

Atomicity. A transaction runs in full or not at all. If something fails halfway, the database rolls back every change inside the transaction boundary.

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

If the connection drops after the first UPDATE, both writes are reverted. The account is never debited without the matching credit.

Consistency. A transaction moves the database from one valid state to another. Every CHECK, FOREIGN KEY, and UNIQUE constraint holds at commit time. If you declared CHECK (balance >= 0), no transaction can leave the balance negative — the database rejects the commit.

Isolation. Concurrent transactions do not corrupt each other; the result is as if they ran one after another. In theory. In practice you pick an isolation level that trades some of that guarantee for throughput.

Durability. Once a transaction commits, the data survives a power cut. This is implemented through a Write-Ahead Log: changes are appended to the log first, then applied to data files, so a crashed process can be replayed from the journal on startup.

Not ACID: an in-memory dictionary with no transaction wrapper, a Redis cache without AOF, a filesystem write without fsync. If a spec stores money in any of those without a durable backing store, that is a reportable defect.

Anomalies under concurrency

When isolation is too loose, four classical anomalies appear. These are exactly the names interviewers expect you to use.

Dirty read. Transaction T1 reads a value that T2 has written but not committed. If T2 rolls back, T1 has already acted on a value that never existed.

T1: SELECT balance FROM accounts WHERE id=1; -- sees 500 (uncommitted T2)
T2: UPDATE accounts SET balance=500 WHERE id=1; -- (ROLLBACK)
T1: -- proceeds on 500, but the real value was 1000 all along

Non-repeatable read. T1 reads the same row twice; between reads T2 commits an UPDATE and T1 now sees a different value for the same primary key inside one logical operation.

T1: SELECT balance FROM accounts WHERE id=1; -- 1000
T2: UPDATE accounts SET balance=500 WHERE id=1; COMMIT;
T1: SELECT balance FROM accounts WHERE id=1; -- now 500

Phantom read. T1 reads a range by a predicate, T2 inserts a new row that matches that predicate, T1 re-reads and sees rows that "appeared from nowhere."

T1: SELECT count(*) FROM orders WHERE status='new'; -- 5
T2: INSERT INTO orders (status) VALUES ('new'); COMMIT;
T1: SELECT count(*) FROM orders WHERE status='new'; -- 6

Lost update. Two transactions read a value, both increment it, both write back — one increment vanishes.

T1: SELECT balance FROM accounts WHERE id=1; -- 1000
T2: SELECT balance FROM accounts WHERE id=1; -- 1000
T1: UPDATE accounts SET balance = 1000 + 100; COMMIT; -- 1100
T2: UPDATE accounts SET balance = 1000 + 200; COMMIT; -- 1200
-- expected 1300, actual 1200, one increment lost

SQL isolation levels

The SQL standard names four levels, ordered weakest to strongest. The table is the single artifact every SA should be able to redraw on a whiteboard.

In Postgres, Repeatable Read already blocks phantom reads thanks to MVCC; in the strict ANSI definition it does not.

Defaults vary by engine, and interviewers ask about this on purpose:

Setting it explicitly inside a session:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- statements
COMMIT;

When to reach for each level: Read Uncommitted is never the right answer for write paths and is acceptable only for throwaway read-only analytics. Read Committed is the workhorse default for OLTP — profiles, content, dashboards. Repeatable Read belongs on reports that must see one consistent picture across many statements. Serializable is for money, inventory holds, and seat reservations. You pay for it in retries and lock contention, so apply it surgically.

MVCC and snapshot isolation

MVCC (Multi-Version Concurrency Control) is the technique most modern databases use to keep readers and writers from blocking each other. The database stores multiple versions of each row; a reader sees the version visible to its snapshot, and writers create new versions instead of overwriting.

In Postgres, every row carries hidden columns xmin (the transaction that created it) and xmax (the transaction that deleted it). A transaction sees a row when xmin <= my_snapshot_id and xmax > my_snapshot_id (with extra rules for in-progress writers).

Snapshot Isolation is isolation built on MVCC. Every read inside the transaction returns data as of the moment the transaction (or its first statement) started. Writes are checked for conflicts at commit time; conflicting writers lose.

Write skew is the anomaly that pure snapshot isolation does not catch. Two transactions read the same data, then each updates a different row based on what they read. Individually both look correct; together they violate an invariant the system was supposed to hold.

Rule: at least one doctor must remain on call.
T1: reads on-call count >= 2 (Anna, Boris). Removes Anna.
T2: reads on-call count >= 2 (Anna, Boris). Removes Boris.
Both commit. No one is on call. Invariant broken.

Postgres SERIALIZABLE is implemented as SSI (Serializable Snapshot Isolation), which detects these read-write dependency cycles and aborts one of the offending transactions with a serialization_failure. Your application is expected to catch that error and retry. If the spec uses Serializable, the spec must also describe the retry policy — otherwise the engineer will silently swallow the failure.

BASE and eventual consistency

In distributed systems, strict ACID is often replaced with BASE — Basically Available, Soft state, Eventual consistency. The acronym is deliberately the chemical opposite of ACID, and it signals a different posture toward correctness.

The CAP theorem says that in the presence of a network partition, a distributed database can guarantee only one of Consistency or Availability. You always get partition tolerance because partitions happen whether you want them or not; the choice is what you sacrifice when one occurs.

• CP systems give up availability under partition. Synchronous Postgres replication with quorum, MongoDB with writeConcern: majority, etcd, ZooKeeper.
• AP systems give up strong consistency under partition. Cassandra at low consistency levels, DynamoDB with eventually-consistent reads, most CDNs.
• CA is only possible on a single node, which is by definition not a distributed system.

Eventual consistency means that once writes stop, every replica will converge to the same state in finite time. While writes are still flowing, readers may see stale or out-of-order data. This is fine for social feeds, product catalogs, DNS, and object storage. It is not fine for balances, holds, tickets sold against a fixed capacity, or anything where two clients converging on the same final state is not enough.

Common pitfalls

The trap that ends careers fastest is not naming the isolation level in the ticket at all. The engineer takes the engine default, it does not match the business rule, and the bug surfaces only under load. Always specify the level and the conflict policy in the acceptance criteria, even when the answer is "Read Committed."

A close second is applying Serializable everywhere as a safety blanket. Under write-heavy load, Serializable in Postgres produces a stream of serialization_failure retries, and throughput collapses. Pick Serializable for the specific path that needs it — the wallet debit, the seat hold — and let the rest run at Read Committed.

Ignoring lost updates is the bug that hides in almost every read-modify-write feature shipped without review. A user clicks "like" twice in 200 ms, both requests read the count, both write count + 1, one vanishes. The fix is either pessimistic locking with SELECT ... FOR UPDATE, an optimistic version column with WHERE version = N, or an atomic UPDATE counters SET value = value + 1. The spec should pick one and say so.

Assuming "this will never happen at the same time" always loses to production. "Never" means once a month, on the worst possible row, after the customer has already complained.

Confusing ACID with security. ACID is about correctness of concurrent execution. It says nothing about authorization, encryption, or SQL injection — those belong in a different section of the spec, and conflating them on a whiteboard loses an interviewer's confidence fast.

Treating BASE as "no guarantees." Cassandra with LOCAL_QUORUM reads and writes gives strong consistency per key; DynamoDB with conditional writes gives compare-and-swap. BASE is a menu of weaker guarantees, and a good SA picks the right item from that menu rather than the cheapest by default.

Related reading

• Case interview for systems analyst
• Acceptance criteria with Given-When-Then for SA
• Kafka for systems analyst interviews
• SQL injection and XSS for systems analyst interviews
• Systems analyst resume guide

If you want to drill SA scenarios like wallet debits, seat holds, and idempotent payments with real interview-grade questions, NAILDD has a growing bank of systems-analyst problems built around exactly these failure modes.

FAQ

Which isolation level should I pick for payments?

At minimum Repeatable Read, ideally Serializable for the path that moves money. A pragmatic alternative is Read Committed plus SELECT ... FOR UPDATE on the rows you are mutating — a targeted pessimistic lock that avoids paying Serializable's overhead across the whole transaction. Whichever you choose, the spec must also describe the retry policy when a serialization failure or lock timeout occurs.

How is Serializable different from Repeatable Read in Postgres?

Repeatable Read in Postgres is snapshot isolation: no dirty or non-repeatable reads, no phantoms in practice, but it still permits write skew. Serializable is Serializable Snapshot Isolation (SSI), which tracks read-write dependencies between concurrent transactions and aborts one of them with a serialization_failure when it detects a cycle. Your application catches that error and retries — that retry loop is a load-bearing piece of the design.

What is a deadlock and how do I handle it in the spec?

Two transactions each hold a lock the other is waiting on. The database detects the cycle and kills one with a deadlock error. You cannot eliminate deadlocks under fine-grained locking, but you can make them rare by always acquiring locks in a consistent order — for example, always lock the lower account ID before the higher one in a transfer. The spec should state the lock ordering rule and the retry policy.

Does MongoDB support ACID?

Since version 4.0, MongoDB supports multi-document ACID transactions across replica sets, and since 4.2 across sharded clusters. Before 4.0, atomicity was limited to a single document. The question behind the question is usually "when ACID, when BASE" — business-critical state goes to an ACID engine, while social-graph, catalog, and event data can live in a BASE store.

Is the isolation level set in the application or the database?

The database enforces it; the application requests it. The client opens a transaction and issues SET TRANSACTION ISOLATION LEVEL ... (or sets it through the driver), and the database guarantees the behavior. From the SA's point of view, the spec names the level, the engineer wires it through the ORM, and the database does the work.

Is this an official spec?

No. This is a practitioner summary based on SQL:1992 and the docs of Postgres, MySQL, and Oracle. Real engine behavior often diverges from the strict standard — Postgres Repeatable Read is stricter than ANSI. Confirm against engine docs before locking the spec.