这一段时间在往sqlproxy1.9版本里添加对多行插入的insert的语句的支持,其中就涉及到了如何处理auto_increment字段的自增值分配问题。 为此特别参考了一下mysql的内部实现,并记录一下。
用例:
我们以如下sql语句的执行过程中auto_increment字段的锁处理过程来讲解
sql1:
insert into t1 values (1),(null),(2);
sql2:
insert into t2 select * from t1;
(t1,t2 表结构一样都只有一个自增字段c)
对插入行数的评估:
sql1 和 sql2 的对于mysql来说的差别在于,sql1要插入的行数是已知的(3行,通过语句解析得到),sql2要插入的行数是未知的(设为0)。
mysql将解析之后评估的行信息保存在lex->many_values并且把它作为参数传给了mysql_insert (sql/sql_insert.cc)
2857
res= mysql_insert(thd, all_tables,
lex->field_list,
lex->many_values,
2858
lex->update_list, lex->value_list,
2859
lex->duplicates, lex->ignore);
然后将行数通过ha_start_bulk_insert保存到handler的字段estimation_rows_to_insert中
835
if (thd->locked_tables_mode <= LTM_LOCK_TABLES)
836
table->file->ha_start_bulk_insert(values_list.elements);
插入时自增字段的处理:
mysql_insert 最终存储引擎接口会通过innodb的 ha_innobase::write_row 方法进行插入。(storage/innobase/handler/ha_innodb.cc )
如果要插入的表有自增列, 就调用update_auto_increment()去获取和设置handler的next_insert_id(要插入的值),并且,如果需要的话,预留一些可用的auto_increment值。
5011
5012
if (table->next_number_field && record == table->record[0]) {
5013
5014
5016
prebuilt->autoinc_error = DB_SUCCESS;
5017
5018
if ((error = update_auto_increment())) {
“预留一些可用的auto_increment值”,举例来说:
假设目前表t1的全局auto_increment_id值为100, 对于插入一行来说,这一次只需要取100这个值并把表全局auto_increment_id++。 但为了减少锁冲突,我们可以预先缓存一些值,例如10,那么操作之后表全局auto_increment_id+10。 之后的9行插入都不需要再去获取锁了。
如下是update_auto_increment实现的简单描述(sql/handler.cc)
int handler::update_auto_increment()
{
...
sql1要插入的第一行(1)走的是如下的代码,因为是一个给定的值,所以mysql直接把这个值设置到handler的next_insert_id里,并没有获取任何的锁。
2400
if ((nr= table->next_number_field->val_int()) != 0 ||
2401
(table->auto_increment_field_not_null &&
2402
thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO))
2403
{
2410
adjust_next_insert_id_after_explicit_value(nr);
2411
insert_id_for_cur_row= 0; // didn't generate anything
2412
DBUG_RETURN(0);
2413
}
如下的代码是当预留的auto_increment值不够用时(multi-row插入的第一行 / 要插入的行数未知时会碰到)
如果是第一次缓存且有预估的值(estimation_rows_to_insert > 0),那么直接将要申请的数量设置为预估值。
否则的话就是属于插入行数未知的情况。
auto_inc_intervals_count初始为0, 每次进入+1.
所以第一次预估为 1<<0 = 1
二
1<<1 = 2
三
1<<3 = 4
依次类推,auto_inc_intervals_count的最大取值为AUTO_INC_DEFAULT_NB_MAX_BITS=16
1<<16 = 65536 (但只会取65535).
顺便提一下, 上述的行为可能导致自增字段的空洞,即如果预分配的id没有被这个语句使用的话,也不会被别的使用了。具体的可以参考这个blog:http://www.zhaokunyao.com/archives/3811
sql2的执行可能会多次走到下面这段代码,这取决于插入时t1表中的行数。
2415
if ((nr= next_insert_id) >= auto_inc_interval_for_cur_row.maximum())
2416
{
...
2444
if ((auto_inc_intervals_count == 0) && (estimation_rows_to_insert > 0))
2445
nb_desired_values= estimation_rows_to_insert;
2446
else
2447
{
2448
2449
if (auto_inc_intervals_count <= AUTO_INC_DEFAULT_NB_MAX_BITS)
2450
{
2451
nb_desired_values= AUTO_INC_DEFAULT_NB_ROWS *
2452
(1 << auto_inc_intervals_count);
2453
set_if_smaller(nb_desired_values, AUTO_INC_DEFAULT_NB_MAX);
2454
}
2455
else
2456
nb_desired_values= AUTO_INC_DEFAULT_NB_MAX;
2457
}
2458
get_auto_increment是上锁去申请并获取缓存auto_increment值的实现。只有insert语句进行到了这里才开始真正的需要去获取auto_increment相关的锁
2459
get_auto_increment(variables->auto_increment_offset,
2460
variables->auto_increment_increment,
2461
nb_desired_values, &nr,
2462
&nb_reserved_values);
....
2523
auto_inc_intervals_count++;
....
如下代码的作用是如果还有可用的预留id值,那么直接取最小的。sql1之后的2行插入都是走这里。
2543
set_next_insert_id(compute_next_insert_id(nr, variables));
....
}
如何上锁:
由上面分析可知主要的上锁工作在get_auto_increment中实现(storage/innobase/handler/ha_innodb.cc)。
9930
error = innobase_get_autoinc(&autoinc);
9850 ulint
9851 ha_innobase::innobase_get_autoinc(
9852
9853
ulonglong*
value)
9854 {
9855
*value = 0;
9856
9857
prebuilt->autoinc_error = innobase_lock_autoinc();
innobase_lock_autoinc是自增锁获取的入口函数(storage/innobase/handler/ha_innodb.cc):
该函数中主要有3个分支,分别对应innodb自增锁的3种模式
case AUTOINC_NO_LOCKING: (值为2)
这种模式下每个session只会去获取全局auto_increment_id的锁&table->autoinc_mutex
这个锁在这get_auto_increment函数调用结束后就会被释放。
4811
dict_table_autoinc_lock(prebuilt->table);
case AUTOINC_NEW_STYLE_LOCKING: (值为1)
这种锁模式下如果是简单的insert或replace语句(插入行数已知)每个session会先获取
&table->autoinc_mutex,然后去检查是否有session正在持有innodb的AUTO_INC锁。AUTO_INC锁是
个表锁,它会一直持续到语句的结束,这个过程中别的session都不能获取新的自增id。
如果没有session正在持有innodb的AUTO_INC锁,那么直接返回并开始获取全局的自增id。否则放
弃&table->autoinc_mutex 并转入下一个模式AUTOINC_OLD_STYLE_LOCKING。
4819
if (thd_sql_command(user_thd) == SQLCOM_INSERT
4820
|| thd_sql_command(user_thd) == SQLCOM_REPLACE) {
4821
dict_table_t* table = prebuilt->table;
4824
dict_table_autoinc_lock(table);
4828
if (table->n_waiting_or_granted_auto_inc_locks) {
4830
dict_table_autoinc_unlock(table);
4831
} else {
4832
break;
4833
}
4834
}
n_waiting_or_granted_auto_inc_locks的值在row_lock_table_autoinc_for_mysql中增加,在
row_unlock_table_autoinc_for_mysql中减少,而row_unlock_table_autoinc_for_mysql只会在语句处理结束的时候会被调用(commit/rollback/xa_prepare, 详见storage/innobase/handler/ha_innodb.cc)。
case AUTOINC_OLD_STYLE_LOCKING: (值为0)
这个是mysql之前的处理方式,即在整个语句执行过程中上表锁。(主要是load file 和 insert select)
4838
error = row_lock_table_autoinc_for_mysql(prebuilt);
4839
4840
if (error == DB_SUCCESS) {
4841
4842
4843
dict_table_autoinc_lock(prebuilt->table);
4844
}
innodb默认的模式为AUTOINC_NEW_STYLE_LOCKING, 它较AUTOINC_OLD_STYLE_LOCKING有更大的并发性,较AUTOINC_NO_LOCKING更加复制安全(对于插入行数未知的语句,并且是语句级别的复制,对于行级复制没有关系,但多个session插入会导致自增id在多个session间穿插)。因为对于插入行数未知的语句,可能需要多次去申请并预留全局的自增id,在并发的情况下每次申请的间隔全局自增id都可能变化。
总结:
innodb为了提高并发性引入了AUTO_INC锁, 如果全局没有session正在做 “行数未知的插入”, 那么每个session只需要获取一个轻量级的mutex锁就可以了。
用例:
我们以如下sql语句的执行过程中auto_increment字段的锁处理过程来讲解
sql1:
sql2:
(t1,t2 表结构一样都只有一个自增字段c)
对插入行数的评估:
sql1 和 sql2 的对于mysql来说的差别在于,sql1要插入的行数是已知的(3行,通过语句解析得到),sql2要插入的行数是未知的(设为0)。
mysql将解析之后评估的行信息保存在lex->many_values并且把它作为参数传给了mysql_insert (sql/sql_insert.cc)
2857
2858
2859
然后将行数通过ha_start_bulk_insert保存到handler的字段estimation_rows_to_insert中
插入时自增字段的处理:
mysql_insert 最终存储引擎接口会通过innodb的 ha_innobase::write_row 方法进行插入。(storage/innobase/handler/ha_innodb.cc )
如果要插入的表有自增列, 就调用update_auto_increment()去获取和设置handler的next_insert_id(要插入的值),并且,如果需要的话,预留一些可用的auto_increment值。
“预留一些可用的auto_increment值”,举例来说:
假设目前表t1的全局auto_increment_id值为100, 对于插入一行来说,这一次只需要取100这个值并把表全局auto_increment_id++。 但为了减少锁冲突,我们可以预先缓存一些值,例如10,那么操作之后表全局auto_increment_id+10。 之后的9行插入都不需要再去获取锁了。
如下是update_auto_increment实现的简单描述(sql/handler.cc)
int handler::update_auto_increment()
{
...
sql1要插入的第一行(1)走的是如下的代码,因为是一个给定的值,所以mysql直接把这个值设置到handler的next_insert_id里,并没有获取任何的锁。
2400
2401
2402
2403
2410
2411
2412
2413
如下的代码是当预留的auto_increment值不够用时(multi-row插入的第一行 / 要插入的行数未知时会碰到)
如果是第一次缓存且有预估的值(estimation_rows_to_insert > 0),那么直接将要申请的数量设置为预估值。
否则的话就是属于插入行数未知的情况。
auto_inc_intervals_count初始为0, 每次进入+1.
所以第一次预估为 1<<0 = 1
依次类推,auto_inc_intervals_count的最大取值为AUTO_INC_DEFAULT_NB_MAX_BITS=16
1<<16 = 65536 (但只会取65535).
顺便提一下, 上述的行为可能导致自增字段的空洞,即如果预分配的id没有被这个语句使用的话,也不会被别的使用了。具体的可以参考这个blog:http://www.zhaokunyao.com/archives/3811
sql2的执行可能会多次走到下面这段代码,这取决于插入时t1表中的行数。
2415
2416
...
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
get_auto_increment是上锁去申请并获取缓存auto_increment值的实现。只有insert语句进行到了这里才开始真正的需要去获取auto_increment相关的锁
2459
2460
2461
2462
....
2523
....
如下代码的作用是如果还有可用的预留id值,那么直接取最小的。sql1之后的2行插入都是走这里。
2543
....
}
如何上锁:
由上面分析可知主要的上锁工作在get_auto_increment中实现(storage/innobase/handler/ha_innodb.cc)。
innobase_lock_autoinc是自增锁获取的入口函数(storage/innobase/handler/ha_innodb.cc):
该函数中主要有3个分支,分别对应innodb自增锁的3种模式
case AUTOINC_NO_LOCKING:
case AUTOINC_NEW_STYLE_LOCKING: (值为1)
n_waiting_or_granted_auto_inc_locks的值在row_lock_table_autoinc_for_mysql中增加,在
row_unlock_table_autoinc_for_mysql中减少,而row_unlock_table_autoinc_for_mysql只会在语句处理结束的时候会被调用(commit/rollback/xa_prepare, 详见storage/innobase/handler/ha_innodb.cc)。
case AUTOINC_OLD_STYLE_LOCKING: (值为0)
4838
innodb默认的模式为AUTOINC_NEW_STYLE_LOCKING, 它较AUTOINC_OLD_STYLE_LOCKING有更大的并发性,较AUTOINC_NO_LOCKING更加复制安全(对于插入行数未知的语句,并且是语句级别的复制,对于行级复制没有关系,但多个session插入会导致自增id在多个session间穿插)。因为对于插入行数未知的语句,可能需要多次去申请并预留全局的自增id,在并发的情况下每次申请的间隔全局自增id都可能变化。
总结:
innodb为了提高并发性引入了AUTO_INC锁, 如果全局没有session正在做 “行数未知的插入”, 那么每个session只需要获取一个轻量级的mutex锁就可以了。