In MySQL’s InnoDB engine, multi-version concurrency control (MVCC) lets ordinary reads see a consistent snapshot while other transactions change rows. InnoDB uses undo information to reconstruct older row versions when needed. The snapshot’s timing depends on the transaction’s isolation level; MVCC does not make every query lock-free.
This guide describes behavior documented in the MySQL 8.4 Reference Manual. Check the manual for your deployed version before relying on version-specific details.
As an Amazon Associate I earn from qualifying purchases.
How does MVCC work in MySQL?
The MySQL 8.4 Reference Manual describes InnoDB as “a multi-version storage engine.” Rather than keeping a complete extra copy of every table, InnoDB tracks changes so it can present the appropriate version of a changed row to a consistent read.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →InnoDB row metadata includes a transaction identifier and a pointer to undo information. When a query needs an earlier version, InnoDB can follow that undo history to reconstruct the row as it appeared at the query’s snapshot point. Undo information also enables rollback: changes made by a transaction can be undone if it does not commit.
Insert undo is needed to roll back an inserted row and can be discarded after commit. Update undo can also be needed to reconstruct prior values for consistent reads, so it must remain available while an active snapshot might still need it. See InnoDB Multi-Versioning.
What is a consistent read in InnoDB?
The manual defines it this way: “A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time.” Ordinary SELECT statements are consistent, nonlocking reads by default under InnoDB’s REPEATABLE READ and READ COMMITTED isolation levels.
A consistent read sees changes committed before its snapshot was established. It does not see changes committed afterward or uncommitted changes from other transactions. It does see the current transaction’s own earlier writes. That exception can produce a view that never existed as a complete database state: a transaction might see its own newly updated row alongside older snapshot versions of other rows. Details are in Consistent Nonlocking Reads.
Recommended Free Tools
When is the snapshot established?
For InnoDB’s default REPEATABLE READ level, the first consistent read in a transaction establishes the snapshot used by subsequent consistent reads in that transaction. The snapshot is not necessarily taken when the transaction starts. A commit ends the transaction; a later transaction’s first consistent read establishes a new snapshot.
READ COMMITTED instead gives each consistent read a fresh snapshot. A later plain SELECT can therefore see commits that an earlier SELECT in the same transaction could not see. The manual documents these behaviors in Transaction Isolation Levels.
A two-session timeline
- Session A begins a transaction and runs a plain
SELECT. This establishes its snapshot under REPEATABLE READ. - Session B updates a row and commits.
- Session A runs the same plain
SELECTagain. Under REPEATABLE READ, it continues to see the earlier snapshot; under READ COMMITTED, this read can see Session B’s committed change.
This timeline concerns ordinary consistent reads. Writes and locking reads have distinct behavior; do not assume every statement in a transaction uses the same historical view.
REPEATABLE READ vs. READ COMMITTED
| Behavior | REPEATABLE READ | READ COMMITTED |
|---|---|---|
| InnoDB default | Yes | No |
| Snapshot timing for ordinary consistent reads | The first consistent read establishes the snapshot reused by later consistent reads in the transaction. | Each consistent read obtains a fresh snapshot. |
| Effect on repeated plain SELECTs | They remain consistent with one another, subject to the transaction seeing its own writes. | A later read can see commits that an earlier read could not. |
| Locking behavior relevant to searches | Depending on the statement and index search, locking operations can use gap or next-key locks. | Gap locking for searches and index scans is disabled, except for foreign-key and duplicate-key checks. An UPDATE may use a semi-consistent read in the documented case. |
These are isolation-level rules, not a guarantee that every query plan or locking operation behaves identically. Lock scope depends on the statement and indexes involved. InnoDB also supports READ UNCOMMITTED, which can expose uncommitted changes (dirty reads), and SERIALIZABLE, which is stricter and changes plain SELECT behavior when autocommit is disabled. See the MySQL 8.4 isolation-level documentation.
Why can a query show an older value?
If a transaction is using REPEATABLE READ, a plain consistent read after another transaction commits may still use the snapshot established by the transaction’s first consistent read. The older value is intentional: it keeps ordinary reads in that transaction consistent with one another.
Check whether the session is inside a transaction, which isolation level it uses, and whether an earlier consistent read established the snapshot. A commit or rollback ends that transaction’s snapshot; a subsequent transaction can see newer committed data. Also distinguish plain reads from locking reads and data-changing statements, which do not simply behave like ordinary snapshot reads.
Rank #4
Does MVCC mean queries never lock?
No. Ordinary consistent reads under REPEATABLE READ and READ COMMITTED do not set locks on the tables they access, allowing concurrent sessions to modify rows while those reads run. InnoDB also uses row-level locking, and write statements and explicit locking reads have locking behavior.
Use a locking read for read-then-change decisions
Suppose an application checks that a parent row exists before inserting a related child row. A plain snapshot read does not protect the parent from being deleted by another transaction before the insert. A locking read can protect the row until the transaction completes:
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11START TRANSACTION;
SELECT id FROM parent WHERE id = 42 FOR SHARE;
-- Insert the related child row here.
COMMIT;FOR SHARE takes shared locks on rows read. FOR UPDATE locks encountered index records and associated entries in a manner similar to an UPDATE. These locks are released at commit or rollback. The exact scope depends on the search and indexes; see InnoDB Locking Reads.
Best Value
Do not treat a data-changing statement as if it necessarily reads only the same historical snapshot as a plain SELECT. MySQL documents differences between consistent nonlocking reads and locking statements, so mixing them in a REPEATABLE READ transaction can expose different views of data.
Can a long-running transaction make undo history grow?
Yes. If an active snapshot could still need an older row version, InnoDB cannot discard the corresponding update undo records. A long-running transaction—including a read-only transaction that performs consistent reads—can delay purge and contribute to a growing History list length. MySQL recommends committing or rolling back transactions regularly, including read-only transactions that issue consistent reads. See InnoDB Purge Configuration.
What to check
- Inspect the TRANSACTIONS section of
SHOW ENGINE INNODB STATUSfor the History list length. - Look for old or open transactions that may still require earlier row versions.
- Treat the history-list value as a clue to purge lag and transaction age, not as a diagnosis by itself.
The MySQL 8.4 manual says History list length is “usually less than a few thousand.” This is a general observation, not a target, threshold, or guarantee.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
