iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
InnoDB’s multi-version concurrency control (MVCC) lets ordinary reads see a consistent snapshot while other transactions change rows. In MySQL 8.4, the snapshot timing depends on isolation level: the default REPEATABLE READ reuses the snapshot from a transaction’s first consistent read, while READ COMMITTED takes a fresh snapshot for each consistent read. MVCC supports nonlocking reads; it does not make every query lock-free.
How does MVCC work in MySQL?
MVCC is InnoDB’s way of managing visibility across concurrent transactions. The MySQL 8.4 Reference Manual describes InnoDB as “a multi-version storage engine.” Instead of keeping a complete extra copy of every table, InnoDB stores undo information for changed rows. Each row includes transaction metadata and a pointer into undo information; when a consistent read needs an earlier version, InnoDB can follow that history to reconstruct it.
Undo information also supports rollback. Insert undo can be discarded after a committed insert, but update undo may remain necessary while an active snapshot could still need an earlier row version. MVCC therefore handles both transaction recovery and consistent reads.
What is a consistent read in InnoDB?
The manual defines a consistent read as a query for which “InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time.” An ordinary SELECT is generally a consistent, nonlocking read. It sees changes committed before its snapshot was established, not changes committed afterward or uncommitted changes from other transactions.
#1 Best Overall
There is an important exception: a transaction sees its own earlier writes. If it updates a row and then selects it, the SELECT can show that update even while other rows remain visible as they were in the transaction’s older snapshot. The resulting view may not match a single state that ever existed globally.
In REPEATABLE READ, the snapshot is established by the first consistent read, not necessarily when the transaction begins. Committing ends that transaction; a subsequent query in a new transaction can use a later snapshot.
Rank #2
What is the difference between REPEATABLE READ and READ COMMITTED?
REPEATABLE READ is InnoDB’s default isolation level. READ COMMITTED changes when an ordinary consistent read gets its view of committed data.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →| Behavior | REPEATABLE READ | READ COMMITTED |
|---|---|---|
| InnoDB default | Yes | No |
| Snapshot timing for ordinary consistent reads | The first consistent read establishes the snapshot; later consistent reads in the transaction reuse it. | Each consistent read obtains a fresh snapshot. |
| Practical effect | Repeated plain SELECTs in one transaction continue to use the same snapshot, apart from the transaction’s own writes. | A later plain SELECT can see a commit that was not visible to an earlier SELECT in the same transaction. |
| Locking behavior | Range and locking operations can use gap or next-key locks in documented cases. | Gap locking for searches and index scans is disabled, except for foreign-key and duplicate-key checks. UPDATE can use a semi-consistent read in the documented case. |
A timeline example
- Transaction A starts and performs a plain SELECT. That consistent read establishes its snapshot.
- Transaction B updates a row and commits.
- Transaction A performs another plain SELECT. Under REPEATABLE READ, it continues to use its earlier snapshot; under READ COMMITTED, it gets a fresh snapshot and can see B’s committed change.
These differences concern consistent reads, not a promise that all statements in a transaction use one identical view. Locking reads and data-changing statements have behavior that differs from ordinary consistent SELECTs. The other InnoDB isolation levels are READ UNCOMMITTED, which can expose uncommitted data (a dirty read), and SERIALIZABLE, which changes plain SELECT behavior when autocommit is disabled.
Why does MySQL show an older value inside a transaction?
Under REPEATABLE READ, a plain SELECT after another transaction commits can still show the value visible when the first consistent read established the snapshot. That is expected snapshot behavior, not necessarily stale storage or a failed update. A new transaction, or a consistent read under READ COMMITTED, can use a newer snapshot.
Also distinguish the statement type. Ordinary consistent reads, locking reads, and data-changing statements do not all necessarily see data the same way. In particular, mixing consistent nonlocking reads with locking statements in a REPEATABLE READ transaction can expose different views.
Does MVCC mean MySQL queries never lock?
No. Ordinary consistent reads in REPEATABLE READ and READ COMMITTED do not set locks on the tables they access, so other sessions can modify those tables while the reads run. Explicit locking reads are different:
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →SELECT ... FOR SHAREtakes shared locks on rows read.SELECT ... FOR UPDATElocks encountered index records and associated entries similarly to an UPDATE.
These locks are released at commit or rollback. For example, if an application must check that a parent row exists before inserting a related child row, a plain SELECT leaves an opportunity for another transaction to delete the parent before the insert. A FOR SHARE read protects the row while the transaction continues. Actual lock scope depends on the search and indexes.
Best Value
Can a long-running transaction cause undo history to grow?
Yes. If an active snapshot could still need earlier row versions, 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 InnoDB History list length.
MySQL’s 8.4 Reference Manual says the History list length is typically “usually less than a few thousand.” This is a general observation, not a target, limit, or guarantee. To investigate purge lag, inspect the TRANSACTIONS section of SHOW ENGINE INNODB STATUS for History list length, and consider transaction age as well; the value alone does not diagnose the cause. Commit or roll back transactions regularly, including read-only transactions.
Version scope and official documentation
The behavior described here is documented for InnoDB in MySQL 8.4. Implementation and isolation details can vary by version, so check the manual for the MySQL release you deploy. Relevant sections are InnoDB Multi-Versioning, Consistent Nonlocking Reads, Transaction Isolation Levels, Undo Logs, Purge Configuration, and Locking Reads.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesQuick 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.
