A Straightforward Guide for MVCC in MySQL

In this article, I'll walk through how InnoDB (MySQL's default storage engine) works across different isolation levels. I'm assuming you already have a basic understanding of multi-version concurrency control, isolation levels, and InnoDB locks — I won't explain those concepts here.

• If you're not familiar with isolation levels -> I highly recommend reading this article first (you can just focus on the high-level idea of isolation levels and the different anomalies)
• If you're not familiar with InnoDB locks -> I highly recommend reading this article to understand the different lock types in InnoDB, focusing especially on next-key locks, since that's the one that matters here
• If you're not familiar with multi-version concurrency control -> I highly recommend reading this article to understand what MVCC is

Note that this article won't go into detail on isolation levels, locks or MVCC themselves, since those are already covered in depth in the articles above. Instead, the goal here is to help you understand how InnoDB actually works across different isolation levels.

 

How does InnoDB implement MVCC

Recall from the Postgres article (LINK) that Postgres fully applies MVCC across all isolation levels, for both reads and updates, with only minor differences between levels. InnoDB, on the other hand, only adopts half of that: it applies MVCC to the read logic, but uses a different strategy entirely for updates. We'll dig into the details later — for now, just know that InnoDB doesn't fully rely on MVCC. It's more of a hybrid approach, and that's one of the core differences between Postgres and MySQL.

The other key difference is in how each stores data: Postgres stores the full row in every version, while InnoDB only stores the delta (the difference) in each version.

As the diagram above shows, every version except the latest one only stores the difference, not the full record. If a transaction wants to read the V2 version, it starts with the latest version to get the full record, then follows the pointer to V2 and applies the difference. In this case, that just means changing like_count to 7 to get the correct full record for V2.

More specifically, InnoDB stores these versions in a dedicated, separate structure called the undo log — unlike Postgres, which stores every version in the same structure (the heap). This is part of what makes InnoDB's cleanup process simpler.

 

Repeatable Read (Default)

Repeatable Read is InnoDB's default isolation level, and it's a good place to start, since the other levels are all quite similar to it, just with a few differences.

For the read logic, InnoDB works exactly the same as the base MVCC read logic. It builds the MID_FLIGHT_TRANSACTION_LIST at the first SQL statement, and that list is frozen for the rest of the transaction's lifetime — it never gets mutated. When reading a tuple, InnoDB uses the exact same visibility rule to determine which version is visible.

The update logic is where things get interesting. InnoDB doesn't use the MVCC update concept at all — instead, it relies purely on locking. And this isn't limited to UPDATE/DELETE/INSERT: SELECT ... FOR UPDATE and SELECT ... FOR SHARE follow the exact same logic below, even though they're technically SELECT statements.

When a transaction attempts to update a row, it works like this:

1. Acquire the lock (this might be a record lock, an exclusive lock, or a next-key lock)
2. Fetch the latest row version (there's no walking through the version chain, and no visibility check).
3. Create an undo_log record that only stores the difference.
4. Update the record directly and mark it as the latest version.

There are two key things that make this different from Postgres's update logic:

• There's no walking through the version chain. Once a transaction successfully acquires the lock, it works directly on the latest version. InnoDB doesn't need to walk the version chain because of how exclusive locks work — only one transaction can hold an exclusive lock on a row at any given time, and a transaction only releases its lock once it commits or aborts. So once a transaction acquires the lock, it's guaranteed that row is the latest committed version.
• Depending on the WHERE clause's uniqueness, the transaction might acquire a different type of lock — a record lock, or a next-key lock.

Let's look at an interesting example.

In the diagram above, row ID 1 has two versions already created and committed by other transactions (the first by TrxID=2, the second by TrxID=3).

Let's focus on transaction B's lifecycle:

1. At T=12, transaction B starts, gets TrxID=4, reads for the first time, and builds its list as {}.
2. At T=30, transaction B reads again. It reuses the same list and walks the version chain:
   • V3, TrxID=5: not created by me, not in the list, and my TrxID (4) is smaller than it — I can't read it.
   • V2, TrxID=3: not created by me, not in the list, and my TrxID (4) is larger than it — I can read it.
3. At T=35, transaction B updates row ID 1. It successfully acquires the lock and works directly on the V3 version.

Transaction B is following the exact read and update logic we just described, but something's off: at T=30 and T=35, it's working with two different versions. At T=30 it read the value 15, but at T=35 it's updating the value 20. This is an interesting anomaly that's actually called out in the official documentation — we'll see how InnoDB addresses it at the Serializable level.

 

Serializable Level

Now let's move up a level from Repeatable Read. There are two major differences:

1. Every read request now acquires a shared lock (S) — though shared locks don't block each other.
2. The read logic now works exactly like the update logic, with no MVCC involved at all (no walking the version chain):
   1. Acquire a shared lock.
   2. Fetch the latest row version.

The first difference is what prevents the lost-update problem, and the second is what prevents the issue we just saw above.

Let's revisit the same example, but under Serializable's mechanism.

Let's focus on T=30, since that's the only thing that differs from the example above. At T=30, when transaction B reads row ID 1, it doesn't use a list at all (it never builds one in the first place), and there's no walking the version chain. All it does is acquire the shared lock, then read the latest version.

Now both T=30 and T=35 work on the same version.

The reason this solves the earlier problem comes back to the same idea we discussed above — the nature of shared and exclusive locks, and the fact that the two are mutually exclusive. Once a transaction successfully acquires a shared lock, that guarantees no other transaction can be holding an exclusive lock on that row, which means the latest version is always the committed one.

 

Read Committed

Now let's go down a level from Repeatable Read. There are quite a few interesting differences here.

For read logic, the only difference is that the list is rebuilt fresh for every read statement — which works very similarly to Postgres's logic.

For update logic, there are two differences:

1. There are no gap locks at this level — only plain record locks. This is the core reason phantom reads occur at this level.
2. Semi-consistent read for UPDATE — a behavior specific to Read Committed that changes how UPDATE decides whether a row matches the WHERE clause when it runs into a lock conflict.
   • The core idea is: when there's a lock conflict, instead of waiting for the lock to release, InnoDB first checks the current latest committed value. If it matches the WHERE clause, it waits for the lock to release as usual. If it doesn't match, it skips that row immediately.
   • The reason for this is simple — if we waited for the lock and then found out the row doesn't even match the WHERE clause, we'd have wasted that time for nothing.

Let's look at an example.

• At T=20, transaction A runs an update query and successfully acquires the exclusive lock on row ID 2. It's still running and hasn't committed yet.
• At T=30, transaction B runs an update query that scans two rows:
  • Row ID 1 has no lock, so B successfully acquires the exclusive lock on it.
  • Row ID 2 already has an exclusive lock held by transaction A. Instead of blocking immediately, InnoDB first reads the row's latest committed version — not A's in-progress, uncommitted change, but whatever was true before A started (value=3, since A hasn't committed yet). That value doesn't match the WHERE clause, so transaction B simply skips this row.

Note that if row ID 2's value had been 8 instead, transaction B would block and wait, then re-check the value once transaction A commits or aborts, and check whether it matches the WHERE clause at that point.

This is one of the more interesting mechanisms in InnoDB's Read Committed level.

 

Read Uncommitted

Read Uncommitted is the lowest isolation level, and as most of us know, it's the only level where the dirty read anomaly occurs. Its update logic is identical to what we covered under Repeatable Read — nothing changes there at all. The only difference is on the read side: no ReadView is built, and no visibility check runs, so a plain SELECT just returns whatever's currently in the row, committed or not. That's the mechanism behind the dirty read anomaly.

 

Summary

In this article, we've explained how InnoDB actually works across different isolation levels. InnoDB takes a hybrid approach at most levels: read logic fully adopts MVCC, while update logic relies purely on locking.

• Read Committed
  • Every read rebuilds the list, walks the version chain, and uses MVCC's visibility rule to check what's visible.
  • There are no gap locks for update statements, which is why phantom reads occur at this level.
  • For updates, there's a special mechanism called semi-consistent read, which checks the latest version when there's a lock conflict. This improves efficiency by letting a transaction keep working without blocking, but it also introduces the possibility of data inconsistency.
• Repeatable Read
  • Every read follows the exact same logic as base MVCC: it builds the list on the first query, then reuses it for the rest of the transaction, applying the same visibility rule when walking the version chain.
  • For updates, it acquires the appropriate lock and works directly on the latest version.
  • There's a special issue specific to InnoDB here, since a read and an update within the same transaction can end up working on different versions.
• Serializable
  • This is the only level with no MVCC involved at all.
  • Every read acquires a shared lock, and once that lock is successfully acquired, InnoDB works directly on the latest version.

 

Reference

• MySQL Official Document
• #19 - Multi-Version Concurrency Control (CMU Intro to Database Systems)

 
 

I'm not a database engineer—just a software engineer who loves learning and sharing what I've figured out. If you spot any mistakes or have suggestions, please let me know in the comments. I genuinely appreciate the feedback and want to make sure this is as accurate and helpful as possible.

Comments

[Raknaos]

Good walkthrough — the part that took me longest to internalize was that isolation levels are contracts about when conflicts surface, not guarantees they won't happen. The MVCC snapshot explanation covers reads well; worth adding a note on what happens on the write side, since that's where the surprises live: two transactions can both pass their snapshot read and still collide at commit with a deadlock or lock-wait that the reader never anticipated.

One practical addition that bit me in production: gap locks under REPEATABLE READ. Everything reads fine from the snapshot, then an INSERT into a range someone else's query scanned blocks out of nowhere. Understanding that the phantom-protection is implemented via those locks made the behavior finally make sense — might be worth a follow-up section, since that's the isolation level most people run by default.