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

Introduction

Transactions rely on ACID properties—Atomicity, Consistency, Isolation, Durability. The article explores how MySQL implements these features, starting with redo log, undo log, lock technology, and MVCC.

Redo Log and Undo Log

Redo Log

What is redo log?

Redo log records changes after a transaction commits to ensure durability. It consists of an in‑memory redo log buffer and a on‑disk redo log file.

Example SQL sequence:

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, redo log provides the committed changes to recover the database.

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

Undo Log

What is undo log?

Undo log records the state of data before modification, enabling rollback of uncommitted or failed transactions.

When data is inserted, updated, or deleted, the previous state is stored in undo log.

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

MySQL Lock Technology and MVCC

Lock Technology

MySQL uses shared (read) locks and exclusive (write) locks to control concurrent access. Shared locks allow multiple reads simultaneously, while exclusive locks block other reads and writes until the lock is released.

Read‑write locks enable parallel reads but not parallel writes, forming the basis of isolation.

MVCC Basics

Multi‑Version Concurrency Control stores two hidden columns per row: creation version and expiration version. MVCC allows lock‑free reads by providing each transaction a consistent snapshot.

InnoDB implements MVCC by storing version numbers in hidden columns for each row.

Transaction Implementation

Atomicity is achieved through undo log.

Durability is achieved through redo log.

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

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

Atomicity

All operations in a transaction must either fully succeed or fully roll back. Undo log records the previous state, and during rollback the system generates inverse statements (delete for insert, insert for delete, original values for update).

Undo log generation

Each data‑changing operation creates an undo log entry before the change is persisted.

Undo log must be written before the data page is flushed to disk.

Rollback uses undo log to reverse the operation.

Why write the log before the data? To ensure recovery can revert changes if a crash occurs.

Rollback using undo log

Based on the type of entry, the system generates the appropriate reverse SQL statement.

If undo log contains an insert record → generate DELETE.

If it contains a delete record → generate INSERT.

If it contains an update record → generate UPDATE to original values.

Durability

After commit, changes must survive crashes. MySQL uses a buffer pool for performance; writes go to the pool first and are later flushed to disk. Redo log records changes continuously, allowing recovery after a crash.

Redo log is sequential, reducing I/O overhead.

Buffer pool flushes data pages in larger chunks.

Isolation

MySQL supports four isolation levels:

READ UNCOMMITTED – allows dirty reads.

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.

SERIALIZABLE – highest isolation, no concurrency.

READ UNCOMMITTED

Dirty reads are possible; reads do not acquire locks, allowing high concurrency.

Pros: high performance.

Cons: dirty reads.

READ COMMITTED

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

REPEATABLE READ

Ensures the same result for repeated reads within a transaction. Implemented via read‑write locks or MVCC.

Pros: simple implementation with locks.

Cons: no read‑write parallelism.

MVCC implementation allows read‑write parallelism but is more complex.

SERIALIZABLE

Provides the strongest isolation by serializing transactions; no concurrency benefits.

Consistency

Transactions move the database from one consistent state to another. Proper use of undo/redo logs, locks, and MVCC ensures that even in the presence of errors or crashes, the final state remains consistent.

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; if a crash occurs before the buffer pool flushes, redo log restores the committed state.

Conclusion

The article summarizes the techniques MySQL uses to implement transaction ACID properties:

Atomicity – undo log for rollback.

Durability – redo log for crash recovery.

Isolation – locks and MVCC for concurrent access.

Consistency – combination of the above ensures data remains consistent.