データベースプログラミング全般スレ
■ このスレッドは過去ログ倉庫に格納されています
0001デフォルトの名無しさん
垢版 |
NGNG
データベース板もあるが、あそこは過疎板だからこっちに立てました。
データベース関連のプログラミングならな～んでもOK。
色んな話をしませまうる号。
0472デフォルトの名無しさん
垢版 |
2016/08/25(木) 16:48:36.83ID:9o9g1zI8
>>469
> そういう事は考えられるのでしょうか？
考えられる。

> またそれを回避するにはどういう SQL 文（トランザクションでしょうか？）を書いたら良いのでしょうか？
Z.Xにユニーク制約をつけるのが一番簡単。
0473デフォルトの名無しさん
垢版 |
2016/08/25(木) 16:52:28.51ID:9o9g1zI8
>>471
> Z テーブルの X カラムにユニーク属性は付けていません。
そうなんだ。
じゃ最適解はわからない。テーブルロックか？
0477デフォルトの名無しさん
垢版 |
2016/08/29(月) 14:33:26.91ID:SnTWW0f8
>>476
こういうケースで問題がある。

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はデータを登録できない。
0478デフォルトの名無しさん
垢版 |
2016/08/29(月) 22:22:51.33ID:/ybRo7IQ
>>469の要件は満たしてるのに、

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

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

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


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

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

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

これをエラーにする必要もないんだが。
0479デフォルトの名無しさん
垢版 |
2016/08/30(火) 10:23:03.89ID:FG8f7euN
>>478
> なんでこういう条件を勝手に追加するんだか。
別に勝手に追加したわけじゃなくて、>>477は普通の正常系の話。
複数人が別々のデータを同時に登録するなら、それは成功してしかるべき。

> これをエラーにする必要もないんだが。
なにか勘違いしてるようだけど、>>478は、
・お互い分離レベルが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のブロック解除
> これをエラーにする必要もないんだが。
どういう意味？
エラーにするんじゃなくて、エラーが発生するんだけど。
0480デフォルトの名無しさん
垢版 |
2016/08/30(火) 10:26:02.84ID:FG8f7euN
というか、クライアントを二つ立ち上げて、>>477を実査にやってみれば、俺が言ってることを納得できると思う。
0481デフォルトの名無しさん
垢版 |
2016/08/30(火) 22:41:53.12ID:d4TkCE35
>別に勝手に追加したわけじゃなくて、>>477は普通の正常系の話。
>複数人が別々のデータを同時に登録するなら、それは成功してしかるべき。

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

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

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

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

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

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

>>478と同じことを書くけど、おまえさんはSQL標準で規定されたトランザクションの仕様と
個々のDBMSの実装の区別がついていない。
0483デフォルトの名無しさん
垢版 |
2016/08/31(水) 10:23:23.44ID:csb4seou
標準規格のserializableの話だとして>>478を読み直すと、結論としては>>481はMySQLの分離レベルの
実装はなってないということか？
0484469
垢版 |
2016/08/31(水) 20:31:43.28ID:goJly4rw
返答ありがとうございました。

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

すいませんでした。


やりたい事をまとめますと「複数同時に実行される A.php で、明確に１レコードだけが挿入されるようにしたい」です。
0485469
垢版 |
2016/08/31(水) 20:32:39.61ID:goJly4rw
結局処理は、シリアライズな？トランザクションの使い方も良く分かっていないので以下のように設計しました。

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

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

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

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

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

大丈夫ですよね？


PDOを利用しています。

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


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

凄くタコなやり方ですが、これ以外に安全を担保するやり方を知らないので。。。
0486デフォルトの名無しさん
垢版 |
2016/08/31(水) 21:55:15.56ID:WV/fnjvU
Qテーブルに1レコードしか存在し得ないよう制約がかけられているのならOK。複数挿入できるならNG。
そもそもそんなQテーブルが作れるのなら素直にZにユニーク制約をかけることを検討した方が
いいんじゃないかとは思うが。
あと、ロングトランザクションなら楽観的ロックも仕方ないが、ちゃんとトランザクションの使い方を
学んだ方が後々苦労せずに済むと思うがねぇ。
0487デフォルトの名無しさん
垢版 |
2016/08/31(水) 22:08:34.19ID:3y45z8zT
そもそもMySQLって、エンジンによってはトランザクションそのものをサポートしてなかった気がするけど
MySQLの排他制御がどうなってるか知らんが、普通に考えれば

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

でできるんじゃね
同時実効性とかロック待ちで性能低下とか知った事じゃないけどな
0488デフォルトの名無しさん
垢版 |
2016/09/01(木) 00:33:23.21ID:nGqVI4Wr
>>469
1で存在チェックしてから、2で挿入するって、
1と2の間に、他のプロセスが割り込めるから、典型的なダメパターン。
教科書に書いてある、やっちゃいけない事前問い合わせ

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

①のような丸囲み文字は、Windowsの環境依存文字だから、使わないように
0490デフォルトの名無しさん
垢版 |
2016/09/01(木) 10:25:48.93ID:lxKZC7pd
>>485
おい、いつのまにか対象とするテーブルが一つから二つに増えてるぞ。
あと、値が49ってなんだよ・・・。

> 大丈夫ですよね？
全然駄目。
すなおにテーブルロックすべし。
0491469
垢版 |
2016/09/02(金) 10:39:25.56ID:13McY78U
返答ありがとうございます。

一つ間違っていました。

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

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

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

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

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

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

進捗があったらまた書き込ませていただきます。
0493デフォルトの名無しさん
垢版 |
2016/09/02(金) 11:22:48.43ID:GozEyCoO
>>492
なんか話が変わってきているが、>>469に沿って言えば、Z.Xにユニーク制約を付けるのが「普通」。
あと、トランザクションの大小は関係ない。
3msで終了する「存在チェック→insert」というトランザクションでも、複数人が実行するのなら競合する可能性はある。
0494デフォルトの名無しさん
垢版 |
2016/09/02(金) 22:39:01.72ID:JARk4f24
>>492
トランザクションを勉強するのはいいが、ロックのことは一旦忘れた方がいい。理解の妨げになる。
明示的にロックを「使う」なんて言うのはトランザクション分離レベルを理解できなかったジジイのやること。
0495デフォルトの名無しさん
垢版 |
2016/09/05(月) 11:48:06.49ID:Q7D4v3jm
トランザクション分離レベルのことがわかっていると、今回のケースでテーブルロックを使わなくてもいいってことか？
0496デフォルトの名無しさん
垢版 |
2016/09/05(月) 20:21:03.85ID:EI9/AJWb
今回のケースに限らず、基本的にロックなんて使う必要ない。そもそも標準SQLにロックなんてないしな。
必要があるとすれば、SQL92のトランザクション対応してない古いシステムでどうしてもやらないと
ならない場合とか、全部わかってる人があえて標準じゃできない使い方をする場合くらい。
0497デフォルトの名無しさん
垢版 |
2016/09/06(火) 03:41:01.52ID:XjpGsw+e
>>494-495
ロックってのは基本的には分離レベルに応じてDBMSが勝手にやってくれる
今回の例ならSERIALIZABLEでトランザクション流せば良いだけ

理想は分離レベルの指定だけで済ますことなんだが
現実的には、どの分離レベルでどういうSQL流したらどういうロックが獲得されるかはちゃんと理解しとかないと
パフォーマンス的な問題がでるかもしれんがな
0498デフォルトの名無しさん
垢版 |
2016/09/06(火) 07:48:18.17ID:4rtM9TBt
>理想は分離レベルの指定だけで済ますことなんだが
>現実的には、どの分離レベルでどういうSQL流したらどういうロックが獲得されるかはちゃんと理解しとかないと

それは別に相反する話じゃないが。
0499デフォルトの名無しさん
垢版 |
2016/09/06(火) 11:27:23.34ID:JNt9wvm4
>>497
> 今回の例ならSERIALIZABLEでトランザクション流せば良いだけ
同じテーブルに同時に別のデータをINSERTすることがないという限定条件付きだけどな。

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

その「テーブルをロックする」というのが、MySQLで上の限定条件に限り、目的と合致するというだけの話。
0500デフォルトの名無しさん
垢版 |
2016/09/06(火) 20:19:05.08ID:XjpGsw+e
>>499
お前の言う限定条件ってのは理解できない
だれかに言われてたけど、トランザクションは常に失敗の可能性があるってことすら理解してないのか？
あるいは同時実行されるトランザクションが複数あれば、ロック待ちが発生する可能性があるって事が理解できない？
>>論理的にはテーブルをロックするしかない
だから、SERIALIZABLEなトランザクションってのは必要ならそう言う動作するわけだが
MySQLどうこうじゃなくて、SERIALIZABLEを正しく実装してる全てのDBMSで正しく動作するけど？

MySQLがトランザクションとSERIALIZABLE分離レベルを正しく実装してるかどうかはしらん
0502デフォルトの名無しさん
垢版 |
2016/09/07(水) 03:06:30.86ID:09Xqd2ts
>>477がちょっと気になったんだが
本当に先行トランザクションのuser1のinsertがブロックされたり
user2が(ロックタイムアウトじゃなくて)デッドロックで落ちたりするのか？

それがホントなら誰かMySqlのャ鴻bク周りにつｂ｢て詳しい解説ャTイト教えてくｂ
0503デフォルャgの名無しさん
垢版 |
2016/09/07(水) 03:09:09.50ID:09Xqd2ts
うは、なんか文字化けしとる
MySqlのロック周りについて詳しい解説サイト教えてくれ
と書いたんだが、さて
0504デフォルトの名無しさん
垢版 |
2016/09/07(水) 10:18:08.32ID:99igoHFu
>>500
まず、俺がトランザクションについて理解していないとか、分離レベルについて理解していないとか、
そういう思い込みを捨てろ。俺に言わせれば、お前の方が理解していないように見えるんだが。

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

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

> それがホントなら誰かMySqlのロック周りについて詳しい解説サイト教えてくれ
「mysql serializable」でググった1ページ目には、その「詳しい解説」は見つからなかったのか？
0505デフォルトの名無しさん
垢版 |
2016/09/07(水) 10:40:31.01ID:99igoHFu
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: リトライが行われた場合、このトランザクションは成功するかもしれません
0506デフォルトの名無しさん
垢版 |
2016/09/07(水) 10:42:56.92ID:99igoHFu
各RDBMSがserializableなトランザクションの実装で保証するのは「ファントムリードがないこと」であって、
それを実現する方法は各RDBMSの実装にまかされている。
0508デフォルトの名無しさん
垢版 |
2016/09/07(水) 11:58:09.82ID:99igoHFu
少し解説を加えると、RDBMSはserializableなトランザクション内の文脈を見て成功・失敗を決めているわけではなく、
ただ単にファントムリードが発生しないような実装にしているだけ。

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

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

そういう面倒くさいことをしてまでも、ファントムリードを防がなければならないケースに限って、トランザクションを
serializableにするというのが正しい使いだと思う。
ユニーク性を担保したいだけだったら、テーブルロックで十分。
0510デフォルトの名無しさん
垢版 |
2016/09/07(水) 15:44:40.75ID:99igoHFu
>>509
デッドロックになるようなロックのかけ方すればエラーになるだろうけど、それと今回の話とは関係ないよね。
0511デフォルトの名無しさん
垢版 |
2016/09/07(水) 19:16:59.25ID:09Xqd2ts
デッドロックとロックタイムアウトの区別もつかない人か

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

同時実行する他のトランザクションがエラーになるのはダメとか、それは分離レベルが保証することではないし
リトライすればOKな事もあるとか、もはや分離レベル関係ないし
0512デフォルトの名無しさん
垢版 |
2016/09/07(水) 22:23:54.80ID:fjXPLH9h
>いや、serializableなトランザクションに関する各RDBMSの実装が異なっているというのが前提で、
>だからこそ「serializableなトランザクションを使えばうまくいく」という一般論にはならない。

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

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

ファントムリードが起きない「だけ」なんだが、直列化可能性を保証するにはそれで十分なわけ。
まさか「ファントムリードがない」ってのを、単に他トランザクションで挿入したレコードを
見せないだけとでも思ってるんだろうか。
0513デフォルトの名無しさん
垢版 |
2016/09/08(木) 10:04:35.37ID:uHWEQ8CC
>>511
もうお前の主張にはなにも興味はないが、mysqlとpostgresqlの実装にたいする感想くらいくれよ。
0514デフォルトの名無しさん
垢版 |
2016/09/08(木) 10:09:10.39ID:uHWEQ8CC
>>511
> まあserializableで単独テーブルに対するアクセスでデッドロックするのもどうなんだという感じがしないでもないが
あ、これがmysqlの実装に対する感想なのか？
じゃ、もういいや。
0515デフォルトの名無しさん
垢版 |
2016/09/08(木) 10:14:11.58ID:uHWEQ8CC
最後に一つだけ。
「SERIALIZABLEは無条件に直列化可能であることを保証するもの」の意味が全然わからんが、
ユニーク性を担保するためには、同時に実行される可能性があるトランザクションを「直列化」する
必要があり、それにはテーブルロックを使うのが最も簡単。

これに反論がある場合に限ってレスしてくれ。
0516デフォルトの名無しさん
垢版 |
2016/09/08(木) 11:18:11.85ID:uHWEQ8CC
>>515への直接のレスがなければ、これでserializable話は終了します。
いい加減うざいだろうし。

「serializableなトランザクションとは何か」は、以下のスライドが俺が見つけた範囲だと一番わかりやすいと思う（それでもわかりづらいんだが）。
『トランザクションをSerializableにする4つの方法』
http://www.slideshare.net/kumagi/serializable4-56309007
0517デフォルトの名無しさん
垢版 |
2016/09/08(木) 21:47:43.84ID:8O2pDGJY
「SERIALIZABLEじゃ無理」→「問題がある」→「ロックの方が簡単」

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

「(今からトランザクションを理解するより)ロックの方が簡単」という個人的事情なのかね？
0518デフォルトの名無しさん
垢版 |
2016/09/09(金) 03:27:30.69ID:VuAPiSR8
>>517
ロックの詳細や問題点を正確に把握できてないんじゃないの

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

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

デッドロック以外にロックでエラーは出ないと思ってるんだろ
0520デフォルトの名無しさん
垢版 |
2016/12/04(日) 21:38:58.08ID:OeUSkEhR
Oracleってdomain使えないん？
0521デフォルトの名無しさん
垢版 |
2016/12/06(火) 22:37:42.39ID:ZdJwFyPe
今どきRDBMSとかダサすぎ。
■ このスレッドは過去ログ倉庫に格納されています
ニューススポーツなんでも実況