HandlerSocket返回错误码167的bug分析
用HandlerSocket大量并发插入数据到多个InnoDB的表(都使用自增id)的时候就会大量出现167的错误,从而TPS下降极为厉害.
让我们来分析这个bug是怎么产生的.
我们先来重现这个bug:
先建两个表:
CREATE TABLE test1 ( id int(20) unsigned NOT NULL AUTO_INCREMENT, data varchar(200) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1; CREATE TABLE test2 ( id int(20) unsigned NOT NULL AUTO_INCREMENT, data varchar(200) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1;
然后用HandlerSocket大并发的执行类似这样SQL的命令:
insert into test1 (data) values (\'1\'); insert into test1 (data) values (\'2\'); insert into test2 (data) values (\'3\'); insert into test2 (data) values (\'4\');
那么很快就会出现167的错误
那我们来看看167是什么意思, 167是
Handler->ha_write_row()
返回的错误码,即
#define HA_ERR_AUTOINC_ERANGE 167 /* Failed to set row autoinc value */
表示已经越界了
查看可能返回HA_ERR_AUTOINC_ERANGE的地方,就在int handler::update_auto_increment()这个方法里,当
if (unlikely(nr == ULONGLONG_MAX)) DBUG_RETURN(HA_ERR_AUTOINC_ERANGE);
时,会有问题
而什么时候nr会等于ULONGLONG_MAX呢,看上面的代码
/* This call ignores all its parameters but nr, currently */ get_auto_increment(variables->auto_increment_offset, variables->auto_increment_increment, nb_desired_values, &nr, &nb_reserved_values); if (nr == ULONGLONG_MAX) DBUG_RETURN(HA_ERR_AUTOINC_READ_FAILED); // Mark failure /* That rounding below should not be needed when all engines actually respect offset and increment in get_auto_increment(). But they don\'t so we still do it. Wonder if for the not-first-in-index we should do it. Hope that this rounding didn\'t push us out of the interval; even if it did we cannot do anything about it (calling the engine again will not help as we inserted no row). */ nr= compute_next_insert_id(nr-1, variables);
在nr= compute_next_insert_id(nr-1, variables); 这一步会导致nr等于ULONGLONG_MAX
那么在get_auto_increment()这步的时候nr返回就必须是0,而nr传进去的时候就是0
那么就是get_auto_increment()没有起效果
我们再看InnoDB的get_auto_increment()实现
UNIV_INTERN void ha_innobase::get_auto_increment( /*============================*/ ulonglong offset, /*!< in: table autoinc offset */ ulonglong increment, /*!< in: table autoinc increment */ ulonglong nb_desired_values, /*!< in: number of values reqd */ ulonglong *first_value, /*!< out: the autoinc value */ ulonglong *nb_reserved_values) /*!< out: count of reserved values */ { trx_t* trx; ulint error; ulonglong autoinc = 0; /* Prepare prebuilt->trx in the table handle */ update_thd(ha_thd()); error = innobase_get_autoinc(&autoinc); if (error != DB_SUCCESS) { *first_value = (~(ulonglong) 0); return; } /* This is a hack, since nb_desired_values seems to be accurate only for the first call to get_auto_increment() for multi-row INSERT and meaningless for other statements e.g, LOAD etc. Subsequent calls to this method for the same statement results in different values which don\'t make sense. Therefore we store the value the first time we are called and count down from that as rows are written (see write_row()). */ trx = prebuilt->trx; /* Note: We can\'t rely on *first_value since some MySQL engines, in particular the partition engine, don\'t initialize it to 0 when invoking this method. So we are not sure if it\'s guaranteed to be 0 or not. */ /* We need the upper limit of the col type to check for whether we update the table autoinc counter or not. */ ulonglong col_max_value = innobase_get_int_col_max_value( table->next_number_field); /* Called for the first time ? */ if (trx->n_autoinc_rows == 0) { trx->n_autoinc_rows = (ulint) nb_desired_values; /* It\'s possible for nb_desired_values to be 0: e.g., INSERT INTO T1(C) SELECT C FROM T2; */ if (nb_desired_values == 0) { trx->n_autoinc_rows = 1; } set_if_bigger(*first_value, autoinc); /* Not in the middle of a mult-row INSERT. */ } else if (prebuilt->autoinc_last_value == 0) { set_if_bigger(*first_value, autoinc); /* Check for -ve values. */ } else if (*first_value > col_max_value && trx->n_autoinc_rows > 0) { /* Set to next logical value. */ ut_a(autoinc > trx->n_autoinc_rows); *first_value = (autoinc - trx->n_autoinc_rows) - 1; } *nb_reserved_values = trx->n_autoinc_rows; /* With old style AUTOINC locking we only update the table\'s AUTOINC counter after attempting to insert the row. */ if (innobase_autoinc_lock_mode != AUTOINC_OLD_STYLE_LOCKING) { ulonglong need; ulonglong current; ulonglong next_value; current = *first_value > col_max_value ? autoinc : *first_value; need = *nb_reserved_values * increment; /* Compute the last value in the interval */ next_value = innobase_next_autoinc( current, need, offset, col_max_value); prebuilt->autoinc_last_value = next_value; if (prebuilt->autoinc_last_value < *first_value) { *first_value = (~(ulonglong) 0); } else { /* Update the table autoinc variable */ dict_table_autoinc_update_if_greater( prebuilt->table, prebuilt->autoinc_last_value); } } else { /* This will force write_row() into attempting an update of the table\'s AUTOINC counter. */ prebuilt->autoinc_last_value = 0; } /* The increment to be used to increase the AUTOINC value, we use this in write_row() and update_row() to increase the autoinc counter for columns that are filled by the user. We need the offset and the increment. */ prebuilt->autoinc_offset = offset; prebuilt->autoinc_increment = increment; dict_table_autoinc_unlock(prebuilt->table); }
能导致first_value不改变的请求只有
if (trx->n_autoinc_rows == 0) { trx->n_autoinc_rows = (ulint) nb_desired_values; /* It\'s possible for nb_desired_values to be 0: e.g., INSERT INTO T1(C) SELECT C FROM T2; */ if (nb_desired_values == 0) { trx->n_autoinc_rows = 1; } set_if_bigger(*first_value, autoinc); /* Not in the middle of a mult-row INSERT. */ } else if (prebuilt->autoinc_last_value == 0) { set_if_bigger(*first_value, autoinc); /* Check for -ve values. */ } else if (*first_value > col_max_value && trx->n_autoinc_rows > 0) { /* Set to next logical value. */ ut_a(autoinc > trx->n_autoinc_rows); *first_value = (autoinc - trx->n_autoinc_rows) - 1; }
这3个判断都没有满足
而HandlerSocket再并发插入多表时确实可能导致3个条件都不满足.
为什么呢,在于trx->n_autoinc_rows和prebuilt->autoinc_last_value这两个变量
由于新的InnoDB自增id的策略,在多行插入的时候为了减少自增id的锁堵塞,所以会一次生成一个大于auto_increment_offset跨度的id序列,trx->n_autoinc_rows表示就是这个序列的大小
prebuilt->autoinc_last_value表示的是这个表的这个序列的最后一个值
1.第一个条件不满足
大家注意到了n_autoinc_rows是在trx上的,autoinc_last_value是在prebuilt上的,trx又是在THD上的.这就导致多表自增的时候一个表生成的n_autoinc_rows,可能会作用于两个表上.
比如nb_desired_values=2而实际只插入了一条记录,那么循环后trx->n_autoinc_rows = 1 那么换成另一个表的时候后trx->n_autoinc_rows = 1,那么第一个条件就不满足.
2.第二个条件不满足
而第二个条件,即当第二个表也有自增过之后
/* Compute the last value in the interval */ next_value = innobase_next_autoinc( current, need, offset, col_max_value); prebuilt->autoinc_last_value = next_value;
prebuilt->autoinc_last_value必然是大于0的,就是有错误,*first_value=0 最后prebuilt->autoinc_last_value也会等于1,那么这个时候第二个条件也会不满足!
3.第三个条件不满足
而当*first_value=0 时,第三个条件自然也是满足不了的.
所以最后就导致update_auto_increment()返回了167错误~
解决办法:
1.设置innodb_autoinc_lock_mode=0 是InnoDB采用老式的自增id分配算法可以避免这种请求
2.修改HandlerSocket代码,在dbcontext::cmd_insert_internal()的const int r = hnd->ha_write_row(buf)之前加上hnd->start_stmt(thd,TL_WRITE)对trx->n_autoinc_rows进行重置来避免这个问题
建议继续学习:
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:淘宝文通 来源: 苦逼程序员
- 标签: HandlerSocket
- 发布时间:2012-02-26 22:50:57
- [70] Twitter/微博客的学习摘要
- [66] 如何拿下简短的域名
- [65] IOS安全–浅谈关于IOS加固的几种方法
- [64] find命令的一点注意事项
- [63] android 开发入门
- [63] Go Reflect 性能
- [61] 流程管理与用户研究
- [59] 图书馆的世界纪录
- [59] 读书笔记-壹百度:百度十年千倍的29条法则
- [59] Oracle MTS模式下 进程地址与会话信