Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

SELECT 時に明示的な行ロックを行なう方法

SELECT 〜 FOR UPDATE は行レベルでロックをするための SQL 文である。そのために FOR UPDATE で SELECT した後のプロシージャ処理で処理中のレコードが更新されたり、削除されたりすることを防ぐことが可能である。

しかし、一度に大量のレコードをロックしたり、表全体をロックするような大規模のロックは 同時操作性にも問題が生ずるので極力控えるように設計したい。要件上、どうしても必要な場合には LOCK TABLE などの表ロックを検討する。

SELECT 〜 FOR UPDATE [WAIT | NOWAIT]

Oracle 9i から FOR UPDATE の WAIT に待機秒数が指定できる。(※1)

SELECT 〜 WHERE ...
FOR UPDATE
  [OF [table_name.]column_name1 [,column_name2...]]
  [WAIT [n_sec] | NOWAIT]
--
-- (例) 10 秒までは競合するトランザクションの完了を待つ 
SELECT col FROM table WHERE col = 1 FOR UPDATE OF col WAIT 10 ;

FOR UPDATE OF で指定するカラム名は、どのテーブルをロックするかを決定するためのものである。 指定フィールドだけが更新できるという制限ではない。対象テーブルが１つの場合にはあまり意味が無い。

(※1) 排他モード(X) で表がロックされている場合には、待ち時間の指定は無効になり WAIT のみと同じになる。
参考 : 表ロックの種類と相互関係

INSERT 、UPDATE、DELETE で NOWAIT や待機時間を制限する指定はできない。(※2)
UPDATE や DELETE は SELECT 〜 FOR UPDATE を使用して該当する行をロック可能であるが、 INSERT の場合には直接的な回避方法がない。 間接的な回避策としては 主キー としてシーケンスによる代替キーを定義するか、ロック順序設計自体を見直す。
(※2) 参考： ORA-02049: タイムアウト: 分散トランザクションがロックを待機しています。 の仕組みを本来の目的と異なる方法として利用することはできるがお勧めできない。

行が一意に特定できなくなる操作などには 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 することでロックを解放することをお勧めする。 （正常ケースで ROLLBACK するのは混乱してしまう可能性がある）

SELECT ... FOR UPDATE ;
IF (condition) THEN
	UPDATE ... SET ... ;
	-- COMMIT;  ここには COMMIT 文を記述しない
END IF;
COMMIT ;   -- ← ここで COMMIT すると FOR UPDATE は常に解放される
...

ロック中の行は無視する SKIP LOCKED 句

SELECT ... FROM ... FOR UPDATE 句を単純に複数のプロセスで行うと競合して多重化できない。その場合には

SELECT ... FROM ... WHERE ROWNUM <= 1000 FOR UPDATE SKIP LOCKED

などと記述することで処理を効率よく行うことが可能
(注意) マニュアルに公開されていない文法のため、おそらくサポートされない。

DDL における WAIT

Oracle 11g からは DDL_LOCK_TIMEOUT 初期化パラメータによって DDL においての待機時間を設定できる。
⇒ ORA-00054:リソース・ビジー、NOWAITが指定されていました。

---

関連 DML

• INSERT ／ UPDATE ／ DELETE ／ MERGE