MySQL Undo/Redo Log: Master Transaction Rollback and Persistence
Undo Log (Rollback Log)
Basic Concepts
Undo Log is a key component of database transaction management, used to record old values before data modification, supporting transaction rollback and MVCC.
Lifecycle Management
- Generation timing: Generated before any DML operation in a transaction begins execution
- Commit handling: Not immediately deleted when transaction commits
- Recovery mechanism: Asynchronously cleaned by background purge thread
Undo Log Types (Logical Log)
- INSERT operation: Records corresponding DELETE operation
- DELETE operation: Records complete row data
- UPDATE operation: Records old values before modification
Storage Structure
- Uses segment-based management: InnoDB uses Rollback Segment to organize Undo logs
- Default configuration: Each database instance contains 128 rollback segments
Functions
Transaction Atomicity
Undo Log is the core mechanism for MySQL to implement transaction atomicity. When a transaction encounters system errors, the user executes ROLLBACK statement, or deadlock detection triggers rollback, MySQL uses Undo Log to restore data to its pre-transaction state.
Concurrency Control (MVCC)
- Version chain management: Each record contains DB_TRX_ID and DB_ROLL_PTR
- Snapshot read implementation
- Isolation level support
Redo Log (Redo Log)
Basic Concepts
Redo Log is a log file that records all data modification operations in a transaction, ensuring transaction durability.
Features
- Physical log: Records physical changes to data pages
- Sequential write: Uses append-write method
- Circular use: Uses circular buffer design
Working Principles
- Write-Ahead Logging (WAL)
- Circular write mechanism
- Crash recovery process
innodb_flush_log_at_trx_commit Parameter
| Setting | Description | Risk | Performance |
|---|---|---|---|
| 0 | Batch write every second | Most recent 1 second may be lost | Highest |
| 1 (default) | Each commit | No data loss | Lowest |
| 2 | Write to OS Cache | No loss on process crash | Medium |
Summary
| Feature | Undo Log | Redo Log |
|---|---|---|
| Function | Rollback, MVCC | Durability |
| Type | Logical log | Physical log |
| Guarantees | Atomicity, Isolation | Durability |