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

Question

質問内容は結論から言うと、
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 ###

以上です。

mi-si · Accepted Answer

#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は、行ごとにロックフラグなどを記録する領域を持っています。マニュアルなどに書いてなければおそらく企業秘密です。

taka_tetsu · Answer

>(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した場合の挙動も試してみてください。

iwashimizu · Answer

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

mi-si · Answer

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/

taka_tetsu · Answer

1)読取一貫性が保証されています。
正常終了した場合にはダーティーリードはありません。

2) select for updateは行排他です。
 
3) ○

4)、5) insert時および、ユニーク項目update時はブロック単位にかかるような・・・
ごめんなさい、怪しいです。

挙動の確認は、SQL*Plusを2つ立ち上げれば出来るはずです。

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

#2 補足です。

>(1)正常終了以外でダーティーリードが

1)

この回答への補足

1)Oracleのアーキテクチャ上、ダーティリードは発生しません。

この回答への補足

1)読取一貫性が保証されています。

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング