Analysis of redo and undo in MySQL transactions (picture and text)

The content of this article is about the analysis (pictures and texts) of redo and undo in MySQL transactions. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

We all know that transactions have four characteristics: atomicity, consistency, isolation and durability. All operations in a transaction are either performed or not performed at all. This is the purpose of the transaction. . The isolation of transactions is achieved by the lock mechanism, and the atomicity, consistency and durability are guaranteed by the redo log and undo log of the transaction. So this article will discuss several issues about redo and undo in transactions:

• What are redo logs and undo logs?

• redo How to ensure the durability of transactions?

• Is undo log the reverse process of redo log?

redo log

Type of Redo

Redo log (redo log) is used Guarantee the durability of the transaction, which is the D in transaction ACID. In fact, it can be divided into the following two types:

• Physical Redo log

• Logical Redo log

In the InnoDB storage engine, In most cases Redo is a physical log, which records the physical changes of the data page. The logical redo log does not record the actual modification of the page, but records a type of operation that modifies the page. For example, when creating a new data page, a logical log needs to be recorded. Regarding the logical Redo log, it involves more low-level content. Here we only need to remember that in most cases, Redo is a physical log. DML modification operations on the page need to record Redo.

The role of Redo

The main function of Redo log is for database crash recovery

Composition of Redo

Redo log can be simply divided into the following two parts:

• The first is the redo log buffer in memory, which is volatile. In the memory,

• The second is the redo log file. ), is persistent and is saved on the disk.

When to write Redo?

The above picture simply reflects the writing process of Redo. Here is a detailed description Let’s talk about the timing of writing Redo:

• After the data page modification is completed, and before the dirty pages are flushed out of the disk, the redo log is written. Note that the data is modified first and the log is written later

• ##The redo log is written back to the disk before the data page

• aggregation Modifications to indexes, secondary indexes, and undo pages all need to be recorded in Redo logs.

The overall process of Redo

The following takes an update transaction as an example to grasp the redo log flow process from a macro perspective, as shown in the following figure:

• Step 1: First read the original data from the disk into the memory and modify the memory copy of the data

• The second step: Generate a redo log and write it into the redo log buffer, which records the modified value of the data

• The third step: When the transaction is committed, Refresh the contents of the redo log buffer to the redo log file, and use append writing to the redo log file

• Step 4: Regularly refresh the modified data in the memory to the disk

How does redo ensure the durability of transactions?

InnoDB is the storage engine of transactions. It achieves the durability of transactions through the

Force Log at Commit mechanism, that is, when the transaction is committed, the redo log buffer is first written to the redo log file. Persistence is not completed until the commit operation of the transaction is completed. This approach is also called Write-Ahead Log (pre-log persistence). Before persisting a data page, the corresponding log page in memory is persisted.

In order to ensure that each log is written to the redo log file, after each time the redo buffer is written to the redo log file, by default, the InnoDB storage engine needs to call a

fsync operation ,Because the redo log is opened without the O_DIRECT option, the redo log is first written to the file system cache. In order to ensure that the redo log is written to disk, an fsync operation must be performed. fsync is a system call operation. The efficiency of fsync depends on the performance of the disk. Therefore, the performance of the disk also affects the performance of transaction submission, that is, the performance of the database.
(The O_DIRECT option is an option in the Linux system. After using this option, the file will be directly IO operated and written directly to the disk without going through the file system cache)

Above The

Force Log at Commit mechanism mentioned is controlled by the parameter innodb_flush_log_at_trx_commit provided by the InnoDB storage engine. This parameter can control the strategy of redo log flushing to disk. Setting the parameter value also Users can be allowed to set non-persistence, as follows:

• When the setting parameter is 1, (default is 1), it means that the transaction must be called once when committing

  fsync Operation, the safest configuration, ensuring durability

• When the parameter is set to 2, only the write operation is performed when the transaction is committed. Only the redo log buffer is guaranteed to be written to the system's page cache, and no fsync operation is performed. , so if the MySQL database goes down, the transaction will not be lost, but the operating system may go down.

• When the parameter is set to 0, it means that redo will not be written when the transaction is submitted. Log operation, this operation is only completed in the master thread, and the fsync operation of redo log is performed every 1 second in the master thread, so the instance crash will lose transactions within 1 second at most. (The master thread is responsible for asynchronously refreshing the data in the buffer pool to the disk to ensure data consistency)

fsync and write operations It is actually a system call function, which is used in many persistence scenarios. For example, two functions are also used in Redis's AOF persistence. The fsync operation submits the data to the hard disk and forces the hard disk to synchronize. It will be blocked until the writing to the hard disk is completed and returns. Performing a large number of fsync operations will have a performance bottleneck, and write The operation returns immediately after writing the data to the system's page cache, and then relies on the system's scheduling mechanism to flush the cached data to the disk. The order is user buffer——>page cache——>disk.

In addition to the Force Log at Commit mechanism mentioned above to ensure the durability of transactions, the actual implementation of redo logs also depends on for mini-transaction.

How is Redo implemented in InnoDB? Connection to mini-transaction?

The implementation of Redo is actually closely related to mini-transaction. Mini-transaction is a mechanism used internally by InnoDB. Mini-transaction is used to ensure the data in the data page under concurrent transaction operations and when the database is abnormal. Consistency, but it is not part of the transaction.

In order for mini-transaction to ensure the consistency of data page data, mini-transaction must follow the following three protocols:

• The FIX Rules

• Write-Ahead Log

• Force-log-at-commit

The FIX Rules

When modifying a data page, you need to obtain the x-latch (exclusive lock) of the page. When acquiring a data page, you need the s-latch (read lock or shared lock) of the page. ) or x-latch, which holds the lock on the page until the operation to modify or access the page is completed.

Write-Ahead Log

Write-Ahead Log was mentioned in the previous explanation. Before persisting a data page, the corresponding log page in memory must first be persisted. Each page has an LSN (log sequence number), which represents the log sequence number (LSN occupies 8 bytes and is monotonically increasing). When a data page needs to be written to a persistent device, it requires less than the LSN of the page in the memory. The log is written to the persistence device first

So why do we have to write the log first? Is it possible to write data directly to disk without writing logs? In principle, it is possible, but it will cause some problems. Data modification will generate random IO, but the log is sequential IO. The append method writes sequentially, which is a serial method, so that the performance of the disk can be fully utilized.

Force-log-at-commit

This is the content of how to ensure the durability of transactions mentioned above. Let’s summarize it again here. The content corresponds. Multiple pages can be modified in a transaction. Write-Ahead Log can ensure the consistency of a single data page, but cannot guarantee the durability of the transaction. Force-log-at-commit requires that when a transaction is committed, it generates all mini -The transaction log must be flushed to disk. If after the log flush is completed, the database crashes before the pages in the buffer pool are flushed to the persistent storage device, then when the database is restarted, the integrity of the data can be ensured through the log.

Redo log writing process

The above figure shows the writing of redo log Process, each mini-transaction corresponds to each DML operation, such as an update statement, which is guaranteed by a mini-transaction. After the data is modified, redo1 is generated, which is first written into the private Buffer of the mini-transaction, and the update statement After completion, copy redo1 from the private Buffer to the public Log Buffer. When the entire external transaction is committed, the redo log buffer is flushed into the redo log file.

undo log

The definition of undo log

undo log mainly records the logical changes of data, in order to To roll back the previous operation when an error occurs, you need to record all the previous operations, and then roll back when an error occurs.

The role of undo log

undo is a logical log that has two functions:

• For transactions Rollback

• MVCC

I won’t say much about MVCC (Multi-version Concurrency Control) here. This article focuses on undo log for transaction rollback.

undo log only logically restores the database to its original state. During rollback, it actually does the opposite work. For example, an INSERT corresponds to a DELETE, and for each UPDATE, it corresponds to A reverse UPDATE puts back the line before modification. The undo log is used for transaction rollback operations to ensure the atomicity of the transaction.

The writing timing of undo log

• Record the undo log before the DML operation modifies the clustered index

• Modifications to secondary index records do not record undo logs

It should be noted that modifications to undo pages also need to record redo logs.

The storage location of undo

In the InnoDB storage engine, undo is stored in the rollback segment (Rollback Segment), each rollback segment records 1024 undo log segments, and undo is performed in each undo log segment. To apply for a page, before 5.6, the Rollback Segment was in the shared table space. After 5.6.3, you can use innodb_undo_tablespace sets the location of undo storage.

Types of undo

In the InnoDB storage engine, undo log is divided into:

• insert undo log

• update undo log

insert undo log refers to the undo log generated during the insert operation, because the record of the insert operation is only visible to the transaction itself. Invisible to other transactions. Therefore, the undo log can be deleted directly after the transaction is submitted, and no purge operation is required.

The update undo log records the undo log generated by delete and update operations. The undo log may need to provide an MVCC mechanism, so it cannot be deleted when the transaction is committed. When submitting, put it into the undo log list and wait for the purge thread to perform the final deletion.

Supplement: The two main functions of the purge thread are: cleaning up the undo page and clearing the data rows with the Delete_Bit identifier in the page. In InnoDB, the Delete operation in a transaction does not actually delete the data row, but a Delete Mark operation that marks the Delete_Bit on the record without deleting the record. It is a kind of "fake deletion", which is just marked. The real deletion work needs to be completed by the background purge thread.

Is undo log the reverse process of redo log?

undo log Is it redo? The reverse process of log? In fact, the answer can be derived from the previous article. Undo log is a logical log. When rolling back a transaction, it only logically restores the database to its original state, while redo log Log is a physical log, which records the physical changes of data pages. Obviously undo log is not the reverse process of redo log.

redo & undo summary

The following is the simplified process of redo log undo log to facilitate understanding of the two log processes:

假设有A、B两个数据，值分别为1,2.
1. 事务开始
2. 记录A=1到undo log
3. 修改A=3
4. 记录A=3到 redo log
5. 记录B=2到 undo log
6. 修改B=4
7. 记录B=4到redo log
8. 将redo log写入磁盘
9. 事务提交

In fact, In insert/update/delete operations, redo and undo record different contents and amounts. In InnoDB memory, the general sequence is as follows:

• Write undo redo

• Write undo

• Modify data page

• Write Redo

Summary

This article analyzes the The redo and undo logs are compiled with reference to some information books. Some places may not be clearly stated. If there is anything wrong, please point it out.

The above is the detailed content of Analysis of redo and undo in MySQL transactions (picture and text). For more information, please follow other related articles on the PHP Chinese website!