Формат: markdown
Время создания: 13.11.2026, 08:02 UTC
Будет удалена: 13.11.2027, 08:02 UTC

RAW

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.

Упакуем пароль или код в cсылку для передачи