MariaDBでAUTO_INCREMENTの値が戻る罠

GMOアドマーケティングのT.Kです。
MariaDBでAUTO_INCREMENTの値が戻る現象に遭遇したので、紹介したいと思います。
発生条件はパーティションを使っているテーブルに対して、並列トランザクションでINSERTを行い、片方のトランザクションで払い出したAUTO_INCREMENTの値を使わなかった場合です。

• 検証環境
  OS: CentOS 7
  DB: MariaDB 10.4.13
  各種設定はデフォルト値
• 再現手順
  • テーブル
    
    テーブル作成

    MySQL

    CREATE TABLE `hoge_report` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `hoge_id` INT(10) NOT NULL , `request` INT(10) NOT NULL, `report_date` date NOT NULL, PRIMARY KEY (`id`, `report_date`) , UNIQUE KEY (`hoge_id`, `report_date`) ) ENGINE=InnoDB PARTITION BY RANGE COLUMNS(`report_date`) ( PARTITION `p202002` VALUES LESS THAN ('2020-03-01'), PARTITION `p202003` VALUES LESS THAN ('2020-04-01') );

    1 2 3 4 5 6 7 8 9 10 11 12 13

    CREATE TABLE `hoge_report` (   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,   `hoge_id` INT(10) NOT NULL ,   `request` INT(10) NOT NULL,   `report_date` date NOT NULL,   PRIMARY KEY (`id`, `report_date`) ,   UNIQUE KEY (`hoge_id`, `report_date`) ) ENGINE=InnoDB PARTITION BY RANGE  COLUMNS(`report_date`) ( PARTITION `p202002` VALUES LESS THAN ('2020-03-01'), PARTITION `p202003` VALUES LESS THAN ('2020-04-01') );

  • transaction１
    
    transaction１

    MySQL

    > INSERT INTO hoge_report(hoge_id, request, report_date) VALUES(1, 10, '2020-02-29'); Query OK, 1 row affected (0.001 sec)

    1 2	> INSERT INTO hoge_report(hoge_id, request, report_date) VALUES(1, 10, '2020-02-29'); Query OK, 1 row affected (0.001 sec)

  • transaction２
    ロック待ちにするために、transaction１と同じUNIQUE KEYを使用します。
    
    transaction 2

    MySQL

    > INSERT INTO hoge_report(hoge_id, request, report_date) VALUES(1, 15, '2020-02-29');

    1	> INSERT INTO hoge_report(hoge_id, request, report_date) VALUES(1, 15, '2020-02-29');

  • transaction１
    
    transaction１

    MySQL

    > INSERT INTO hoge_report(hoge_id, request, report_date) VALUES(2, 20, '2020-02-29'); Query OK, 1 row affected (0.001 sec) MariaDB [test]> SELECT AUTO_INCREMENT from information_schema.`TABLES` WHERE TABLE_NAME='hoge_report'; +----------------+ | AUTO_INCREMENT | +----------------+ | 4 | +----------------+ 1 row in set (0.001 sec) MariaDB [test]> commit; Query OK, 0 rows affected (0.002 sec)

    1 2 3 4 5 6 7 8 9 10 11 12 13

    > INSERT INTO hoge_report(hoge_id, request, report_date) VALUES(2, 20, '2020-02-29'); Query OK, 1 row affected (0.001 sec)   MariaDB [test]> SELECT AUTO_INCREMENT from information_schema.`TABLES` WHERE TABLE_NAME='hoge_report'; +----------------+ | AUTO_INCREMENT | +----------------+ |              4 | +----------------+ 1 row in set (0.001 sec)   MariaDB [test]> commit; Query OK, 0 rows affected (0.002 sec)

  • transaction２
    
    ERROR 1062 (23000): Duplicate entry '1-2020-02-29' for key 'hoge_id'

    1	ERROR 1062 (23000): Duplicate entry '1-2020-02-29' for key 'hoge_id'

• 状態の確認
  ここまでは想定通りの動作のように見えます。
  しかし、改めてAUTO_INCREMENTを確認すると「2」に戻っています。
  
  > SELECT AUTO_INCREMENT from information_schema.`TABLES` WHERE TABLE_NAME='hoge_report'; +----------------+ | AUTO_INCREMENT | +----------------+ | 2 | +----------------+

  1 2 3 4 5 6

  > SELECT AUTO_INCREMENT from information_schema.`TABLES` WHERE TABLE_NAME='hoge_report'; +----------------+ | AUTO_INCREMENT | +----------------+ |              2 | +----------------+

  
  追加でデータをINSERTすると、しっかり「2」が使われています。
  
  > INSERT INTO hoge_report(hoge_id, request, report_date) VALUES(10, 100, '2020-02-29'); Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from hoge_report; +----+---------+---------+-------------+ | id | hoge_id | request | report_date | +----+---------+---------+-------------+ | 1 | 1 | 10 | 2020-02-29 | | 2 | 10 | 100 | 2020-02-29 | | 3 | 2 | 20 | 2020-02-29 | +----+---------+---------+-------------+ 3 rows in set (0.000 sec)

  1 2 3 4 5 6 7 8 9 10 11 12

  > INSERT INTO hoge_report(hoge_id, request, report_date) VALUES(10, 100, '2020-02-29'); Query OK, 1 row affected (0.001 sec)   MariaDB [test]> select * from hoge_report; +----+---------+---------+-------------+ | id | hoge_id | request | report_date | +----+---------+---------+-------------+ |  1 |       1 |      10 | 2020-02-29  | |  2 |      10 |     100 | 2020-02-29  | |  3 |       2 |      20 | 2020-02-29  | +----+---------+---------+-------------+ 3 rows in set (0.000 sec)

  
  AUTO_INCREMENTは「3」に増えて、更に同じ日のデータをINSERTしようとするとDuplicate entryエラーが発生します
  
  > SELECT AUTO_INCREMENT from information_schema.`TABLES` WHERE TABLE_NAME='hoge_report'; +----------------+ | AUTO_INCREMENT | +----------------+ | 3 | +----------------+ 1 row in set (0.002 sec) MariaDB [test]> INSERT INTO hoge_report(hoge_id, request, report_date) VALUES(11, 110, '2020-02-29'); ERROR 1062 (23000): Duplicate entry '3-2020-02-29' for key 'PRIMARY'

  1 2 3 4 5 6 7 8 9 10

  > SELECT AUTO_INCREMENT from information_schema.`TABLES` WHERE TABLE_NAME='hoge_report'; +----------------+ | AUTO_INCREMENT | +----------------+ |              3 | +----------------+ 1 row in set (0.002 sec)   MariaDB [test]> INSERT INTO hoge_report(hoge_id, request, report_date) VALUES(11, 110, '2020-02-29'); ERROR 1062 (23000): Duplicate entry '3-2020-02-29' for key 'PRIMARY'

• 対策
  • 案１
    AUTO_INCREMENTの代わりにSEQUENCEを使う
  • 案２
    MariaDB 10.2以下を使う（10.3も値が戻る）
• まとめ
  実際のシステムでは ON DUPLICATE KEY UPDATE を使っていたため、
  エラーは発生せず、データの不整合が起きて、調査に時間がかかりました。
  動作検証の段階で問題に気づけたのは良かったです。
  互換性があるという思い込みが危険だということを改めて認識しました。

T.K