質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

91.26%

  • MySQL

    4559questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    1754questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • PostgreSQL

    749questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

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

解決済

回答 6

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 524

ockeghem

EGセキュアソリューションズ株式会社代表

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

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

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

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


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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 6

checkベストアンサー

+3

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/01/10 06:42

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

    キャンセル

  • 2018/01/10 08:30

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

    キャンセル

  • 2018/01/10 09:46

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

    キャンセル

+3

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

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

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

追記

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/01/10 09:48

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

    キャンセル

  • 2018/01/10 11:18

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

    キャンセル

  • 2018/01/10 11:51

    ありがとうございます

    キャンセル

  • 2018/01/10 13:48

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

    キャンセル

  • 2018/01/10 14:27

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

    キャンセル

+2

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/01/10 09:49

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

    キャンセル

  • 2018/01/10 10:36

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

    キャンセル

+1

プロシージャを利用してトランザクションで処理してはどうでしょうか?
※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秒待たせる処理をしてクロスした処理が競合するかチェックします
この処理は非同期ではないので、コンソールを2つひらいて投入します。

コンソールで

call add_tbl(5,'xxx');


別コンソールで

call add_tbl(5,'yyy');


上記の実際の処理順は

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/01/09 16:25

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

    キャンセル

  • 2018/01/09 16:56

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

    キャンセル

  • 2018/01/09 17:31

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

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

    キャンセル

  • 2018/01/09 17:35

    横やりですいませんが、

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

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

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

    キャンセル

  • 2018/01/09 17:45

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

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

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

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

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

    キャンセル

  • 2018/01/09 17:58

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

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

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

    キャンセル

  • 2018/01/09 18:03 編集

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

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

    キャンセル

  • 2018/01/10 09:49

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

    キャンセル

0

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/01/09 16:03

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

    キャンセル

  • 2018/01/09 16:48

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

    キャンセル

  • 2018/01/09 16:52

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

    キャンセル

0

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

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


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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

ただいまの回答率

91.26%

関連した質問

同じタグがついた質問を見る

  • MySQL

    4559questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    1754questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • PostgreSQL

    749questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。