The problem

  1. What does undo log do?
  2. Undo log Version chain
  3. Where is the undo log stored? When will it be released?

Support for rollback and MVCC

We know that InnoDB supports transactions. So one of the functions of the transaction is to support rollback. Transactions can be designed to add, delete, modify, and so on. What about the rollback and implementation?

  • If a new record is added to the transaction, we record the new primary key and delete it during the rollback.
  • If a record is deleted from the transaction, we record all of the recorded information and insert it as is during the rollback.
  • If a transaction changes a record, then the original information is recorded, and the original information is updated back.

Undo log records this information for rollback. So why MVCC also rely on undo log to implement? You may have heard that a clustered index has a hidden field called row_id for each record, which acts as an internal auto-increment primary key if no primary key is specified. There are also two fields for each record: trx_id and roll_pointer. These two fields are used to implement the MVCC function.

  • trx_id: Changes the transaction ID of the current record.
  • roll_pointer: Indicates only one undo log.

Each undo log record also has the trx_ID and roll_pointer fields.

Preparations:

CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `age` int(11) NOT NULL, `addresss` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `index_age` (`age`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; INSERT student VALUES (1, '1', 1, 'SSSS), (2,' zhang 2 ', 2, 'SSSS);Copy the code

The contents of the cluster index are similar to the following:

id name age address trx_id roll_pointer
1 1 1 ssss 10 Insert_undo_log address
2 A second 2 ssss 10 Insert_undo_log address

Undo log is similar to the following:

id name age address trx_id roll_pointer undo id
1 1 1 ssss 10 empty 0
2 A second 2 ssss 10 empty 1

The undo IDS are sequential increments in the same transaction.

update set age=10 where id = 1; Update statement 1update set age=20 where id = 1; # update statement 2Copy the code
id name age address trx_id roll_pointer undo id
1 1 1 ssss 20 Point to the previous insert log address 0
1 1 10 ssss 21 Last undo log address 0

A few notes:

  • The insert log is reclaimed and released after the last insert transaction.
  • Update statement 1, roll_pointer will save the last insert log. Save the information before the update.
  • Because of automatic commit transaction mode, 2 updates will result in 2 different TRx_id.
  • The actual update logThe information structure is much more complexAnd in theUpdate primary key, and do not update primary keyIn the case. I’m not going to go into detail here.
  • The chain structure formed by roll_pointer is the version chain.

Where is the undo log stored?

After MYSQL 5.6, separate table Spaces can be allocated for undo log. The default number of tablespaces is 2. You can set innodb_undo_TABLespaces to increase the number. Each tablespace is divided into rollback segments. For the number of rollback segments, see Innodb_undo_logs. Each rollback segment holds the Undo log page. If the undo log transaction has been executed or rolled back. Then the corresponding undo log space can be reclaimed. If there is a long transaction that has not been committed or rolled back, the undo log will get bigger and bigger. In severe cases, the disk space of the server will be used up. Therefore, if the disk space usage of the server increases rapidly, you can also check whether long transactions are not committed and undo log occupies a large disk space.

conclusion

Undo log has two functions:

  • Guarantee atomicity of transactions. All update statements in a transaction are either executed or rolled back. All update statements of the same transaction can be rolled back by the undo log with the same TRx_id.
  • Provides version chain, supports MVCC. The Undo Log chain provides the underlying physical foundation for implementing MVCC. To implement MVCC, you also need to use Read View. We’ll talk about that in more detail in MVCC.