“This is the 10th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

InnoDB is now MySQL with more storage engine, the use of its reasons, personal feel because it supports transactions, in the project, basically changes are used for data transaction, in the same transaction, will modify more tables and a list if a failure, you need to roll back the entire transaction? How does MySQL roll back updated data? To ensure atomicity of transactions, a transaction operation will either all succeed or all fail. It is well known that when MySQL updates data, it first logs the original data to the Undo Log file, then changes the cached pages in the BufferPool, and then logs the Redo Log. When the time is right, the dirty pages in the BufferPool are flushed to disk. If the transaction needs to be rolled back in the middle of the transaction, the operation that has been performed in the buffer pool buffer page must be rolled back. Therefore, when the data is rolled back, the undo log needs to find the corresponding rollback log to restore the current data to the data before the transaction.

UndoLog rolls back data

The basic principle of UndoLog rollback data is to record the old data during the data operation, and then generate the opposite statement for the current operation when the rollback is needed to restore the data.

  • INSERT statement: In the undo log, the rollback log of this operation must have a primary key and a corresponding DELETE operation to rollback the INSERT operation. Therefore, we use the increment primary key. If the INSERT fails, the next primary key will not be the primary key of the rollback
  • DELETE statement: Records the deleted data and, if rollback is required, performs an INSERT operation to INSERT the data back
  • UPDATE statement: Record the value before the UPDATE, UPDATE it during the rollback, and UPDATE the old value back
  • SELETE statement: Data is not modified and logs are not recorded

Rollback log format

Each type of log has a specific format. What does an undo log look like using INSERT statements?

  • The start position of the log
  • The length and value of each column of the primary key into which the data is inserted, its length, the exact value, and if no primary key is set, ROW_ID will be used as the hidden column primary key
  • Table ID: indicates the ID of a data table, which can be interpreted as the table name
  • Undo log Indicates the log number
  • The type of the undo log is TRX_UNDO_INSERT_REC
  • End position of a log