before_dml hook 引发的drop table阻塞事件

结论总结下就是：

1. 删除表的过程需要从操作系统层面删除物理文件(其实是调用unlink)，文件越大，需要的时间越长,并且占用IO资源。

2. 删除表的过程涉及到很多其他的内部流程，比如释放AHI，缓存等等。

3. drop table主要耗时在InnoDB层全局的字典信息mutext锁保护下进行，所以会和其他dml冲突。

给出的解决方案可能是：

1. 使用硬连接加速文件移除的过程(drop table伪移除ibd文件)

2. 关闭AHI

然而之所以会在深夜发文，肯定是有不一样的发现，正如标题所示。

现象:一条drop命令后，几乎所有写入操作都阻塞在Opening tables 

Thread 2 (Thread 0x7fab1c0c3700 (LWP 2371)):
#0  0x0000003fb360b68c in pthread_cond_wait@@GLIBC_2.3.2 () from lib64/libpthread.so.0
#1  0x0000000001039c5b in os_event::wait_low(long) ()
#2  0x00000000010e49f9 in sync_array_wait_event(sync_array_t*, sync_cell_t*&) ()
#3  0x0000000000fb3efa in TTASEventMutex<GenericPolicy>::wait ()
#4  0x0000000000fcaaf5 in PolicyMutex<TTASEventMutex<GenericPolicy> >::enter(unsigned int, unsigned int, char const*, unsigned int) ()
#5  0x0000000000fc250a in ha_innobase::get_foreign_key_list(THD*, List<st_foreign_key_info>*) ()
#6  0x0000000000c605df in has_cascade_foreign_key(TABLE*, THD*) ()
#7  0x0000000000c612a0 in Trans_delegate::prepare_table_info(THD*, Trans_table_info*&, unsigned int&) ()
#8  0x0000000000c64088 in Trans_delegate::before_dml(THD*, int&) ()
#9  0x0000000000c9ade1 in run_before_dml_hook(THD*) ()
#10 0x0000000000d7d327 in Sql_cmd_update::try_single_table_update(THD*, bool*) ()
#11 0x0000000000d7e3cc in Sql_cmd_update::execute(THD*) ()
#12 0x0000000000cf6149 in mysql_execute_command(THD*, bool) ()
#13 0x0000000000cfa725 in mysql_parse(THD*, Parser_state*) ()
#14 0x0000000000cfb948 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#15 0x0000000000cfc834 in do_command(THD*) ()
#16 0x0000000000dc9b9c in handle_connection ()
#17 0x0000000000f46844 in pfs_spawn_thread ()
#18 0x0000003fb3607aa1 in start_thread () from lib64/libpthread.so.0
#19 0x0000003fb32e8aad in clone () from lib64/libc.so.6

int run_before_dml_hook(THD *thd)
{
int out_value= 0;
  (void) RUN_HOOK(transaction, before_dml, (thd, out_value));


if (out_value)
    my_error(ER_BEFORE_DML_VALIDATION_ERROR, MYF(0));


return out_value;
}

/*
  if there is no observers in the delegate, we can return 0
  immediately.
*/
#define RUN_HOOK(group, hook, args)             \
  (group ##_delegate->is_empty() ?              \
0 : group ##_delegate->hook args)

...//省略若干层调用
error= mysql_execute_command(thd, true);
    mysql_execute_command(THD*, bool)
        Sql_cmd_insert::execute(THD*)
            Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*)
                run_before_dml_hook(THD*)
                    Trans_delegate::before_dml(THD*, int&)
                        Trans_delegate::prepare_table_info(THD*, Trans_table_info*&, unsigned int&)
                            has_cascade_foreign_key(TABLE*, THD*)
                                ha_innobase::get_foreign_key_list(THD*, List<st_foreign_key_info>*)

prepare_table_info(thd, param.tables_info, param.number_of_tables);
  prepare_transaction_context(thd, param.trans_ctx_info);

int
ha_innobase::get_foreign_key_list(
/*==============================*/
	THD*			thd,		/*!< in: user thread handle */
	List<FOREIGN_KEY_INFO>*	f_key_list)	/*!< out: foreign key list */
{
	update_thd(ha_thd());

	TrxInInnoDB	trx_in_innodb(m_prebuilt->trx);
	m_prebuilt->trx->op_info = "getting list of foreign keys";
	mutex_enter(&dict_sys->mutex);//加锁
	for (dict_foreign_set::iterator it
		= m_prebuilt->table->foreign_set.begin();
	     it != m_prebuilt->table->foreign_set.end();
	     ++it) {

		FOREIGN_KEY_INFO*	pf_key_info;
		dict_foreign_t*		foreign = *it;

		pf_key_info = get_foreign_key_info(thd, foreign);

		if (pf_key_info != NULL) {
			f_key_list->push_back(pf_key_info);
		}
	}
	mutex_exit(&dict_sys->mutex); //释放锁
	m_prebuilt->trx->op_info = "";
	return(0);
}

//semisync_master_plugin.cc:407
Trans_observer trans_observer = {
  sizeof(Trans_observer),		// len

  repl_semi_report_before_dml,      //before_dml
  repl_semi_report_before_commit,   // before_commit
  repl_semi_report_before_rollback, // before_rollback
  repl_semi_report_commit,	// after_commit
  repl_semi_report_rollback,	// after_rollback
};

/**
   Observes and extends transaction execution
*/
typedef struct Trans_observer {
  uint32 len;
  int (*before_dml)(Trans_param *param, int& out_val);
  int (*before_commit)(Trans_param *param);
  int (*before_rollback)(Trans_param *param);
  int (*after_commit)(Trans_param *param);
  int (*after_rollback)(Trans_param *param);
} Trans_observer;

//semisync_master_plugin.cc:77
int repl_semi_report_before_dml(Trans_param *param, int& out)
{
  return 0;
}

//observer_trans.cc
/*
  Transaction lifecycle events observers.
*/

int group_replication_trans_before_dml(Trans_param *param, int &out)
{
    DBUG_TRACE;
    ... //省略部分代码

  if ((out += (param->trans_ctx_info.transaction_write_set_extraction ==
               HASH_ALGORITHM_OFF))) {
    /* purecov: begin inspected */
    LogPluginErr(ERROR_LEVEL, ER_GRP_RPL_TRANS_WRITE_SET_EXTRACTION_NOT_SET);
    return 0;
    /* purecov: end */
  }

  if (local_member_info->has_enforces_update_everywhere_checks() &&
      (out += (param->trans_ctx_info.tx_isolation == ISO_SERIALIZABLE))) {
    LogPluginErr(ERROR_LEVEL, ER_GRP_RPL_UNSUPPORTED_TRANS_ISOLATION);
    return 0;
  }
  
    for (uint table = 0; out == 0 && table < param->number_of_tables; table++) {
    if (param->tables_info[table].db_type != DB_TYPE_INNODB) {
      LogPluginErr(ERROR_LEVEL, ER_GRP_RPL_NEEDS_INNODB_TABLE,
                   param->tables_info[table].table_name);
      out++;
    }

    if (param->tables_info[table].number_of_primary_keys == 0) {
      LogPluginErr(ERROR_LEVEL, ER_GRP_RPL_PRIMARY_KEY_NOT_DEFINED,
                   param->tables_info[table].table_name);
      out++;
    }
    if (local_member_info->has_enforces_update_everywhere_checks() &&
        param->tables_info[table].has_cascade_foreign_key) {
      LogPluginErr(ERROR_LEVEL, ER_GRP_RPL_FK_WITH_CASCADE_UNSUPPORTED,
                   param->tables_info[table].table_name);
      out++;
    }
}

1. 其他线程出现行锁等待

2. 出现行锁死锁需要回滚

3. 对于information_schem中的innodb类型的表进行访问等等