新人エンジニアのつぶやき

日々の学びをアウトプットするためのブログです

【MySQL】 ON DUPLICATE KEY UPDATE　にはご用心

この記事について

いいコードはいい心から

今回はmysqlの罠についてです。便利なものほど疑えという格言が似合う体験をしたのでまとめました。 触れるのはON DUPLICATE KEY UPDATE 句についてです。INSERTとUPDATEを自動で振り分けてくれるとても便利な句ですが、実は罠が潜んでいます。 どんな罠なのか、、、、 なんと、ON DUPLICATE KEY UPDATEを含んだクエリを実行するとデッドロックがさらに発生しやすくなるというものです。 そのため、実装は避ける必要がありそうです。

今回はこのようなことがわかった経緯についてまとめました。

ON DUPLICATE KEY UPDATEとは

です。

例えば、以下のようなテーブルがあったとする

// userテーブル、ulidはPRIMARY KEYとする

ulid first_name last_name
01FVSHW3S63T5D5Q8KTT132RK3 Japan Taro
01FVSHW3S7HW03J702MAE82MQS Japan Jiro
01FVSHW3SER8977QCJBYZD9HAW Japan Saburo

そこに以下のようなクエリが実行されたとする

INSERT INTO 
  user (
    ulid
    , first_name
    , last_name
  ) VALUES
  (
    '01FVSHW3S99VWCKTQVG1EQB6CM',
    'US',
    'John'
  ),
  (
    '01FVSHW3SER8977QCJBYZD9HAW',
    'US',
    'SAM'
  );

この場合、ulidが01FVSHW3SER8977QCJBYZD9HAWのレコードはすでにテーブルに存在するため、エラーとなってしまう。 そのため、SQL側を実行する前にインサートするデータを選抜しなければならない。少し面倒ですよね そんな時にON DUPLICATE KEY UPDATE句の出番。以下のように書き換えてみる

INSERT INTO 
  user (
    ulid
    , first_name
    , last_name
  ) VALUES
  (
    '01FVSHW3S99VWCKTQVG1EQB6CM',
    'US',
    'John'
  ),
  (
    '01FVSHW3SER8977QCJBYZD9HAW',
    'US',
    'SAM'
  )
  ON DUPLICATE KEY UPDATE
  ulid = VALUES(ulid)
  , first_name = VALUES(first_name)
  , last_name = VALUES(last_name);

その結果、新規のレコードはインサートされ、既存のレコードは更新されます

ulid first_name last_name
01FVSHW3S63T5D5Q8KTT132RK3 Japan Taro
01FVSHW3S7HW03J702MAE82MQS Japan Jiro
01FVSHW3SER8977QCJBYZD9HAW US SAM
01FVSHW3S99VWCKTQVG1EQB6CM US John

とっても便利なので、よく使われてました。足下に火がついているとも知らずに。。。。

罠

とある日にバグが見つかりました。データが登録できていないという状況でした。サーバーのログを見てみるとDeadLockが発生しており、サーバー側で500エラーが発生していました。 さらに詳しく調べていくことにしました。

調査方法

DeadLockが発生している状況を作り出しつつ、その際にどんなクエリが叩かれているかを確認することにしました。そのためにgeneral.log, slow_query_logをonにしました。またlong_query_timeを0にし、全ての処理のログを出すようにしました。 (long_query_timeで設定された 秒数 より処理時間が長いとスロークエリログの対象になる。)

mysql> set global general_log = on;
mysql> set global slow_query_log=1;
mysql> set global long_query_time=0;

その結果以下のようなログが出てきました

2023-04-04T02:52:08.644934Z 1847 Query INSERT INTO テーブル名 (
          省略
        ) VALUES (  
          省略
         ),(
          省略
         )
        ON DUPLICATE KEY UPDATE
        省略

ON DUPLICATE KEY UPDATE句を含むクエリがログに出てきた。ON DUPLICATE KEY UPDATEに関して調べてみると興味深い記事が出てきました。

Bug #98324 Deadlocks more frequent since version 5.7.26という報告によると、5.7.26 以降にアップグレードすると、ON DUPLICATE KEY UPDATEを含んだクエリでデッドロックがさらに発生するそうです。

マイナー バージョンを 5.7.25 から 5.7.26 以降にアップグレードすると、さらに多くのデッドロックが発生します。 REPLACE ステートメントを使用した単純なテスト ケースでは、アップグレード後に約 20 倍のデッドロック メッセージが発生します。 5.7.26 での変更がおそらく関連しているのではないかと思います。

13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE ステートメントにも以下のように記載れています。

複数の一意キーまたは主キーを持つテーブルに対する INSERT ... ON DUPLICATE KEY UPDATE ステートメントも安全でないとマークされます。 (Bug #11765650、Bug #58637)

今回事象が発生した際のmysqlのバージョンは5.7.38であったため、デッドロックが発生しやすい環境でした。。。 実際に動作確認し、DeadLockが起こることを確認しました。

  1. a.sqlとb.sqlを用意する

// a.sql, b.sql

begin;

INSERT INTO hoge_table (
          省略
        ) VALUES (  
          省略
         ),(
          省略
         )
        ON DUPLICATE KEY UPDATE
        省略;

commit;
  1. ターミナルを２つ準備する。
  2. ターミナルAではwhile true;do mysql -u root database_1 < a.sql; doneでSQLを無限ループさせる
  3. ターミナルBではwhile true;do mysql -u root database_1 < b.sql; doneでSQLを無限ループさせる

その結果、以下のようなログが出力されました

$ while true;do mysql -u root database_1 < a.sql; done
ERROR 1213 (40001) at line 3: Deadlock found when trying to get lock; try restarting transaction

大量に insert into on duplicate updateが走った際にDeadlockが引き起こされることが確認できました。

対応方針

ON DUPLICATE KEY UPDATE句が使えないため、アプリケーション側で制御する必要があります。 今回は２つの対応策を挙げました。

１. try catchで囲み、Exceptionを拾って、エラーをキャッチした場合はupdateの処理を実行するようにする

try {
  save();
} catch (e) {
  update();
}

２. update用の圃場データと、insert用の圃場データをselectで取得し、それぞれ処理を実行する

1の場合、圃場リストのデータを1つ1つsave or updateのクエリを叩く必要がある。これは処理速度の低下につながるため、避けたい。 2の場合は一括登録、一括更新することができる。

よって、２を採用することにしました。

最後に

今回の事象でmysqlにすこーーーーしだけ詳しくなった気がします。綺麗な花には棘があるというように、疑うことも必要なのだなと改めて感じました。