【MySQL】UPDATE or INSERT な UPSERT を諦めた話

概要

MySQLを利用するアプリケーションで、UPSERTをINSERT or UPDATEではなく、UPDATE or INSERT にしたかったけど、諦めた話です。

目的

MySQLでUPSERTはINSERT ... ON DUPLICATE KEY UPDATEを使うのが定番です。
INSERTでduplicateとなった場合にduplicateなレコードに対してUPDATEを行う挙動で、この記事ではINSERT or UPDATEとします。

上記の挙動により生じる、以下の点について対策することが目的となります。

• 更新が主なテーブルの場合、上記のクエリによる重複判断のオーバーヘッドを無くしたい。
• オートインクリメントなサロゲートキー(ナチュラルキーの代理となるid)を持つテーブルで、重複時のオートインクリメント値の増加を抑制したい。(登録の有無に関わらずオートインクリメント値が進むため欠番が生じる）

似たような手段としてREPLACEを使うとDELETE > INSERTになる。

具体例

例に用いるのは以下のテーブルです。
ユーザーの最終アクセス日時を記録する想定です。
（オートインクリメントなサロゲートキーはありませんが）

CREATE TABLE `user_last_accesses` (
  `user_id` int NOT NULL,
  `last_accessed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT or UPDATE

INSERT ... ON DUPLICATE KEY UPDATEを使う。

INSERT INTO user_last_accesses (user_id, last_accessed_at, created_at, updated_at)
VALUES (1, '2024-09-04 12:00:00', '2024-09-04 12:00:00', '2024-09-04 12:00:00')
ON DUPLICATE KEY UPDATE
last_accessed_at = VALUES(last_accessed_at),
updated_at = VALUES(updated_at);

UPDATE or INSERT

1. UPDATE

UPDATE user_last_accesses 
SET last_accessed_at = '2024-09-04 12:00:00',
updated_at = '2024-09-04 12:00:00' 
WHERE
    user_id = 1;

2. AffectedRows（更新されたレコード数）の取得

多くの言語でAffectedRowsの取得に別途クエリ発行は不要です。
RailsならActiveRecord::Base.connection.updateの戻り値がAffectedRowsです。
以下のクエリで直近のクエリによるAffectedRowsを取得することもできます。

SELECT ROW_COUNT();

3. 2が0件の場合はINSERT

データが存在し、UPDATEでレコードのどのカラムも更新されなかった場合、AffectedRowsは0となり、INSERTでDuplicateエラーが発生するため、IGNOREを付けています。
（採番をオートインクリメントに任せる場合、IGNOREされてもオートインクリメント値が進むので微妙）

INSERT IGNORE INTO user_last_accesses ( user_id, last_accessed_at, created_at, updated_at )
VALUES
    ( 1, '2024-09-04 12:00:00', '2024-09-04 12:00:00', '2024-09-04 12:00:00' );

落とし穴

ここまででお気づきの方もいるかもですが、UPDATE or INSERTのロジックには落とし穴があります。

1. トランザクションを利用する
2. トランザクション分離レベルがREPEATABLE READである(MySQLのデフォルト)
3. UPDATE発行時にレコードが存在しない

上記の条件を満たす場合、UPDATEの空振りでギャップロックが発生します。
このギャップロックが別のトランザクションのINSERTとデッドロックを発生させる可能性があります。

• 複数の更新クエリを発行するので、トランザクションは使いたい。
• トランザクション分離レベルをREAD COMMITTEDに変更すれば、ギャップロックは発生しなくなるが、このために変えたくない。

ということで、UPDATE or INSERTなUPSERTは諦めました。

まとめ

結局のところ、

1. SELECTで存在確認
2. 1が存在する場合、UPDATE
3. 1が存在しない場合、INSERT

または

1. 確実にデータが存在する状態を担保する(今回の例であればユーザー登録時に一緒に登録するなど)
2. UPDATE

と、素直なロジックが良いと思ってます。