Summary of MySQL Undo Log and Redo Log

Undo Log

Before a database transaction begins, the records to be modified are stored in the Undo log. When the transaction is rolled back or the database crashes, the Undo log can be used to undo the impact of the uncommitted transaction on the database.

Undo Log Generation and Destruction

Undo Log is generated before the transaction starts. When the transaction is committed, the undo log is not deleted immediately. InnoDB puts the undo log corresponding to the transaction into the deletion list, which is then recycled by the background purge thread.
Undo Log is a logical log that records a change process. For example, if a delete is executed, undolog will record an insert; if an update is executed, undolog will record the opposite update.

Undo Log Storage

Undo log is managed and recorded in segments. The InnoDB data file contains a rollback segment, which contains 1024 undo log segments. Undo log storage can be controlled by the following set of parameters.

show variables like '%innodb_undo%';

Undo Log Function

Implementing transaction atomicity

Undo Log is a product that was created to achieve the atomicity of transactions. During transaction processing, if an error occurs or the user executes a ROLLBACK statement, MySQL can use the backup in the Undo Log to restore the data to the state before the transaction started.

Implementing Multi-Version Concurrency Control (MVCC)

Undo Log is used to implement multi-version concurrency control in the MySQL InnoDB storage engine. Before a transaction is committed, the Undo Log saves the previous version of the data. The data in the Undo Log can be used as an old version snapshot of the data for snapshot reading by other concurrent transactions.

Transaction A manually starts a transaction and performs an update operation. First, the data hit by the update is backed up to the Undo Buffer.
Transaction B manually opens a transaction and executes a query operation. It reads the Undo log data and returns it for snapshot reading.

Redo Log

Refers to any data modified in a transaction. The location where the latest data backup is stored (Redo Log) is called the redo log.

Redo Log Generation and Release

As transaction operations are executed, Redo Logs are generated. When the transaction is committed, the generated Redo Logs are written to the Log Buffer, but not immediately written to the disk file when the transaction is committed.
After the dirty pages of the transaction operation are written to the disk, the mission of the Redo Log is completed, and the space occupied by the Redo Log can be reused (overwritten).

How Redo Log works

Redo Log is a product that was created to achieve transaction persistence. To prevent the situation where there are dirty pages that have not been written to the table's ibd file at the time of a failure, redo the data based on the Redo Log when the MySQL service is restarted, thereby achieving the feature of persisting the transaction data that has not been written to disk.

Redo Log Writing Mechanism

The contents of the Redo Log file are written to the file in a sequential loop. When it is full, it goes back to the first file and overwrites it.

write pos is the position of the current record. It moves backwards while writing. After writing to the end of the last file, it returns to the beginning of file 0.
Checkpoint is the current position to be erased, which is also moved backward and cyclic. Before erasing the record, the record must be updated to the data file;
The empty space between write pos and checkpoint can be used to record new operations. If the write pos catches up with the checkpoint, it means it is full. At this time, no new updates can be performed. It is necessary to stop and erase some records to advance the checkpoint.

Redo Log related configuration parameters

Each InnoDB storage engine has at least one redo log file group, and each file group has at least two redo log files, the default being ib_logfile0 and ib_logfile1. You can control Redo Log storage through the following set of parameters

show variables like '%innodb_log%';

The strategy for persisting Redo Buffer to Redo Log can be set through Innodb_flush_log_at_trx_commit

0: Redo buffer is committed every second -> OS cache -> flush cache to disk, which may result in loss of transaction data within one second. The operation is performed once every 1 second by the background Master thread.
1 (default): Redo Buffer -> OS cache -> flush cache to disk is executed for each transaction commit. This is the safest method with the worst performance.
2: Each time a transaction is committed, Redo Buffer -> OS cache is executed, and then the background Master thread executes OS cache -> flush cache to disk every 1 second.

It is generally recommended to select a value of 2, because if MySQL crashes, there is no data loss, and only if the entire server crashes will 1 second of transaction submission data be lost.

The above is the detailed summary of MySQL Undo Log and Redo Log. For more information about MySQL Undo Log and Redo Log, please pay attention to other related articles on 123WORDPRESS.COM!