# MVCC and Snapshot Isolation: What Interviewers Want You to Know Most modern databases (PostgreSQL, MySQL InnoDB, Oracle, CockroachDB) let readers and writers work at the same time without blocking each other. The mechanism is multi-version concurrency control (MVCC). ## How it works Instead of overwriting a row, an update creates a **new version** tagged with the ID of the transaction that wrote it. Old versions stay around for a while. Each transaction gets a **snapshot**: a record of which transactions had committed when it started. When it reads a row, it picks the newest version whose writer is visible in its snapshot and ignores versions written later or by uncommitted transactions. Result: readers never wait for writers, and writers never wait for readers. Writers still conflict with other writers on the same row. ## Snapshot isolation and its anomaly Under snapshot isolation, if two transactions update the same row, the second to commit fails ("first committer wins") and must retry. That prevents lost updates. It does not prevent **write skew**: two transactions read overlapping data, each updates a different row, and together they break a rule. Classic example: two doctors on call both check "is someone else on call?", see yes, and both go off call. Fixes: `SELECT ... FOR UPDATE` on the rows you rely on, a constraint, or **serializable** isolation (PostgreSQL's SSI detects these patterns and aborts one transaction). ## The cleanup cost Old versions must be removed once no snapshot can see them: PostgreSQL's **VACUUM**, InnoDB's purge of undo logs. Long-running transactions hold back cleanup, causing table bloat and slower queries. A good operational point to mention. ## Interview uses - Explaining why analytics queries don't block checkout writes. - Choosing an isolation level for a booking or payment flow. - Explaining time-travel queries (`AS OF SYSTEM TIME`) in some databases. Disclosure: I work on Linkjob AI (https://www.linkjob.ai/), an AI interview assistant.