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

Undo log Version chain

Undo log is used to record the operation data before the transaction rollback. Each data has two hidden fields, trx_ID and roll_pointer.

  • Trx_id: indicates the ID of the transaction that last updated this data
  • Roll_pointer: points to the undo log before the transaction was generated

With this field, the Undo log does not have only one log for the same data, but each transaction is logged, which is linked by a linked list. Let’s take a look at the undo log version chain through a specific example.

At the beginning, transaction A (transaction ID: 10) inserts A data value of A, then the hidden field of this data and the undo log pointing to it are shown as follows:

The value of the inserted data is A, the transaction di is 10, and since it is the first insert, the version chain roll_pointer points to an empty undo log. Transaction B then modiates the value to B and the transaction ID is 20. An undo log is generated before the update and roll_pointer points to the actual undo log rollback log, as shown below:

Transaction ID = 30, transaction ID = 30, transaction ID = 30, transaction ID = 30

As can be seen from the above schematic diagram, when multiple transactions are executed in sequence, each transaction will update the hidden fields TXr_id and roll_pointer after modifying the data, and at the same time, the undo log logs that have been modified are connected in series through the roll_pinter pointer, forming an important version chain.

Multi-version link ReadView mechanism

When a transaction is executed, a ReadView is generated with four important things:

  • M_ids: what transactions are running in MySQL at this time that have not been committed
  • Min_trx_id: the smallest value in m_IDS;
  • Max_trx_id: specifies the maximum transaction ID of the next mysql transaction to be generated
  • Creator_trx_id: indicates the ID of the current transaction

Following the above example, there is already a row of data in the database, transaction ID is 30, the initial situation is as follows:

At this point, two transactions are executed concurrently, transaction A (ID =40) and transaction B (ID =50). Transaction B updates data and transaction A queries data, as shown in the figure below:

M_id = min_trx_id=40, max_trx_id=60, min_trx_id=40, max_trx_id=60, min_trx_id=40, max_trx_id=60, min_trx_id=40, max_trx_id=60 Creator_trx_id =45 creator_trx_id=45 creator_trx_id=45 creator_trx_id= 30 In this case, it is considered that before the transaction starts, the data to modify this row of data has been submitted, and this row of data can be queried. As follows:

At this time, transaction B also starts execution, changes this row of data to the value B, and sets txr_id to its own ID (50). At the same time, roll_pointer points to the undo log before modification, and transaction B commits the transaction successfully, as shown in the figure below:

Txt_id =50, txr_id is greater than min_txr_id=40 in ReadView and smaller than max_txr_id= 60 in ReadView, indicating that this data was modified when transaction A started. Txr_id =50 in m_ids (40,50), txr_id=50 in m_ids (40,50)

Transaction A will find the latest undo log with trx_id=30 according to roll_pointer. < min_txr_id=40 in ReadView, indicating that the version of this data was committed before transaction A started. Then assume that transaction A changes the value of this row of data to A after querying the data, where trx_id=40, and save the snapshot chain of the value before modification, as shown in the figure below:

Trx_id =45; trx_id=45; trx_id=45; trx_id=45;

Then, during transaction A, another transaction D, with transaction ID 60, updates the data value to D, and commits successfully. The undo log version chain at this time is shown in the following figure:

At this time, when transaction A queries again, it finds that the transaction ID is larger than its own, which means that the data is not allowed to be queried after transaction A starts and another transaction updates the data. At this time, it will follow the undo log version chain to find the data that value A updates by itself.