5.7版本很多ddl都是在线的了，允许并行的dml，允许不代表线上不会有问题，下面看下alter添加字段过程中的加锁情况，主要是mdl部分的。 在open_table部分会获取两个mdl锁 MDL_INTENTION_EXCLUSIVE 以及MDL_SHARED_UPGRADABLE，在执行mysql_inplace_alter_table的过程中会进行mdl锁升级，升级shared 锁成MDL_EXCLUSIVE 堆栈如下：
mysqld!MDL_context::upgrade_shared_lock(MDL_ticket*, enum_mdl_type, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/mdl.cc:3937) mysqld!mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:7458) mysqld!mysql_alter_table(THD*, char const*, char const*, st_ha_create_information*, TABLE_LIST*, Alter_info*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:9807) mysqld!Sql_cmd_alter_table::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_alter.cc:333) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:4845) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0) libsystem_pthread.dylib!thread_start (Unknown Source:0) 在prepare完成后，会将MDL_EXCLUSIVE降低成MDL_SHARED_UPGRADABLE共享锁，堆栈如下 mysqld!MDL_ticket::downgrade_lock(enum_mdl_type) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/mdl.cc:4544) mysqld!mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:7583) mysqld!mysql_alter_table(THD*, char const*, char const*, st_ha_create_information*, TABLE_LIST*, Alter_info*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:9807) mysqld!Sql_cmd_alter_table::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_alter.cc:333) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:4845) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0) libsystem_pthread.dylib!thread_start (Unknown Source:0) 使用inplace方式的alter，进入inplace_alter_table，在执行完后，会再次升级成为exclusive ,堆栈如下： mysqld!wait_while_table_is_used(THD*, TABLE*, ha_extra_function) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_base.cc:2598) mysqld!mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:7595) mysqld!mysql_alter_table(THD*, char const*, char const*, st_ha_create_information*, TABLE_LIST*, Alter_info*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:9807) mysqld!Sql_cmd_alter_table::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_alter.cc:333) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:4845) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0) libsystem_pthread.dylib!thread_start (Unknown Source:0) 然后提交，下面看下dml mdl锁的获取，堆栈如下，可以看到是获取了MDL_SHARED_WRITE mysqld!open_table(THD*, TABLE_LIST*, Open_table_context*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_base.cc:3672) mysqld!open_and_process_table(THD*, LEX*, TABLE_LIST*, unsigned int*, unsigned int, Prelocking_strategy*, bool, Open_table_context*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_base.cc:5226) mysqld!open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_base.cc:5849) mysqld!open_tables_for_query(THD*, TABLE_LIST*, unsigned int) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_base.cc:6626) mysqld!Sql_cmd_update::try_single_table_update(THD*, bool*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:2890) mysqld!Sql_cmd_update::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:3037) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:3616) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0) libsystem_pthread.dylib!thread_start (Unknown Source:0) select执行过程中会获取MDL_SHARED_READ，从下面的兼容矩阵来看，在alter获取x锁的期间，是会阻塞dml的，如果有慢 sql在执行，那么ddl会被阻塞，后续该表的操作也会阻塞，所以再次的，慢sql ,长事务是万恶之首。 在最后的提交阶段，执行row_merge_lock_table会对表加x锁，row_mysql_lock_data_dictionary 会对数据字典加锁 在后面会有row_merge_drop_table删除表上的锁，堆栈如下： mysqld!lock_remove_all_on_table(dict_table_t*, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/lock/lock0lock.cc:4770) mysqld!row_drop_table_for_mysql(char const*, trx_t*, bool, bool, dict_table_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0mysql.cc:4517) mysqld!row_merge_drop_table(trx_t*, dict_table_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0merge.cc:4258) mysqld!ha_innobase::commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/handler0alter.cc:8931) mysqld!handler::ha_commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:4863) mysqld!mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:7635) mysqld!mysql_alter_table(THD*, char const*, char const*, st_ha_create_information*, TABLE_LIST*, Alter_info*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:9807) mysqld!Sql_cmd_alter_table::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_alter.cc:333) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:4845) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0) libsystem_pthread.dylib!thread_start (Unknown Source:0) MDL锁类型 MDL_INTENTION_EXCLUSIVE MDL_SHARED MDL_SHARED_HIGH_PRIO MDL_SHARED_READ MDL_SHARED_WRITE MDL_SHARED_WRITE_LOW_PRIO MDL_SHARED_UPGRADABLE MDL_SHARED_READ_ONLY MDL_SHARED_NO_WRITE MDL_SHARED_NO_READ_WRITE MDL_EXCLUSIVE 兼容矩阵 Request | Granted requests for lock | type | S SH SR SW SWLP SU SRO SNW SNRW X | ----------+---------------------------------------------+ S | + + + + + + + + + - | SH | + + + + + + + + + - | SR | + + + + + + + + - - | SW | + + + + + + - - - - | SWLP | + + + + + + - - - - | SU | + + + + + - + - - - | SRO | + + + - - + + + - - | SNW | + + + - - - + - - - | SNRW | + + - - - - - - - - | X | - - - - - - - - - - | Request | Pending requests for lock | type | S SH SR SW SWLP SU SRO SNW SNRW X | ----------+--------------------------------------------+ S | + + + + + + + + + - | SH | + + + + + + + + + + | SR | + + + + + + + + - - | SW | + + + + + + + - - - | SWLP | + + + + + + - - - - | SU | + + + + + + + + + - | SRO | + + + - + + + + - - | SNW | + + + + + + + + + - | SNRW | + + + + + + + + + - | X | + + + + + + + + + + |
在online ddl执行期间，所有的修改会被记录到row_log_t* online_log;
/** @brief Buffer for logging modifications during online index creation
All modifications to an index that is being created will be logged by
row_log_online_op() to this buffer.
All modifications to a table that is being rebuilt will be logged by
row_log_table_delete(), row_log_table_update(), row_log_table_insert()
to this buffer.
When head.blocks == tail.blocks, the reader will access tail.block
directly. When also head.bytes == tail.bytes, both counts will be
reset to 0 and the file will be truncated. */
struct row_log_t {
有兴趣学习源码的加群一起学习啊 QQ： 700072075
