Understanding MySQL InnoDB Transaction Isolation Levels, Undo Logs, and MVCC

The article begins by reviewing the four InnoDB isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) and the concurrency problems that can arise under each level.

It then introduces Undo Logs, which preserve the pre‑transaction state of rows to support atomic rollback and MVCC‑based non‑locking reads. Two types are described: TRX_UNDO_INSERT (generated by INSERT statements and discarded after commit or rollback) and TRX_UNDO_UPDATE (covers UPDATE and DELETE, retained after commit for possible MVCC use).

The concept of Multi‑Version Concurrency Control (MVCC) is explained as a technique that allows transactions to read consistent snapshots of data without waiting for locks, thereby increasing concurrency.

Read View, the snapshot used by MVCC, is detailed next. It stores metadata such as m_low_limit_id , m_up_limit_id , m_creator_trx_id , m_ids , m_low_limit_no , m_closed , and m_view_list to determine which versions are visible to a given transaction.

The creation and closing of Read Views differ between isolation levels. For READ COMMITTED, a new Read View is created and closed for each statement; for REPEATABLE READ, a single Read View is created at the first consistent read and reused until the transaction ends. The relevant source code is shown:

innobase_start_trx_and_assign_read_view(
    /*====================================*/
    handlerton*    hton,  /*!< in: InnoDB handlerton */
    THD*      thd)   /*!< in: MySQL thread handle of the user for
            whom the transaction should be committed */
{
   ......
   /* In RR level, start transaction with consistent snapshot creates the view directly; otherwise it is created on the first SELECT */
   trx->isolation_level = innobase_map_isolation_level(
      thd_get_trx_isolation(thd));

   if (trx->isolation_level == TRX_ISO_REPEATABLE_READ) {
      trx_assign_read_view(trx);
   } else {
      push_warning_printf(thd, Sql_condition::SL_WARNING,
                HA_ERR_UNSUPPORTED,
                "InnoDB: WITH CONSISTENT SNAPSHOT"
                " was ignored because this phrase"
                " can only be used with"
                " REPEATABLE READ isolation level.");
   }
}

Despite REPEATABLE READ providing snapshot reads, the article demonstrates that phantom reads can still occur when snapshot reads are mixed with current reads. Two examples are given using a simple test table with an auto‑increment primary key, showing how concurrent inserts become visible only to current reads.

Finally, the article lists several reference links to MySQL documentation on locking reads, consistent non‑locking reads, and MVCC implementation details.