Database Transactions and Isolation Levels: Interview Questions with Real Anomalies

Two doctors are on call. Each checks that another doctor is still available, then independently goes off call. Both transactions commit. The database accepted every statement, yet the hospital now has nobody on call.

That is the kind of example that turns a database interview from vocabulary into engineering. Interviewers rarely care whether you can recite four isolation levels in order. They want to know whether you can trace two concurrent transactions, name the broken invariant, choose a control mechanism, and explain what the application does when a transaction must retry.

This guide teaches that reasoning through dirty reads, non-repeatable reads, phantoms, lost updates, and write skew. For hands-on preparation, use PracHub's database and system design interview questions to practice explaining the invariant before revealing a solution.

Quick answer: what should you say in an interview?

A transaction groups work into one logical unit, while isolation controls which concurrent effects that unit may observe. A strong answer does not begin with a memorized matrix. It begins with a business rule such as "one seat can be sold once" or "at least one doctor must remain on call."

Then describe a concrete interleaving. Show what T1 reads, what T2 reads, which rows each transaction writes, and why the final state cannot be produced by any correct serial execution. Only after the failure is visible should you choose a fix: an atomic conditional update, a row or range lock, optimistic versioning, a database constraint, or Serializable isolation with whole-transaction retries.

What database interviewers are actually testing

Isolation questions test whether you can protect invariants under concurrency. The SQL syntax is secondary. A candidate who says "use Serializable" without discussing retries is incomplete; a candidate who says "use a lock" without naming the rows or predicate being protected is equally vague.

Interviewers also listen for scope. A database transaction can atomically update rows in one database, but it does not make an HTTP call, Kafka publish, and payment-provider request one atomic action. Those workflows need idempotency, an outbox, state transitions, or reconciliation in addition to database isolation.

A senior-level answer should cover six elements in order: the invariant, the concurrent history, the anomaly, the control mechanism, the failure or retry path, and a test that proves the invariant still holds.

ACID without the acronym dump

Atomicity means all database changes in the transaction commit or none do. Consistency means a successful transaction preserves the rules the system is designed to enforce, including constraints and application invariants. Isolation limits interference between concurrent transactions. Durability means committed results survive the failures covered by the database's durability contract.

The subtle point is consistency: ACID does not invent your business rule. If "at least one doctor remains on call" is not encoded through the transaction, locking strategy, constraint, or serializable execution, the database may commit a state the application considers invalid.

Likewise, COMMIT does not guarantee that an external side effect happened exactly once. It only closes the database transaction. If the process commits and then crashes before responding, the client may retry. That is why an idempotency key and stored result often belong in the same transaction as the protected write.

Five anomalies you should be able to draw

1. Dirty read: acting on data that never committed

T1 changes an order from pending to paid but has not committed. T2 reads paid and releases inventory. T1 later rolls back because the payment failed. T2 acted on a state that never became durable.

Read Committed prevents this by hiding uncommitted changes. PostgreSQL goes further in a product-specific way: requesting Read Uncommitted behaves like Read Committed because of its MVCC implementation. That is a useful reminder that the SQL label is not a complete behavioral contract.

2. Non-repeatable read and phantom: the answer changes mid-transaction

For a non-repeatable read, T1 reads an account balance of $500. T2 commits a withdrawal, and T1 reads the same row again as $400. For a phantom, T1 runs SELECT COUNT(*) FROM bookings WHERE room_id = 7 AND status = 'active'; T2 inserts a matching booking; T1 repeats the query and sees a larger set.

Repeatable Read commonly gives a stable transaction snapshot for ordinary reads, but engine details matter. PostgreSQL's Repeatable Read prevents phantoms even though the SQL standard permits an implementation to allow them at that level. MySQL InnoDB uses a consistent snapshot for ordinary reads and can use next-key or gap locks for locking range operations.

3. Lost update: two correct calculations, one missing result

Suppose a counter is 10. T1 reads 10 and calculates 11. T2 also reads 10 and calculates 11. Both write 11, so one increment disappears. The final value is valid as a number but wrong as a history.

The cleanest fix is often not a stronger isolation level but a better write: UPDATE counters SET value = value + 1 WHERE id = ?. For more complex state transitions, use SELECT ... FOR UPDATE or optimistic concurrency such as UPDATE ... WHERE version = ?, then treat zero updated rows as a conflict rather than success.

4. Write skew: why a stable snapshot may still be unsafe

Alice and Bob are both on call. T1 reads that two doctors are available and sets Alice off call. At the same time, T2 reads the same snapshot and sets Bob off call. Because the transactions update different rows, a simple write-write conflict may never occur. Both commit, violating the shared invariant.

This is write skew. PostgreSQL documents that Repeatable Read, which uses snapshot-isolation behavior, can still permit serialization anomalies. Serializable can detect the dangerous dependency and abort one transaction, but the application must retry the whole unit of work. Alternatives include locking a shared schedule row or redesigning the invariant so a database constraint can enforce it.

5. Serialization failure: a correctness feature, not an outage

At Serializable, the database may reject a transaction whose commit would make the combined history impossible to explain as one-at-a-time execution. In PostgreSQL this commonly surfaces as SQLSTATE 40001. The correct response is to discard every decision made in the failed transaction and rerun it from the beginning with a bounded retry policy.

Do not retry only the final UPDATE. The earlier reads may have guided business decisions, so the complete transaction must receive a fresh view. Keep transactions short, avoid user interaction inside them, add jitter to retries under contention, and expose a clear error if the retry budget is exhausted.

Isolation levels: reason from guarantees, not names

Read Uncommitted may expose dirty data in products that implement it distinctly. It is rarely appropriate for correctness-sensitive application decisions. Read Committed hides dirty writes, but two statements in one transaction may observe different committed snapshots.

Repeatable Read or Snapshot Isolation generally gives stable reads, which is valuable for reports and multi-step reasoning. It does not automatically protect every cross-row invariant. Serializable offers the strongest abstraction: committed transactions have an effect equivalent to some serial order, usually at the cost of more conflicts, monitoring, blocking, or retries depending on the engine.

The interview-safe phrase is: "I will verify the target database's exact semantics." PostgreSQL and MySQL both expose familiar labels, but their snapshots, locking reads, gap protection, defaults, and failure modes differ. Production design should cite the engine and version rather than trusting a generic chart.

Choose the smallest mechanism that protects the invariant

Use an atomic conditional statement when one row can decide the operation: decrement inventory only where available > 0. Use a unique or exclusion constraint when invalid state can be represented as forbidden data, such as duplicate idempotency keys or overlapping reservations.

Use pessimistic locking when contention is expected and the rows to protect are known. Lock multiple rows in a deterministic order to reduce deadlocks. Use optimistic versioning when conflicts are uncommon and rejecting a stale writer is cheaper than blocking.

Use Serializable when correctness depends on a predicate or several rows and encoding that invariant directly is impractical. This is not permission to ignore transaction size or retry behavior. The choice is a trade-off among correctness scope, contention, latency, operational complexity, and how safely the application can retry.

A model answer: prevent overselling the last item

Start with the invariant: available_quantity >= 0, and no two successful orders may consume the same unit. The unsafe implementation reads the quantity, checks it in application code, then writes a new value. Two transactions can pass the check before either writes.

A compact safe path is one conditional update:

BEGIN;
UPDATE inventory
SET available_quantity = available_quantity - 1
WHERE sku = :sku AND available_quantity > 0;

-- Continue only when exactly one row was updated.
INSERT INTO orders (order_id, sku, status)
VALUES (:order_id, :sku, 'reserved');
COMMIT;

Store a unique idempotency key with the order so a client retry returns the original result. If the workflow reserves multiple SKUs, lock inventory rows in a stable order or use a serializable transaction and retry conflicts. If payment happens through an external provider, keep the slow network call outside the database transaction and model a hold or reservation state.

Finish by naming tests: launch many concurrent buyers against one unit, assert exactly one success, inject a crash after commit but before response, replay the same idempotency key, force a deadlock or serialization failure, and verify that retries never create a second order.

Practice with PracHub questions

Attempt each prompt by writing the invariant and a two-transaction history before choosing a lock or isolation level. The titles in the first column open the full question and written solution.

A seven-day preparation plan

Frequently asked questions

What isolation level should I choose in an interview?

Choose after stating the invariant and expected contention. Read Committed plus an atomic conditional update may be ideal for one-row inventory. A cross-row scheduling rule may need Serializable, a shared lock row, or a redesigned constraint.

Does Repeatable Read prevent lost updates?

Do not answer this without naming the database and write pattern. Some engines detect conflicting row updates, while a read-modify-write implemented carelessly can still lose data. Atomic updates, locking reads, or version predicates make the intent explicit.

What is the difference between phantom read and write skew?

A phantom is a changed result set when a predicate query is repeated. Write skew is a final-state anomaly: concurrent transactions read a shared condition, write different rows, and together violate an invariant. Stable snapshots can prevent the first visible symptom while still permitting the second.

Is MVCC an isolation level?

No. MVCC is an implementation family that keeps multiple row versions so reads and writes can overlap. The product still defines which snapshot each isolation level sees, how write conflicts are handled, and when transactions must abort.

Should every transaction run at Serializable?

Not automatically. Serializable can simplify correctness reasoning, but it may increase aborts, retries, or blocking. Use it where the invariant warrants the cost, and keep a consistent retry strategy ready.

Final takeaway

The best database transaction answer is a small correctness proof: name the invariant, show the unsafe interleaving, choose a mechanism that blocks or detects it, and explain retry and testing behavior. If you can do that for inventory, bookings, ledgers, and schedules, isolation-level questions stop being trivia.

Use PracHub to practice the full prompts under interview conditions, then compare your reasoning with written solutions. Focus on explaining why the final state is safe, not merely naming a lock.

Sources and Further Reading

• PostgreSQL: Transaction Isolation
• PostgreSQL: Concurrency Control
• PostgreSQL: Explicit Locking
• PostgreSQL: Serialization Failure Handling
• MySQL 8.4: InnoDB Transaction Isolation Levels

Research note: This guide was checked on August 22, 2026. Isolation semantics vary by database engine and version, so confirm behavior in the official documentation for your target system.