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

Introduction

Most readers have used transactions and know their ACID properties: Atomicity, Consistency, Isolation, and Durability. This article investigates how MySQL implements these properties.

What effect does a transaction aim to achieve?

In short, reliability and concurrent processing.

Reliability: the database must guarantee consistency before and after an INSERT or UPDATE, even if an exception occurs or the server crashes. This requires undo log and redo log.

Concurrency: when multiple requests arrive, especially when one modifies data, isolation is needed to avoid dirty reads; the appropriate isolation level is chosen via MySQL's isolation levels.

The article will cover three core techniques: redo log and undo log, lock mechanisms, and MVCC, then explain how each contributes to transaction implementation.

Redo Log and Undo Log

1. Redo Log

What is redo log?

Redo log (also called the redo log) is used to achieve transaction durability. It consists of a redo log buffer in memory and a redo log file on disk.

When a transaction commits, all modification information is written to the redo log.

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

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

Purpose of redo log

MySQL writes changes to the buffer pool first for performance, then a background thread flushes them to disk. If a crash occurs before flushing, some committed changes would be lost. Redo log records committed modifications and persists them to disk, allowing recovery after restart.

Redo log is used to recover data and guarantee the durability of committed transactions.

2. Undo Log

What is undo log?

Undo log (rollback log) records the state of data before it is modified. It stores the logical changes so that, in case of an error, the operation can be rolled back.

Purpose of undo log

Undo log keeps the pre‑modification version of a row, enabling rollback to the original state when an error or explicit ROLLBACK occurs.

Undo log is used to roll back data and ensure the atomicity of uncommitted transactions.

MySQL Lock Techniques and MVCC Basics

1. Lock Techniques

When multiple requests read a table, no lock is needed. When reads and writes coexist, concurrency control is required.

Read‑Write Locks

Two lock types are used:

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

Exclusive lock (write lock) : only one writer can hold the lock; other requests block until the lock is released.

Summary: read‑write locks allow parallel reads but serialize writes.

2. MVCC Basics

MVCC (Multi‑Version Concurrency Control) stores two hidden columns per row: creation version and expiration version. This enables non‑locking reads.

In InnoDB, MVCC relies on undo log and a read view to determine row visibility.

undo log: stores multiple versions of a row.

read view: determines which versions are visible to a transaction.

Transaction Implementation

The previously described redo log, undo log, and lock/MVCC techniques form the basis of transaction implementation.

Atomicity is achieved by undo log.

Durability is achieved by redo log.

Isolation is achieved by (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 operations succeed or all are rolled back. Undo log records the before‑image of each change; during rollback, the system generates inverse statements (delete for insert, insert for delete, reverse update) based on the undo log.

1.1 Generation of undo log

For each INSERT/UPDATE/DELETE, an undo log entry is created before the data is persisted to disk.

Each data change is accompanied by an undo log entry, which must be written before the data page is flushed.

Rollback uses the undo log to perform inverse operations.

Why write the log before writing the data? (explained later)

1.2 Rollback using undo log

When an error occurs or ROLLBACK is issued, the system uses the undo log to restore the original state.

If the undo log contains an insert record, generate a DELETE statement.

If it contains a delete record, generate an INSERT statement.

If it contains an update record, generate an UPDATE to revert to the original values.

2. Implementing Durability

After a transaction commits, its changes must survive a crash. MySQL uses a buffer pool for caching and periodically flushes pages to disk. Because the buffer pool is volatile, redo log records every modification as it occurs, providing a sequential, low‑overhead write path that can be replayed after a crash.

3. Implementing Isolation

Isolation controls the visibility of concurrent transactions. MySQL supports four isolation levels:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ (default)

SERIALIZABLE

Each level balances consistency and concurrency.

READ UNCOMMITTED

Allows reading uncommitted changes, leading to dirty reads but offering high concurrency.

READ COMMITTED

Only committed changes are visible; uses MVCC for non‑locking reads but can suffer non‑repeatable reads and phantom reads.

REPEATABLE READ

Ensures that multiple reads within the same transaction see the same snapshot, achieved via read‑write locks or MVCC.

SERIALIZABLE

The strictest level; prevents all anomalies but reduces concurrency.

4. Implementing Consistency

Consistency is achieved by combining atomicity, durability, and isolation, ensuring that the database moves from one consistent state to another.

Example transaction transferring 400 from a bank account to a finance account demonstrates how undo log, redo log, and isolation work together to maintain consistency even in the presence of errors or crashes.

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

Conclusion

The article presented the mechanisms behind MySQL transaction implementation, including redo log, undo log, lock techniques, and MVCC, and explained how they collectively provide atomicity, durability, isolation, and consistency.