Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the two stages of submission involved in the data editing process in MySQL. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Do you know the two-phase commit in the MySQL database? This is a brief talk about the two-phase commit in the MySQL database, which occurs during data changes (updates, deletions, additions, etc.). The two-phase commit process involves two log systems in the MySQL database: redo logs and binlog files.

The redo log has been introduced before, so it will not be introduced any more. We will simply talk about the binlog file. Binlog is a binary file provided by the MySQL server layer, so all storage engines can use the binlog function. Binlog is an appended logic log that records the original logic of the execution statement. When the file is written to the specified size, it will switch to the next file and continue to write, and will not overwrite the previously written log file.

Binlog log files are mainly used for data recovery and data synchronization among servers in a cluster environment. In our work, we mistakenly delete data or tables, and if we need to restore them, we use binlog logs to restore them, so binlog logs are an important module in MySQL databases.

After knowing these two logs, let's focus on the two-phase commit of the MySQL database. We said earlier that the two-phase commit takes place during the data change. In order to better understand the two-phase commit, we use an update command to explain it. The update statement is as follows:

Mysql > update T set c=c+1 where id=2

Assuming that the value of this row of data c in id=2 is 0 before it is not updated, how is this update statement executed within the MySQL database? In the following execution flowchart:

Update statement execution process

As you can see from the flowchart, under the InnoDB storage engine, the execution of a update statement within MySQL will probably go through the following five steps:

1. The actuator first finds the engine to get the row of id=2 data, and if the data page of the ID=2 row is already in memory, it will be returned directly to the executor; otherwise, it will need to be read into memory from the disk first, and then returned.

2. The executor gets the row data given by the engine, adds 1 to this value, for example, it was N, and now it is Number1, gets a new row of data, and then calls the engine interface to write the new data.

3. The engine updates this new line of data to memory and records the update operation to redo log, when redo log is in prepare state. The executor is then informed that the execution is complete and the transaction can be committed at any time.

4. The executor generates the binlog of this operation and writes the binlog to disk.

5. The executor calls the commit transaction interface of the engine, and the engine changes the redo log just written to the commit (commit) state, and the update is completed.

In these five steps, pay attention to the part marked in red. The redo log is divided into two stages: prepare and commit. This process is called two-phase commit. Can we not split the redo log into two-step commit?

We can use the inverse method to prove that if two-phase commit is not used, there are two cases, one is to submit the redo log before the binlog log, and the other is to submit the binlog log first and then the redo log. Let's take a look at what's wrong with these two commit methods.

Write redo log first and then binlog. Suppose that the MySQL process restarts abnormally when the redo log is finished and the binlog is not finished. An exception occurred in the update during this process. The redo log can ensure the persistence of the data when an exception occurs in the database. After the redo log data is recovered after startup, the value of c is 1, but the binlog has not been written, so the update statement is not recorded in the binlog log file. If the binlog log file is used to restore the temporary library, the value of c of the recovered line of id = 2 is 0. It is not consistent with the value of the original library.

Write binlog first and then redo log. If the binlog is finished and the redo log is not written, the system crashes, and after the system restart, the c value of this line of id=2 data is still 0, but this update is recorded in the binlog log file. If you need to use the binlog log file to restore the temporary library, then the value of c of this line of id=2 data is 1, which is inconsistent with the value of the original library.

From these two assumptions, we can see that no matter which log file is submitted first, there may be data inconsistencies. The log file two-stage commit technology solves the problem of data inconsistency between redo logs and binlog log files, thus ensuring data consistency during data recovery.

These are the two stages of submission involved in the data editing process of MySQL shared by the editor. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.