MySQLで「無ければINSERT、あればUPDATE」を実現する方法

こんにちは、駅奪取エンジニアの id:kimkim0106（旧: id:kaoru_k_0106）です。

今回の記事は、駅奪取でテーブルにレコードが「無ければ INSERT、あれば UPDATE」（いわゆる UPSERT）をする箇所で Duplicate entry が出ていたのを修正したり、未然に防ぐ実装をしたときに得られた知見です。

このような処理はよく使われますが、うまく実装しないとエラーが発生したりパフォーマンスの問題が生じたりします。この記事では、自分が試した方法のメリット・デメリットについて説明します。

前提条件
Duplicate entry とは
1. Duplicate entry が出たらトランザクション自体をやり直す
2. INSERT ... ON DUPLICATE KEY UPDATE
3. とりあえず INSERT して Duplicate entry が出たら SELECT
4. 前もって必要なレコードを INSERT しておく
5. トランザクションが同時に走らないようにロックを取る
まとめ

前提条件

今回、このようなテーブルがあったとして話を進めます。

CREATE TABLE report (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  report_on DATE         NOT NULL,
  referrer  VARCHAR(50)  NOT NULL,
  dau       INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE report_row(report_on, referrer)
);

トランザクション分離レベルは InnoDB のデフォルトである REPEATABLE READ の場合を想定しています。

また、サンプルコードは Perl にて記載されています。

Duplicate entry とは？

Duplicate entry は主キーが重複したときやユニーク制約違反が起きたときに発生するエラーです。例えば、以下のようにユニーク制約に違反して INSERT すると Duplicate entry が出ます。

mysql> INSERT INTO report (report_on, referrer, dau) VALUES ('2024-05-30', 'campaign_202404', 1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO report (report_on, referrer, dau) VALUES ('2024-05-30', 'campaign_202405', 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO report (report_on, referrer, dau) VALUES ('2024-05-30', 'campaign_202405', 1);
ERROR 1062 (23000): Duplicate entry '2024-05-30-campaign_202405' for key 'report.report_row'

1. Duplicate entry が出たらトランザクションをやり直す

use DBI;
use Sub::Retry qw/retry/;

retry $delay, $retry_times, sub {
    try {
        $dbh->begin_work;

        my $report = $dbh->selectrow_hashref(
            "SELECT * FROM report WHERE report_on = ? AND referrer = ?",
            undef,
            $today, $referrer
        );

        if ($report) {
            $dbh->do(
                "UPDATE report SET dau = dau + 1 WHERE id = ?", undef, $report->{id});
        } else {
            $dbh->do("INSERT INTO report (report_on, referrer, dau) VALUES (?, ?, 1)", undef, $today, $referrer);
        }

        $dbh->commit;
    }
    catch {
        $dbh->rollback;
        die $@;
    }
};

メリット
- Duplicate entry でエラーにならない
デメリット
- トランザクションが大きい場合、時間がかかる

一番シンプルなやり方で、標準 SQL の範囲にも収まっているので、MySQL に限らず他の DB エンジンでも使える手法です。

ですが、トランザクションが大きい場合、全体をやり直すため実行時間が長くなってしまいます。ただし、トランザクションのやり直しは同時に INSERT しようとしたときだけなので、毎回発生するわけではありません。

また、この方法では同時に複数のトランザクションが実行されていた場合に Duplicate entry が起こり得ます。別トランザクションで INSERT されたレコードが最初の SELECT で取得できなかった場合、こちらのトランザクションでも INSERT してしまうので Duplicate entry が出ます。

注意点としては、最初の SELECT で FOR UPDATE をつけると、2 つのトランザクションが同時に実行されたときに Deadlock が起こります。

+-------------------------+-------------------------+
|      Transaction 1      |      Transaction 2      |
+-------------------------+-------------------------+
| BEGIN;                  | BEGIN;                  |
| SELECT ... FOR UPDATE;  |                         |
|                         | SELECT ... FOR UPDATE;  |
| INSERT INTO report ...; |                         |
|                         | INSERT INTO report ...; |
+-------------------------+-------------------------+

まず、SELECT ... FOR UPDATE で該当レコードが存在しないとギャップロックが取得されます。ギャップロックは共有ロックなので他のトランザクションをブロックしません。一方、INSERT は排他ロックなので他のトランザクションをブロックし、INSERT が互いにブロックすると Deadlock になります。トランザクション分離レベルが REPEATABLE READ の場合、ギャップロックは避けられません。

駅奪取で Duplicate entry が出ていた箇所もできればこの方法で直したかったですが、トランザクションが大きく影響範囲も大きかったのと、実行時間にも懸念があったので、別の方法にしました。

2. INSERT ... ON DUPLICATE KEY UPDATE

$dbh->do(
    "INSERT INTO report (report_on, referrer, dau)
     VALUES (?, ?, 1)
     ON DUPLICATE KEY UPDATE dau = dau + 1",
    undef,
    $today, $referrer,
);

メリット
- クエリが 1 つで済む
デメリット
- 複数のユニークキーが存在するテーブルには非推奨
- AUTO INCREMENT の値が余分に増えてしまう

MySQL の場合、 INSERT ... ON DUPLICATE KEY UPDATE を使うことで 1 つのクエリで実現できます。

一見これで解決しそうですが、いくつか注意点があります。

一般に、一意のインデックスが複数含まれているテーブルに対して ON DUPLICATE KEY UPDATE 句を使用することは避けるようにしてください。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE ステートメント

まず、MySQL のドキュメントにも書かれている通り、ユニークキーが複数ある場合は非推奨となっているため、そのようなテーブルに対しては避けたほうが良さそうです。

また、テーブルに AUTO INCREMENT のカラムがある場合、UPDATE の場合でも AUTO INCREMENT の値が増えてしまいます。 UPDATE の頻度が高いテーブルであれば、AUTO INCREMENT の値がどんどん大きくなり、場合によってはオーバーフローのおそれもあります。 innodb_autoinc_lock_mode を 0 にすることで UPDATE で AUTO INCREMENT の値が増えなくなりますが、並列性が下がります。

また、以下のように最初に SELECT して存在しない場合だけ INSERT ... ON DUPLICATE KEY UPDATE をすることで、AUTO INCREMENT の値が増えにくくなります。

$dbh->begin_work;

my $report = $dbh->selectrow_hashref(
    "SELECT * FROM report WHERE report_on = ? AND referrer = ?",
    undef,
    $today, $referrer
);

if ($report) {
    $dbh->do("UPDATE report SET dau = dau + 1 WHERE id = ?", undef, $report->{id});
} else {
    $dbh->do(
        "INSERT INTO report (report_on, referrer, dau)
         VALUES (?, ?, 1)
         ON DUPLICATE KEY UPDATE dau = dau + 1",
        undef,
        $today, $referrer,
    );
}

$dbh->commit;

3. とりあえず INSERT して Duplicate entry が出たら SELECT

use DBI;
use Try::Tiny;

$dbh->begin_work;
try {
    $dbh->do("INSERT INTO report (report_on, referrer, dau) VALUES (?, ?, 1)", undef, $today, $referrer);
}
catch {
    my $error = $_;
    if ($error =~ /Duplicate entry/) {
        $report = $dbh->selectrow_hashref(
            "SELECT * FROM report WHERE report_on = ? AND referrer = ? FOR UPDATE = 1",
            undef,
            $today, $referrer
        );
        $dbh->do("UPDATE report SET dau = dau + 1 WHERE id = ?", undef, $report->{id});
    }
    else {
        die $error;
    }
};
$dbh->commit;

メリット
- Duplicate entry は起きなくなる
- トランザクション全体をやり直さないので 1. よりは早い
デメリット
- Deadlock が起きるようになる

この方法は、シンプルでわかりやすく、一見問題なさそうです。しかし、1. と同様に SELECT するときに FOR UPDATE しているためギャップロックが発生します。そのため、同時に INSERT しようとしたときに Deadlock が起きてしまうのでおすすめしません。

4. 前もって必要なレコードを INSERT しておく

use DBI;

my $rows = $dbh->selectrow_arrayref("
    SELECT DISTINCT referrer
    FROM report
    WHERE report_on = ? AND dau > 0
", {}, $today);

for my $row (@$rows) {
    $dbh->do("INSERT INTO report (report_on, referrer) VALUES (?, ?)", undef, $tomorrow, $rows->{referrer});
}

use DBI;

$dbh->begin_work;

# 前もってINSERTしているので基本的にレコードが存在する
my $report = $dbh->selectrow_hashref(
    "SELECT * FROM report WHERE report_on = ? AND referrer = ?",
    undef,
    $today, $referrer
);

if ($report) {
    $dbh->do("UPDATE report SET dau = dau + 1 WHERE id = ?", undef, $report->{id});
} else {
    # こちらに来ることはほとんどないので、Duplicate entryが起きることもほとんどない
    $dbh->do("INSERT INTO report (report_on, referrer, dau) VALUES (?, ?, 1)", undef, $today, $referrer);
}

$dbh->commit;

メリット
- 既存のコードの変更が不要で、副作用が少ない
デメリット
- Duplicate entry が絶対に出なくなるわけではない

これは、どのようなレコードが INSERT されるか前もってわかるのであれば、先に INSERT しておくことで同時に INSERT されずに済むという解決策です。

例えば、今回の例に出したテーブルは、毎日 referrer ごとにレコードが INSERT されます。そこで、翌日のレコードを前日のうちにあらかじめ INSERT しておくことで、基本的に UPDATE するだけで済みます。これにより、Duplicate entry が発生しづらくなります。

一方で、前日存在しなかった referrer が同時に INSERT されようとした場合は Duplicate entry が発生し得ます。ただ、referrer の種類があまり増えないものであったり、同時にくることが少なければ、これで十分な対策になります。

駅奪取では日替わりタイミングで起きていた Duplicate entry をこの方法でなくすことができました。

5. トランザクションが同時に走らないようにロックを取る

use DBI;
use Try::Tiny;

my $key = $today . '_' . $referrer;

try {
    my $get_lock = $dbh->selectrow_array("SELECT GET_LOCK(?, ?)", undef, $key, $timeout);

    if ($get_lock) {
        $dbh->begin_work;
        my $report = $dbh->selectrow_hashref(
            "SELECT id, dau FROM report WHERE report_on = ? AND referrer = ?",
            undef,
            $today, $referrer
        );

        if ($report) {
            $dbh->do("UPDATE report SET dau = dau + 1 WHERE id = ?", undef, $report_id);
        } else {
            $dbh->do("INSERT INTO report (report_on, referrer, dau) VALUES (?, ?, 1)", undef, $today, $referrer);
        }

        $dbh->commit;
        $dbh->do("SELECT RELEASE_LOCK(?)", undef, $key);
    } else {
        die "cannot get lock";
    }
} catch {
    $dbh->rollback;
    $dbh->do("SELECT RELEASE_LOCK(?)", undef, $key);
};

メリット
- トランザクションが同時に走らないので Duplicate entry や Deadlock が起きなくなる
デメリット
- 並列性が下がり、パフォーマンスが低下する

トランザクションが同時に走るから Duplicate entry が起きるのであれば、同時に走らないようにロックしてしまうという手もあります。 MySQL で完結する仕組みとして GET_LOCK と RELEASE_LOCK を使う方法があります。

ただし、データベース分離レベルを SERIALIZABLE にするのと同じようなことをしているので、並列性が下がり、パフォーマンスが低下するので気をつけないといけません。

トランザクション内でリトライしたくない処理がある場合は、この方法を使うとうまく実装できるかもしれません。

まとめ

この記事では、MySQL で「無ければ INSERT、あれば UPDATE」を実現するための方法をいくつか紹介しました。各方法にはメリット・デメリットがあり、ケースバイケースで使い分けが必要です。

できれば 1. もしくは 2. のように修正するのがいいと思うのですが、スキーマや影響範囲、パフォーマンスの制約を考慮すると難しい場合もあります。その場合は、他の方法を使ってみるとうまくいくかもしれません。

参考資料

モバファクでは中途採用・新卒採用ともに絶賛募集中です。
会社の情報については、モバファクブログでも発信しています。
技術好きな方、モバファクにご興味をお持ちの方は、ぜひご応募ください！
・モバファクブログ：https://corpcomn.mobilefactory.jp/
・採用サイト：https://recruit.mobilefactory.jp/

Mobile Factory Tech Blog

技術好きな方へ！モバイルファクトリーのエンジニアたちが楽しい技術話をお届けします！