INSERT...SELETC でauto_increment値が飛ぶ話

環境

CREATE TABLE `t2` (
  `id` int NOT NULL,
  `id2` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `id2` (`id2`)
)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 8.0.28-debug |
+--------------+
1 row in set (0.00 sec)

mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select @@auto_increment_offset;
+-------------------------+
| @@auto_increment_offset |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          2 |
+----------------------------+
1 row in set (0.01 sec)

※innodb_autoinc_lock_mode=1でも同様のことは起こる

事象

insert...selectでテストデータ増やしてたらauto_increment値が飛び飛びの値となった。

mysql> insert into t2(id) values(1);
Query OK, 1 row affected (0.02 sec)

--①insert...selectで順に(2),(3),(4)の行を挿入
mysql> insert into t2(id) select id+1 from t2;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2(id) select id+2 from t2;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

--②想定通りの挿入結果
mysql> select * from t2;
+----+-----+
| id | id2 |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
|  4 |   4 |
+----+-----+
4 rows in set (0.02 sec)

--③同様に(5),(6),(7),(8)を挿入
mysql> insert into t3(id) select id+4 from t3;

↓
--こうなって欲しい
+----+-----+
| id | id2 |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
|  4 |   4 |
|  5 |   5 |
|  6 |   6 |
|  7 |   7 |
|  8 |   8 |
+----+-----+

--こうなる
mysql> select * from t3;
+----+-----+
| id | id2 |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
|  4 |   4 |
|  5 |   6 |
|  6 |   7 |
|  7 |   8 |
|  8 |   9 |
+----+-----+
8 rows in set (0.00 sec)

なお、①,③の部分をinsert...valuesで直接値を指定するとこのような事象は起きない。

なんでこうなる

insert...valuesは直接値を指定するが故に、事前に挿入行数が分かっている挿入となるが、一方でinsert...selectは事前に挿入行数が分からないために見積もりがずれてしまう。そのためauto_increment値の計算に齟齬が発生してしまう。

どんな計算をしていてどのような理由でズレが生じるのか次章から見ていく。

ちなみにズレが生じるポイントは③ではなく②の時点から起きている。正常と思われていた②の状態でschema_auto_increment_columnsを確認すると、以下のようにauto_increment値が6になってしまっている。これはカラムの「現在の auto_increment値」であり、本来は5になって欲しい値である。

すなわち、このauto_increment値が6の状態で③を行うことにより、次にinsertされる行のauto_increment値が6になってしまうのである。

mysql> select * from sys.schema_auto_increment_columns where table_name = 't2'\G
*************************** 1. row ***************************
        table_schema: kubo
          table_name: t2
         column_name: id2
           data_type: int
         column_type: int
           is_signed: 1
         is_unsigned: 0
           max_value: 2147483647
      auto_increment: 6
auto_increment_ratio: 0.0000
1 row in set (0.02 sec)

ソース

現在のauto_increment値を読み出している部分
現在のauto_increment値を更新している部分

を順に見ていく。その前にこれらのパートを呼び出している大元のメソッドについてざっくり触れる

update_auto_increment

auto_incrementに関するアレコレは以下のupdate_auto_incrementメソッドでやりくりされる。

このメソッドをざっくり理解するために以降は以下のような状況を例として用いる

auto_increment_increment＝5（つまり5ずつ増える）
auto_increment_offset＝10（つまり開始位置が10）
各行のauto_increment値が隙間なく95まで埋まっている
insert...valueでauto_increment値が100,105,110が埋まる予定である

まずauto_increment値が100の行を挿入する時にこのメソッドが呼び出される。

この時点ではnext_insert_idもauto_inc_interval_for_cur_row.minimum()も０のため以下のアサーションが通る。ちなみに、このメソッドの後半でこれらの値が付与されて、次のauto_increment値が105の行を挿入する際に参照される。

int handler::update_auto_increment() {
  ulonglong nr, nb_reserved_values = 0;
  bool append = false;
  THD *thd = table->in_use;
  struct System_variables *variables = &thd->variables;
  assert(table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK);
  DBUG_TRACE;

  /*
    next_insert_id is a "cursor" into the reserved interval, it may go greater
    than the interval, but not smaller.
  */
  assert(next_insert_id >= auto_inc_interval_for_cur_row.minimum());

同様に続きの条件式もtrueとなる。同じく次のauto_increment値が105の行を挿入する際にはこれらの条件式が活きてくる。

if ((nr = next_insert_id) >= auto_inc_interval_for_cur_row.maximum()) {

次に上記のifにネストしている部分では、auto_inc_intervals_countとestimation_rows_to_insertの評価がされる。auto_inc_intervals_countは予約されたauto_incrementの間隔である。これはまだ予約を行っていないので0になる。また、estimation_rows_to_insertは見積もられた挿入行数である。insert...valuesの場合はすでに挿入行数がわかっているので、正の数が入る。一方でinsert...selectの場合はこの値は0である。

今回の例の場合はestimation_rows_to_insertは3となり条件式はtrueとなる。そしてnb_desired_valuesにestimation_rows_to_insertの値が入る。変数名から察するにestimation_rows_to_insertの数だけauto_increment値が入ると期待されているということである。

if ((auto_inc_intervals_count == 0) && (estimation_rows_to_insert > 0))
        nb_desired_values = estimation_rows_to_insert;
      else if ((auto_inc_intervals_count == 0) &&
               (thd->lex->bulk_insert_row_cnt > 0)) {
        /*
          For multi-row inserts, if the bulk inserts cannot be started, the
          handler::estimation_rows_to_insert will not be set. But we still
          want to reserve the autoinc values.
        */
        nb_desired_values = thd->lex->bulk_insert_row_cnt;
      } else /* go with the increasing defaults */
      {
        /* avoid overflow in formula, with this if() */
        if (auto_inc_intervals_count <= AUTO_INC_DEFAULT_NB_MAX_BITS) {
          nb_desired_values =
              AUTO_INC_DEFAULT_NB_ROWS * (1 << auto_inc_intervals_count);
          nb_desired_values =
              std::min(nb_desired_values, ulonglong(AUTO_INC_DEFAULT_NB_MAX));
        } else
          nb_desired_values = AUTO_INC_DEFAULT_NB_MAX;
      }

続けてif ((nr = next_insert_id) >= auto_inc_interval_for_cur_row.maximum())のネストしている部分では、以下のようにget_auto_incrementを呼び出している。内部では特にschema_auto_increment_columnsのauto_increment値を参照して、今回入れるべきauto_increment値、次の行または次のinsertの機会に入れるべきauto_increment値を計算をしている。

get_auto_increment(variables->auto_increment_offset,
                         variables->auto_increment_increment, nb_desired_values,
                         &nr, &nb_reserved_values);
      if (nr == ULLONG_MAX) return HA_ERR_AUTOINC_READ_FAILED;  // Mark failure

次に予約されたauto_increment範囲や次の行または次のinsertの機会に入れるべきauto_increment値がauto_inc_interval_for_cur_rowに格納される。またnext_insert_idにも値が格納される。これらは次の行（auto_increment値が105,110の行を挿入する時）に関する評価の際に用いられる。

if (append) {
    auto_inc_interval_for_cur_row.replace(nr, nb_reserved_values,
                                          variables->auto_increment_increment);
    auto_inc_intervals_count++;
    /* Row-based replication does not need to store intervals in binlog */
    if (mysql_bin_log.is_open() && !thd->is_current_stmt_binlog_format_row())
      thd->auto_inc_intervals_in_cur_stmt_for_binlog.append(
          auto_inc_interval_for_cur_row.minimum(),
          auto_inc_interval_for_cur_row.values(),
          variables->auto_increment_increment);
  }

  /*
    Record this autogenerated value. If the caller then
    succeeds to insert this value, it will call
    record_first_successful_insert_id_in_cur_stmt()
    which will set first_successful_insert_id_in_cur_stmt if it's not
    already set.
  */
  insert_id_for_cur_row = nr;
  /*
    Set next insert id to point to next auto-increment value to be able to
    handle multi-row statements.
  */
  set_next_insert_id(compute_next_insert_id(nr, variables));

  return 0;

現在のauto_increment値を読み出している部分

では、まず現在のauto_increment値を読み出しているソースを見てみる

以下がauto_increment値をディクショナリから読み出している部分となる。つまりここで次に入れるべきauto_increment値が決まる。

/** Reads the next autoinc value (== autoinc counter value), 0 if not yet
 initialized.
 @return value for a new row, or 0 */
ib_uint64_t dict_table_autoinc_read(const dict_table_t *table) /*!< in: table */
{
  ut_ad(dict_table_autoinc_own(table));

  return (table->autoinc);
}

これを呼び出している箇所は以下である。

/** Read the next autoinc value. Acquire the relevant locks before reading
 the AUTOINC value. If SUCCESS then the table AUTOINC mutex will be locked
 on return and all relevant locks acquired.
 @return DB_SUCCESS or error code */

dberr_t ha_innobase::innobase_get_autoinc(
    ulonglong *value) /*!< out: autoinc value */
{
  *value = 0;

  m_prebuilt->autoinc_error = innobase_lock_autoinc();

  if (m_prebuilt->autoinc_error == DB_SUCCESS) {
    /* Determine the first value of the interval */
    *value = dict_table_autoinc_read(m_prebuilt->table);

    /* It should have been initialized during open. */
    if (*value == 0) {
      m_prebuilt->autoinc_error = DB_UNSUPPORTED;
      dict_table_autoinc_unlock(m_prebuilt->table);
    }
  }

  return (m_prebuilt->autoinc_error);
}

ここでm_prebuiltはrow_prebuilt_tという型であり、テーブルハンドラに予め用意された関連情報を表す。これによって、必要になった際にディクショナリを読みに行くなどしなくても済む。これはテーブルオープン時に構成される。

そして、このメソッドは先ほど紹介したha_innobase::get_auto_incrementから呼び出される。参考までに、このメソッドを用いている位置までのバックトレースは以下である。

ha_innobase::innobase_get_autoinc(ha_innobase * const this, ulonglong * value) (/mysql-8.0.28/storage/innobase/handler/ha_innodb.cc:19357)
ha_innobase::get_auto_increment(ha_innobase * const this, ulonglong offset, ulonglong increment, ulonglong nb_desired_values, ulonglong * first_value, ulonglong * nb_reserved_values) (/mysql-8.0.28/storage/innobase/handler/ha_innodb.cc:19400)
handler::update_auto_increment(handler * const this) (/mysql-8.0.28/sql/handler.cc:3815)
ha_innobase::write_row(ha_innobase * const this, uchar * record) (/mysql-8.0.28/storage/innobase/handler/ha_innodb.cc:8880)
handler::ha_write_row(handler * const this, uchar * buf) (/mysql-8.0.28/sql/handler.cc:7937)
write_record(THD * thd, TABLE * table, COPY_INFO * info, COPY_INFO * update) (/mysql-8.0.28/sql/sql_insert.cc:2165)
Query_result_insert::send_data(Query_result_insert * const this, THD * thd, const mem_root_deque<Item*> & values) (/mysql-8.0.28/sql/sql_insert.cc:2400)
Query_expression::ExecuteIteratorQuery(Query_expression * const this, THD * thd) (/mysql-8.0.28/sql/sql_union.cc:1315)
Query_expression::execute(Query_expression * const this, THD * thd) (/mysql-8.0.28/sql/sql_union.cc:1353)
Sql_cmd_dml::execute_inner(Sql_cmd_dml * const this, THD * thd) (/mysql-8.0.28/sql/sql_select.cc:781)
Sql_cmd_dml::execute(Sql_cmd_dml * const this, THD * thd) (/mysql-8.0.28/sql/sql_select.cc:581)
mysql_execute_command(THD * thd, bool first_level) (/mysql-8.0.28/sql/sql_parse.cc:3553)
dispatch_sql_command(THD * thd, Parser_state * parser_state) (/mysql-8.0.28/sql/sql_parse.cc:5174)
dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (/mysql-8.0.28/sql/sql_parse.cc:1938)
do_command(THD * thd) (/mysql-8.0.28/sql/sql_parse.cc:1352)
handle_connection(void * arg) (/mysql-8.0.28/sql/conn_handler/connection_handler_per_thread.cc:302)
pfs_spawn_thread(void * arg) (/mysql-8.0.28/storage/perfschema/pfs.cc:2947)
libpthread.so.0!start_thread(void * arg) (/build/glibc-SzIz7B/glibc-2.31/nptl/pthread_create.c:477)
libc.so.6!clone() (/build/glibc-SzIz7B/glibc-2.31/sysdeps/unix/sysv/linux/x86_64/clone.S:95)

現在のauto_increment値を更新している部分

最後に、現在のauto_increment値を更新しているソースを調べる。

先程のauto_increment値を読み出している部分よりも後になるはずので、innobase_get_autoincを呼び出した後をじっくり見ると、以下のように次の「現在のauto_increment値」を計算して（innobase_next_autoinc）更新する（dict_table_autoinc_update_if_greater）ようなソースが確認できる。

※dict_table_autoinc_update_if_greaterの方のソースは紹介しないが、メソッド内ではm_prebuilt->table->autoincの値を更新している様子が確認できる。今回の場合で言えば、innobase_next_autoincの計算結果に更新している。

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;
  dberr_t error;
  ulonglong autoinc = 0;

  /* Prepare m_prebuilt->trx in the table handle */
  update_thd(ha_thd());

  error = innobase_get_autoinc(&autoinc);
  
  ...
  ...
  
/* Compute the last value in the interval */
    next_value = innobase_next_autoinc(current, *nb_reserved_values, increment,
                                       offset, col_max_value);

    m_prebuilt->autoinc_last_value = next_value;

    if (m_prebuilt->autoinc_last_value < *first_value) {
      *first_value = (~(ulonglong)0);
    } else {
      /* Update the table autoinc variable */
      dict_table_autoinc_update_if_greater(m_prebuilt->table,
                                           m_prebuilt->autoinc_last_value);
    }

schema_auto_increment_columnsのauto_increment値が想定と異なるのはおそらくinnobase_next_autoincでの計算によるものと推察できる。

計算の中身を除いてみる。

/** Compute the next autoinc value.

 For MySQL replication the autoincrement values can be partitioned among
 the nodes. The offset is the start or origin of the autoincrement value
 for a particular node. For n nodes the increment will be n and the offset
 will be in the interval [1, n]. The formula tries to allocate the next
 value for a particular node.

 Note: This function is also called with increment set to the number of
 values we want to reserve for multi-value inserts e.g.,

         INSERT INTO T VALUES(), (), ();

 innobase_next_autoinc() will be called with increment set to 3 where
 autoinc_lock_mode != TRADITIONAL because we want to reserve 3 values for
 the multi-value INSERT above.
 @return the next value */
ulonglong innobase_next_autoinc(
    ulonglong current,   /*!< in: Current value */
    ulonglong need,      /*!< in: count of values needed */
    ulonglong step,      /*!< in: AUTOINC increment step */
    ulonglong offset,    /*!< in: AUTOINC offset */
    ulonglong max_value) /*!< in: max value for type */
{
  ulonglong next_value;
  ulonglong block = need * step;

  /* Should never be 0. */
  ut_a(need > 0);
  ut_a(block > 0);
  ut_a(max_value > 0);

  /* According to MySQL documentation, if the offset is greater than
  the step then the offset is ignored. */
  if (offset > block) {
    offset = 0;
  }

  /* Check for overflow. Current can be > max_value if the value is
  in reality a negative value.The visual studio compilers converts
  large double values automatically into unsigned long long datatype
  maximum value */

  if (block >= max_value || offset > max_value || current >= max_value ||
      max_value - offset <= offset) {
    next_value = max_value;
  } else {
    ut_a(max_value > current);

    ulonglong free = max_value - current;

    if (free < offset || free - offset <= block) {
      next_value = max_value;
    } else {
      next_value = 0;
    }
  }

  if (next_value == 0) {
    ulonglong next;

    if (current > offset) {
      next = (current - offset) / step;
    } else {
      next = (offset - current) / step;
    }

    ut_a(max_value > next);
    next_value = next * step;
    /* Check for multiplication overflow. */
    ut_a(next_value >= next);
    ut_a(max_value > next_value);

    /* Check for overflow */
    if (max_value - next_value >= block) {
      next_value += step;

      if (max_value - next_value >= offset) {
        next_value += offset;
      } else {
        next_value = max_value;
      }
    } else {
      next_value = max_value;
    }
  }

  ut_a(next_value != 0);
  ut_a(next_value <= max_value);

  return (next_value);
}

いくつか変数があるので整理する。多分こんな感じ

current　＝　現在のauto_increment値
next_value　＝　次の「現在のauto_increment値」
need　＝　挿入予定のauto_increment値が何個あるか
step　＝　いくつずつauto_increment値が増えるか
offset　＝　いくつからauto_increment値が増えているか
max_value　＝　auto_incrementの最大限界値
block　＝ need*step

ちなみにこの計算ロジックはinsert....selectの場合は挿入する行数だけ呼びだされる。挿入行数が事前に分かるinsert...valuesの場合は一度しか呼ばれない（事前に分かっていれば次の「現在のauto_increment値」の計算は１回で十分ということ）。

さて、注目すべきは後半の部分。まずは

next = (current - offset) / step;
...
next_value = next * step;

でnext_valueが現在のauto_increment値と開始値の差分となる

そして

/ Check for overflow /
    if (max_value - next_value >= block) {
      next_value += block;

  <span class="synStatement">if</span> (max_value - next_value &gt;= offset) {
    next_value += offset;
  } <span class="synStatement">else</span> {
    next_value = max_value;
  }
} <span class="synStatement">else</span> {
  next_value = max_value;
}

その差分にblockとoffsetを足し合わせる。

何が起きてるのか分かりにくいので、insert...valueとinsert...selectの場合でそれぞれ例を見てみる。以下の条件でデバッグ実行して変数を覗き見した結果をもとに説明する。

auto_increment_increment＝1（つまり1ずつ増える）
auto_increment_offset＝1（つまり開始位置が1）
各行のauto_increment値が隙間なく2まで埋まっている
insert...valueもしくはinsert...selectでauto_increment値が3,4が埋まる予定である

insert...select

	1 回目	２回目
current	3	4
need	1	2
step	1	1
offset	1	1
max_value	2147483647	2147483647
block	1	2
next_value	4	6

insert...selectの場合この計算ロジックを２回通る。１回目はauto_increment値が3となる行にカーソルが当たり実行される。この時、以下の計算により

next = (current - offset) / step;
...
next_value = next * step;

next_valueが3-1=2となる。つまり開始位置と挿入しようとしている位置との距離が2となる。そして以下の計算により

/* Check for overflow */
    if (max_value - next_value >= block) {
      next_value += block;

      if (max_value - next_value >= offset) {
        next_value += offset;
      } else {
        next_value = max_value;
      }
    } else {
      next_value = max_value;
    }

next_valueが2+1+1=4となる。つまり開始位置に距離とblock（「何個のauto_increment値を挿入予定か」と「いくつずつauto_incrementが増えるか」の積）を足すことで、次の「現在のauto_increment値」を表している。

２回目はauto_increment値が4となる行にカーソルが当たり実行される。同様の計算により、次の「現在のauto_increment値」が6となってしまう。これはblockが1でなく2、特にneedが2になってしまうことに由来する。つまり、行数が正しく見積もられていない事（挿入行数が事前に分からない事）に由来している。

※初めはblockを足さずにstepを足すのが妥当かと思われたが、そのようにすると次のinsert...valueでの計算が拗れる

insert...value

	1 回目	２回目
current	3	-
need	2	-
step	1	-
offset	1	-
max_value	2147483647	-
block	2	-
next_value	5	-

insert...valueの場合はこの計算ロジックは1回しか通らない。挿入行数が事前に分かっているので、1回目の時点でneedが2になっていることが分かる。そのため。この1回の計算ロジックで次回の「現在のauto_increment値」が計算できている。

ちなみに

同じような現象を示したMySQL Bugsがある

MySQL Bugs: #57643: InnoDB skips auto_increment values with LOAD DATA INFILE

MySQL Bugs: #70692: auto_increment wrong value when using insert into... select from

そもそもauto_incrementは連番を保証したものではないのでバグではないよとのコメントがあったり

I suggest that it is "not a bug". I believe that there is nothing saying that AUTO_INCREMENT values must avoid wasting ids. Sure, it is annoying.

There are many other cases of 'burned' ids: * Rolled back Transaction. * INSERT IGNORE with multiple rows (and other INSERTs/REPLACEs).

An un-burned id that is annoying in a different direction: 1. DELETE the row with MAX(id) 2. Restart the server 3. Now that id will be reused (InnoDB only?).

そういう事前に挿入行数がわからない場合は大きめのインターバルを予約する仕組みなんだよーといったコメントがあったりした。なるほど〜

According to the source code https://github.com/mysql/mysql-server/blob/8.0/sql/handler.cc#L3820, for statements like INSERT INTO ... SELECT, where the exact number of inserted rows is unknown in advance, the SQL handler reserves intervals of auto-increment values while processing the records, every time reserving twice as larger an interval as the previous one as soon as the number of rows reaches the highest value of the previous interval. For example, when inserting 4 records, first, an interval of 1 gets reserved, afterwards an interval of 2, then 4, and in the end 8. In this particular case, the next auto-increment value is going to be 8 instead of 5, and as a result, a gap between 4 and 8 appears. With larger numbers, the gap gets much more significant, for instance, while processing data in bulk say by 5000, the next auto-increment will be the current column value + 8192 instead of the current column value + 5000 + 1. As a workaround to this, data could be processed in batches equal to the value of 2ⁿ-1. However, even with this, gaps can't be avoided at all but at least could be reduced.

amamanamam

データベースと仲良くなりたいです