MySQL & #183; Engine Features & #183; InnoDB undo log roaming

MySQL Engine Features-InnoDB undo log roaming

This article describes the entire Undo lifecycle process. The code analysis is based on the latest version of MySQL5.7. This article can also serve as a guide to the Code for understanding the entire Undo module. Due to the large number of modules involved, some details are not in-depth.

Preface

Undo log is an important part of InnoDB MVCC transaction features. When we change the record, an undo record will be generated. The Undo record is recorded in the system tablespace (ibdata) by default, but starting from 5.6, you can also use an independent Undo tablespace.

The old version of data is stored in the Undo record. When an old transaction needs to read data, in order to read the data of the old version, it is necessary to follow the undo chain to find a record that satisfies its visibility. When the version chain is very long, it is generally considered a time-consuming operation (for example, bug #69812 ).

Most data change operations include INSERT, DELETE, and UPDATE. The INSERT operation is only visible to the current transaction before the transaction is committed, therefore, the Undo logs generated can be directly deleted after the transaction is committed (who needs to have visibility into the data just inserted !!), For UPDATE/DELETE operations, you need to maintain multi-version information. In InnoDB, The Undo logs generated by the UPDATE and DELETE operations are classified into one type, namely, update_undo.

Basic file structure

In order to ensure that concurrent transactions do not conflict when writing their respective undo logs, InnoDB uses rollback segments to maintain the concurrent write and persistence of undo logs. A rollback segment is actually an Undo file organization method. Each rollback segment has multiple undo log slots. The specific file organization method is shown in:

Shows the basic layout structure of the Undo rollback segment, where:

1. Rseg0 is reserved in the system tablespace ibdata;
2. Rseg 1 ~ Rseg 32 these 32 rollback segments are stored in the system tablespace of the temporary table;
3. Rseg33 ~ It is stored in the independent undo tablespace according to the configuration (if the independent Undo tablespace is not opened, it is stored in ibdata)

If we use an independent Undo tablespace, the Undo rollback segments are always allocated from the First undo space round-robin. In most cases, this is OK, but suppose we increase the number of rollback segments from 33 to 128, all rollback segments may be stored in the same undo space. (Refer to the trx_sys_create_rsegs function and bug #74471)

Each rollback segment maintains a segment header page, which divides 1024 slots (TRX_RSEG_N_SLOTS). Each slot corresponds to an undo log object, therefore, InnoDB theoretically supports a maximum of 96*1024 general transactions.

MySQL InnoDB table-Basic BTree Data Structure

Optimization of the count (*) function in the InnoDB Storage engine of MySQL

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

Key struct

To facilitate the management and use of undo records, the following key struct objects are maintained in the memory:

1. All rollback segments are recorded intrx_sys->rseg_array, The array size is 128, corresponding to different rollback segments;
2. The rseg_array type is trx_rseg_t, which is used to maintain rollback segment information;
3. The trx_rseg_t object of each rollback segment also needs to manage the undo log information. The corresponding struct is trx_undo_t. Multiple linked lists are used to maintain the trx_undo_t information;
4. When a transaction is started, a rollback segment is specified for it. the undo log page used by the transaction will be allocated from the rollback segment;
5. After the transaction is committed, the rollback segments that require purge will be put on the purge Queue (purge_sys->purge_queue).

The relationships between structs are as follows:

Allocate rollback segments

When starting a read/write transaction (or converting from a read-only transaction to a read/write transaction), we need to assign a rollback segment to the transaction in advance:

For read-only transactions, if writing to a temporary table is generated, You need to allocate a rollback segment for it and use the temporary table rollback segment (1st ~ 32), function entry:trx_assign_rseg -->trx_assign_rseg_low-->get_next_noredo_rseg.

In MySQL5.7, the transaction is started with a read-only transaction by default. When it is determined to be a read/write transaction, it is converted to the read/write mode, and the transaction ID and rollback segment are allocated to it. Call the function:trx_set_rw_mode -->trx_assign_rseg_low --> get_next_redo_rseg.

The distribution of common rollback segments is as follows:

1. The round-robin method is used to assign the rollback segment to the transaction. If the rollback segment is marked as skip_allocation (this undo tablespace is too large, the purge thread needs to perform the truncate operation on it ), to the next one;
2. After selecting a rollback segment to the transaction,rseg->trx_ref_countIn this way, the undo tablespace file where the rollback segment is located cannot be truncate;
3. The temporary table rollback segment is assignedtrx->rsegs->m_noredo, The rollback segment of a common read/write operation is assignedtrx->rsegs->m_redoIf the transaction uses a temporary table in the read-only stage and is then converted to a read/write transaction, two rollback segments will be allocated to the transaction.

Use rollback segments

When a data change occurs, we need to use the Undo log to record the data before the change to maintain multi-version information. The insert and delete/update records undo separately. Therefore, the Undo slot needs to be allocated separately from the rollback segment.

Entry function:trx_undo_report_row_operation

The process is as follows:

1. Judge whether the current change is a temporary table. If it is a temporary table, use the temporary table rollback segment for allocation; otherwise, use the normal rollback segment;
2. When the temporary table operation record is undo, the redo log is not written;
3. If the operation type is TRX_UNDO_INSERT_OP and the insert undo slot is not allocated, call the Functiontrx_undo_assign_undoFor allocation;
4. If the operation type is TRX_UNDO_MODIFY_OP and the Update undo slot is not assigned, call the Functiontrx_undo_assign_undo.

Let's take a look at the process of the trx_undo_assign_undo function:

1. First, always allocate trx_undo_t (Functiontrx_undo_reuse_cachedWhen some conditions are met, the transaction will put trx_undo_t In the cached list when it is committed, so that the new transaction can reuse these undo objects without scanning the rollback segment, find available slots, which will be introduced in the transaction commit section );
   • For INSERTtrx_rseg_t::insert_undo_cachedAnd modify the header reuse information (trx_undo_insert_header_reuse) and reserved XID space (trx_undo_header_add_space_for_xid)
   • For DELETE/UPDATEtrx_rseg_t::update_undo_cachedAnd create a new undo log header (trx_undo_header_create) on the Undo log hdr page, and reserve the XID bucket (trx_undo_header_add_space_for_xid)
   • After the trx_undo_t object is obtained, it will be removed from the cached list. And initialize trx_undo_t information (trx_undo_mem_init_for_reuse ),trx_undo_t::stateSet to TRX_UNDO_ACTIVE

2. If the trx_undo_t does not have a cache, You need to allocate an idle undo slot (trx_undo_create) from the rollback segment and create the corresponding undo page for initialization;

   A rollback segment can support 1024 concurrent transactions. If unfortunately all rollback segments are used up (usually this will hardly happen), the error DB_TOO_MANY_CONCURRENT_TRXS will be returned.

   Each Undo log segment actually corresponds to an independent segment. The starting position of the segment header is in the TRX_UNDO_SEG_HDR + TRX_UNDO_FSEG_HEADER offset position of the UNDO header page (SEE)

3. The trx_undo_t allocated to the transaction will be added to the linked list.trx_rseg_t::insert_undo_listOrTrx_rseg_t: update_undo_list;

4. For undo operations produced by data dictionary operations (DDL), such as creating or deleting tables, you also need to record the operation table id to the undo log header (TRX_UNDO_TABLE_ID ), set TRX_UNDO_DICT_TRANS to TRUE. (Trx_undo_mark_as_dict_operation ).

In general, the undo header page mainly includes the following information:

How to Write undo logs

Entry function:trx_undo_report_row_operation

When an undo slot is assigned and an available idle zone is initialized, you can write undo records to it. The written page no is taken fromundo->last_page_no, Which is the same as hdr_page_no initially.

For INSERT_UNDO, call the trx_undo_page_report_insert function to insert data. The record format is roughly shown in:

For UPDATE_UNDO, call the Functiontrx_undo_page_report_modifyInsert. The update undo record format is roughly as follows:

During the write process, there may be insufficient single page space, leading to write failure. We need to clear and reset the newly written area (trx_undo_erase_page_end) and apply for a new page (trx_undo_add_page) add to the undo log segment andundo->last_page_noPoint to the newly allocated page and try again.

After Undo log writing is completed, construct a new rollback segment pointer and return (trx_undo_build_roll_ptr ), the rollback segment pointer includes the id of the rollback segment where the undo log is located, the page no where the log is located, and the offset in the page, which must be recorded in the clustered index record.

For more details, please continue to read the highlights on the next page:

1 2 Next Page