SELECT→INSERT or UPDATEの落とし穴

MySQLのお話。
「データが無ければINSERT、あればUPDATE」をしたい、ただ、設計思想的に「FOR UPDATEは使わない」場合、以下のように設計したとする。

1. まずSELECTして対象の1データを取得
2. データが取得できなければ、INSERT文を実行
3. データが取得できれば、UPDATE文を実行

開発環境レベルだとこれでも全く問題ないんだけど、運用時に問題が発生することがある。

別トランザクションが同じデータに対して、同じ処理を行う場合、全く同時に処理が行われしまうと、どちらもINSERTしようとして、後に終わったトランザクションが重複エラーになってしまう。

こんなイメージ。

★トランA：
START TRANSACTION;
↓
★トランA：
SELECT * FROM `test` WHERE `user_id` = 5;
↓
★トランA：
(データが取得出来なかったのでINSERT)
INSERT INTO `test` (`user_id`, `cnt`) VALUES (5, 1);
↓
●トランB：
START TRANSACTION;
↓
●トランB：
SELECT * FROM `test` WHERE `user_id` = 5;
↓
●トランB：
(データが取得出来なかったのでINSERT)
INSERT INTO `test` (`user_id`, `cnt`) VALUES (5, 1);
↓
★トランA：
COMMIT;
↓
●トランB：
COMMIT; → 重複エラー!!

終わった後のデータイメージ。

+---------+-----+
| user_id | cnt |
+---------+-----+
|       5 |   1 |
+---------+-----+

外部要因で処理に1秒レベルの遅延が発生している時はよく起こる。
なので、1行のSQLで処理を完結させた方がいい。

そこで、「ON DUPLICATE KEY UPDATE」の登場。

★トランA：
START TRANSACTION;
↓
★トランA：
INSERT INTO `test` (`user_id`, `cnt`) VALUES (5, 1)
 ON DUPLICATE KEY UPDATE `cnt` = `cnt` + 1;
↓
●トランB：
START TRANSACTION;
↓
●トランB：
INSERT INTO `test` (`user_id`, `cnt`) VALUES (5, 1)
 ON DUPLICATE KEY UPDATE `cnt` = `cnt` + 1;
↓
★トランA：
COMMIT;
↓
●トランB：
COMMIT; → 正常終了

終わった後のデータイメージ。

+---------+-----+
| user_id | cnt |
+---------+-----+
|       5 |   2 |
+---------+-----+