Understanding MySQL Transaction Implementation: Redo/Undo Logs, Locks, MVCC, and Isolation Levels

Everyone has used transactions and knows their ACID properties—Atomicity, Consistency, Isolation, and Durability. This article explores how MySQL implements these properties internally.

Redo Log and Undo Log Introduction

1. Redo Log

What is a redo log?

A redo log records changes made by a transaction to guarantee durability. It consists of a redo log buffer and the redo log file itself.

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;

MySQL buffers modifications in memory for performance and periodically flushes them to disk. If a crash occurs before the flush, redo logs stored on disk allow recovery of committed transactions.

Purpose of redo log

Redo logs persist the changes of committed transactions, enabling recovery after a crash.

2. Undo Log

What is an undo log?

An undo log records the state of data before it is modified, enabling rollback of uncommitted or failed transactions. It stores logical changes rather than physical disk pages.

Before each INSERT/UPDATE/DELETE, MySQL writes an undo record containing the previous row values.

Purpose of undo log

Undo logs allow the system to revert changes when a transaction is rolled back, ensuring atomicity for uncommitted work.

MySQL Lock Technology and MVCC Basics

1. Lock Technology

When multiple requests read a table, no lock is needed. When reads and writes coexist, MySQL uses two lock types to control concurrency:

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

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

Read‑write locks form the basis of transaction isolation.

2. MVCC Basics

MVCC (Multi‑Version Concurrency Control) stores two hidden columns per row: a creation version and an expiration version. These version numbers replace actual timestamps.

In InnoDB, MVCC is implemented by keeping two hidden columns for each row: one for the row’s creation version and one for its expiration version.

MVCC enables lock‑free reads by providing each transaction with a consistent snapshot (read view). It relies on undo logs to reconstruct older versions of rows.

Transaction Implementation

The three techniques described above—redo log, undo log, and lock/MVCC—realize the ACID properties:

Atomicity: achieved via undo log.

Durability: achieved via redo log.

Isolation: achieved via (read‑write locks + MVCC).

Consistency: the combination of the three ensures data remains consistent.

1. Atomicity Implementation

Atomicity means a transaction is indivisible: either all its statements succeed or none do. When an error occurs, MySQL rolls back using undo logs.

1.1 Generation of Undo Log

For each data‑changing statement (INSERT/UPDATE/DELETE), MySQL creates an undo record containing the previous row values. The undo log must be written before the actual data page is flushed to disk.

1.2 Rollback Using Undo Log

During rollback, MySQL examines undo records and generates inverse statements:

If an undo record shows a newly inserted row, generate a DELETE.

If it shows a deleted row, generate an INSERT.

If it shows an updated row, generate an UPDATE that restores the original values.

2. Durability Implementation

When a transaction commits, its changes are first stored in the buffer pool (memory). The buffer pool periodically flushes pages to disk, but a crash before the flush could lose data. Redo logs, written sequentially to disk, guarantee that committed changes can be replayed after a restart.

3. Isolation Implementation

MySQL supports four isolation levels, each defining which changes are visible to other transactions:

READ UNCOMMITTED – allows dirty reads; highest concurrency.

READ COMMITTED – prevents dirty reads but may cause non‑repeatable reads and phantom reads.

REPEATABLE READ (default) – prevents dirty and non‑repeatable reads; uses locks or MVCC to provide a consistent snapshot.

SERIALIZABLE – strongest isolation; essentially executes transactions sequentially.

READ UNCOMMITTED

Changes are visible before commit, leading to dirty reads. No locks are taken, so read‑write concurrency is high.

READ COMMITTED

Only committed changes are visible. MySQL uses exclusive locks for writes and MVCC for reads, but non‑repeatable reads and phantom reads can still occur.

REPEATABLE READ

All reads within a transaction see the same snapshot. MySQL achieves this either with read‑write locks (simple but blocks writes) or with MVCC (allows read‑write parallelism).

SERIALIZABLE

The strictest level; transactions are executed as if they were serialized, eliminating all concurrency anomalies at the cost of performance.

4. Consistency Implementation

Databases always transition from one consistent state to another.

Example: transferring 400 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: amount=400
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 after commit but before the buffer pool flushes, the redo log enables recovery.

Conclusion

This article described how MySQL implements transaction support using three core technologies:

Atomicity – undo log for rollback.

Durability – redo log for crash recovery.

Isolation – locks and MVCC to control concurrent access.

Consistency – achieved by combining the above mechanisms.