Learn about MySQL's undo log

When I first learned about MySQL, I saw the term undo log, but I didn't know what undo log was, so I had to figure out what undo log was before I could continue to see some MySQL data. What is the principle of undo log? How does it match the other features of the database?
This note is only analyzed in principle and does not involve specific implementation methods.

What is undo log?

Undo log is a log in which the reverse operation of the database is recorded.
If the contents of the database as a state machine, then the data write operation is to modify the state Machine command, and undo corresponds to modify the state machine Reverse command.
So theoretically every command that is modified for the state machine produces a corresponding undo log, so that when the transaction is rolled back, the state machine can be modified to the original appearance of the transaction.
If we have a business:
CREATE TABLE table1 (c1 int);
BEGIN TRANSACTION;
Insert INTO table1 (100); The database executes this command should produce an undo log, the statement for the state machine to revert to the original unmodified state, undo should be delete table1 where c1 = 100;
Insert INTO table1 (200); Undo should be the delete table1 where c1 = 200;
Update table1 Set C1 = C1 = 200; Undo:update table1 Set C1 = where C1 = 300
Rollback

Why do I have undo log, or what does undo log solve?

In fact, this question is not good, because undo is designed, there is no direct factor that must have the undo log, their more familiar with the Postgres there is no undo log. Here's a comparison with Postgres to see why MySQL has undo
MySQL and Postgres are based on MVCC, but MySQL and postgres are not the same for MVCC implementations.
Why have MVCC? Because MVCC and its predecessor lock based, can achieve read-write conflict this feature, if there is no MVCC, a transaction read one data, another transaction write the same data, the two transactions can not be executed concurrently, The latter transaction must wait until the previous transaction has completed execution, but with MVCC, the two transactions can be executed concurrently, and the performance of the large, improved database, so that the mainstream of the database is now implemented MVCC.
But each data is slightly different for MVCC implementations:
where the implementation of MySQL has undo log, and the implementation of Postgres does not have undo log. But Postgres has clog inside, and clog records the state of each transaction. All versions of each line of the
Postgres are stored together, allowing the version that is generated by the aborted transaction to be retained, the latest version of MySQL is saved in the tablespace, and the historical version is saved in the undo log. MySQL in the transaction rollback, should be synchronous use of undo log to revert to the latest version of the state, while deleting the corresponding transaction generated by the undo log, and Postgres through the asynchronous Vaccum, to the aborted transaction generated by the historical version to delete. The most important point in the
MVCC is the line visibility judgment. Here is a simple description of the Postgres's line visibility and the MySQL line visibility judgment.

• both MySQL and Postgres assign a XID to each transaction, which is a single-height increment within the database, uniquely identifying a transaction and defining the order of transaction-related events in the database. (The transaction-related event here mainly refers to the BEGIN TRANSACTION)
• MVCC inside the snapshot read requires every transaction (or the first statement, Because the different decision of the transaction isolation level is whether a transaction is a snapshot or a statement, a snapshot) has a snapshot, and both MySQL and postgres choose to use the list of currently active transactions as snapshot. At the beginning of each transaction (at the beginning of a statement), take a look at the XID of all transactions that have started but have not ended for the current entire system.
• MySQL and Postgres record two fields for each version of each row x_min, X_max (MySQL does not know what to call, but it should be about the same), X_xmin represents the creation of this version of the corresponding transaction XID, X_ Max represents the XID
that deletes the transaction for this version.

• to determine if a version is visible, Postgres requires the following information:
  • Snapshot (active transaction list)
  • clog for the current transaction, you can query the status of the current transaction according to XID, and the transaction has three states Pendi Ng, committed, aborted
  • on a version of X_min, X_max
  • First, postgres to see first, x_min whether the corresponding XID can be found in snapshot, if you can find, explain the corresponding When snapshot started, the transaction that created this version was not finished and returned directly to the version that is not visible
  • if x_min is not found in snapshot, the transaction that created this line is ended when the snapshot is taken, and the transaction ends in two states. Aborted, committed
    • by querying clog, see if x_min corresponds to a transaction is aborted, if it is aborted, the transaction that created this line has been rolled back, the row is not visible
    • if the transaction is Committed: Indicates that the action to create the line has ended before snapshot, and this time also needs to be divided:
      • The row's X_max is empty, the line is the most recent version, and the row is directly returned to the row is visible
      • the line has X_ma X, but the X_max corresponding transaction is also in the snapshot list, indicating that when the snapshot is taken, the transaction that deleted the row has not ended, the operation corresponds to snapshot is not visible, return the row is visible.
      • The row has X_max, and X_max is not in the snapshot list, but X_max corresponds to clog, which indicates the transaction aborted for the delete operation, and returns the row is visible
      • the row has aborted AX, and X_max is not in the snapshot list, but X_max corresponds to committed, which indicates that the operation to delete the row was successful before snapshot, returning the row is not visible.

• The line visibility of MySQL should be simpler because only the version that was successfully submitted is saved in the historical version
  • Several parameters of row visibility judgment: Snapshot, X_min, X_max for the corresponding transaction (statement)
  • X_min exists in snapshot, the row is not visible
  • X_min does not exist in snapshot, there is no x_max, the row is visible. (The line is the latest version of UJN8)
  • X_min does not exist in snapshot, and X_max does not exist in the snapshot list, and the row is not visible, indicating that the delete operation was completed before snapshot.

When to use the Undo

As mentioned above, undo this action occurs when rollback a transaction.
Also occurs at the time of data recovery, because some transactions are executing the process, the data crash, then after the database restart is done redo, the corresponding non-committed transactions to undo the action.
At the same time, undo log records the historical version, read the historical version, but also from the undo log to read.

All of the above about MySQL is from the Internet to see the data obtained, understanding is not necessarily correct, the following will be based on the MySQL code to verify.

Learn about MySQL's undo log