Interview prompt
Explain transactions, isolation, and mvcc snapshots to an engineer who understands the surrounding system but has not used this technique. Walk from its contract to a concrete operation, then discuss where it fails or becomes expensive.
A strong answer
A transaction groups operations under a database contract such as atomicity and consistency. Multi-version concurrency control keeps versions of rows so readers can observe a consistent snapshot while writers create new versions. The exact snapshot and conflict rules depend on the database and isolation level.
Under snapshot-based isolation, a transaction may read a row version that was committed when its snapshot began, even if another transaction later updates the row. Concurrent writes can cause one transaction to wait or fail. An application must treat serialization or deadlock failures as expected outcomes to handle safely.
A complete answer also calls out the assumptions that control correctness. Isolation levels are named standards but implementations differ in details and anomalies. A transaction can be atomic while still making a logically stale decision if its reads and writes do not protect the invariant. Long-running snapshots can also delay cleanup of obsolete versions.
Close by describing one representative test or measurement. Two transactions each check that fewer than five reservations exist, then insert one. Explain why transaction boundaries alone may not prevent exceeding five and name a database mechanism that can enforce the invariant.
Follow-up questions
Answer the follow-ups in the frontmatter. Use the linked article for the concept and the trace to make the explanation concrete.