Shulou(Shulou.com)06/01 Report--

This article mainly introduces MySQL redo and undo simple analysis, I hope you can supplement and update some knowledge, if there are other problems to understand, you can continue to pay attention to my updated article in the industry information.

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

What are redo logs and undo logs?

How does redo ensure transaction persistence?

Is undo log the inverse of redo log?

Types of Redo

The redo log is used to ensure the persistence of transactions, i.e. 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, Redo is mostly a physical journal that records physical changes to data pages. The logical Redo log is not to record the actual modification of the page, but to record a type of operation to modify the page, such as when creating a new data page, you need to record the logical log. About logical Redo log involves more low-level content, here we only need to remember that in most cases, Redo is a physical log, DML page modification operations, all need to record Redo.

Redo's role

Redo log is mainly used for database crash recovery.

Composition of Redo

Redo log can be divided into two parts:

One is the redo log buffer in memory, which is volatile and in memory.

The second is redo log file, which is persistent and stored on disk.

When to Write Redo?

The above picture simply reflects the writing process of Redo. Here is the timing of writing Redo:

After the data page modification is complete, the redo log is written before the dirty page is flushed off disk. Note that first modify the data, then write the log

redo logs are written back to disk before data pages

Redo logs are required for changes to aggregated indexes, secondary indexes, and undo pages.

Redo's overall process

Take an update transaction as an example, grasp the redo log flow process macroscopically, as shown in the following figure:

Step 1: Read the original data from disk into memory and modify the memory copy of the data

Step 2: Generate a redo log and write it to redo log buffer, which records the modified value of the data.

Step 3: When the transaction commits, refresh the contents of redo log buffer to redo log file, and append to redo log file.

Step 4: Periodically flush modified data from memory to disk

How does redo ensure transaction persistence?

InnoDB is a storage engine for transactions. It implements transaction persistence through the Force Log at Commit mechanism, that is, when a transaction is committed, the redo log buffer is first written to the redo log file for persistence, and the transaction is not completed until the commit operation is completed. This practice, also known as Write-Ahead Log persistence, persists log pages in memory before persisting a data page.

In order to ensure that each log is written to redo log file, by default, the InnoDB storage engine needs to call fsync operation once after each redo buffer is written to redo log file. Because redo log is open and there is no O_DIRECT option, redo log is written to file system cache first. To ensure that redo logs are written to disk, an fsync operation must be performed. Fsync is a system call operation, and its efficiency depends on disk performance, so disk performance also affects transaction commit performance, that is, database performance.

(O_DIRECT option is an option in Linux system. After using this option, direct IO operation is performed on files without passing through file system cache and directly writing to disk)

The Force Log at Commit mechanism mentioned above is controlled by the parameter innodb_flush_log_at_trx_commit provided by the InnoDB storage engine. This parameter can control the policy of redo log flushing to disk. Setting this parameter value can also allow users to set non-persistent situations, as follows:

When the parameter is set to 1 (default is 1), it means that the fsync operation must be invoked once when the transaction is committed. The safest configuration ensures persistence.

When the parameter is set to 2, only write operation is performed when the transaction is submitted, and only redo log buffer is written to the page cache of the system without fsync operation. Therefore, if MySQL database is down, transactions will not be lost, but transactions may be lost if the operating system is down.

When the parameter is set to 0, it means that the redo log operation is not performed when the transaction is committed. 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. Therefore, the instance crash loses transactions within 1 second at most. (master thread is responsible for asynchronously flushing data in the buffer pool to disk to ensure data consistency)

The fsync and write operations are actually system call functions that are used in many persistence scenarios, such as Redis 'AOF persistence. The fsync operation commits the data to the hard disk, forcing the hard disk to synchronize, and will block until the write to the hard disk is completed. There is a performance bottleneck when a large number of fsync operations are performed, while the write operation writes the data to the page cache of the system and returns immediately. Later, the cached data is flushed to the disk by relying on the scheduling mechanism of the system. The order is user buffer--> page cache-->disk.

In addition to the Force Log at Commit mechanism mentioned above to ensure transaction persistence, the implementation of redo logs actually depends on mini-transaction.

How is Redo implemented in InnoDB? Contact with mini-transaction?

The implementation of Redo is closely related to mini-transaction, which is a mechanism used internally by InnoDB to ensure the consistency of data in data pages under concurrent transaction operations and database exceptions, but it is not a transaction.

In order for a mini-transaction to guarantee the consistency of the data in the data page, the mini-transaction must follow three protocols:

The FIX Rules

Write-Ahead Log

Force-log-at-commit

The FIX Rules

To modify a data page, you need to obtain an x-latch(exclusive lock) for that page, and to acquire a data page, you need an s-latch(read lock or shared lock) for that page, or x-latch, which holds the lock on that page until the modification or access operation is complete.

Write-Ahead Log

Write-Ahead Log was mentioned in the previous explanation. Before you can persist a data page, you must first persist the corresponding log page in memory. Each page has an LSN(log sequence number), which represents the log sequence number (LSN occupies 8 bytes and increases monotonically). Before a data page needs to be written to a persistent device, logs smaller than the LSN of the page in memory are required to be written to the persistent device first.

Why do I have to write a journal first? Can you write data directly to disk without writing logs? In principle, it is possible, but it will cause some problems. Data modification will produce random IO, but the log is sequential IO. Append mode is sequential writing, which is a serial mode, so as to make full use of disk performance.

Force-log-at-commit

This is also the content of how to ensure the persistence of transactions mentioned earlier, which is summarized here again, echoing the above content. Multiple pages can be modified in a transaction. Write-Ahead Log can guarantee the consistency of a single data page, but it cannot guarantee the persistence of the transaction. Force-log-at-commit requires that when a transaction is committed, all mini-transaction logs generated by it must be flushed to disk. If the database is down before the pages in the buffer pool are flushed to the persistent storage device after the log refresh is completed, the database can be restarted to ensure the integrity of the data through logs.

Redo log write flow

The above figure shows the process of writing redo logs. Each mini-transaction corresponds to each DML operation, such as an update statement, which is guaranteed by a mini-transaction. After modifying the data, redo1 is generated. First, it is written into the private Buffer of mini-transaction. After the update statement ends, redo1 is copied from the private Buffer to the public Log Buffer. When the entire external transaction commits, the redo log buffer is flushed back into the redo log file.

undo log

Definition of undo log

The undo log mainly records logical changes in the data. In order to roll back previous operations when an error occurs, it is necessary to record all previous operations and then roll back when an error occurs.

The role of undo log

undo is a logical log that has two functions:

Rollback for transactions

MVCC

About MVCC(multi-version concurrency control) content here is not much to say, this article focuses on undo log for transaction rollback.

The undo log, which only logically restores the database to its original state, actually does the opposite when rolling back, such as an INSERT for a Delete, and for each UPDATE for an opposite UPDATE, putting back the rows before the modification. The undo log is used for transaction rollback operations to ensure atomicity of transactions.

Time to write undo log

Record undo log before DML operation modifies cluster index

The secondary index records changes, not undo logs

It should be noted that changes to the undo page also need to be recorded in the redo log.

Storage location of undo

In the InnoDB storage engine, undo is stored in rollback segments, each rollback segment records 1024 undo log segments, and undo page requests are made in each undo log segment. Before 5.6, Rollback segments are in shared tablespaces. After 5.6.3, undo storage locations can be set by innodb_undo_tablespace.

Types of undo

In the InnoDB storage engine, undo logs are divided into:

insert undo log

update undo log

The insert undo log refers to the undo log generated in the insert operation, because the record of the insert operation is visible only to the transaction itself and not to other transactions. Therefore, the undo log can be deleted directly after the transaction is committed, without the need for a purge operation.

The update undo log records undo logs generated by delete and update operations. The undo log may need to provide 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 make the final deletion.

Supplement: The two main roles of the purge thread are to clean up undo pages and clear data rows with Delete_Bit tags in pages. In InnoDB, the Delete operation in a transaction is not actually deleting the data row, but a Delete Mark operation that identifies Delete_Bit on the record without deleting the record. It's a kind of "fake delete" that just makes a mark, and the real delete work needs to be done by the background purge thread.

Is undo log the inverse of redo log?

Is undo log the inverse of redo log? In fact, the answer can be obtained from the previous paragraph, undo log is a logical log, when the transaction is rolled back, only the database is logically restored to the original appearance, and redo log is a physical log, recording the physical changes of the data page, obviously undo log is not the reverse process of redo log.

redo & undo summary

The following is a simplified procedure for redo log + undo log, which is easy to understand the process of the two logs:

Suppose there are two data A and B, with values 1 and 2.1 respectively. Start of Business 2. Record A=1 to undo log3. Amendment A=34. Record A=3 to redo log5. Record B=2 to undo log6. Amendment B=47. Record B=4 to redo log8. Write redo log to disk 9. transaction commits

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

Write undo redo

Write undo

Modify data page

Write Redo

summary

This article analyzes the redo and undo logs in transactions, referring to some information books, and may be unclear in some places. If there is anything wrong, please point it out.

The above is the MySQL transaction redo and undo analysis (graphic) details, more please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.