【MySQL】ユニークキー制約はついてないけど重複させたくない時にやったこと

実装環境

• MySQL 5.7
  • トランザクション分離レベル: REPEATABLE READ

やりたいこと

ユーザ作成APIが同時に実行された際に、同じnameのユーザを登録させたくない。
以下のような userテーブルがあり、nameにはユニークキー 制約はついていない。

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`)
);

試したこと

2つのトランザクション内で同じクエリを発行し、動作を確認する。
1. SELECT FOR UPDATE を使う。

BEGIN;

SELECT * FROM user WHERE name='test' FOR UPDATE;

INSERT INTO user( name, age ) VALUES ( 'test', 20 );

COMMIT;

この方法だと、トランザクション1で INSERT 後、トランザクション2で INSERTするとデッドロックが発生してしまった。
デッドロック発生によりロールバックすることで、同じnameのユーザレコードは重複しないという条件は満たせるが、デッドロックはあまりよろしくない。
2. NOT EXSITS を使う。

BEGIN;

INSERT INTO user( name, age ) 
SELECT 'test', 20
WHERE NOT EXISTS( SELECT * FROM user WHERE name='test' );

COMMIT;

この方法だとトランザクション1で INSERT 後、トランザクション2で INSERTしても、1がcommitされるまで2のクエリは実行されない。
サブクエリでSELECTをつけることにより、テーブルロック(インテンションロック)がかかっている様子。

結論

今回の場合は NOT EXISTS でうまくいった。
ユニークキー制約をつければ、こんな複雑なクエリを実装する必要はないので、
可能であればユニークキー制約をつけ、deplicatedError が発生したらロールバックするような挙動が望ましい。

7/16 追記

MySQL5.5の場合

なんとMySQL5.5で実装する必要があった。 MySQL5.5の場合、上記のNOT EXISTS 文ではsyntaxエラーが出てしまい使えない。 というわけで再度調べた。

BEGIN;

INSERT INTO user(name, age) 
SELECT name, age FROM (SELECT 'test' AS name, 20 AS age ) AS u 
WHERE NOT EXISTS ( SELECT * FROM user WHERE name='test' );

COMMIT;

上記のSQLで成功。

MySQL5.5の場合、FROMをつけて値を取得する場所を明示的に指定する必要があるようだ。
サブクエリにはaliasをつけないと怒られるので、
特に使用していないがAS uをFROMの後につけている。

参考：

stackoverflow.com