所属しているプロジェクトで INSERT ON DUPLICATE KEY UPDATE を使っているが、問題が起きたのでまとめておく。
起きた問題
INSERT のときだけでなく、UPDATE の処理が走った際にも、AUTO_INCREMENT が増えてしまい、AUTO_INCREMENT で更新される値が予測を超えて増加し、int型の最大値に達してしまい、データの挿入ができなくなった。
INSERT ON DUPLICATE KEY UPDATEとは
INSERT しようとした時に、UNIQUE インデックス または PRIMARY KEY に重複した値を発生させるレコードが挿入されていた場合は、古いレコードの UPDATE が実行されるという構文。
使っていた状況
例えばユーザーの獲得した様々な種類のポイントとかいろんな行動のサマリなどのように、確実にレコードがあるかどうか分からないようなテーブルに対して、「レコードがないならば挿入したいけど、レコードがあるならば更新したい」という状況で使っていた。
先にデータがあるかどうかを確認するためにロックを取りつつ SELECT を行うと、そのレコードがない場合にはギャップロックになってしまい、SELECT の条件に関係ないような多くの INSERT もブロックされてしまう。
しかし、ロックを取らずに SELECT すると、SELECT から INSER での隙間で別のトランザクションから INSERT されてしまう可能性がある。
そのような状況下で、INSERT もしくは UPDATE がひとつの処理で行われる INSERT ON DUPLICATE KEY UPDATE を利用していた。
どうすれば避けられるか？
bigintにする
AUTO_INCREMENT によって増えるカラムの型を bigint でスキーマを定義する。
または、bigint になるように ALTER をたたく。
int(unsigned)の最大値は4294967295と約42億であるが、bigint(unsigned)の最大値は18446744073709551615と約1844京と想像できないくらいに大きな数になる。
bigintにすることで、たいていの状況では、人やプロジェクトが生きているうちには使い切れないと思えるような最大値になる。
MySQLの設定を変える
MySQLでは、ストレージエンジンとしてInnoDBを採用している。
デフォルトでは innodb_autoinc_lock_mode = 1 で、「連続」 ロックモードに設定されている。
この設定を innodb_autoinc_lock_mode = 0 で、「従来」 ロックモードに設定することで、すべての 「INSERT のような」ステートメントでは、特殊なテーブルレベル AUTO-INC ロックが取得され、ステートメントの終了まで保持される。
これにより、特定のステートメントによって割り当てられた自動インクリメント値が連続的になる。
しかし、高速化のためにデフォルトが「連続」ロックモードになっているので、処理のパフォーマンスを落としてまで利用するべきなのかは分からない。
仕様を見直す
例えば、このプロジェクトでは、一つの行動の結果により、いろいろなタイプのポイントやサマリが更新される仕様であった。
さらに言うと同時開催されているキャンペーンの数だけ、同じだけのデータを記録する仕様であった。
つまり、一つの行動で 開催しているキャンペーン×ポイントタイプの種類 の数だけ INSERT が試され、レコードがある場合は AUTO_INCREMENT が増えるのである。
しかも使っていないようなタイプのポイントも記録していたので、無駄があったことになる。
仕様と処理を見直し、本当に必要な処理なのかどうか判断することも必要となる。（DBへのアクセスが減ると、処理時間も減るしね）
まとめ
スキーマ定義で、AUTO_INCREMENT を設定しているものは bigint の型にしておけばとりあえず安心なのではなかろうか。
しかし、つど仕様や処理を見直して、必要であれば仕様レベルから変えて行くことで、プロジェクトとしての健康度は保っていくと思う。
参考
https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html
https://dev.mysql.com/doc/refman/5.6/ja/innodb-auto-increment-configurable.html