Redo Undo Log of InnoDB

InnoDB's Redo Undo Log this article introduces the MySQL database InnoDB Storage engine Redo Log roaming-Undo Log is to achieve the atomicity of transactions, in the MySQL database InnoDB Storage engine, the Undo Log is also used to implement multi-version concurrency control (MVCC for short ). -The Atomicity of a transaction. If an error occurs during execution, roll back to the state before the start of the transaction, just as this transaction has never been executed. -The principle of Undo Log is very simple. In order to satisfy the atomicity of transactions, data is first backed up to one place before any data is operated (this storage data backup is called Undo Log ). Then modify the data. If an error occurs or the user executes the ROLLBACK statement, the system can use the backup in the Undo Log to restore the data to the status before the start of the transaction. In addition to ensuring the atomicity of transactions, Undo logs can also be used to assist in transaction persistence. -Once a transaction's Durability is completed, all modifications made to the database by the transaction will be permanently saved to the database. To ensure durability, the database system records the modified data to persistent storage. -An Undo Log is used to simplify the process of atomic and persistent transactions. Assume there are two data types, A and B, with the values 1 and 2 respectively. A. the transaction starts. b. record A = 1 to undo log. c. modify A = 3. d. record B = 2 to undo log. e. modify B = 4. f. write the undo log to the disk. G. Write Data to the disk. H. Here is an implicit precondition for transaction commit: 'Data is read to the memory first, then the data in the memory is modified, and then the data is written back to the disk '. The atomicity and persistence can be ensured at the same time because of the following features: A. Undo log is recorded before data is updated. B. To ensure durability, data must be written to the disk before the transaction is committed. Data must be persistent as long as the transaction is successfully committed. C. The Undo log must be persistent to the disk prior to data. If the system crashes between G and H, the undo log is complete and you can roll back and forth the transaction. D. If the system crashes between A-F because the data is not persisted to the disk. Therefore, the data on the disk remains in the State before the transaction starts. Defect: before each transaction is committed, data and Undo Log are written to the disk, which may lead to a large number of disk IO, so the performance is very low. If data can be cached for a period of time, I/O can be reduced to improve performance. However, the transaction persistence will be lost. Therefore, another mechanism is introduced to achieve persistence, that is, the Redo Log.-Redo Log-principle is opposite to the Undo Log, and the Redo Log records the backup of new data. Before a transaction is committed, you only need to persist the Redo Log and do not need to persist the data. When the system crashes, the Redo Log is persistent even though the data is not persistent. The system can recover all data to the latest status based on the Redo Log Content. -The simplified process of Undo + Redo transactions is assumed that there are two data types: A and B. The values are 1, 2, respectively. a. the transaction starts. b. record A = 1 to undo log. c. modify A = 3. d. record A = 3 to redo log. e. record B = 2 to undo log. f. modify B = 4. g. record B = 4 to redo log. h. write the redo log to the disk. I. Features of transaction commit-Undo + Redo transactions A. To ensure durability, the Redo Log must be persistent before the transaction is committed. B. Data is cached in the memory instead of being written to the disk before the transaction is committed. C. Redo Log ensures transaction persistence. D. Undo Log ensures the atomicity of transactions. E. There is an implicit feature that data must be written later than redo log to persistent storage. -I/O performance Undo + Redo is designed to improve I/O performance. Although I/O for writing data is reduced by caching data, new I/O is introduced, that is, the I/O for writing Redo logs. If the IO performance of the Redo Log is poor, it cannot improve the performance. To ensure good IO performance of Redo logs, the Redo Log Design of InnoDB has the following features: A. Try to keep Redo logs stored in A continuous space. Therefore, when the system starts for the first time, the space of the log file is fully allocated. Redo logs are recorded in sequential append mode, and performance is improved through sequential IO. B. Write logs in batches. Logs are not directly written to files, but are first written to redo log buffer. when the log needs to be refreshed to the disk (such as transaction commit), many logs are written to the disk together. c. concurrent transactions share the storage space of Redo logs. Their Redo logs are recorded in the order of statement execution in turn to reduce the space occupied by logs. For example, the record content in the Redo Log may be as follows: Record 1: <trx1, insert…> Record 2: <trx2, update...> Record 3: <trx1, delete…> Record 4: <trx3, update...> Record 5: <trx2, insert...> D. Because of C, when a transaction writes the Redo Log to the disk, it also writes the logs of other uncommitted transactions to the disk. E. Only sequential append operations are performed on the Redo Log. When a transaction needs to be rolled back, its Redo Log records will not be deleted from the Redo Log. -Recovery (Recovery)-as mentioned earlier, uncommitted transactions and rolled back transactions also record the Redo Log. Therefore, during Recovery, these transactions require special processing. there are two different recovery policies:. only committed transactions are redone during restoration. B. During recovery, redo all the transactions, including uncommitted transactions and rolled back transactions. Then, roll back the uncommitted transactions through the Undo Log. -InnoDB Storage engine recovery mechanism MySQL database InnoDB Storage engine uses the B policy, the InnoDB Storage engine's recovery mechanism has several features: A. When Redo Log is redone, transactions are not concerned. No BEGIN, COMMIT, or ROLLBACK actions are performed during restoration. It does not care which transaction each log belongs. Although transaction ID and other transaction-related content are recorded in the Redo Log, the content is only part of the data to be operated. B. To use the B policy, the Undo Log must be persistent and the corresponding Undo Log must be written to the disk before the Redo Log is written. This association between Undo and Redo logs makes persistence complex. To reduce the complexity, InnoDB regards the Undo Log as data, so the operations to record the Undo Log will also be recorded in the redo log. In this way, the undo log can be cached like the data, instead of being written to the disk before the redo log. The Redo Log containing the Undo Log operation looks like this: Record 1: <trx1, Undo log insert <undo_insert…> Record 2: <trx1, insert...> Record 3: <trx2, Undo log insert <undo_update...> Record 4: <trx2, update...> Record 5: <trx3, Undo log insert <undo_delete…> Record 6: <trx3, delete…> C. At this point, there is another problem that has not been clarified. Since Redo is not transactional, wouldn't the transaction be rolled back again? This is indeed the case. At the same time, Innodb also records the operations during transaction rollback to the redo log. In essence, the rollback operation also modifies the data. Therefore, the data operations during rollback are also recorded in the Redo Log. The Redo Log of a rolled back transaction looks like this: Record 1: <trx1, Undo log insert <undo_insert…> Record 2: <trx1, insert A…> Record 3: <trx1, Undo log insert <undo_update...> Record 4: <trx1, update B...> Record 5: <trx1, Undo log insert <undo_delete…> Record 6: <trx1, delete C…> RECORD 7: <trx1, insert C> Record 8: <trx1, update B to old value> record 9: <trx1, delete A> when A rolled back transaction is restored, redo is performed before undo, so data consistency is not damaged. -Redo: recv_recovery_from_checkpoint_start () Undo: recv_recovery_rollback_active () Redo Log of Undo Log: trx_undof_page_add_undo_rec_log ()