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 main function of Redo log is for database crash recovery
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.
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
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 afsync 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)
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:
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.
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!