[開発][DB][Oracle] SELECT FOR UPDATE

SELECT時に明示的な行ロックを行う方法
SELECTFOR UPDATEは行レベルでロックするためのSQL文。
そのためFOR UPDATESELECTした後のプロシージャ処理で処理中のレコードが更新されたり、削除されたりすることを防げる。
しかし、
一度に大量のレコードをロックしたり、表全体をロックするような大規模のロックは同時操作性を落とす。
要件次第では、LOCK TABLE等の表ロックを検討すべき。

SELECTFOR UPDATE [WAIT | NOWAIT]
Oracle 9i からFOR UPDATEのWAITの待機秒数が指定可能。
※排他モード(X)で表がロックされている場合には、
待ち時間の指定は無効になり、WAITのみと同じになる。

SELECT ~ WHERE ...
FOR UPDATE
 OF [hoge_table] hoge_column [, piyo_column...] ]
  WAIT [n_sec] | NOWAIT]

ex
SELECT col FROM table WHERE col = 'hoge' FOR UPDATE OF col WAIT 10;
(10秒までは競合するトランザクションの完了を待つ)

FOR UPDATE OF
で指定するカラム名は、どのテーブルをロックするかを設定させるためのもの。
※指定フィールドだけが更新できるゆう制限でない。
※対象テーブルが1つの場合は、あんまし意味なか。

INSERT, UPDATE, DELETEで
NOWAITや待機時間を制限する指定は不可。

UPDATEやDELETEは
SELECT ~ FOR UPDATEを使用して該当する行をロックできるが、
INSERTは
直接的な回避方法はない。
漢籍的な回避策としては、
主キーとしてシーケンスによる代替キーを定義するか、
ロック順序設計を見直す。

行が一意に特定できなくなる操作などには
FOR UPDATE句は使用できない。
-集合演算子 (UNION / INTERSECT / MINUS)
-外部結合
-DISTINCT, GROUP BY, 集計関数(グループ関数)
-CURSOR式(ネステッド・カーソル)

ビュー及ぶインラインビューにもFOR UPDATEを使用出来るが、
以下内容を含むビューには使用できない。
(=更新できないビューにも該当するもの)
-ORDER BY
-集合演算子 (UNION / INTERSECT / MINUS)
-外部結合
-DISTINCT, GROUP BY, 集計関数(グループ関数)
-CONNECT BY ほか

UPDATEしないSELECT FOR UPDATEの終了処理
SELECT ... FOR UPDATEである行をロックしても条件分岐で結局更新しない場合、
ロックされた行をCOMMITかROLLBACKしないで放置しとくと、
同時実行性能を低下させる。
→ロジック上で正常終了として更新しない場合も、
 COMMITしてロックを開放すること。

ロック中の行は無視するSKIP LOCKED
SELECT ... FROM ... FOR UPDATE句を
単純に複数のプロセスで行うと競合して多重化できない。
その場合は、
SELECT ... FROM ... WHERE ROWNUM <= 1000 FOR UPDATE SKIP LOCKED
と記述することで処理を効率化できる。


ref:
Oracle DBA, Oracle SQL and PL/SQL Tips
http://www.shift-the-oracle.com/sql/select-for-update.html

tag : DB Oracle SELECT FOR UPDATE SKIP LOCKED

2009-05-10 17:47 : 開発 : コメント : 0 : トラックバック : 0 :
コメントの投稿
非公開コメント

« next  ホーム  prev »

search

ad



counter


tag cloud

category cloud