雑文発散

▼ [MySQL] ON DUPLICATE KEY UPDATE での AUTO_INCREMENT の挙動

Embulk の embulk-output-mysql プラグインの merge モードを繰り返し試していたときに、AUTO_INCREMENT 設定をしていた id の値がガンガン進んでいくことに気が付いた。

merge モードだと INTO <target_table> SELECT * FROM <intermediate_table_1> UNION ALL SELECT * FROM <intermediate_table_2> UNION ALL ... ON DUPLICATE KEY UPDATE ... が発行されるみたい。

ON DUPLICATE KEY UPDATE って馴染みがなかったんだけど、まぁ、いわゆる UPSERT のひとつみたいだ。ユニークキーに重複するレコードが無ければ INSERT し、重複するレコードがあれば UPDATE するというざっくりした理解でいた。

そういう挙動なら、INSERT の数だけが AUTO_INCREMENT されるのかな？と思ってたら、INSERT / UPDATE の挙動に関わらず、ON DUPLICATE KEY UPDATE の実行の数だけ AUTO_INCREMENT されているように見えた。

MySQL に詳しい人にその辺を聞いてみたら、MySQL リファレンスの「構成可能な InnoDB の自動インクリメントロック」に次のように書かれている挙動がそれじゃないの？と教えてもらった。

「失われた」自動インクリメント値とシーケンスギャップ

すべてのロックモード (0、1、および 2) では、自動インクリメント値を生成したトランザクションがロールバックされると、これらの自動インクリメント値が「失われます」。「INSERT のような」ステートメントが完了したかどうか、およびそれを含むトランザクションがロールバックされたかどうかに関係なく、自動インクリメントカラムの値は一度生成されたら、ロールバックできません。このような失われた値は再使用されません。したがって、テーブルの AUTO_INCREMENT カラムに格納されている値にはギャップが存在する可能性があります。

あと、この日記を書くためにこのページを見返してみたら、この辺も該当しそうな気がしてきた。

「混在モード挿入」

これらは、新しい行の一部 (全部ではない) の自動インクリメント値を指定する 「単純挿入」 ステートメントです。次の例を示します。c1 はテーブル t1 の AUTO_INCREMENT カラムです。

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

INSERT ... ON DUPLICATE KEY UPDATE は別のタイプの 「混在モード挿入」 で、最悪の場合には実質 INSERT のあとに UPDATE を実行することに相当しますが、AUTO_INCREMENT カラムに割り当てられた値は、更新フェーズで使用される可能性も使用されない可能性もあります。

MySQL サーバの気持ちになってみれば、INSERT 系のクエリが届いたら、その後の処理が INSERT になるか UPDATE になるかは置いておいて、まずは AUTO_INCREMENT の値を払いだしちゃうのが楽だもんなぁ。

それにしても MySQL のリファレンスをろくに読んでないことを改めて認識させられた。割と使ってはいるので、もうちょっと深いところも理解しておく必要があるとは思いつつ、あまりモチベーションが上がらないのはなぜなんだろうか。