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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

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

PostgreSQL

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

SQL

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

Q&A

解決済

6回答

12207閲覧

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

ockeghem

総合スコア11701

MySQL

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

PostgreSQL

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

SQL

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

0グッド

2クリップ

投稿2018/01/09 06:10

編集2018/01/09 07:02

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

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

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

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


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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答6

0

ベストアンサー

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

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

投稿2018/01/09 16:59

yuba

総合スコア5568

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ockeghem

2018/01/09 21:42

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

2018/01/09 23:30

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

2018/01/10 00:46

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

0

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

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

SQL

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

追記

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

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

投稿2018/01/09 12:08

編集2018/01/09 14:13
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ockeghem

2018/01/10 00:48

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

2018/01/10 02:18

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

2018/01/10 02:51

ありがとうございます
sazi

2018/01/10 04:48

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

2018/01/10 05:27

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

0

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

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

投稿2018/01/09 07:40

hihijiji

総合スコア4150

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ockeghem

2018/01/10 00:49

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

2018/01/10 01:36

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

0

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

元データ作成

SQL

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

プロシージャの作成

SQL

1drop procedure if exists add_tbl; 2delimiter // 3create procedure add_tbl(in in_id int,in_data varchar(10)) 4begin 5start transaction; 6insert into tbl values(in_id,in_data); 7set @a:=(select count(*) from tbl where id=in_id); 8if @a>=2 then 9rollback; 10else 11commit; 12end if; 13end 14//

実行

  • 競合するデータを投入

※投入されない

SQL

1call add_tbl(1,'ddd'); 2select * from tbl;
  • 競合しないデータを投入

※投入される

SQL

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

transaction同士の競合テスト

SQL

1drop procedure if exists add_tbl; 2delimiter // 3create procedure add_tbl(in in_id int,in_data varchar(10)) 4begin 5start transaction; 6insert into tbl values(in_id,in_data); 7select sleep(5); 8set @a:=(select count(*) from tbl where id=in_id); 9if @a>=2 then 10rollback; 11else 12commit; 13end if; 14end 15//

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

コンソールで

SQL

1call add_tbl(5,'xxx');

別コンソールで

SQL

1call add_tbl(5,'yyy');

上記の実際の処理順は

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

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

投稿2018/01/09 07:17

編集2018/01/09 08:35
yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yambejp

2018/01/09 07:25

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

2018/01/09 07:56

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

2018/01/09 08:31

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

2018/01/09 08:35

横やりですいませんが、 sleep仕込んでも競合の解決にはなりません。 select countした時に別のトランザクションが見えない可能性があることを払拭できません。 検証であればselect countを完全同時に行う必要があります。 例えばsleepをselect countの後に持ってくると2レコード登録できてしまうと思います。 ただ方法としてinsert後のcountは一番妥当だと思っていて、 insertの前にテーブルロックか任意のレコードにforupdateかければ、 一意性は保てると思います。 ちなみに行ロックですが、 insertするレコードではなくて、任意のロック用レコード(ダミーなど)であれば 必ず行ロックかかります。 当然処理速度は落ちますので、 1秒間に大量のinsertする必要のあるテーブルには いろいろ検証が必要になります。
yambejp

2018/01/09 08:45

szk.さん、フォローありがとうございます > sleep仕込んでも競合の解決にはなりません 誤解されているのでしょうか?それとも私の理解がおかしいのでしょうか? sleepはinsertとcountの順番をクロスさせるためのデバグ用です。 insert,insert,count,countの順番を作り出したかっただけですが。 またロックは行ロックでは意味が無いのでは? テーブルロックして一切のデータ投入・更新処理を排除した上で やらないと一意性は担保できないと思います。 いずれにしても理論的にはできますよという例なので、 unique属性を超える実用性があるとは私も思っていません。 「やれる」ということと「やってもいい」と言うのは必ずしも同義ではないので
szk.

2018/01/09 08:58

>誤解されているのでしょうか?それとも私の理解がおかしいのでしょうか? すいません、私の前提条件が違いました。。。 両方が破棄されない検証であって、一意制約の検証ではなかったのですね。 申し訳ありません。 行ロックは、固定の1レコードということです。 別のテーブルの1レコードでもいいですし、対象のテーブルにロック用のレコードを先に登録しておけば、 必ず行ロックでwaitかかりますよね。 アプリのリリース前にマスタデータとして登録しておく必要はありますが。 私も当然DBの一意制約が一番だと思っていますが、 ockeghemさんの前提に従うと、この方法しかないかと。
yambejp

2018/01/09 09:03 編集

ご指摘いただいたこともありもうちょっと細かく再検証しました。 やはり私の理解不足だったようです 何パターンか検証したところ、競合した処理が発生し デッドロック(ERROR #1213)がかかりますね。 まぁそれでもinsertが失敗するので一意性は保てるような気がします。
ockeghem

2018/01/10 00:49

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

0

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

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


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

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

投稿2018/01/10 06:47

編集2018/01/10 07:07
iwamoto_takaaki

総合スコア2883

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

投稿2018/01/09 06:33

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ockeghem

2018/01/09 07:03

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

2018/01/09 07:48

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

2018/01/09 07:52

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問