Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the structure of Innodb undo". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. General structure

Rollback segments (128)

Undo segments (1024)

Undo log (header insert/modify separate) undo page

Undo record

Undo record

As the first undo page of undo segments, you can store the undo log of multiple things, because if the undo record of this block is not filled up with 3 rollback segment 4, it will enter the cache list of rollback segment, so you can continue to use it next time, but if the first block is not enough to hold the undo record of things, then it is obvious that you need to allocate a new undo page, in which case a undo page can only contain the undo record of one thing.

Things need to assign rollback segments and then undo segments and initialize undo log header,insert and update/delete each time they need to assign different undo segments. A undo segments often corresponds to a undo log,undo log that can contain multiple undo record (because undo log header initialization is done only once from the debug point of view), leaving a undo record for each line of operation as the basis for the mvcc build history version.

The basic unit of undo generation is undo record, and each row of records will contain a undo record, while rollback ptr points to the offset of undo record, and the visibility of each row of records will be judged. If you need to build the pre-build version, use this pointer to build the include:

Whether the first place is insert 2 to 8 is undo segment id 9 to 40, page no 41 to 56 is offset

Each undo log contains a trx_undo_t structure

Each rollback segments contains a trx_rseg_t structure

II. Physical structure

Undo page header every undo page contains

/ * Transaction undo log page header offsets * / / * @ {* / # define TRX_UNDO_PAGE_TYPE 0 / *!

< TRX_UNDO_INSERT or TRX_UNDO_UPDATE */#define TRX_UNDO_PAGE_START 2 /*!< Byte offset where the undo log records for the LATEST transaction start on this page (remember that in an update undo log, the first page can contain several undo logs) */#define TRX_UNDO_PAGE_FREE 4 /*!< On each page of the undo log this field contains the byte offset of the first free byte on the page */#define TRX_UNDO_PAGE_NODE 6 /*!< The file list node in the chain of undo log pages */ undo semgent header 第一个page 才会用 undo segment header信息 #define TRX_UNDO_STATE 0 /*!< TRX_UNDO_ACTIVE, ... */#ifndef UNIV_INNOCHECKSUM#define TRX_UNDO_LAST_LOG 2 /*!< Offset of the last undo log header on the segment header page, 0 if none */#define TRX_UNDO_FSEG_HEADER 4 /*!< Header for the file segment which the undo log segment occupies */#define TRX_UNDO_PAGE_LIST (4 + FSEG_HEADER_SIZE) /*!< Base node for the list of pages in the undo log segment; defined only on the undo log segment's first page */ 每一个undo log undo log header undo log record 相应的undo实际内容 undo log record 相应的undo实际内容 undo log header 包含 #define TRX_UNDO_TRX_ID 0 /*!< Transaction id */#define TRX_UNDO_TRX_NO 8 /*!< Transaction number of the transaction; defined only if the log is in a history list */#define TRX_UNDO_DEL_MARKS 16 /*!< Defined only in an update undo log: TRUE if the transaction may have done delete markings of records, and thus purge is necessary */#define TRX_UNDO_LOG_START 18 /*!< Offset of the first undo log record of this log on the header page; purge may remove undo log record from the log start, and therefore this is not necessarily the same as this log header end offset */#define TRX_UNDO_XID_EXISTS 20 /*!< TRUE if undo log header includes X/Open XA transaction identification XID */#define TRX_UNDO_DICT_TRANS 21 /*!< TRUE if the transaction is a table create, index create, or drop transaction: in recovery the transaction cannot be rolled back in the usual way: a 'rollback' rather means dropping the created or dropped table, if it still exists */#define TRX_UNDO_TABLE_ID 22 /*!< Id of the table if the preceding field is TRUE */#define TRX_UNDO_NEXT_LOG 30 /*!< Offset of the next undo log header on this page, 0 if none */#define TRX_UNDO_PREV_LOG 32 /*!< Offset of the previous undo log header on this page, 0 if none */#define TRX_UNDO_HISTORY_NODE 34 /*!< If the log is put to the history list, the file list node is here */三、分配步骤和写入 第一步为 分配rollback segments #0 get_next_redo_rseg (max_undo_logs=128, n_tablespaces=4) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/trx/trx0trx.cc:1138#1 0x0000000001c0bce8 in trx_assign_rseg_low (max_undo_logs=128, n_tablespaces=4, rseg_type=TRX_RSEG_TYPE_REDO) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/trx/trx0trx.cc:1314#2 0x0000000001c1097d in trx_set_rw_mode (trx=0x7fffd7804080) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/trx/trx0trx.cc:3352#3 0x0000000001a64013 in lock_table (flags=0, table=0x7ffeac012ae0, mode=LOCK_IX, thr=0x7ffe7c92ef48) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:4139#4 0x0000000001b7950e in row_search_mvcc (buf=0x7ffe7c92e350 "\377", mode=PAGE_CUR_GE, prebuilt=0x7ffe7c92e7d0, match_mode=1, direction=0) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:5100#5 0x00000000019d5443 in ha_innobase::index_read (this=0x7ffe7c92de10, buf=0x7ffe7c92e350 "\377", key_ptr=0x7ffe7cd57590 "\004", key_len=4, find_flag=HA_READ_KEY_EXACT) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9536#6 0x0000000000f9345a in handler::index_read_map (this=0x7ffe7c92de10, buf=0x7ffe7c92e350 "\377", key=0x7ffe7cd57590 "\004", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at /root/mysqlc/percona-server-locks-detail-5.7.22/sql/handler.h:2942#7 0x0000000000f83e44 in handler::ha_index_read_map (this=0x7ffe7c92de10, buf=0x7ffe7c92e350 "\377", key=0x7ffe7cd57590 "\004", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at /root/mysqlc/percona-server-locks-detail-5.7.22/sql/handler.cc:3248 第二步 对于主键每行更改操作都会调用trx_undo_report_row_operation 他会分配undo segments 并且会负责写入undo record #0 trx_undo_report_row_operation (flags=0, op_type=2, thr=0x7ffe7c932828, index=0x7ffea4016590, clust_entry=0x7ffe7c932cc0, update=0x0, cmpl_info=0, rec=0x7fffb580d369 "", offsets=0x7fffec0f3e00, roll_ptr=0x7fffec0f3688) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/trx/trx0rec.cc:1866#1 0x0000000001c5795b in btr_cur_del_mark_set_clust_rec (flags=0, block=0x7fffb4ccaae0, rec=0x7fffb580d369 "", index=0x7ffea4016590, offsets=0x7fffec0f3e00, thr=0x7ffe7c932828, entry=0x7ffe7c932cc0, mtr=0x7fffec0f38f0) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/btr/btr0cur.cc:4894#2 0x0000000001b9f218 in row_upd_del_mark_clust_rec (flags=0, node=0x7ffe7c932550, index=0x7ffea4016590, offsets=0x7fffec0f3e00, thr=0x7ffe7c932828, referenced=0, mtr=0x7fffec0f38f0) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/row/row0upd.cc:2778#3 0x0000000001b9f765 in row_upd_clust_step (node=0x7ffe7c932550, thr=0x7ffe7c932828) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/row/row0upd.cc:2923#4 0x0000000001b9fc74 in row_upd (node=0x7ffe7c932550, thr=0x7ffe7c932828) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/row/row0upd.cc:3042#5 0x0000000001ba0155 in row_upd_step (thr=0x7ffe7c932828) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/row/row0upd.cc:3188#6 0x0000000001b3d3a0 in row_update_for_mysql_using_upd_graph (mysql_rec=0x7ffe7c9318d0 "\375\001", prebuilt=0x7ffe7c931d50) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/row/row0mysql.cc:3040#7 0x0000000001b3d6a1 in row_update_for_mysql (mysql_rec=0x7ffe7c9318d0 "\375\001", prebuilt=0x7ffe7c931d50) at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/row/row0mysql.cc:3131#8 0x00000000019d47c3 in ha_innobase::delete_row (this=0x7ffe7c931390, record=0x7ffe7c9318d0 "\375\001") at /root/mysqlc/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9141 大概流程 switch (op_type){ case TRX_UNDO_INSERT_OP: undo = undo_ptr->

Insert_undo; / / if it is insert, use the insert_undo type for the trx_undo_t pointer if (undo = = NULL) {/ / if it is already assigned, do not assign err = trx_undo_assign_undo (/ / assign undo segment and initialize undo log header trx, undo_ptr, TRX_UNDO_INSERT); undo = undo_ptr- > insert_undo .} break; default: ut_ad (op_type = = TRX_UNDO_MODIFY_OP); / / assert undo = undo_ptr- > update_undo; if (undo = = NULL) {err = trx_undo_assign_undo (trx, undo_ptr, TRX_UNDO_UPDATE) / / assign undo segment and initialize undo log header undo = undo_ptr- > update_undo;.}. Case TRX_UNDO_INSERT_OP:// note that each line will operate offset = trx_undo_page_report_insert (/ / write insert undo log record undo_page, trx, index, clust_entry, & mtr); break; default: ut_ad (op_type = = TRX_UNDO_MODIFY_OP) / / write delete update undo log record offset = trx_undo_page_report_modify (undo_page, trx, index, rec, offsets, update, cmpl_info, clust_entry, & mtr) }. * roll_ptr = trx_undo_build_roll_ptr (/ / every line in the build rollback ptr primary key has this for MVCC build rollback version op_type = = TRX_UNDO_INSERT_OP, undo_ptr- > rseg- > id, page_no, offset); fourth, decompose undo log record

I wrote the undo log record to the error log, which is simply broken down below.

The table structure is as follows:

Mysql > show create table T1 +- -+ | Table | Create Table | | +-+- -+ | T1 | CREATE TABLE `t1` (`id1` int (11) NOT NULL `id2` int (11) DEFAULT NULL, PRIMARY KEY (`id1`) KEY `id2` (`id2`) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-+- -+ 1 row in set (0.00 sec)

The undo record of insert, which is constructed in trx_undo_page_report_insert.

Statement

Mysql > insert into T1 values (28 sec); Query OK, 1 row affected (0.00 sec)

The output is as follows:

Trx_undo_assign_undo:assign undo space:RSEG SLOT:34,RSEG SPACE ID:2 PAGE NO:3UNDO SLOT:0,UNDO SPACE ID:2 UNDO LOG HEADER PAGE NO:27,UNDO LOG HEADER OFFSET:86,UNDO LOG LAST PAGE:27trx_undo_page_report_insert:undo log recordTABLE_NAME:test/t1 TRX_ID:12591,UODO RECORD LEN:10 len 10; hex 011e0b0032048000001c

The actual record of 011e0b0032048000001c is undo record is parsed as follows:

011c page inside this undo record ends location 0b type is # define TRX_UNDO_INSERT_REC 11 (0X0b) 00 undo no, only 32 table_id can query INNODB_SYS_TABLES against 04 field length 4 bytes 8000001c record primary key 28 (0X1c) I inserted

The undo record of update, which is constructed in trx_undo_page_report_modify.

Statement:

Mysql > update T1 set id2=1000 where id1=14;Query OK, 1 row affected (5 min 40.91 sec) Rows matched: 1 Changed: 1 Warnings: 0

The output is as follows:

Trx_undo_assign_undo:assign undo space:RSEG SLOT:41,RSEG SPACE ID:1 PAGE NO:5UNDO SLOT:1,UNDO SPACE ID:1 UNDO LOG HEADER PAGE NO:37,UNDO LOG HEADER OFFSET:1389,UNDO LOG LAST PAGE:37trx_undo_page_report_modify:undo log recordTABLE_NAME:test/t1 TRX_ID:12604,UODO RECORD LEN:47 len 47; hex 06560c0032000000003136e0260000002c052e048000000e010304800003e7000e00048000000e0304800003e70627

06560c0032000000003136e0260000002c052e048000000e010304800003e7000e00048000000e030480

It's undo record's record.

The general analysis is as follows:

0656: page internal location 0c: type # define TRX_UNDO_UPD_EXIST_REC 12 (0X0c) 00: undo no, submission will be 32: table_id can query INNODB_SYS_TABLES comparison 00:0000003136e0: thing ID260000002c052e:undo rollback pointer 04: primary key length 0103: position 04: primary key value 0103: position 04: modified value 800003e7: value 999 (0x3e7) 000e: the length of the next character, record the original value? 00: position 04: length 8000000e: primary key value 03: location 04: length 800003e7: value is 999 (0x3e7) 0627:page inside the location of the beginning of this undo record, 0X0656-0X0627 is the length "what is the Innodb undo structure" content introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.