Advanced System Design Fundamentals
Vote

0% completed

​

MVCC and Isolation Levels in Practice

  1. Readers Never Block Writers
  1. How MVCC Actually Works
  1. The Anomalies, Concretely
  1. Isolation Levels in Real Databases
  1. The Mechanisms Seniors Actually Use
  1. The Senior Decision

1. Readers Never Block Writers

Two transactions want the same row at the same moment, one reading it and one writing it. A database can make the reader wait for the writer, or it can keep more than one version of the row and hand the reader the version that was current when it started. The second approach is multi-version concurrency control (MVCC), and it is what every database in this lesson does.

The unit that makes it work is the snapshot: the state of the database as of one moment, which a transaction reads from for as long as it runs. A report that takes ten minutes sees the database as it was when the report began. It blocks no writer, and no writer blocks it.

That solves most of the problem and not all of it. Two transactions reading perfectly consistent snapshots can still both act on information that stops being true the instant the other commits. Isolation levels exist to name how much of that risk you are accepting, because removing all of it is expensive. This lesson is about choosing a level deliberately, and about the mechanisms you use instead when the level is not the right tool.

2. How MVCC Actually Works

The idea is shared between engines. The garbage collection is not, and that difference is what shows up in production incidents.

Postgres keeps every version in the table. Each row version carries two system columns: xmin, the transaction that created it, and xmax, the transaction that deleted or replaced it. A snapshot is basically the set of transactions still running when it was taken. A version is visible to that snapshot when its xmin committed before the snapshot was taken and its xmax has not committed in that snapshot.

An UPDATE therefore never modifies a row. It writes a new version and marks the old one dead. Dead versions accumulate until VACUUM reclaims them, which is the direct connection back to bloat: MVCC produces the garbage, and vacuum is the process that has to keep up with it.

Three versions of one row. Which one a transaction reads is decided entirely by comparing xmin and xmax against its snapshot.
Three versions of one row. Which one a transaction reads is decided entirely by comparing xmin and xmax against its snapshot.

One open transaction stops vacuum. Vacuum cannot remove a dead version that any running transaction might still need to see. A single transaction left open holds that cutoff in place for every table in the database, not only the tables that transaction touched.

This catches teams repeatedly. A forgotten analytics query holds a transaction open for six hours, and the busiest table in the system begins bloating with no change at all in application traffic: more disk, more I/O for the same rows, slower sequential scans. When the question is "why is Postgres a little slower every week," an open transaction blocking vacuum belongs near the top of the list of suspects.

Two settings prevent it:

  • Monitor pg_stat_activity for sessions with an old xact_start, especially ones sitting idle inside a transaction.
  • Set idle_in_transaction_session_timeout so the database ends those sessions without anyone being paged.
Six hours of ordinary traffic with one transaction left open. Dead versions accumulate and every vacuum run reclaims nothing.
Six hours of ordinary traffic with one transaction left open. Dead versions accumulate and every vacuum run reclaims nothing.

InnoDB keeps old versions outside the table. InnoDB stores only the current version in the clustered index and reconstructs older ones on demand from undo logs. A reader that needs an earlier snapshot walks the undo chain backward until it reaches a version its snapshot can see.

Collection is done by the purge thread, and it has the same weakness under a different name. A long-running transaction stops purge from advancing, undo history grows, and the tablespace grows with it. Same cause, different symptom.

3. The Anomalies, Concretely

Isolation levels are defined by which anomalies they permit, so learn each one as a specific sequence of events. That is also how interviewers ask about them.

  • Dirty read. A reads a row that B has written but not committed. B rolls back. A acted on a value that never existed.
  • Non-repeatable read. A reads a row, B updates it and commits, A reads it again and gets a different value. The row changed underneath a transaction that was still running.
  • Phantom read. A runs SELECT ... WHERE balance > 100, B inserts a matching row and commits, A runs the same query and finds an extra row. The set changed, not the row.
  • Lost update. A and B both read seats = 10, both calculate 9, both write 9. One booking disappeared.
  • Write skew. Two transactions each read a set of rows, each write a different row, and together they break a rule that neither broke alone.

Lost update needs a precise statement. The race is in the application, not in the SQL. A single statement that does its own arithmetic, UPDATE rooms SET seats = seats - 1 WHERE id = ?, never loses an update. The decrement is applied to the row rather than to a number your application was holding.

The engines get there by different routes, and the difference matters when you write the calling code. At read committed the second writer waits for the first to commit and then re-evaluates against the new row, so it simply succeeds. At repeatable read and serializable it is aborted with a serialization failure instead, so it succeeds only if you retry it.

The anomaly itself appears when the application reads a value, calculates the new one in its own process, and writes the result back. That read-modify-write cycle is where bookings disappear.

Write skew is the one that survives repeatable read. Two doctors are on call and the rule is that at least one must remain. A checks, sees that B is on call, and signs off. B checks, sees that A is on call, and signs off. Each transaction read a consistent snapshot. Neither overwrote the other's row. No value was lost. The rule broke anyway, because the two transactions wrote to different rows while reading an overlapping set.

That detail is the whole reason write skew is hard. A database can detect two writes to one row. It cannot detect two writes to two rows that were each correct against the data the writer read.

The structural difference: a lost update collides on one row, which the database can detect, while write skew spreads across two rows, which it cannot.
The structural difference: a lost update collides on one row, which the database can detect, while write skew spreads across two rows, which it cannot.

4. Isolation Levels in Real Databases

The SQL standard names four levels. The implementations differ from the standard and from each other, and those differences are interview material.

  • Read committed. Every statement gets a fresh snapshot. No dirty reads, while non-repeatable reads and phantoms are both possible. It is the Postgres default, and it is where the large majority of production systems run all the time.
  • Repeatable read. One snapshot for the whole transaction.
    • In Postgres this is true snapshot isolation. It prevents phantoms as well, which is stricter than the standard requires. Write skew remains possible.
    • In InnoDB it is the default. Consistent reads are served from the transaction's snapshot, while locking reads such as SELECT ... FOR UPDATE, UPDATE and DELETE take next-key locks on the gaps between index records, which is what blocks the inserts that would otherwise appear as phantoms. It is also why InnoDB deadlocks in patterns Postgres does not.
  • Serializable. The result is guaranteed to match some serial order of the transactions.
    • Postgres implements Serializable Snapshot Isolation. It tracks read and write dependencies between transactions and aborts the ones that form a dangerous pattern, which means your application must catch the serialization failure and retry. Code that does not retry is not really running at serializable.
    • MySQL's version is cruder: with autocommit off, plain SELECT statements become locking reads.
  • Read uncommitted. Postgres treats it as read committed. It exists for standard compliance rather than for use.
Only serializable stops write skew, and only serializable aborts your transactions to do it.
Only serializable stops write skew, and only serializable aborts your transactions to do it.

The cost sits at the top of that list. Serializable converts contention into aborts, and aborts arrive in bursts exactly when traffic peaks, which is when retrying everything is least affordable. Neither engine's serializable is a setting you would turn on globally and leave.

5. The Mechanisms Seniors Actually Use

Because stronger isolation is expensive, production systems usually stay at read committed and handle the few dangerous sections explicitly. These are the five tools, roughly in the order they turn out to be the right answer.

  1. SELECT ... FOR UPDATE takes a lock on the rows it reads and holds it until commit. This is the standard fix for a lost update on a single known row.
  2. SELECT ... FOR UPDATE SKIP LOCKED turns a table into a work queue. Each worker takes rows nobody else holds instead of waiting behind them.
  3. Optimistic locking adds a version column. Read the version, then write with WHERE id = ? AND version = ? and treat zero rows affected as a conflict to retry. No lock is held, which suits low contention well.
  4. Advisory locks such as pg_advisory_lock are application-level locks that are not attached to any row. The usual use is "only one of these jobs runs at a time."
  5. Unique constraints are the cheapest correctness guarantee available. One booking per seat, enforced by a unique index, holds whatever the isolation level is, because the database checks it atomically at insert time and no amount of concurrency gets past it.
The same race, fixed four ways. The cost line matters as much as the mechanism.
The same race, fixed four ways. The cost line matters as much as the mechanism.

The order of that list carries the lesson. The first four coordinate transactions with each other. The last one removes the need to coordinate at all, which is why it is worth checking first: when a rule can be stated as a constraint, state it as a constraint rather than defending it with a lock.

6. The Senior Decision

Work it as three questions, in order.

  • Which anomaly is actually possible here? Name it. "This is a lost update on one row" and "this is write skew across two rows" lead to completely different fixes.
  • What is the cheapest mechanism that prevents it? A unique constraint costs nothing at runtime. A row lock costs contention on one row. Serializable costs aborts across the entire workload.
  • What happens when the fix fires? A lock means waiting. Optimistic locking and serializable both mean retrying, and retry logic that does not exist is a defect that shows up at peak.

Keep the system at read committed, and spend the stronger mechanisms on the small number of places that genuinely need them: ledgers, inventory decrements, anything that moves money. Raising the global isolation level is almost never the correct response to one broken rule, because it prices the whole workload for a problem that exists in one transaction.

What the interviewer is scoring: "design a seat booking system" is an isolation question, and what is being graded is whether you name the anomaly before proposing anything. An answer that reaches a unique constraint scores higher than one that reaches serializable, because it shows you know what each fix costs. "Why is our Postgres getting slower every week?" is the vacuum question from section 2, and the expected answer names the open transaction rather than the query plan. Expect the follow-up "what happens when two of those transactions collide?" and have the retry path ready: candidates routinely propose serializable and then cannot say what their application does when it receives a serialization failure.

Flashcards Review

MVCC

1 / 22

Reading Progress

0%

Vote for new content

On This Page

  1. Readers Never Block Writers
  1. How MVCC Actually Works
  1. The Anomalies, Concretely
  1. Isolation Levels in Real Databases
  1. The Mechanisms Seniors Actually Use
  1. The Senior Decision