mysql auto_inc_MySQL auto_increment剖析

本文从代码层面剖析innodb实现auto_increment的机制。

1 流程

MySQL执行插入语句总的流程如下：

execute

->mysql_insert

->write_record

->ha_write_row

->write_row

->update_auto_increment

->get_auto_increment

->innobase_get_autoinc

->innobase_lock_autoinc

->compute_next_insert_id

->row_insert_for_mysql

->dict_table_autoinc_update_if_greater

MySQL解析器在解析完INSERT语句后，会由execute入口函数执行插入语句。接下来将调用由innodb负责实现的虚函数write_row。

write_row由每个存储引擎负责实现，负责执行最终的数据插入。innodb中write_row逻辑实现如下所示：调用update_auto_increment获取自增的值(或者自增值的范围)

执行row_insert_for_mysql插入数据(非本次剖析重点)

更新内存中保存的autoinc值

2 更新自增数据

update_auto_increment 流程如下update_auto_increment流程图

get_auto_increment流程比较简单，首先是调用innodb_get_autoinc获取锁，然后计算插入ID起止区间(在插入行数确定的情况下，会将内存变量autoinc更新为插入ID区间终止值)，最后返回插入ID起始值。

innodb_get_autoinc调用innobase_lock_autoinc实现加锁。

innobase_lock_autoinc代码如下所示。

ha_innobase::innobase_lock_autoinc(void)

{

dberr_terror = DB_SUCCESS;

longlock_mode = innobase_autoinc_lock_mode;

if (dict_table_is_intrinsic(m_prebuilt->table)) {

/* Intrinsic table are not shared accorss connectionso there is no need to AUTOINC lock the table. */

lock_mode = AUTOINC_NO_LOCKING;

}

switch (lock_mode) {

case AUTOINC_NO_LOCKING:

/* Acquire only the AUTOINC mutex. */

dict_table_autoinc_lock(m_prebuilt->table);

break;

case AUTOINC_NEW_STYLE_LOCKING:

/* For simple (single/multi) row INSERTs, we fallback to theold style only if another transaction has already acquiredthe AUTOINC lock on behalf of a LOAD FILE or INSERT ... SELECTetc. type of statement. */

if (thd_sql_command(m_user_thd) == SQLCOM_INSERT

|| thd_sql_command(m_user_thd) == SQLCOM_REPLACE) {

dict_table_t*ib_table = m_prebuilt->table;

/* Acquire the AUTOINC mutex. */

dict_table_autoinc_lock(ib_table);

/* We need to check that another transaction isn'talready holding the AUTOINC lock on the table. */

if (ib_table->n_waiting_or_granted_auto_inc_locks) {

/* Release the mutex to avoid deadlocks. */

dict_table_autoinc_unlock(ib_table);

} else {

break;

}

}

/* Fall through to old style locking. */

case AUTOINC_OLD_STYLE_LOCKING:

DBUG_EXECUTE_IF("die_if_autoinc_old_lock_style_used",

ut_ad(0););

error = row_lock_table_autoinc_for_mysql(m_prebuilt);

if (error == DB_SUCCESS) {

/* Acquire the AUTOINC mutex. */

dict_table_autoinc_lock(m_prebuilt->table);

}

break;

default:

ut_error;

}

DBUG_RETURN(error);

}

lock_mode的取值有三种，代码中定义的常量如下：

static const long AUTOINC_OLD_STYLE_LOCKING = 0;

static const long AUTOINC_NEW_STYLE_LOCKING = 1;

static const long AUTOINC_NO_LOCKING = 2;

不同的模式下，需要获取的锁也是不一样的。

3 更新autoinc值

在插入数据后，write_row会在必要情况下(如lock_mode为0或插入行数不确定等)更新内存中的autoinc。

/* Get the value that MySQL attempted to store in the table. */

auto_inc = table->next_number_field->val_int();

if (auto_inc >= m_prebuilt->autoinc_last_value) {

/* This should filter out the negative values set explicitly by the user. */

if (auto_inc <= col_max_value) {

ulonglongoffset;

ulonglongincrement;

dberr_terr;

offset = m_prebuilt->autoinc_offset;

increment = m_prebuilt->autoinc_increment;

auto_inc = innobase_next_autoinc(

auto_inc,

1, increment, offset,

col_max_value);

err = innobase_set_max_autoinc(

auto_inc);

if (err != DB_SUCCESS) {

error = err;

}

}

}