Understanding MySQL Transaction Implementation: Redo Log, Undo Log, Locks, and MVCC

Introduction

First, we review the characteristics of a transaction—Atomicity, Consistency, Isolation, and Durability (ACID)—and pose the question of what a transaction aims to achieve: reliability and concurrent processing.

Reliability means the database must keep data consistent before and after an operation, even if an exception or crash occurs, which requires undo and redo logs. Concurrency handling ensures that simultaneous requests do not read dirty data, which is achieved through MySQL isolation levels.

The article will cover three core technologies (redo/undo logs, lock mechanisms, and MVCC) and then explain how they realize the ACID properties.

Introduction to redo log and undo log

MySQL lock technology and MVCC basics

Transaction implementation principles

Summary

2. Redo Log and Undo Log Introduction

1. Redo Log

What is a redo log?

A redo log ("redo" means "re‑do") records changes to guarantee transaction durability. It consists of an in‑memory redo log buffer and an on‑disk redo log file. After a transaction commits, all modifications are written to the redo log.

Example: inserting a row into tb1(id, username) with values (3, 'ceshi').

start transaction;
select balance from bank where name="zhangsan"; // generates redo log: balance=600
update bank set balance = balance - 400; // generates redo log: amount=400
update finance set amount = amount + 400;
commit;

Purpose of redo log

MySQL buffers modifications in the Buffer Pool for performance and periodically flushes them to disk. The redo log persists the changes of committed transactions so that after a crash the system can recover the latest state.

2. Undo Log

What is an undo log?

An undo log (rollback log) records the state of data before it is modified. It complements the redo log, which records the after‑state. Undo logs enable the database to revert changes when an error or explicit rollback occurs.

Before each insert, update, or delete, the previous version of the row is written to the undo log.

Purpose of undo log

When a transaction needs to be rolled back, the undo log provides the original values so the database can restore the pre‑modification state.

3. MySQL Lock Technology and MVCC Basics

1. MySQL Lock Technology

When multiple requests read a table, no action is needed. However, when read and write requests coexist, a concurrency control mechanism is required.

Read‑Write Locks

Two lock types are used:

Shared lock (read lock) : multiple readers can hold the lock simultaneously without blocking.

Exclusive lock (write lock) : only one writer can hold the lock, blocking all other readers and writers until it releases.

Summary: read‑write locks allow concurrent reads but serialize writes, forming the basis of transaction isolation.

2. MVCC Basics

MVCC (Multi‑Version Concurrency Control) provides “read‑write separation” by storing multiple versions of a row.

In InnoDB, MVCC is implemented by two hidden columns per row: a creation version and an expiration version (system version numbers, not timestamps).

MVCC relies on undo logs and a read view to determine which version of a row is visible to a transaction.

undo log: stores previous versions of rows.

read view: determines visibility of a version for the current transaction.

4. Transaction Implementation

The previously described redo log, undo log, and lock technology constitute the foundation of MySQL transaction implementation.

Atomicity is achieved via the undo log.

Durability is achieved via the redo log.

Isolation is achieved through (read‑write locks + MVCC).

Consistency is the result of combining atomicity, durability, and isolation.

1. Implementing Atomicity

Atomicity means a transaction is an indivisible unit: either all its operations succeed or none do. This is realized by rolling back using the undo log.

1.1 Generation of Undo Log

When modifying tables bank and finance , MySQL creates undo log entries for each change (insert, update, delete). These entries are written before the actual data pages are flushed to disk.

Key conclusions:

Every data‑changing statement generates an undo log entry, which must be persisted before the data itself.

Rollback is performed by applying inverse operations derived from the undo log.

1.2 Rolling Back Using Undo Log

During a rollback, MySQL examines the undo log and generates appropriate statements: delete inserted rows, re‑insert deleted rows, or revert updates to their original values.

2. Implementing Durability

After a transaction commits, its changes must survive crashes. MySQL uses a Buffer Pool for caching and periodically flushes dirty pages to disk. Because the Buffer Pool alone cannot guarantee durability, the redo log records committed changes in a sequential, fast‑write format, enabling recovery after a crash.

3. Implementing Isolation

MySQL supports four isolation levels (from lowest to highest): READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Each level balances concurrency performance against consistency guarantees.

READ UNCOMMITTED : allows dirty reads; highest concurrency.

READ COMMITTED : prevents dirty reads but may suffer non‑repeatable reads and phantom reads; uses MVCC for read‑write separation.

REPEATABLE READ (default): guarantees repeatable reads via either read‑write locks or MVCC; still vulnerable to phantom reads.

SERIALIZABLE : highest consistency, lowest concurrency; essentially forces full serialization.

Each level determines how and when locks or MVCC are applied to protect data visibility across concurrent transactions.

4. Implementing Consistency

Databases always transition from one consistent state to another.

Example: transferring 400 units from a bank account to a finance account.

start transaction;
select balance from bank where name="zhangsan"; // redo log: balance=600
update bank set balance = balance - 400; // redo log
update finance set amount = amount + 400;
commit;

If an error occurs after the first update, the undo log rolls back the change, preserving consistency. If a crash happens before the buffer pool flushes, the redo log enables recovery after restart. Proper isolation also prevents dirty, non‑repeatable, and phantom reads, ensuring overall consistency.

Summary

This article described how MySQL implements transaction ACID properties using undo logs for atomicity, redo logs for durability, lock mechanisms and MVCC for isolation, and how these components together guarantee data consistency.