Interviewer: Let's talk about MySQL's two-phase submission mechanism.

This article will take you to understand the two-phase submission mechanism of MySQL, introduce the redo log and bin log, and see how they cooperate to complete the two-phase submission. I hope it will be helpful to everyone!

MySQL ensures the logical consistency of redo log and bin log through the two-stage submission mechanism, thereby ensuring that data is not lost and the data in the master-slave database is consistent.

Speaking of two-phase submission, we have to introduce redo log and bin log first.

redo log

redo log is a redo log, which is a log unique to the InnoDB engine (some interviewers often ask about this).

What does redo log mainly do?

Take updating data as an example. We know that MySQL data is stored on the disk. If every time the data is updated, it goes to the disk to find the data to be updated and performs the update operation. The IO cost will be is very high.

It’s okay if it’s a solid-state drive, but if it’s a mechanical hard drive, then MySQL’s update performance simply cannot meet our business needs.

So, MySQL uses a technology called WAL, Write-Ahead Logging.

When updating data, the update operation (that is, what modifications are made on a certain data page) is first written to the redo log, and then the memory is updated. This update operation is completed. MySQL will flush redo log operation records to disk when the server is idle to maintain data consistency.

It should be noted that although the redo log is also a file on the disk, because the operation is written sequentially, the performance is very high.

Of course, the redo log also has a size limit, and unlimited writing is impossible.

The above figure is an example. Four redo logs are configured. Write pos represents where the current record is written, and check point represents a As it advances, it will continue to move forward and erase data to ensure that the redo log can continue to be written.

Of course, before erasing data, the redo log records will be flushed to disk.

Through redo log, you can ensure that even if MySQL restarts abnormally, the data will not be lost (because redo log is a physical log and can be replayed). This feature is called crash-safe.

bin log

Bin log is a log provided by MySQL Server, called archive log. All engines can use bin log.

What is the difference between bin log and redo log?

1. The providers of these two logs are different: bin log is provided by MySQL Server, and redo log is unique to the InnoDB engine.

2. The redo log mainly records the modifications made to a certain data page. The bin log records the original logic of the statement, such as updating a certain field of a certain row.

3, redo log is written in a loop, and the data will be overwritten. Bin log is appended. When one file is full, the next file is written.

Two-phase submission

After introducing redo log and bin log, let’s take a look at how they cooperate to complete two-phase submission.

The above picture is a process of updating data. You can see that before updating a piece of data, MySQL will first load the data into the memory, and then Update the memory and start writing redo log.

At this time, the redo log is in the prepare state. After the bin log is written, and then the transaction is submitted, the update operation of this record is completed.

redo log prepare -> Write bin log -> redo log commit, this process is called two-stage commit.

Let’s analyze the benefits of using two-stage submission.

Scenario 1: When the redo log is in the prepare state, if writing to the bin log fails, the update fails. At this time, the redo log has no commit and the bin log has no records. The status of the two is consistent and there is no problem. .

Scenario 2: When the redo log is in the prepare state, the bin log is written successfully, but the commit fails due to downtime. At this time, the bin log generated a record, the redo log was not written successfully, and the data was temporarily inconsistent.

But don't worry, when MySQL restarts, it will check the records in the prepare state in the redo log. In the redo log, a field called In fact, if the writing is not successful, the submission will be given up.

Through this mechanism, the consistency of redo log and bin log is guaranteed.

Summary

The reason why there are both redo log and bin log in MySQL is because the bin log is an archive log provided by MySQL Server, which itself does not have crash-safe capability. The redo log itself does not have the ability to archive. It is a log written in a loop.

MySQL ensures data consistency by integrating these two logs and using a two-stage submission mechanism.

Writing is not easy, thank you for your likes and attention.

[Related recommendations: mysql video tutorial]

The above is the detailed content of Interviewer: Let's talk about MySQL's two-phase submission mechanism.. For more information, please follow other related articles on the PHP Chinese website!