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

    CREATETABLE`hoge_report`(   `id`BIGINT(20)UNSIGNEDNOT NULLAUTO_INCREMENT,   `hoge_id`INT(10)NOT NULL,   `request`INT(10)NOT NULL,   `report_date`dateNOT NULL,   PRIMARY KEY(`id`,`report_date`),   UNIQUEKEY(`hoge_id`,`report_date`) )ENGINE=InnoDB PARTITION BYRANGE  COLUMNS(`report_date`) ( PARTITION`p202002`VALUESLESS THAN('2020-03-01'), PARTITION`p202003`VALUESLESS 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	>INSERTINTOhoge_report(hoge_id,request,report_date)VALUES(1,10,'2020-02-29'); QueryOK,1rowaffected(0.001sec)

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

    MySQL

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

    1	>INSERTINTOhoge_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

    >INSERTINTOhoge_report(hoge_id,request,report_date)VALUES(2,20,'2020-02-29'); QueryOK,1rowaffected(0.001sec)   MariaDB[test]>SELECTAUTO_INCREMENTfrominformation_schema.`TABLES`WHERETABLE_NAME='hoge_report'; +----------------+ |AUTO_INCREMENT| +----------------+ |              4| +----------------+ 1rowinset(0.001sec)   MariaDB[test]>commit; QueryOK,0rowsaffected(0.002sec)

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

    1	ERROR1062(23000):Duplicateentry'1-2020-02-29'forkey'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

  >SELECTAUTO_INCREMENTfrominformation_schema.`TABLES`WHERETABLE_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

  >INSERTINTOhoge_report(hoge_id,request,report_date)VALUES(10,100,'2020-02-29'); QueryOK,1rowaffected(0.001sec)   MariaDB[test]>select *fromhoge_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  | +----+---------+---------+-------------+ 3rowsinset(0.000sec)

  
  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

  >SELECTAUTO_INCREMENTfrominformation_schema.`TABLES`WHERETABLE_NAME='hoge_report'; +----------------+ |AUTO_INCREMENT| +----------------+ |              3| +----------------+ 1rowinset(0.002sec)   MariaDB[test]>INSERTINTOhoge_report(hoge_id,request,report_date)VALUES(11,110,'2020-02-29'); ERROR1062(23000):Duplicateentry'3-2020-02-29'forkey'PRIMARY'

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

T.K

おすすめ

1. 

   Railsのタグ機能でN+1問題解消

   2024年1月18日

2. 

   Professional Scrum Master ⅠとⅡに合格しました

   2024年1月10日

3. 

   background-imageを活用して、アップする画像を1個で済ませたい

   2023年12月28日