[SQL]一意制約のない列の一意性を保証してINSERTする方法

Question

SQLにおいて、一意であることを保証しなければならない列については、教科書的には一意制約をつけろということで終わりかと思いますが、現実には一意である必要はあるのに一意制約がついていないケースはよく見かけます。著名ソフトの例としては、WordPress、Joomla!、MovableType等のユーザIDには、DBの一意制約はつけられていますせん。

セキュリティの観点からは、「一意制約つけましょうね」で終わらせてもいいところですが、なんらかの理由で一意制約をつけられない（つけたくない）状況で、特定列の一意性を保証する方法のベスト・プラクティスは知られているのでしょうか?

通常の排他制御では、行ロック等を使用して列値の重複を防止するわけですが、これからINSERTしようという値の場合は、まだ値がないので、行ロックはかけられないと思います。

ということで、一意制約を使わないで一意性を保証するINSERT方法についてお伺いします。

---

すみません、意図が通じにくいようですので追記します。
例えば、列 userid にログインIDを保存するとして、これは一意である必要があります。ところが、なんらかの理由（私がヒアリングした事例だと論理削除をする都合等）で一意制約をつけられない場合、SQLの工夫で userid に重複したIDが登録されることを防ぐ(たとえば、userid として tanaka が二重に登録されることを防ぐ) にはどうすればよいかという質門です。

Answer 1

トランザクションを張ろうというところまではyambejp様と同意見ですが、さらに分離レベルをSERIALIZABLEにしましょうというところを強調しておきたいです。
無論、デッドロックまたは直列化エラーによる失敗がありますのでリトライ処理も記述することが必須です。

分離レベルをSERIALIZABLEにする必要性については先日記事にしました。
https://qiita.com/yuba/items/89496dda291edb2e558c
この記事はPostgreSQLの振る舞いを扱っていますが、トランザクションによる一意性保証をするにはRDB製品の個性と無関係に直列化可能性が必要です。

Comments

[ockeghem]

ありがとうございます。紹介頂いた記事はとても参考になりました。
これを読んで気づきましたが、「通常の排他制御では、行ロック等を使用して列値の重複を防止するわけですが、これからINSERTしようという値の場合は、まだ値がないので、行ロックはかけられないと思います。」というのは誤解で、分離レベルをSERIALIZABLEにすれば、「値が存在しない」という条件も考慮されるということでしょうか?

[yuba]

行ロック（相当の排他）はかけられないというので防げないというのは誤解ではなく、まさにそのご理解の通りで、テーブルロック相当、または述語ロックの排他が必要というのが主旨です。

[ockeghem]

ありがとうございます。理解しました。

Answer 2

一意制約を使わないで一意性を保証するINSERT方法についてお伺いします。

一意にするための条件があれば、その条件でデータが存在するかどうかのチェックを行えば可能かと思います。

insert into テーブル select 値・・・ where not exists(select 1 from テーブル where 一意条件)

追記

なんらかの理由（私がヒアリングした事例だと論理削除をする都合等）で一意制約をつけられない場合

例示されているものでは、一意制約が付けられないとは思えませんが、一意制約は付けないとしても、
一意にするための条件についてはパフォーマンスを考慮してインデックスを作成するケースはあるかと思います。
仮に制約条件にNull値が含まれているとしても、Nullを置き換えるようなファンクションインデックスにすれば良いかと思います。
結局のところインデックスを作成することも考え、一意制約を行うことについて再考されてはどうでしょうか。

Comments

[ockeghem]

回答ありがとうございます。このSQL文はアトミックに実行され、SQL文実行中に他のSQL文の影響は受けないという理解でよろしいでしょうか?

[sazi]

単独のSQLとして発行し、トランザクション分離レベルがシリアライザブルであれば影響は受けません。

[ockeghem]

ありがとうございます

[sazi]

結局のところ、UPSERTでupdateしない事と同じと考えれば良いかと思います。

[ockeghem]

ありがとうございます。腑に落ちました

Answer 3

RDB的には一意制約なしに運用するなんてノーロープバンジーです。
一意制約を付けた状態でいくらでも回避策があります。
と言うかそれを設計するのがプロの仕事です。
1.論理削除はせず物理的に削除済みテーブルにデータを移す。←お勧め
2.NULLにして別列にIDを移す(SQL Server では不可)

一意制約を付けない場合は、
1.フロントエンドで対応するのでマスターメンテスタッフを増員する。
2.SQLで対応するので、とっても遅くなってるけど我慢する。
3.ストアドやトリガで対応するので後でテンヤワンヤになるけど諦める。
などの方法があります。

Comments

[ockeghem]

回答ありがとうございます。まさにご指摘の通りなのですが、仮にやるとしたらどういう方法があるのだろうという興味と、質門本文にもあるように著名ソフトウェアが一意制約をつけていないので、そういうケースではどうしているのだろうという趣旨です。

[hihijiji]

想定するユーザー数が3桁程度以下であれば、テーブルロックでも運用できるかもしれません。
組込データベースに近い形で書き込むフロントエンドが固定できるならフロントエンド側でも対応可能でしょう。
いずれにしろケースバイケースですから、一意制約を付けない場合のベスト・プラクティスなどはありません。
ベスト・プラクティスと言うなら「一意制約を付ける」これだけです。

Answer 4

プロシージャを利用してトランザクションで処理してはどうでしょうか？
※MySQLを利用する前提

元データ作成

create table tbl(id int,data varchar(10));
insert into tbl values(1,'aaa'),(2,'bbb'),(3,'ccc');

プロシージャの作成

drop procedure if exists add_tbl;
delimiter //
create procedure add_tbl(in in_id int,in_data varchar(10))
begin 
start transaction;
insert into tbl values(in_id,in_data);
set @a:=(select count(*) from tbl where id=in_id);
if @a>=2 then
rollback;
else
commit;
end if;
end
//

実行

• 競合するデータを投入

※投入されない

call add_tbl(1,'ddd');
select * from tbl;

• 競合しないデータを投入

※投入される

call add_tbl(4,'eee');
select * from tbl;

transaction同士の競合テスト

drop procedure if exists add_tbl;
delimiter //
create procedure add_tbl(in in_id int,in_data varchar(10))
begin 
start transaction;
insert into tbl values(in_id,in_data);
select sleep(5);
set @a:=(select count(*) from tbl where id=in_id);
if @a>=2 then
rollback;
else
commit;
end if;
end
//

insert後に5秒待たせる処理をしてクロスした処理が競合するかチェックします
この処理は非同期ではないので、コンソールを２つひらいて投入します。

コンソールで

call add_tbl(5,'xxx');

別コンソールで

call add_tbl(5,'yyy');

上記の実際の処理順は

• (5,'xxx')を投入(その後wait)
• (5,'yyy')を投入(その後wait)
• id=5をチェックして、競合がないのでcommit
• id=5をチェックして、競合があるのでないのでrollback

結果としてデータを確認すれば
5,xxxのみ投入されているのがわかります。

Comments

[yambejp]

普通に考えると、先にcountしてデータが0だったらという考え方が先にきますが
その場合複数のSQLが走っていて自分がチェックした直後に別の人が投入している
可能性が捨てきれません。
投入後に競合していたら取り消すという考え方をするのが賢明です。

[ockeghem]

ありがとうございます。お手数をおかけします。
これだと、2つのトランザクションが両方とも「競合した」と判断して、その結果、両方が取り消される可能性はないのでしょうか?

[yambejp]

テスト用のプロシージャのサンプルを追記しておきました。
理論上競合の問題はないはずです。
逆に競合した時点で投入が回避されるのでidの一意性は担保されます。
とはいえ、何らかのバグが内包されている可能性はあるので
より堅牢に処理をしたいのであれば、テーブルをロックする必要があります。
ただしロックするくらいならunique属性を付ける方針をおすすめします。

つまり、idをユニークに投入する専用のプロシージャは、
別のinsert文で投入されることを防げないし、
投入後にupdateされることも防げないからです

[szk.]

横やりですいませんが、

sleep仕込んでも競合の解決にはなりません。
select countした時に別のトランザクションが見えない可能性があることを払拭できません。
検証であればselect countを完全同時に行う必要があります。
例えばsleepをselect countの後に持ってくると2レコード登録できてしまうと思います。

ただ方法としてinsert後のcountは一番妥当だと思っていて、
insertの前にテーブルロックか任意のレコードにforupdateかければ、
一意性は保てると思います。

ちなみに行ロックですが、
insertするレコードではなくて、任意のロック用レコード（ダミーなど）であれば
必ず行ロックかかります。
当然処理速度は落ちますので、
1秒間に大量のinsertする必要のあるテーブルには
いろいろ検証が必要になります。

[yambejp]

szk.さん、フォローありがとうございます

> sleep仕込んでも競合の解決にはなりません

誤解されているのでしょうか？それとも私の理解がおかしいのでしょうか？
sleepはinsertとcountの順番をクロスさせるためのデバグ用です。
insert,insert,count,countの順番を作り出したかっただけですが。

またロックは行ロックでは意味が無いのでは？
テーブルロックして一切のデータ投入・更新処理を排除した上で
やらないと一意性は担保できないと思います。

いずれにしても理論的にはできますよという例なので、
unique属性を超える実用性があるとは私も思っていません。
「やれる」ということと「やってもいい」と言うのは必ずしも同義ではないので

[szk.]

＞誤解されているのでしょうか？それとも私の理解がおかしいのでしょうか？
すいません、私の前提条件が違いました。。。
両方が破棄されない検証であって、一意制約の検証ではなかったのですね。
申し訳ありません。

行ロックは、固定の１レコードということです。
別のテーブルの１レコードでもいいですし、対象のテーブルにロック用のレコードを先に登録しておけば、
必ず行ロックでwaitかかりますよね。
アプリのリリース前にマスタデータとして登録しておく必要はありますが。

私も当然DBの一意制約が一番だと思っていますが、
ockeghemさんの前提に従うと、この方法しかないかと。

[yambejp]

ご指摘いただいたこともありもうちょっと細かく再検証しました。
やはり私の理解不足だったようです

何パターンか検証したところ、競合した処理が発生し
デッドロック(ERROR #1213)がかかりますね。
まぁそれでもinsertが失敗するので一意性は保てるような気がします。

[ockeghem]

お手数をおかけします。ありがとうございました。

Answer 5

.Netでキーを無指定でupdateクエリを自動生成させると、Where句に更新前のカラムをすべて指定して強引に一意性を作っていたのを思い出しました。（Nullの場合もうまく処理してた記憶があります。）

実行速度に難があるかもしれませんが、論理削除には効果がありそうです。

---

訊かれているのはinsertでしたね・・・
失礼しました。

テーブルロックして、対象レコードがないことを確認してinsertするか。
insertしてから、select で行数数えてだめならrollbackあたりくらいしか思いつきません・・・

Answer 6

mySQL AUTO_INCREMENTの設定
PostgreSQLでMySQLのようなAUTO_INCREMENTをしたいとき
が参考になるでしょう。

Comments

[ockeghem]

回答ありがとうございます。しかし、私の質問が悪かったようですので、質問本文の方に追記しました。

[Orlofsky]

RDBMSでは特定の行だけをDELETEしなければならない、UPDATEしなければならない、といった要件が出ているものですから一意制約は必要です。
上記のURLを良く読んでいただけるとINSERT文には一意制約が付いた列の記述はしないで済むことが理解いただけるのでは？

[ockeghem]

ありがとうございます。あらためてよく読んでみます。