MySQL中Innodb关于Handler_commit每次DML增加2的原因是什么
本篇内容主要讲解“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的原因是什么”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。