Master MySQL Transactions: ACID, Isolation Levels, and MVCC Explained

Preface

After covering MySQL's three main logs (undo log, redo log, binlog), it is essential to continue with an analysis of MySQL transactions, especially the role of undo logs in MVCC (Multi‑Version Concurrency Control).

Note that InnoDB supports transactions, while MyISAM does not.

📚 Total length: 9k+ characters ⏳ Estimated reading time: 13 minutes 📢 Keywords: transaction, isolation level, MVCC, ReadView

What is a MySQL Transaction?

A MySQL transaction guarantees data consistency; it is a logical unit of work consisting of multiple database operations that must either all succeed or all fail.

-- Example transfer of 100,000 units from account A to account B
UPDATE account SET money = money - 50000 WHERE name = 'A';
UPDATE account SET money = money + 50000 WHERE name = 'B';
-- If any statement fails or the system crashes, a ROLLBACK restores the original state; otherwise COMMIT finalizes the transaction.

Problems Solved by Transactions

Without transactions, concurrent modifications to the same row can cause conflicts, data corruption, or loss.

If user A modifies a row while user B does the same, the data may become inconsistent.

Transactions ensure data accuracy, reduce the impact of database failures, and improve system availability and stability.

Transaction Characteristics

Transactions obey the four ACID properties: Atomicity, Consistency, Isolation, Durability.

ACID

Atomicity : The transaction is indivisible; it either fully executes or does not execute at all.

Consistency : The database state remains consistent before and after the transaction.

Isolation : Transactions execute independently of each other, preventing interference.

Durability : Once committed, changes persist even after system crashes or restarts.

How ACID Is Enforced

Durability is guaranteed by the redo log.

Atomicity is guaranteed by the undo log.

Isolation is guaranteed by MVCC (multi‑version concurrency control) plus locking.

Consistency follows from the combination of durability, atomicity, and isolation.

Basic Usage

In everyday development, ORMs often handle transaction control, but the underlying MySQL commands are:

By default, MySQL runs in autocommit mode, treating each statement as a separate transaction.

START TRANSACTION;   -- or BEGIN to begin a new transaction
-- DML statements
ROLLBACK;          -- undo the transaction
COMMIT;            -- finalize the transaction

After START TRANSACTION, all subsequent statements belong to the same transaction until COMMIT or ROLLBACK.

DML stands for Data Manipulation Language (INSERT, UPDATE, DELETE, SELECT).

Transaction Types

MySQL supports implicit and explicit transactions.

Implicit transactions : Statements like INSERT, UPDATE, DELETE automatically start, commit, or roll back transactions.

Check autocommit status with SHOW VARIABLES LIKE 'autocommit'; (ON means autocommit is enabled).

Explicit transactions : Use BEGIN, COMMIT, and ROLLBACK to control transaction boundaries.

BEGIN;
-- SQL statements
COMMIT;

Concurrent Transaction Issues

When multiple clients run concurrently, MySQL can encounter dirty reads, non‑repeatable reads, and phantom reads.

Dirty Read

A transaction reads data modified by another uncommitted transaction, leading to inconsistent results.

Non‑Repeatable Read

The same query within a transaction returns different results because another transaction modified the data and committed.

Phantom Read

Repeated queries with the same condition return a different number of rows because another transaction inserted or deleted rows that satisfy the condition.

Summary:

Dirty read: reading uncommitted data.

Non‑repeatable read: same query yields different values.

Phantom read: row count changes between queries.

Transaction Isolation Levels

How can we avoid dirty, non‑repeatable, and phantom reads?

Isolation levels define how visible a transaction’s changes are to others.

READ UNCOMMITTED may cause all three phenomena.

READ COMMITTED prevents dirty reads but allows the other two.

REPEATABLE READ prevents dirty and non‑repeatable reads but may allow phantom reads.

SERIALIZABLE prevents all three.

Setting Isolation Levels

Check the default level (usually REPEATABLE‑READ) with: SHOW VARIABLES LIKE 'transaction_isolation'; Two ways to change it:

Configure transaction‑isolation=REPEATABLE‑READ in my.cnf or my.ini.

Use SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL ... to modify the current or global setting.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Without SESSION/GLOBAL, the next transaction uses the new level.

GLOBAL affects all new connections.

SESSION affects only the current connection.

Analyzing Isolation Levels

Using a test table

user(id INT AUTO_INCREMENT, name VARCHAR(32), point INT DEFAULT 0, PRIMARY KEY(id))

, we demonstrate how each level behaves.

// Create table
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'User ID',
  `name` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT 'Name',
  `point` int DEFAULT '0' COMMENT 'Points',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

// Insert a row
INSERT INTO user (name, point) VALUES('xiaoxu', 100);

READ UNCOMMITTED example: Transaction A reads 100, updates to 150 but does not commit; Transaction B reads 150, leading to a dirty read if A later rolls back.

READ COMMITTED prevents dirty reads but still allows non‑repeatable reads, as shown by two reads of the same row returning different values after another transaction commits.

REPEATABLE READ uses a single ReadView for the whole transaction, so repeated reads see the same data even after other transactions commit.

SERIALIZABLE forces transactions to execute sequentially, eliminating all concurrency anomalies.

MVCC (Multi‑Version Concurrency Control)

Problems Solved by MVCC

MVCC eliminates read‑write conflicts without locking, improves concurrent performance, and resolves dirty reads, non‑repeatable reads, and phantom reads (though it cannot prevent lost updates).

How MVCC Is Implemented

MVCC relies on three hidden fields per row, the undo log, and a Read View.

Hidden Fields

InnoDB adds to each row: db_row_id: 6‑byte hidden row identifier. db_trx_id: 6‑byte ID of the transaction that created or last modified the row. db_roll_ptr: 7‑byte pointer to the previous version stored in the rollback segment.

Undo Log

The undo log records the previous state of a row before it is modified, enabling rollback to the original state if needed.

Read View

A Read View captures a snapshot of the database at the moment of a snapshot read, storing the creator transaction ID and the set of active transaction IDs.

Visibility rules compare a row’s trx_id with the Read View fields ( creator_trx_id, min_trx_id, max_trx_id, and the active ID set) to decide whether the row is visible to the current transaction.

MVCC Isolation Level Analysis

In READ COMMITTED, each SELECT creates a new Read View, leading to non‑repeatable reads. In REPEATABLE READ, the first SELECT creates the Read View, which is reused, preventing non‑repeatable reads but still allowing phantom reads.

Conclusion

After reading this article, you should have a solid understanding of MySQL transactions, ACID properties, isolation levels, and MVCC mechanisms, enabling you to design reliable and consistent database operations.