Understanding MySQL Transactions: Isolation Levels, MVCC, and Best Practices

Concept of Transactions

A transaction is a group of database operations that must either all succeed or all fail. The classic bank transfer example shows that moving 1,000 CNY from account A to account B requires both the debit and the credit to be applied; otherwise the database becomes inconsistent and the operation must be rolled back.

In other words, a transaction guarantees an all‑or‑nothing outcome.

MySQL implements transaction support at the storage‑engine level. Not all engines support it—MyISAM, for instance, lacks transaction capabilities, which is why InnoDB has become the default engine.

Isolation and Isolation Levels

Transactions are part of the ACID properties (Atomicity, Consistency, Isolation, Durability). Isolation prevents phenomena that arise when multiple transactions run concurrently, such as dirty reads, non‑repeatable reads, and phantom reads. Different isolation levels trade off consistency for performance.

Read Uncommitted – Allows dirty reads; rarely used in production.

Read Committed – Prevents dirty reads; each statement sees only data committed before it starts.

Repeatable Read – Guarantees that rows read twice in the same transaction return the same values, eliminating non‑repeatable reads but not phantom reads.

Serializable – The strictest level; transactions are executed as if they were serialized, using read and write locks.

Example scenarios illustrate how the same sequence of operations yields different results under each level, using variables V1, V2, V3 to denote the values observed by a reading transaction.

Implementation of Isolation in InnoDB

InnoDB uses Multi‑Version Concurrency Control (MVCC). Every row update creates a new version and an associated undo log entry. A read‑view (snapshot) is built when a transaction starts, allowing the engine to present a consistent view of the data.

When a transaction reads a row, InnoDB examines the row's row_trx_id against the snapshot's low and high watermarks to decide visibility:

Versions with row_trx_id less than the low watermark are visible (committed before the transaction).

Versions with row_trx_id greater than the high watermark are invisible (created after the transaction).

Versions whose row_trx_id falls between the watermarks are visible only if the originating transaction has already committed.

Undo logs are used to reconstruct older versions on demand; they are discarded once no active transaction needs them.

Transaction Start Methods

MySQL provides several ways to start a transaction:

Explicit statements: BEGIN or START TRANSACTION, with COMMIT and ROLLBACK to finish.

Disabling autocommit: SET autocommit=0 causes the first DML statement to start a transaction, which remains open until an explicit COMMIT, ROLLBACK, or connection termination.

Frameworks that automatically issue SET autocommit=0 can unintentionally create long‑running transactions. The author recommends keeping autocommit=1 and using explicit BEGIN when a transaction is needed, or the COMMIT WORK AND CHAIN syntax to reduce round‑trips.

MVCC Working Principle

Each transaction receives a unique, monotonically increasing transaction ID. When a row is updated, the new version stores the updating transaction’s ID ( row_trx_id). The snapshot consists of an array of all active transaction IDs at the moment the transaction starts, plus a low watermark (minimum ID) and a high watermark (maximum ID + 1).

Visibility rules are applied by comparing a row’s row_trx_id with the snapshot:

Green region: committed before the snapshot or generated by the current transaction → visible.

Gray region: generated by a future transaction → invisible.

Pink region: either uncommitted (still in the active array) → invisible, or committed but after the snapshot → invisible.

Examples with tables T and t demonstrate how different isolation levels affect the values returned by concurrent transactions A, B, and C.

Current Read vs. Consistent Read

Updates always use a “current read” – they read the latest version, acquire the necessary lock, and then write a new version. SELECT statements under Repeatable Read use a consistent read based on the snapshot, while under Read Committed a new snapshot is built for each statement.

Locking reads ( SELECT ... LOCK IN SHARE MODE or SELECT ... FOR UPDATE) also perform a current read, ensuring the latest version is examined and appropriate locks are taken.

Summary

The article provides a low‑level walkthrough of MySQL transaction mechanics, covering ACID fundamentals, isolation levels, MVCC implementation, transaction start options, and practical tips to avoid long‑running transactions and lock contention.