この記事はモバイルファクトリー Advent Calendar 2020 12日目の記事です。

こんにちは！新卒エンジニアのid:dorapon2000です。弊チームではDuplicate entryエラーの解消のためにMySQLのINSERT ON DUPLICATE KEY UPDATE構文を一部で用いています。しかし、使う際にいくつかハマりポイントがあったため、どのようにして回避したかについてお話しようと思います。

INSERT ... ON DUPLICATE KEY UPDATE構文

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文

-- a=1,b=2,c=3のレコードがなければ作成する。あればa=1,b=2,c=c+1で更新する
INSERT INTO table (a, b, c) VALUES (1, 2, 3)
  ON DUPLICATE KEY UPDATE c = c + 1;

「該当レコードがなければINSERT、あればUPDATE」を1クエリで実現できる構文です。

例えば、日次スコアテーブルにあるユーザーのスコアを記録したい場合、まだ1回も記録していなければレコードを作成して記録し、すでに記録してあれば追加分のスコアを増やして更新することができます。

もし、INESRT ON DUPLICATE構文を利用しない場合、アプリケーション側のコードは次のようになります。

ユニークキーAでレコードを取得
if (レコードが存在する) {
    AのレコードをUPDATE
}
else {
    AのレコードをINSERT
}

こちらのコードは特定の条件下でDuplicate entryエラー（ユニークキー制約違反によるエラー）が発生する可能性があります。特定の条件というのは、ごく短い間に、異なるスレッドが同時にこの条件分岐に差し掛かり、どちらもレコードが存在しないelse節へ到達してしまうことです。その場合、どちらでも同じユニークキーでINSERTを実行しようとしてDuplicate entryエラーが発生します。

INESRT ON DUPLICATE構文を使えば、1クエリで完結するためDuplicate entryも回避でき、コードもすっきりします。実際には、弊チームではSQL::Makerを利用しているため、そのプラグインのSQL::Maker::Plugin::InsertOnDuplicateを使っています。

AUTO_INCREMENT問題

MySQL 5.6で確認された問題として、INSERT ON DUPLICATE構文を利用した際に、更新時にもAUTO_INCREMENTカラムがAUTO_INCREMENTされるという問題がありました。

INSERTでidが10のレコードが作成される
既存のレコードのいずれかをINSERT ON DUPLICATEによって更新する
INSERTでidが12のレコードが作成される ←11ではない！？

上の例では、idの11が歯抜けになっています。歯抜け自体は問題にはなりませんが、頻繁に更新されるテーブルでINSERT ON DUPLICATEを利用しており、idカラムがUNSIGNED INTの場合、最大値である42億を超過する可能性があります。上限に達するとそれ以上カラムをINSERTできなくなるため、アプリケーションは正常に動作しなくなるでしょう。

解決策① BIGINT化

この問題に対処する方法として、まずAUTO_INCREMENTカラムをINTからBIGINT(2⁶⁴ − 1)にすることが考えられます。しかし、気になる点として以下のような点があります。

テーブルが歯抜けだらけになり少し気持ち悪い
上限はあるため根本的な解決策とは言えない
すでにテーブルが運用中であれば、本番DBにINTからBIGINTにするためのALTERを打つ必要がある

解決策② INSERT ON DUPLICATEの利用回数を抑える

実際の運用では、以下のように実装することでINSERT ON DUPLICATEでDuplicate entryを解消しつつ、AUTO_INCREMENTの副作用を最小限に抑えるようにしています。

ユニークキーAのレコードを取得
if (存在している) {
    UPDATE
}
else {
    INSERTのトリガーを発動
    INSERT ... ON DUPLICATE KEY UPDATE
}

普通の更新はAUTO_INCREMENTされない通常のUPDATEで更新します。しかし、Duplicate entryが発生する可能性があるときだけ（elseの部分）、 INSERT ON DUPLICATEを利用します。こうすることによって、更新時にはAUTO_INCREMENTされてしまいますが、必要最低限に抑えるようにしています。

少し美しくないですが、メソッド化して呼び出せるようにすればそこまで気になりませんでした。

なお、INSERT ON DUPLICATEによる挿入ではINSERTトリガーが働きません。そのため、else節でINSERTのトリガーも強制的に発動させています。こちらもちょっとしたハマリポイントです。