Transactions & Isolation interview questions

ACID guarantees, isolation levels, the anomalies each permits, and locking versus MVCC.

4 questions in Databases & SQL.

Databases & SQLA 22-minute read on how the topic fits together, before the questions.Database fundamentals58 short answers on one page, for revising rather than studying.

hard4

hardConceptScenario

Two doctors can go off call only if at least one remains. Both check the rule at the same moment, both see two doctors on call, and both go off call. What isolation problem is this, and what actually fixes it?

This is write skew: two transactions read the same set, each writes a different row, and the pair commits a state neither would have produced alone. No row-level constraint can catch it because the invariant is a property of the set, so the fix is either serializable isolation, a lock on something both transactions must touch, or remodelling the invariant onto a single row.

6 minmid, senior, staff

hardScenarioConcept

You are getting a handful of deadlock errors an hour under load. How do you find the cause, and how do you stop them?

A deadlock is a cycle in the wait-for graph, so read the engine's own report to get both statements and the locks each held. Nearly all of them come from two code paths taking the same rows in different orders; impose one order, shorten transactions, and retry, because a deadlock is always safe to retry.

7 minmid, senior, staff

hardConceptScenario

Walk me through the transaction isolation levels and which anomalies each one permits.

Each level is defined by the anomalies it allows: READ COMMITTED permits non-repeatable reads and phantoms, PostgreSQL's REPEATABLE READ is snapshot isolation so it prevents both yet still permits write skew, and only SERIALIZABLE rules out all four.

6 minmid, senior, staff

hardScenarioDesign

A user submits the form twice and you get two identical orders. Why did the check-then-insert not prevent it, and how do you fix it in the database?

Two transactions can each run the SELECT before either commits, so neither sees the other and both inserts succeed. The fix is a serialisation point the database owns - a unique index on a natural or client-supplied key - plus a defined answer for whoever loses the race. It also connects idempotency to the point an interviewer is testing.

6 minmid, senior, staff