MySQL 8.0 DDL atomicity feature and implementation principle

1. Overview of DDL Atomicity

Before 8.0, there was no unified data dictionary dd. The server layer and the engine layer each had a set of metadata. The metadata of the sever layer included (.frm, .opt, .par, .trg, etc.), which was used to store table definitions, partition table definitions, trigger definitions and other information; the innodb layer also had its own set of metadata, including table information, index information, etc. There was no mechanism to ensure the consistency of these two sets of metadata, which led to metadata inconsistency problems under abnormal circumstances. In a typical scenario, in a table deletion operation, the frm of the sever layer was successfully deleted, but the data dictionary of the engine layer was not updated, resulting in the failure of rebuilding the table with the same name. Similarly, for example, if you drop table t1, t2, problems may occur such as only t1 being deleted while t2 still exists.

An important task of 8.0 is to unify the data dictionary, separate the DD (data dictionary) module, abandon the metadata of the server layer, and abstract the metadata of InnoDB into a DD interface for common use by the server layer and InnoDB layer. Based on DD, the atomicity feature of DDL is introduced to ensure that DDL operations are either performed in full or not performed at all. The key to implementing this logic is to treat all modifications involved in DDL, including DD data dictionary modification, engine layer modification (creating files, initializing tablespace, creating btree, etc.) and writing binlog as a "transaction", and use the atomicity of transactions to ensure the atomicity of DDL operations.

2. Principles of DDL atomicity implementation

The key to achieving atomicity is to ensure that the modification of the DD data dictionary, the modification of the engine layer and the writing of the binlog are one transaction. MySQL's existing XA transaction mechanism can effectively ensure the consistency of DML transactions and binlog. The DDL data dictionary is also stored by the InnoDB engine, so it is easy to make the DD data dictionary modification consistent with the binlog; then another problem that needs to be solved is the consistency of the DD data dictionary and the engine layer modification. Not all modifications at the engine layer are recorded in redo, such as creating a file, renaming a file name, or clearing the cache, etc., which cannot be simply solved by the XA mechanism. Therefore, 8.0 also introduces a DDL_LOG mechanism. Specifically, some operations that do not record redo are written into the ddl_log table in the form of logs. This table is an InnoDB engine table. By ensuring that the ddl_log data is consistent with the DD data dictionary modifications, the consistency issues of DD data dictionary modifications, engine layer modifications, and binlog writing are ultimately resolved.

3. Comparison before and after DD introduction

 ​

​  ​

4.DDL operation implementation logic

After the introduction of the ddl_log table, the DDL operation has some changes on the original basis. There are two main points. One is that during the execution of DDL, the DDL operation will be recorded in the ddl_log table. The other is that a post_ddl stage is added. After the DDL transaction is committed, some DDL finishing actions are performed, such as drop-table. The actual deletion of physical files is done in the post-ddl stage. The main function of post-ddl is to read the ddl-log content and replay it. The DDL operation types are as follows:

enum class Log_Type : uint32_t {
/** Smallest log type */
SMALLEST_LOG = 1,
/** Drop an index tree */
FREE_TREE_LOG = 1,
/** Delete a file */
DELETE_SPACE_LOG,
/** Rename a file */
RENAME_SPACE_LOG,
/** Drop the entry in innodb_dynamic_metadata */
DROP_LOG,
/** Rename table in dict cache. */
RENAME_TABLE_LOG,
/** Remove a table from dict cache */
REMOVE_CACHE_LOG,
/** Alter Encrypt a tablespace */
ALTER_ENCRYPT_TABLESPACE_LOG,
/** Biggest log type */
BIGGEST_LOG = ALTER_ENCRYPT_TABLESPACE_LOG
};

By using the innodb_print_ddl_logs switch, you can see the content written to the innodb_ddl_log table during the DDL process. The following uses the ddl_logs generated by several typical ddl operations to illustrate how to ensure the atomicity of ddl.

4.1 create table

Statement: create table dd_tt(id int primary key, c1 int);

[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=352, thread_id=23, space_id=71, old_file_path=./mysql/dd_tt.ibd]
[InnoDB] DDL log delete: 352
[InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=353, thread_id=23, table_id=1128, new_file_path=mysql/dd_tt]
[InnoDB] DDL log delete: 353
[InnoDB] DDL log insert : [DDL record: FREE, id=354, thread_id=23, space_id=71, index_id=231, page_no=4]
[InnoDB] DDL log delete: 354
[InnoDB] DDL log post ddl: begin for thread id: 23
[InnoDB] DDL log post ddl: end for thread id: 23

illustrate:

1. All insert operations are a separate transaction, and the corresponding reverse delete operation is part of the entire DDL transaction.

2. The insert operation records the reverse operation of the file operation. For example, if you create table_space, the reverse operation is delete_space_log.

3. If the DDL transaction is ultimately successful, all reverse delete operations will also take effect, and the ddl_log will be cleaned up normally. If the DDL transaction fails during execution (for example, the instance crashes), the delete operation will be rolled back, and three insert_logs will remain in the ddl_log table. When recovering, these ddl_logs will be replayed to clean up the garbage generated during the DDL process.

4. During crash-recovery, if the binlog has been written to the disk, the corresponding ddl transaction is in the prepare state, then the final transaction needs to be committed and the ddl_log is cleaned up; if the binlog has not been written to the disk, the ddl transaction needs to be rolled back, and 3 records remain in the ddl_log table. After the fault recovery is completed, these records need to be replayed, which is actually the reverse operation of creating files and creating btrees to ensure that it is clean after the rollback.

4.2 drop table

Statement: drop table dd_tt;

[InnoDB] DDL log insert : [DDL record: DROP, id=355, thread_id=23, table_id=1128]
[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=356, thread_id=23, space_id=71, old_file_path=./mysql/dd_tt.ibd]
[InnoDB] DDL log post ddl: begin for thread id: 23
[InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=356, thread_id=23, space_id=71, old_file_path=./mysql/dd_tt.ibd]
[InnoDB] DDL log replay : [DDL record: DROP, id=355, thread_id=23, table_id=1128]
[InnoDB] DDL log post ddl: end for thread id: 23

Note: For the drop operation, only the ddl_log is operated during the execution process, and the actual drop physical table operation is not performed. In the post-ddl stage, the records in the ddl_log table are read and replayed to perform the actual deletion operation. If a crash occurs during execution, the entire ddl transaction will be rolled back, including the contents of ddl_log, and the entire drop operation will be as if it did not happen.

4.3 add index

Statement: alter table dd_tt add index idx_c1(c1);

[InnoDB] DDL log insert : [DDL record: FREE, id=360, thread_id=23, space_id=72, index_id=233, page_no=5]   
[InnoDB] DDL log delete: 360
[InnoDB] DDL log post ddl: begin for thread id: 23                
[InnoDB] DDL log post ddl: end for thread id: 23

Note: Creating an index is similar to creating a table. The insert operation is a transaction and is submitted separately. A delete operation will be recorded as part of the entire DDL transaction. If the transaction is eventually submitted, the DDL-log content will be deleted. If the transaction is eventually rolled back, a FREE-log will remain in the DDL-log. Replay can be used to clean up the created index to achieve the rollback effect.

4.4 drop index

Statement: alter table dd_tt drop index idx_c1;

[InnoDB] DDL log insert : [DDL record: FREE, id=361, thread_id=23, space_id=72, index_id=233, page_no=5]
[InnoDB] DDL log post ddl: begin for thread id: 23
[InnoDB] DDL log replay : [DDL record: FREE, id=361, thread_id=23, space_id=72, index_id=233, page_no=5]
[InnoDB] DDL log post ddl: end for thread id: 23

illustrate:

Similar to drop table, only logs are recorded during the execution process, and the actual deletion operation is performed in the post-ddl stage.

4.5 add column

Statement: alter table dd_tt add column c2 int;

[InnoDB] DDL log post ddl: begin for thread id: 23
[InnoDB] DDL log post ddl: end for thread id: 23

illustrate:

8.0 adds columns using instant-ddl, which only modifies metadata, similar to dml transactions, and does not rely on ddl-log to ensure atomicity.

4.6 drop column

Statement: alter table dd_tt drop column c2;

Statement breakdown:

1.Prepare stage:

create table #sql-ib1129-2815969725;

[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=362, thread_id=23, space_id=73, old_file_path=./mysql/#sql-ib1129-2815969725.ibd] 
[InnoDB] DDL log delete: 362
[InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=363, thread_id=23, table_id=1130, new_file_path=mysql/#sql-ib1129-2815969725]  
[InnoDB] DDL log delete: 363
[InnoDB] DDL log insert : [DDL record: FREE, id=364, thread_id=23, space_id=73, index_id=234, page_no=4]         
[InnoDB] DDL log delete: 364

2.Peform stage: nothing about ddl-log

3. Commit stage:

3.1 alter table dd_tt rename to #sql-ib1130-2815969726;

[InnoDB] DDL log insert : [DDL record: DROP, id=365, thread_id=23, table_id=1129] <br>[InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=366, thread_id=23, space_id=72, old_file_path=./mysql/#sql-ib1130-2815969726.ibd, new_file_path=./mysql/dd_tt.ibd]
[InnoDB] DDL log delete: 366
[InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=367, thread_id=23, table_id=1129, old_file_path=mysql/#sql-ib1130-2815969726, new_file_path=mysql/dd_tt]
[InnoDB] DDL log delete: 367

Reverse operation: alter table mysql/#sql-ib1130-2815969726 rename to dd_tt;

3.2 alter table #sql-ib1129-2815969725 rename to dd_tt;

[InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=368, thread_id=23, space_id=73, old_file_path=./mysql/dd_tt.ibd, new_file_path=./mysql/#sql-ib1129-2815969725.ibd]
[InnoDB] DDL log delete: 368
[InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=369, thread_id=23, table_id=1130, old_file_path=mysql/dd_tt, new_file_path=mysql/#sql-ib1129-2815969725]
[InnoDB] DDL log delete: 369

Reverse operation: alter ter table dd_tt rename to mysql/#sql-ib1129-2815969725;

[InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=368, thread_id=23, space_id=73, old_file_path=./mysql/dd_tt.ibd, new_file_path=./mysql/#sql-ib1129-2815969725.ibd]
[InnoDB] DDL log delete: 368
[InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=369, thread_id=23, table_id=1130, old_file_path=mysql/dd_tt, new_file_path=mysql/#sql-ib1129-2815969725]
[InnoDB] DDL log delete: 369

Only operations are recorded and cleaned up in the post-ddl phase.

Post-ddl stage:

drop table #sql-ib1130-2815969726;

[InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=368, thread_id=23, space_id=73, old_file_path=./mysql/dd_tt.ibd, new_file_path=./mysql/#sql-ib1129-2815969725.ibd]
[InnoDB] DDL log delete: 368
[InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=369, thread_id=23, table_id=1130, old_file_path=mysql/dd_tt, new_file_path=mysql/#sql-ib1129-2815969725]
[InnoDB] DDL log delete: 369

Note: Drop column is a copy type DDL. The basic logic is to create a temporary table, copy the data, and then perform a rename operation. It mainly includes 4 stages:

1.Prepare stage: The process of creating a temporary table is similar to the DDL-log operation in the table creation process. Insert-log is directly submitted as a separate transaction, and delete-log is part of the entire transaction.

If an exception occurs at this stage, reverse operation records will remain in the ddl-log table, which can be cleaned up during replay during crash-recovery.

2.Peform phase: Data copying is completed and online-ddl logic is implemented.

3. After copying the data, you need to perform a rename operation to exchange the table name.

1) DROP, delete the temporary table

2)RENAME SPACE/TABLE Rename ./mysql/#sql-ib1130-2815969726.ibd to dd_tt.idb

3)REANAME SPACE/TABLE Rename dd_tt.idb to /#sql-ib1129-2815969725.idb

4) Record the operation of deleting the old table sql-ib1130-2815969726.ibd, and perform the actual deletion in the post-ddl stage.

If an exception occurs at this stage, the same insert-log is a separate transaction, and delete is part of the entire transaction. The insert-log will remain in the ddl-log table. You can clean it up through replay, restore the dd_tt data, and clean up the temporary table #sql-ib1130-2815969726.ibd.

4. Post-ddl stage:

1). Physically delete the old file ./mysql/#sql-ib1130-2815969726.ibd

2). Clean up the relevant information in mysql.innodb_dynamic_metadata.

It should be noted that since the contents stored in the ddl-log table are actually operated in reverse order, when collecting ddl-log, it is actually collected and played back in reverse order.

4.7 truncate table

Statement: truncate table dd_tt;

Statement breakdown:

1.rename dd_tt to #sql-ib1130-2815969727;

[InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=372, thread_id=23, space_id=73, old_file_path=./mysql/#sql-ib1130-2815969727.ibd, new_file_path=./mysql/dd_tt.ibd
[InnoDB] DDL log delete : 372

2.drop table #sql-ib1130-2815969727;

[InnoDB] DDL log insert : [DDL record: DROP, id=373, thread_id=23, table_id=1130]     
[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=374, thread_id=23, space_id=73, old_file_path=./mysql/#sql-ib1130-2815969727.ibd]

3.create table dd_tt;

[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=375, thread_id=23, space_id=74, old_file_path=./mysql/dd_tt.ibd]     
[InnoDB] DDL log delete : 375
[InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=376, thread_id=23, table_id=1131, new_file_path=mysql/dd_tt]      
[InnoDB] DDL log delete: 376
[InnoDB] DDL log insert : [DDL record: FREE, id=377, thread_id=23, space_id=74, index_id=235, page_no=4]         
[InnoDB] DDL log delete: 377
[InnoDB] DDL log post ddl: begin for thread id: 23                      
[InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=374, thread_id=23, space_id=73, old_file_path=./mysql/#sql-ib1130-2815969727.ibd] 
[InnoDB] DDL log replay : [DDL record: DROP, id=373, thread_id=23, table_id=1130]    

[InnoDB] DDL log post ddl: end for thread id: 23

illustrate:

1. Rename dd_tt to sql-ib1130-2815969727

2. Mark the sql-ib1130-2815969727 table for deletion, and it will be actually deleted in the post-ddl stage

3. Create a new table dd_tt. The same insert operation is submitted as a separate transaction, and the delete operation is part of the entire transaction. If it is rolled back, the insert operation will remain and be cleaned up through the replay action.

5.DDL operation code stack

5.1 create-table

Sql_cmd_create_table::execute
-->mysql_create_table
 -->mysql_create_table_no_lock
  -->create_table_impl
  -->rea_create_base_table
   -->ha_create_table
    -->ha_create
     -->ha_innobase::create
     -->innobase_basic_ddl::create_impl
      -->create_table_info_t::create_table
      {
       ......
      }
 -->trans_commit_implicit
  -->ha_commit_trans
  -->MYSQL_BIN_LOG::prepare
   -->ha_prepare_low //All transaction engines prepare
    {
    binlog_prepare
    innobase_xa_prepare
    }
  -->MYSQL_BIN_LOG::commit
   -->MYSQL_BIN_LOG::ordered_commit
    -->MYSQL_BIN_LOG::process_flush_stage_queue
     -->MYSQL_BIN_LOG::flush_thread_caches
     -->binlog_cache_mngr::flush
      -->binlog_cache_data::flush
       -->MYSQL_BIN_LOG::write_gtid
        -->Log_event::write
        -->MYSQL_BIN_LOG::Binlog_ofile::write //Write binlog-gtid
       -->MYSQL_BIN_LOG::write_cache
        --> MYSQL_BIN_LOG::do_write_cache
         -->Binlog_cache_storage::copy_to
         -->stream_copy
          -->Binlog_event_writer::write
           -->MYSQL_BIN_LOG::Binlog_ofile::write //Write binlog-ddl statement -->MYSQL_BIN_LOG::sync_binlog_file
    -->MYSQL_BIN_LOG::process_commit_stage_queue
     -->ha_commit_low
     {
      binlog_commit
      innobase_commit
      -->trx_commit_for_mysql
       -->trx_commit
        -->trx_commit_low
         -->trx_commit_in_memory
         -->trx_undo_insert_cleanup
     }
 -->innobase_post_ddl(ht->post_ddl(thd))
  -->Log_DDL::post_ddl
  -->replay_by_thread_id


-->create_table_info_t::create_table
 -->create_table_def
  -->dict_mem_table_create //Construct innodb memory as dictionary memory object -->row_create_table_for_mysql
  -->dict_build_table_def
   -->dict_build_tablespace_for_table
    -->Create a new xxx.idb file-->Log_DDL::write_delete_space_log
    {
     -->Log_DDL::insert_delete_space_log
     -->trx_start_internal //Start transaction internally and submit it separately.
     -->Construct DDL_Record (DELETE_SPACE_LOG)
     -->DDL_Log_Table::insert (write physical B-Tree)
     -->Log_DDL:delete_by_id //Delete the ddl_log operation as part of the ddl transaction.
    }
    -->fil_ibd_create
    -->Initialize segment, extent, page
  -->Log_DDL::write_remove_cache_log
  -->Log_DDL::insert_remove_cache_log
  -->Log_DDL::delete_by_id
 -->create_index (primary table, secondary index)
  -->dict_create_index_tree_in_mem
  -->btr_create
  -->Log_DDL::write_free_tree_log
   -->Log_DDL::insert_free_tree_log
   -->Log_DDL:delete_by_id<br>
crash-recovery
 -->ha_post_recover
  -->post_recover_handlerton
    -->innobase_post_recover
     -->Log_DDL::recover
      -->Log_DDL::replay_all
       -->Log_DDL::replay
        {
         replay_delete_space_log
         replay_remove_cache_log
         replay_free_tree_log
         ......
        }
       -->delete_by_ids
        -->DDL_Log_Table::remove

5.2 drop table

mysql_rm_table
 -->mysql_rm_table_no_locks
  -->drop_base_table
   -->ha_delete_table
    -—>handler::ha_delete_table
     -->ha_innobase::delete_table
     -->innobase_basic_ddl::delete_impl
      -->row_drop_table_for_mysql
       -->Log_DDL::write_drop_log // Record deletion of innodb_dynamic_metadata log-—>Log_DDL::write_delete_space_log // Record deletion of ibd log-->dd::drop_table
    -->dd::cache::Dictionary_client::drop<dd::Table>
     -->dd::cache::Storage_adapter::drop<dd::Table>
      -->dd::sdi::drop
  -->innobase_post_ddl
   -->Log_DDL::post_ddl
    -->Log_DDL::replay_by_thread_id
     -->Log_DDL::replay
      —>Log_DDL::replay_delete_space_log // post-ddl actually deletes innodb_dynamic_metadata
      —>Log_DDL::replay_drop_log // post-ddl actually deletes ibd
     -->delete_by_ids
      -->DDL_Log_Table::remove

When dropping a table, only the deletion action log is recorded. These logs are part of the transaction as a whole. If the transaction is finally committed, the post_ddl stage will read the log to actually delete the table. If the transaction is rolled back, the ddl_log will also be rolled back as part of the transaction.

Reference Documentation

https://dev.mysql.com/worklog/task/?id=9045

https://dev.mysql.com/worklog/task/?id=9173

https://dev.mysql.com/worklog/task/?id=9175

https://dev.mysql.com/worklog/task/?id=9525

https://dev.mysql.com/worklog/task/?id=9536

Summarize

The above is the introduction of the atomicity characteristics and implementation principles of MySQL 8.0 DDL by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!