Oracleの排他制御について教えてください

質問

質問内容は結論から言うと、
INSERT文の時の排他制御について
知りたい。

以下の私の認識を踏まえた上で、
INSERT文の排他制御について
質問させてください。
なお、私の認識に誤りがあれば
指摘してください。

### 私の認識 start ######

1)Oracle
では、select文の時に
for updateを
書かなければ、なんのロックもかからず、
読み取り専用リソースへのアクセスで
ない限り、
ダーティーリードの可能性がある。

2)
select文で for update
を指定した場合は
該当行について
共有ロックがかかる。
行単位の共有ロックがかかる。
その際、
他のトランザクションが
for updateつきで
selectしてきても、
共有ロック同士なので、
互い排他制御しない。

(3)
update 文の場合は
該当行について、
占有ロックがかかる
行単位の占有ロックがかかる。

(4)
INSERT文の時には、
ロックをかけようにも
INSERT前の段階では、
ロック対象行は存在しない。
複数のトランザクションが
INSERTした行のPK
の値が偶然同じであった場合
ロストアップデートの危険があるので
私の創造では、INSERT文の時は
テーブル全体をロックしないと、
うまくいかないように思えます。

(5)
update, insert文については、
Oracleでは、自動的に該当行について
占有ロックを行う。
なお、INSERT文については、
下記の質問事項における疑問点
が解消されていないため、
行単位なのかどうか、私の中では
自身がもてないのが現状です。
### 私の認識 end ######

### 主な質問内容 start ###
私の認識の(4)を踏まえた上で
INSERT文の時のはいた制御
の範囲や挙動について、
教えてください。
### 主な質問内容 end ###

以上です。

回答No.4

mi-si · ベストアンサー

#2 補足です。
2)
SELECT FOR UPDATE文は
NO WAIT オプションをつけたときは即時エラーに、
つけないときはロック解除待ちになります。
どちらを使うかはアプリケーションの作り方のポリシーによります。

セッション＝クライアントとサーバの間の接続。データベースに接続してから切断するまでの間の接続が確立している状態

トランザクション＝データベースの処理をひとまとまりにしたモノ。たとえば、口座Aから口座Bへの振り込み処理の場合、口座Aからの引き出し、口座Bへの振り込みの処理を１トランザクションとして扱います。どちらかが失敗した場合はロールバック（巻き戻し）、成功した場合はコミット（確定）します。

業務用アプリケーションの場合、大抵、１回のセッションの中では複数回のトランザクションが発生します。複数のユーザーがそのアプリケーションを利用していればその数だけセッションが張られます。（Webアプリに関してはその限りではないですが）

デッドロック＝
Aと言うセッションがトランザクション内で
1-1 表Xのa行に更新をかけます。
1-2 次に表Yにb行に更新をかけます。
1-3 コミットします（トランザクションの終了）。

同時に
Bと言うセッションがトランザクション内で
2-1 表Yにb行に更新をかけます。
2-2 次に表Xのa行に更新をかけます。
2-3 コミットします（トランザクションの終了）。

これが、1-1 2-1 1-2 2-1 1-3 2-3
と言う順番で実行された場合デッドロックが発生します。つまりAは、X-aにロックをかけたままY-bにロックをかけようとして待ち状態になる。逆にBは、Y-bにロックをかけたまま、X-aにロックをかけようとして待ち状態になる。この状態をデッドロックと呼びます。

なお、Oracleでは、UNDOセグメントではなくロールバックセグメントと呼びます。

a)アーキテクチャに突っ込んだ話になるので、詳しくはOracle データベース概要のドキュメントを読んでください。

基本的に、SELECT中に他のセッション（アプリケーションの方が良いかな？）からの更新が確定した場合、SGA(システムグローバルエリア＝Oracleサーバの共有メモリスペース)に前のデータのバックアップを取ります。これによって読み取り一貫性を保証します。ここにデータが入りきらない場合はデータベースのロールバックセグメント（たしか）に吐き出します。

b)
簡単に言えば、Oracleは、行ごとにロックフラグなどを記録する領域を持っています。マニュアルなどに書いてなければおそらく企業秘密です。

回答No.5

taka_tetsu

>(1)正常終了以外でダーティーリードが
>あるのですか？

読み取り一貫性を確保するためにUNDOセグメント(Oracle8iまではロールバックセグメントのこと)を使用するので他のセッションであまりにも大量の更新を行うと読み取りで使用したセグメントが破棄されるときがあります。

>ユニーク項目のupdate ってどういう意味でしょうか？
>ちょっと、勉強不足で。。
>UNIQキーは候補キーと同じだと認識していますが。
>　　UNIQキーがついている項目をUPDATEする場合は
>ブロック単位という意味でしょうか。

どうもブロック単位じゃないみたいですね。
失礼しました。

>簡単に確認できる方法が
>SQLプラスにあるのでしょうか？

単純に考えればいいですよ。
ちなみにSQL*Plusでは、トランザクションは
常に開始した状態です。
明示的にコミットを発行することでDBに変更内容を
反映させます。

1. SQL*Plusを2つ立ち上げ、両方とも接続する
2. 両方のSQL*Plusから、同一テーブルに対し、同一レコードのinsert文を発行する。
すると、あとから発行した方は応答がなくなる。つまり排他待ちとなる。
3.先の方でcommitを発行する。
すると、あとの方はロックが解除されinsertを行おうとするが、一意制約違反となる。
4.もし、3でcommitではなくrollbackをした場合は、あとからの方のロックが解除された時点でinsertが成功する。
(ただし、commitしてないので確定はしていないが)

こんな感じで。

キー項目やユニーク項目をupdateした場合の挙動も試してみてください。

回答No.3

iwashimizu

1)
Oracleでは読み取り一貫性が保証されます。
FOR UPDATEを使用しなくてもダーティーリードはありません。

2)
SELECT FOR UPDATEを発行すると該当行が排他ロックされます。
他のトランザクションが同じ行に対してSELECT FOR UPDATEを
発行するとロック解除待ちになります。
待ちたくない場合にはSELECT FOR UPDATE NOWAITを使用します。
その場合、該当行がロックされている場合にエラーとなります。

3)
UPDATEを発行すると該当行がロックされます。
他のトランザクションが同じ行に対してUPDATEを発行すると
ロック解除待ちになります。

4)
別トランザクションから同じPKを持つデータをINSERTしようとした場合、
先にINSERTした方がCOMMITまたはROLLBACKを行うまで、
後からINSERTした方は待ち状態になります。
これも行単位で行われ、テーブル単位ではロックされません。
(よって他のトランザクションが異なるPKを持つデータはINSERTできます。)

5)
INSERTやUPDATEを発行すると該当行が排他ロックされます。

2から5をまとめると、
INSERT，UPDATE，DELETE，SELECT FOR UPDATEのいずれの場合も
該当行が排他ロックされる。
他のトランザクションからロックされている行に対して
INSERT、UPDATE、DELETE、SELECT FOR UPDATEを発行しようとすると、
ロック解除待ちとなる。
待ちたくない場合にはSELECT FOR UPDATE NOWAITを利用する。

詳細はOTNにあるOracle9iデータベース概要を参照。

参考URL：

http://otn.oracle.co.jp

補足

ありがとうございます。

(1)
http://www.cskedu.com/keyword/backno/44_yomitori.asp
ここを見てみました。
select発行時での、確定データを見ることが保証されているのですね
なるほど、だから、ロックなしで
selectしても、ダーティーリードしないのですね。
それでは、Oracleには、共有ロック(read lock)
という概念自体が存在しない(必要ない)ということでしょうか？

あと、UNDOセグメントについての理解がまだ・・・
例えば、
a)
for update なしでselectしたトランザクションが終わる前に
変更のトランザクションがコミットした場合にも、
読み取り一貫性が保証するためのUNDOセグメントのデータ構造
について。
b)
SELECTを発行しているトランザクションが
行ごとにUNDOセグメントを見るべき行であるか
どうかをと判断するため
データ構造についてなどです。

(2)なるほど、
select for update は排他ロックだったのかぁ
ところで、
No2の方がロックがかかった行を他のセッションがロックを
かけようとするとエラーになると書いていますが・・？
No3さんの方はロック解除待ちと書いていますが？
あれれ？　トランザクションとセッションは
同じ概念だと考えていいんですよね？

(3)わかりました

(4)insert前にそのPKがある行があるものとして、
ない行に対してロックしてから、
insertするという理解の仕方でよいでしょうか？
あと、
偶然。同じPKの行を複数のトランザクションが
insertしようとしたら、
後のほうは、ロック解除待ちから
抜けて、ロックを取得して、insertしようとした
時に一意制約違反になってしまうということですね？

あと、すべてを通じた質問になるかもしれませんが
ロック取得時は、PK単位でしょうか？
PKのないテーブルも定義できますよね？
行の物理的な位置情報に対してロックしに行ってる
のでしょうか？

(5)(4)がinsert前の存在しない行に対して、
その行が存在するものとして、ロックしに
いっているという答えであれば、(5)に関しても
理解します。insert以外については元から理解して
いました。

NOWAITは急いでいる時に
とにかく、早くレスポンスをださなければ
ならないときは、いいですね。

以上

回答No.2

mi-si

1)Oracleのアーキテクチャ上、ダーティリードは発生しません。常に読み取り一貫性が保証されています。select文を発行した時点でのデータが読み出されます。

2)select for updateで、ロックがかかった行を他のセッションがロックをかけようとすると排他制御エラーになります。と言うのはOracleでは、読み取りに関してはselect for update文は必要ないからです。select for updateは、トランザクション内で、後からupdate文を発行することを前提に先にロック制御を行う為に使う文だからです。

3)特別な指定をしない限り常に行レベルロックです。

4)Oracleは、update,insert,delete文、すべてにおいて行レベルロックをします。（なお、テーブルロックは後からつけられた機能です。）プライマリキーが、あるセッションでインサートされ、トランザクションが完了していない場合でも、同じプライマリキーを別のセッションでインサートを行うと一意制約エラーに成ります。完全に同時に行われた場合はデッドロック処理が行われたと思います。

手元に確認環境がないので確認できないですがアーキテクチャ上はこうなってたはずです。

参考URL：

http://otn.oracle.co.jp/

補足

ありがとうございます。

(1)
No3さんに対する補足にも記載しましたが、
読み取り一貫性なるものがOracleに
あるみたいで・・・
理解できました。
でも、UNDOセグメントの詳細が
まだ理解不足です。

(2)
No3さんはロック解除待ちと書いておりますが。
トランザクションとセッションは
同一概念と考えてよろしいのでしょうか？
だとすると？？？？？

(3)わかりました、ありがとうございます。

(4)
>プライマリキーが、あるセッションでインサートされ、
>トランザクションが完了していない場合でも、
>同じプライマリキーを別のセッションでインサートを行うと一意制約エラーに成ります。

勉強になります。

>完全に同時に行われた場合はデッドロック処理が行われたと思います。

システムの世界で、完全同時ということは、
あるのでしょうか？

デッドロックは
・複数のプロセス(スレッドなども含む)
が1度に複数の同じリソースに対して
　ロックを取得する
・そのロックの取得の順序が同じでない。
時におこる可能性があり、
上記の条件のうちいづれかでも
異なる場合はデッドロックは発生しない
という認識なのですが、
デッドロックのおこる可能性について
”完全に同じ～”
と新しい説明を見ました。
完全に同じとは、具体的にどういう
ことでしょうか？

デッドロック処理とは、
両トランザクションを互いにエラーにする
ということでしょうか？
通常のデッドロック処理は、
ある程度、タイムアウトなどの時間などで
判断するような方式でしょうか？
”完全に同じ～”
形式のデッドロックだと、
タイムアウトの時間なんて、関係ないから
いきなり、デッドロック処理なのかなぁ

回答No.1

taka_tetsu

1)読取一貫性が保証されています。
正常終了した場合にはダーティーリードはありません。

2) select for updateは行排他です。

3) ○

4)、5) insert時および、ユニーク項目update時はブロック単位にかかるような・・・
ごめんなさい、怪しいです。

挙動の確認は、SQL*Plusを2つ立ち上げれば出来るはずです。

補足

ありがとうございます。

(1)正常終了以外でダーティーリードが
あるのですか？

(2)わかりました。
(3)ありがとうございます
(4)
No3さんの回答に対する補足の(4)に対する
再回答次第では、行単位以外も考えてみます。
それ以前にNo2さんが、insertでも、行単位だと
言いきっているようです。

ユニーク項目のupdate ってどういう意味でしょうか？
ちょっと、勉強不足で。。
UNIQキーは候補キーと同じだと認識していますが。
　　UNIQキーがついている項目をUPDATEする場合は
ブロック単位という意味でしょうか。

>挙動の確認は、SQL*Plusを2つ立ち上げれば出来るはずです。
あまり、oracleになれていないです。
排他制御のチェックとなると確認が難しいので
埋め込みSQLを含んだプログラムのなかで、
sleepさせるようなロジックを作る必要が
あるのではないかと、考えますが。
簡単に確認できる方法が
SQLプラスにあるのでしょうか？
手元に、大量のデータが詰まった
Oracle DBがあって、１トランザクションの
ロックの時間が結構あるような
環境があればよいのですが。
仕事中に、タスクの一環以外でそんな
実験をするのは、他の人に迷惑がかかって
しまいそうです。