データベースプログラミング全般スレ

1 ：デフォルトの名無しさん：04/10/09 13:19:17.net

データベース板もあるが、あそこは過疎板だからこっちに立てました。
データベース関連のプログラミングならな〜んでもOK。
色んな話をしませまうる号。

476 ：デフォルトの名無しさん：2016/08/25(木) 16:52:28.51 ID:9o9g1zI8.net

>>474
> Z テーブルの X カラムにユニーク属性は付けていません。
そうなんだ。
じゃ最適解はわからない。テーブルロックか？

477 ：デフォルトの名無しさん：2016/08/25(木) 21:09:39.15 ID:KSci4OQ9.net

�@と�AをSerializableなトランザクションでやればいい。

478 ：デフォルトの名無しさん：2016/08/26(金) 15:10:39.88 ID:UM2x8X0F.net

>>477
それでは無理

479 ：デフォルトの名無しさん：2016/08/26(金) 20:31:32.61 ID:dDBcLzC2.net

できるだろ。
SERIALIZABLEの意味わかってるか？

480 ：デフォルトの名無しさん：2016/08/29(月) 14:33:26.91 ID:SnTWW0f8.net

>>479
こういうケースで問題がある。

table t: (a integer, b integer)
user1がa=3のレコードを、user2がa=4のレコードを同時に作成しようとする。
本来なら、別のデータを登録する処理なので、両立すべき。

user1: set tx_isolation = serializable;
user1: begin;
user1: select count(*) from t where a=3; -> データがないことを確認できる
user2: set tx_isolation = serializable;
user2: begin;
user2: select count(*) from t where a=4; -> データがないことを確認できる
user1: insert into t values(3, 300); -> 処理がブロックする
user2: insert into t values(4, 400); -> エラー発生(Deadlock found)、user1のブロック解除
user2: rollback;
user1: commit;

結果として、user2はデータを登録できない。

481 ：デフォルトの名無しさん：2016/08/29(月) 22:22:51.33 ID:/ybRo7IQ.net

>>472の要件は満たしてるのに、

>本来なら、別のデータを登録する処理なので、両立すべき。

なんでこういう条件を勝手に追加するんだか。

しかも、それぞれのトランザクション分離レベルで守らなければならないことと
実装に任されていることとの区別もついていないようだ。


>user2: select count(*) from t where a=4; -> データがないことを確認できる

ここでuser2をブロックあるいは失敗させてuser1を成功させてもSERIALIZABLEの
要件は満たすし、仮にここでブロックする実装なら

>user2: insert into t values(4, 400); -> エラー発生(Deadlock found)、user1のブロック解除

これをエラーにする必要もないんだが。

482 ：デフォルトの名無しさん：2016/08/30(火) 10:23:03.89 ID:FG8f7euN.net

>>481
> なんでこういう条件を勝手に追加するんだか。
別に勝手に追加したわけじゃなくて、>>480は普通の正常系の話。
複数人が別々のデータを同時に登録するなら、それは成功してしかるべき。

> これをエラーにする必要もないんだが。
なにか勘違いしてるようだけど、>>481は、
・お互い分離レベルがserializableなtransactionにする
・処理は、「存在確認」→「なければinsert」という処理
を普通に行う実装で、「->」以降はそれを実行した結果。

> >user2: select count(*) from t where a=4; -> データがないことを確認できる
> ここでuser2をブロックあるいは失敗させてuser1を成功させてもSERIALIZABLEの
> 要件は満たすし
serializableの要件を満たすことが目的ではないし、普通に実装すればuser2はブロックしないし
失敗もしない。

> >user2: insert into t values(4, 400); -> エラー発生(Deadlock found)、user1のブロック解除
> これをエラーにする必要もないんだが。
どういう意味？
エラーにするんじゃなくて、エラーが発生するんだけど。

483 ：デフォルトの名無しさん：2016/08/30(火) 10:26:02.84 ID:FG8f7euN.net

というか、クライアントを二つ立ち上げて、>>480を実査にやってみれば、俺が言ってることを納得できると思う。

484 ：デフォルトの名無しさん：2016/08/30(火) 22:41:53.12 ID:d4TkCE35.net

>別に勝手に追加したわけじゃなくて、>>480は普通の正常系の話。
>複数人が別々のデータを同時に登録するなら、それは成功してしかるべき。

そもそもトランザクションは常に失敗の可能性があるってことすら理解してないのか。
>>472はそんなもの問題として挙げてないし、これが実際に問題となるかどうかはもう少し
具体的にアクセス規模や性能要件なんかを出して議論するものだ。

>serializableの要件を満たすことが目的ではないし、普通に実装すればuser2はブロックしないし
>失敗もしない。
>エラーにするんじゃなくて、エラーが発生するんだけど。

>>481に書いた「実装」ってのはDBMSの実装のことな。
SERIALIZABLEの要件を満たすのもエラーを出すのもDBMS側の仕事。
極端な話、

>user1: select count(*) from t where a=3; -> データがないことを確認できる

安直なDBMならここでtにテーブル排他ロックをかけるかもしれない。それでも規格準拠だ。
それを踏まえてもういちど>>481を読み返してみな。

>というか、クライアントを二つ立ち上げて、>>480を実査にやってみれば、俺が言ってることを納得できると思う。

>>481と同じことを書くけど、おまえさんはSQL標準で規定されたトランザクションの仕様と
個々のDBMSの実装の区別がついていない。

485 ：デフォルトの名無しさん：2016/08/31(水) 10:08:49.24 ID:csb4seou.net

>>484
あ、MySQL前提じゃなかったんだね。
じゃ話があわない。

486 ：デフォルトの名無しさん：2016/08/31(水) 10:23:23.44 ID:csb4seou.net

標準規格のserializableの話だとして>>481を読み直すと、結論としては>>484はMySQLの分離レベルの
実装はなってないということか？

487 ：469：2016/08/31(水) 20:31:43.28 ID:goJly4rw.net

返答ありがとうございました。

前提条件が不明確でいらぬトラブルを生んでしまったかもしれません。

すいませんでした。


やりたい事をまとめますと「複数同時に実行される A.php で、明確に１レコードだけが挿入されるようにしたい」です。

488 ：469：2016/08/31(水) 20:32:39.61 ID:goJly4rw.net

結局処理は、シリアライズな？トランザクションの使い方も良く分かっていないので以下のように設計しました。

�@ Z テーブルの X カラムの値が 49 のレコードが存在するかチェックする（ほとんどの場合はココではじかれる）

�A Q テーブルに T レコードが存在していなければ、ユニークなユーザーIDの入った T レコードを挿入する
　INSERT INTO SELECT ？

�Bその直後、その T レコードの id （オートインクリメント）の値が一番大きい T レコードを取得する

�C T レコードを挿入したのが自分（ユーザーIDでチェックする）ならば、Z テーブルの X カラムの値が 49 のレコードを insert する。

これで A.php が複数同時に走ったとしても一回しか通らないはずです。

大丈夫ですよね？


PDOを利用しています。

要は他スレッド？の A.php による挿入をブロックしなければならないのですが、オーソドックスなやり方を知りたいです。。。


コードが正しいのか、タイミングをほぼ同時にできるかつ自動で微妙に時間をでずらすソフトを作成して、百本ノックならぬ１００億本ノックで安全を担保しようとしています。

凄くタコなやり方ですが、これ以外に安全を担保するやり方を知らないので。。。

489 ：デフォルトの名無しさん：2016/08/31(水) 21:55:15.56 ID:WV/fnjvU.net

Qテーブルに1レコードしか存在し得ないよう制約がかけられているのならOK。複数挿入できるならNG。
そもそもそんなQテーブルが作れるのなら素直にZにユニーク制約をかけることを検討した方が
いいんじゃないかとは思うが。
あと、ロングトランザクションなら楽観的ロックも仕方ないが、ちゃんとトランザクションの使い方を
学んだ方が後々苦労せずに済むと思うがねぇ。

490 ：デフォルトの名無しさん：2016/08/31(水) 22:08:34.19 ID:3y45z8zT.net

そもそもMySQLって、エンジンによってはトランザクションそのものをサポートしてなかった気がするけど
MySQLの排他制御がどうなってるか知らんが、普通に考えれば

テーブル全体に排他ロック獲得
チェック
インサート
テーブルロック解除

でできるんじゃね
同時実効性とかロック待ちで性能低下とか知った事じゃないけどな

491 ：デフォルトの名無しさん：2016/09/01(木) 00:33:23.21 ID:nGqVI4Wr.net

>>472
1で存在チェックしてから、2で挿入するって、
1と2の間に、他のプロセスが割り込めるから、典型的なダメパターン。
教科書に書いてある、やっちゃいけない事前問い合わせ

一々、2回もSQL実行するのは無駄。
いきなり、挿入すればいい。
挿入できなければエラーになるだけ

�@のような丸囲み文字は、Windowsの環境依存文字だから、使わないように

492 ：デフォルトの名無しさん：2016/09/01(木) 04:24:13.09 ID:kgZUAtCs.net

ｶﾀｶﾅは日本でしか通じないから
Englishで書いてください

493 ：デフォルトの名無しさん：2016/09/01(木) 10:25:48.93 ID:lxKZC7pd.net

>>488
おい、いつのまにか対象とするテーブルが一つから二つに増えてるぞ。
あと、値が49ってなんだよ・・・。

> 大丈夫ですよね？
全然駄目。
すなおにテーブルロックすべし。

494 ：469：2016/09/02(金) 10:39:25.56 ID:13McY78U.net

返答ありがとうございます。

一つ間違っていました。

丸3 の「一番大きい T レコードを〜」は「一番小さい T レコードを〜」でした。

>>489
>Qテーブルに1レコードしか存在し得ないよう制約がかけられているのならOK。複数挿入できるならNG。
複数挿入できないように制約をかけます。
良く分かってないので怖いですが、INSERT INTO SELECT（行が無ければ挿入？）で実現しようとしています。

>>490
テーブルロックは使わない方法でご教授いただければと思います。

>>491
丸2 で「存在していなければ挿入」とするので、丸1 は必要ないのですが、念のために入れてるだけです。

>>493
テーブルロックは使わない方法でご教授いただければと思います。

495 ：469：2016/09/02(金) 10:41:00.77 ID:13McY78U.net

>2回もSQL実行するのは無駄
指摘を受けてハッとしました。
やりたい事を伝え忘れていました。
「Z テーブルの X カラムの値が 49 のレコード」は結果テーブル１行だけというのは変わらないのですが、
その直前にわりと大きめのトランザクションが走ります。
そのトランザクションが複数走るのではと心配になっていました。
トランザクションはまとめて一気に走らせて「何かに失敗したら全部ロールバックできる便利な機能」位しか理解できていません。
仕組みが良く分かっていないので今勉強中です。

２人から同じご指摘いただいているので、普通はテーブルロックで行うって事ですよね。。。
ちょっとそちらも勉強して理解を深めようと思います。

進捗があったらまた書き込ませていただきます。

496 ：デフォルトの名無しさん：2016/09/02(金) 11:22:48.43 ID:GozEyCoO.net

>>495
なんか話が変わってきているが、>>472に沿って言えば、Z.Xにユニーク制約を付けるのが「普通」。
あと、トランザクションの大小は関係ない。
3msで終了する「存在チェック→insert」というトランザクションでも、複数人が実行するのなら競合する可能性はある。

497 ：デフォルトの名無しさん：2016/09/02(金) 22:39:01.72 ID:JARk4f24.net

>>495
トランザクションを勉強するのはいいが、ロックのことは一旦忘れた方がいい。理解の妨げになる。
明示的にロックを「使う」なんて言うのはトランザクション分離レベルを理解できなかったジジイのやること。

498 ：デフォルトの名無しさん：2016/09/05(月) 11:48:06.49 ID:Q7D4v3jm.net

トランザクション分離レベルのことがわかっていると、今回のケースでテーブルロックを使わなくてもいいってことか？

499 ：デフォルトの名無しさん：2016/09/05(月) 20:21:03.85 ID:EI9/AJWb.net

今回のケースに限らず、基本的にロックなんて使う必要ない。そもそも標準SQLにロックなんてないしな。
必要があるとすれば、SQL92のトランザクション対応してない古いシステムでどうしてもやらないと
ならない場合とか、全部わかってる人があえて標準じゃできない使い方をする場合くらい。

500 ：デフォルトの名無しさん：2016/09/06(火) 03:41:01.52 ID:XjpGsw+e.net

>>497-498
ロックってのは基本的には分離レベルに応じてDBMSが勝手にやってくれる
今回の例ならSERIALIZABLEでトランザクション流せば良いだけ

理想は分離レベルの指定だけで済ますことなんだが
現実的には、どの分離レベルでどういうSQL流したらどういうロックが獲得されるかはちゃんと理解しとかないと
パフォーマンス的な問題がでるかもしれんがな

501 ：デフォルトの名無しさん：2016/09/06(火) 07:48:18.17 ID:4rtM9TBt.net

>理想は分離レベルの指定だけで済ますことなんだが
>現実的には、どの分離レベルでどういうSQL流したらどういうロックが獲得されるかはちゃんと理解しとかないと

それは別に相反する話じゃないが。

502 ：デフォルトの名無しさん：2016/09/06(火) 11:27:23.34 ID:JNt9wvm4.net

>>500
> 今回の例ならSERIALIZABLEでトランザクション流せば良いだけ
同じテーブルに同時に別のデータをINSERTすることがないという限定条件付きだけどな。

普通は、同じテーブルに同時に別のデータをINSERTすることもあるし、同時に同じデータをINSERTすることもある。
で、同時に同じデータをINSERTされないようにするには、普通はunique制限を付ける。
なんらかの理由でunique制限を付けられない場合は、論理的にはテーブルをロックするしかない。

その「テーブルをロックする」というのが、MySQLで上の限定条件に限り、目的と合致するというだけの話。

503 ：デフォルトの名無しさん：2016/09/06(火) 20:19:05.08 ID:XjpGsw+e.net

>>502
お前の言う限定条件ってのは理解できない
だれかに言われてたけど、トランザクションは常に失敗の可能性があるってことすら理解してないのか？
あるいは同時実行されるトランザクションが複数あれば、ロック待ちが発生する可能性があるって事が理解できない？
>>論理的にはテーブルをロックするしかない
だから、SERIALIZABLEなトランザクションってのは必要ならそう言う動作するわけだが
MySQLどうこうじゃなくて、SERIALIZABLEを正しく実装してる全てのDBMSで正しく動作するけど？

MySQLがトランザクションとSERIALIZABLE分離レベルを正しく実装してるかどうかはしらん

504 ：デフォルトの名無しさん：2016/09/06(火) 20:56:58.82 ID:4rtM9TBt.net

ロックロック言う奴はやっぱりトランザクション分離レベルが理解できてないという好例>>502

505 ：デフォルトの名無しさん：2016/09/07(水) 03:06:30.86 ID:09Xqd2ts.net

>>480がちょっと気になったんだが
本当に先行トランザクションのuser1のinsertがブロックされたり
user2が(ロックタイムアウトじゃなくて)デッドロックで落ちたりするのか？

それがホントなら誰かMySqlのャ鴻bク周りにつｂ｢て詳しい解説ャTイト教えてくｂ

506 ：デフォルャgの名無しさん：2016/09/07(水) 03:09:09.50 ID:09Xqd2ts.net

うは、なんか文字化けしとる
MySqlのロック周りについて詳しい解説サイト教えてくれ
と書いたんだが、さて

507 ：デフォルトの名無しさん：2016/09/07(水) 10:18:08.32 ID:99igoHFu.net

>>503
まず、俺がトランザクションについて理解していないとか、分離レベルについて理解していないとか、
そういう思い込みを捨てろ。俺に言わせれば、お前の方が理解していないように見えるんだが。

> MySQLどうこうじゃなくて、SERIALIZABLEを正しく実装してる全てのDBMSで正しく動作するけど？
いや、serializableなトランザクションに関する各RDBMSの実装が異なっているというのが前提で、
だからこそ「serializableなトランザクションを使えばうまくいく」という一般論にはならない。
なので、「MySQLならこういう限定条件であればserializableなトランザクションを使えば良い」という
ようにしか言えない。

> >>480がちょっと気になったんだが
> 本当に先行トランザクションのuser1のinsertがブロックされたり
> user2が(ロックタイムアウトじゃなくて)デッドロックで落ちたりするのか？
いやいや、実行結果って書いたじゃん。実際に自分でもやってみたら？

> それがホントなら誰かMySqlのロック周りについて詳しい解説サイト教えてくれ
「mysql serializable」でググった1ページ目には、その「詳しい解説」は見つからなかったのか？

508 ：デフォルトの名無しさん：2016/09/07(水) 10:40:31.01 ID:99igoHFu.net

PostgreSQLでもやってみた。

user1: begin transaction isolation level serializable;
user1: select count(*) from t where a=3; -> データがないことを確認できる
user2: set tx_isolation = serializable;
user2: begin transaction isolation level serializable;
user2: select count(*) from t where a=4; -> データがないことを確認できる
user1: insert into t values(3, 300); -> insertは完了する
user2: insert into t values(4, 400); -> insertは完了する
user1: select count(*) from t; -> 1
user2: select count(*) from t; -> 1
user1: commit; -> 成功する
user2: commit; -> エラー発生
> ERROR: トランザクション間で read/write の依存性があったため、アクセスの直列化ができませんでした
> DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
> HINT: リトライが行われた場合、このトランザクションは成功するかもしれません

509 ：デフォルトの名無しさん：2016/09/07(水) 10:42:56.92 ID:99igoHFu.net

各RDBMSがserializableなトランザクションの実装で保証するのは「ファントムリードがないこと」であって、
それを実現する方法は各RDBMSの実装にまかされている。

510 ：デフォルトの名無しさん：2016/09/07(水) 10:43:58.68 ID:99igoHFu.net

訂正：
>>508の
> user2: set tx_isolation = serializable;
は削除し忘れ。

511 ：デフォルトの名無しさん：2016/09/07(水) 11:58:09.82 ID:99igoHFu.net

少し解説を加えると、RDBMSはserializableなトランザクション内の文脈を見て成功・失敗を決めているわけではなく、
ただ単にファントムリードが発生しないような実装にしているだけ。

あと、ロックと処理がブロックするというのは別の話。
ファントムリードを防ぐという目的の場合に、read lock対read lockなら処理はブロックしないという実装もありだし、
read lock対read lockでも処理をブロックするという実装もありえる。

またserializableなトランザクションの場合、リトライすればOKな場合がある。
そもそも、トランザクションが重ならなければお互いOKになる場合ね。
そういうケースでは、SQL CODEの内容からリトライ可能かどうかを判定してリトライするという実装が必要。
もちろん1回だけのリトライではまた他のトランザクションと重なる場合があるので、MAX回数を決めてリトライを
続ける必要がある。

そういう面倒くさいことをしてまでも、ファントムリードを防がなければならないケースに限って、トランザクションを
serializableにするというのが正しい使いだと思う。
ユニーク性を担保したいだけだったら、テーブルロックで十分。

512 ：デフォルトの名無しさん：2016/09/07(水) 15:28:03.69 ID:SHvl642M.net

テーブルロックは常に成功するとでも思ってるんだろうか

513 ：デフォルトの名無しさん：2016/09/07(水) 15:44:40.75 ID:99igoHFu.net

>>512
デッドロックになるようなロックのかけ方すればエラーになるだろうけど、それと今回の話とは関係ないよね。

514 ：デフォルトの名無しさん：2016/09/07(水) 19:16:59.25 ID:09Xqd2ts.net

デッドロックとロックタイムアウトの区別もつかない人か

まあserializableで単独テーブルに対するアクセスでデッドロックするのもどうなんだという感じがしないでもないが
それでも少なくとも2重登録の防止って要件はserializableで満たしてるわけだが

同時実行する他のトランザクションがエラーになるのはダメとか、それは分離レベルが保証することではないし
リトライすればOKな事もあるとか、もはや分離レベル関係ないし

515 ：デフォルトの名無しさん：2016/09/07(水) 22:23:54.80 ID:fjXPLH9h.net

>いや、serializableなトランザクションに関する各RDBMSの実装が異なっているというのが前提で、
>だからこそ「serializableなトランザクションを使えばうまくいく」という一般論にはならない。

この時点で理解してないのは明らか。そもそもSERIALIZABLEは無条件に直列化可能で
あることを保証するものだし。

>各RDBMSがserializableなトランザクションの実装で保証するのは「ファントムリードがないこと」であって、
>それを実現する方法は各RDBMSの実装にまかされている。

ファントムリードが起きない「だけ」なんだが、直列化可能性を保証するにはそれで十分なわけ。
まさか「ファントムリードがない」ってのを、単に他トランザクションで挿入したレコードを
見せないだけとでも思ってるんだろうか。

516 ：デフォルトの名無しさん：2016/09/08(木) 10:04:35.37 ID:uHWEQ8CC.net

>>514
もうお前の主張にはなにも興味はないが、mysqlとpostgresqlの実装にたいする感想くらいくれよ。

517 ：デフォルトの名無しさん：2016/09/08(木) 10:09:10.39 ID:uHWEQ8CC.net

>>514
> まあserializableで単独テーブルに対するアクセスでデッドロックするのもどうなんだという感じがしないでもないが
あ、これがmysqlの実装に対する感想なのか？
じゃ、もういいや。

518 ：デフォルトの名無しさん：2016/09/08(木) 10:14:11.58 ID:uHWEQ8CC.net

最後に一つだけ。
「SERIALIZABLEは無条件に直列化可能であることを保証するもの」の意味が全然わからんが、
ユニーク性を担保するためには、同時に実行される可能性があるトランザクションを「直列化」する
必要があり、それにはテーブルロックを使うのが最も簡単。

これに反論がある場合に限ってレスしてくれ。

519 ：デフォルトの名無しさん：2016/09/08(木) 11:18:11.85 ID:uHWEQ8CC.net

>>518への直接のレスがなければ、これでserializable話は終了します。
いい加減うざいだろうし。

「serializableなトランザクションとは何か」は、以下のスライドが俺が見つけた範囲だと一番わかりやすいと思う（それでもわかりづらいんだが）。
『トランザクションをSerializableにする4つの方法』
http://www.slideshare.net/kumagi/serializable4-56309007

520 ：デフォルトの名無しさん：2016/09/08(木) 21:47:43.84 ID:8O2pDGJY.net

「SERIALIZABLEじゃ無理」→「問題がある」→「ロックの方が簡単」

なんだかなぁｗ
普通はトランザクション開始時に隔離レベルを1行指定する方がいちいちテーブルを指定して
ロックをかけるより簡単だと思うんだが。

「(今からトランザクションを理解するより)ロックの方が簡単」という個人的事情なのかね？

521 ：デフォルトの名無しさん：2016/09/09(金) 03:27:30.69 ID:VuAPiSR8.net

>>520
ロックの詳細や問題点を正確に把握できてないんじゃないの

>serializableなトランザクションの場合、リトライすればOKな場合がある
>そういう面倒くさいことをしてまでも、ファントムリードを防がなければならないケースに限って、トランザクションを
> serializableにするというのが正しい使いだと思う。
と、あたかも自分でテーブルロックすれば他のトランザクションやリトライ系については考慮いらないかのようなこと言ってるし

>テーブルロックは常に成功するとでも思ってるんだろうか
っていう突っ込みに対して
>デッドロックになるようなロックのかけ方すればエラーになるだろうけど
って回答してるし
>デッドロックとロックタイムアウトの区別もつかない人か
についてはまともな反論してないからな

デッドロック以外にロックでエラーは出ないと思ってるんだろ

522 ：デフォルトの名無しさん：2016/09/24(土) 11:55:19.93 ID:B225F1SQ.net

再利用性の高いクエリの書き方を教えてください

523 ：デフォルトの名無しさん：2016/12/04(日) 21:38:58.08 ID:OeUSkEhR.net

Oracleってdomain使えないん？

524 ：デフォルトの名無しさん：2016/12/06(火) 22:37:42.39 ID:ZdJwFyPe.net

今どきRDBMSとかダサすぎ。

525 ：デフォルトの名無しさん：2016/12/15(木) 15:36:13.98 ID:7KRIzock.net

https://chrome.google.com/webstore/detail/%E3%81%AF%E3%81%A6%E3%81%AAng/mbgdnfmdelffjdhkdggilmphfdihnmcj?hl=ja