本篇内容主要讲解“MySQL中Innodb关于Handler_commit每次DML增加2的原因是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL中Innodb关于Handler_commit每次DML增加2的原因是什么”吧!

请教一个问题。我每次insert一条语句，查询showglobalstatuslike'Handler_commit';发现每次增加值是2，难道不应该是1吗？最简单的insertintotableavalues(1);一、问题展示

语句如下：

mysql>flushstatus;QueryOK,0rowsaffected(0.10sec)mysql>setsql_log_bin=1;QueryOK,0rowsaffected(0.01sec)mysql>insertintotestmvalues(16,'gaopeng',34);QueryOK,1rowaffected(0.15sec)mysql>showstatuslike'%commit%';+----------------+-------+|Variable_name|Value|+----------------+-------+|Com_commit|0||Com_xa_commit|0||Handler_commit|2|+----------------+-------+3rowsinset(0.01sec)

问为什么 Handler_commit是2而不是1。

其实对于这个问题只要看看这个Handler_commit指标增加的方式就可以看出原因。实际上这个指标出现在ha_commit_low函数中如下：

for(;ha_info;ha_info=ha_info_next){interr;handlerton*ht=ha_info->ht();if((err=ht->commit(ht,thd,all))){my_error(ER_ERROR_DURING_COMMIT,MYF(0),err);error=1;}DBUG_ASSERT(!thd->status_var_aggregated);thd->status_var.ha_commit_count++;//此处增加ha_info_next=ha_info->next();if(restore_backup_ha_data)reattach_engine_ha_data_to_thd(thd,ht);ha_info->reset();/*keepitconvenientlyzero-filled*/}

可以清楚的看到ha_commit_count实际就是调用ht->commit的次数，由于有多个Handler的存在，因此这里需要调用多次。对于开启binlog+innodb的这种结构来讲分别要做：

binlog的commit

Innodb的commit

后面会看到实际binlog的commit什么都没做，但是这是一种协议。
那么如果我们关闭binlog可以发现Handler_commit为1了如下：

mysql>setsql_log_bin=0;QueryOK,0rowsaffected(0.00sec)mysql>insertintotestmvalues(15,'gaopeng',34);QueryOK,1rowaffected(0.10sec)mysql>showstatuslike'%commit%';+----------------+-------+|Variable_name|Value|+----------------+-------+|Com_commit|0||Com_xa_commit|0||Handler_commit|1|+----------------+-------+3rowsinset(0.01sec)三、binlog commit栈帧

#0binlog_commit(hton=0x3485e30,thd=0x7fff2c014430,all=false)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:1833#10x0000000000f64104inha_commit_low(thd=0x7fff2c014430,all=false,run_after_commit=false)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1923#20x000000000185772binMYSQL_BIN_LOG::process_commit_stage_queue(this=0x2e01c80,thd=0x7fff2c014430,first=0x7fff2c014430)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8647#30x0000000001858f5dinMYSQL_BIN_LOG::ordered_commit(this=0x2e01c80,thd=0x7fff2c014430,all=false,skip_commit=false)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9318#40x000000000185700cinMYSQL_BIN_LOG::commit(this=0x2e01c80,thd=0x7fff2c014430,all=false)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440#50x0000000000f63df8inha_commit_trans(thd=0x7fff2c014430,all=false,ignore_global_read_lock=false)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1818

但是实际上binlog_commit什么都没做，因为在此之前他已经做完了需要做的事情比如flush、sync等

staticintbinlog_commit(handlerton*hton,THD*thd,boolall){DBUG_ENTER("binlog_commit");/*Nothingtodo(anymore)oncommit.*/DBUG_RETURN(0);}四、Innodb commit接口

#0innobase_commit(hton=0x2e9edd0,thd=0x7fff2c014430,commit_trx=false)at/root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4652#10x0000000000f64104inha_commit_low(thd=0x7fff2c014430,all=false,run_after_commit=false)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1923#20x000000000185772binMYSQL_BIN_LOG::process_commit_stage_queue(this=0x2e01c80,thd=0x7fff2c014430,first=0x7fff2c014430)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8647#30x0000000001858f5dinMYSQL_BIN_LOG::ordered_commit(this=0x2e01c80,thd=0x7fff2c014430,all=false,skip_commit=false)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9318#40x000000000185700cinMYSQL_BIN_LOG::commit(this=0x2e01c80,thd=0x7fff2c014430,all=false)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440#50x0000000000f63df8inha_commit_trans(thd=0x7fff2c014430,all=false,ignore_global_read_lock=false)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1818

实际上innodb comit才是需要真正做的，这里包含一些事情要做，比如事物状态的改变，资源的释放。

最后select也会增加Handler_commit，增加为1。

到此，相信大家对“MySQL中Innodb关于Handler_commit每次DML增加2的原因是什么”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！