Understanding MySQL Transactions, Isolation Levels, and MVCC

Transaction Basics

In MySQL a transaction groups multiple changes so that either all succeed when the transaction is committed or all are undone when it is rolled back. Not all storage engines support transactions; for example MyISAM does not, which is why InnoDB replaced it.

Transactions have four ACID properties: Atomicity, Consistency, Isolation, and Durability.

To start a transaction use START TRANSACTION or BEGIN ; to commit use COMMIT and to roll back use ROLLBACK .

START TRANSACTION READ WRITE;
SELECT * FROM `ddk_app_config`;
UPDATE ddk_app SET desc='aaa' WHERE id=1;
COMMIT;
ROLLBACK;

MySQL runs with autocommit enabled by default, so each statement is automatically committed unless you disable it with SET SESSION autocommit=0 . It is recommended to keep autocommit=1 and explicitly start transactions when needed.

To inspect long‑running transactions you can query the information_schema.innodb_trx table, for example to find transactions lasting more than 60 seconds:

SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

The table shows only transactions that have not yet been committed.

Dirty Read, Non‑Repeatable Read, and Phantom Read

When multiple transactions run concurrently, three phenomena may appear:

Dirty read : reading data modified by another transaction that has not yet committed and may be rolled back.

Non‑repeatable read : a transaction reads a row, another transaction modifies it and commits, and the first transaction reads the row again and sees a different value.

Phantom read : a transaction re‑executes a query and sees new rows inserted by another committed transaction.

While dirty reads are always harmful, non‑repeatable and phantom reads may be acceptable depending on the business scenario, but they violate the isolation principle of database design.

Isolation Levels

Isolation levels balance performance and consistency when transactions run concurrently. The SQL standard defines four levels:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

READ UNCOMMITTED allows non‑locking reads and can produce dirty reads. READ COMMITTED prevents dirty reads. REPEATABLE READ guarantees that a transaction sees the same data for the duration of the transaction. SERIALIZABLE enforces strict locking, ensuring full isolation at the cost of performance.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW GLOBAL VARIABLES LIKE '%isolation%';
SHOW SESSION VARIABLES LIKE '%isolation%';

The following diagram (omitted here) shows how each isolation level resolves the three read anomalies.

MVCC (Multi‑Version Concurrency Control)

InnoDB implements MVCC using a consistent read view, which provides a snapshot of the database at a specific point in time. This snapshot is used to implement READ COMMITTED and REPEATABLE READ isolation levels.

Under READ COMMITTED a new snapshot is created for each consistent read; under REPEATABLE READ the snapshot is created once at the first consistent read of the transaction (or when the transaction is started with START TRANSACTION WITH CONSISTENT SNAPSHOT ).

Read View

A Read View contains metadata that determines which row versions are visible to a transaction. Important fields include:

m_low_limit_id : the high‑water mark (maximum transaction ID + 1) – transactions with IDs greater or equal are invisible.

m_up_limit_id : the low‑water mark – transactions with IDs less than this are already committed and visible.

m_creator_trx_id : the ID of the transaction that created the view.

m_ids : a set of active (uncommitted) transaction IDs at the moment the view was taken.

Visibility rules for a row version (identified by its DB_TRX_ID ) are:

If DB_TRX_ID < m_up_limit_id , the row is from a committed transaction and is visible.

If DB_TRX_ID >= m_low_limit_id , the row was modified by a transaction that started after the view and is invisible.

If m_up_limit_id <= DB_TRX_ID < m_low_limit_id , the row belongs to a transaction that existed when the view was created; it is visible only if its ID is not in m_ids (i.e., the transaction has already committed).

Case Analysis

Under REPEATABLE READ the same Read View is used for all consistent reads, preventing phantom and non‑repeatable reads. Under READ COMMITTED a new Read View is created for each SELECT, which can lead to phantom reads and non‑repeatable reads as demonstrated by the example diagrams.

Undo Log

The undo log stores the before‑image of each modification. It is used for rolling back a transaction and for MVCC to retrieve previous versions of a row when a newer version is invisible to the current transaction.

When a row is not visible according to the Read View, the engine follows the DB_ROLL_PTR to the previous version until it finds a visible one.

Thank you for reading!