Undo log in MySQL

Concept introduction:

We know that the redo log in MySQL records the behavior of transactions. When the server crashes, data can be restored by redoing transactions. However, sometimes transactions need to be rolled back, that is, we need to know what a transaction looked like before it became the current situation. In this case, the undo log comes in handy. In other words, the undo log is used to restore the data to the state before the modification. Therefore, when modifying the database, we need to know that redo logs and undo logs will be generated in this process.

Storage location:

We also know that redo logs are generally placed in redo log files, commonly known as ib_log, while undo logs are stored in a "segment" inside the database. We talked about this concept in the article on August 21. Those who have forgotten can go back and take a look. The undo log segment is located in the shared tablespace.

Rollback operation:

Now, we already know the concept of undo, which is actually an area in the shared tablespace. Its main function is to restore the transaction to the state before the modification is performed. However, the recovery situations are generally divided into two types, one is logical recovery and the other is physical recovery. What needs to be emphasized here is that undo recovery is logical recovery, that is, if you insert 1 million pieces of data, causing InnoDB to allocate a new data page to store this data, then when the transaction is rolled back, the function of undo is not to recycle the data page, but to change these insert operations into delete operations to perform a rollback. During this process, the size of the shared tablespace does not change. In addition, the undo log converts delete operations into insert operations and update operations into reverse update operations.

Deletion method:

Another point to note is that the process of writing undo logs in the transaction shared tablespace also requires writing redo logs. Once a transaction is committed, it means that the persistence of the transaction takes effect, and the undo log is no longer needed. However, InnoDB does not delete the undo log directly, but puts it in a linked list of undo logs. When to delete it depends on the MySQL purge thread. This is done to avoid other transactions from needing to use the undo log to obtain the previous version of this record.

Space Allocation:

In actual operation, a database instance may perform many transactions. If we allocate a separate log data page to store undo for each transaction, it will waste storage space. Let's do a simple calculation. Assuming that the TPS of an application is 1000, an undo page is allocated for each transaction. The size of a data page is 16kb, and 60*1000 data pages will be generated in 1 minute. Then the space required for one minute is about 960M of disk space, which is obviously unreasonable. Therefore, in innodb, undo pages can be reused. The specific method is that when the transaction is committed, the undo page is put into the linked list, and then it is determined whether the used space of the undo page is less than 75%. If so, the undo page can be reused, and the subsequent undo log can be appended to the back of the current undo log. Of course, we can use show engine innodb status to view the number of undo logs in the linked list, but we will not demonstrate this here.

The above is the detailed content of the undo log in MySQL. For more information about MySQL undo log, please pay attention to other related articles on 123WORDPRESS.COM!