Oracle のロックと対応方法（悲観的排他制御と楽観的排他制御）

Oracle の行ロックについてまとめています。参加したプロジェクトの中で使われていた排他制御についてもまとめてみました。

SELECT FOR UPDATE の確認（悲観的排他）

SELECT FOR UPDATE の動作確認のため、 SQL Developer を2つ起動します。

1つ目の SQL Developer で次の SQL文 を実行します。ここで注意したいのが、 SELECT文に FOR UPDATE を指定することで行レベルのロックが行われます。

-- 1つ目
SELECT ID,NAME FROM MST_ROLE WHERE ID = 2 FOR UPDATE;

-- 2つ目
SELECT ID,NAME FROM MST_ROLE WHERE ID = 2 FOR UPDATE;

データが取得され結果が表示されます。

次にもう一つの SQL Developer で同じテーブルの同じ行を SELECT してみます。すると、結果が返ってこないで問い合わせのプログレスバーが行ったり来たりしたままとなります。

1つ目の SQL Developer でロールバックを行うと2つ目の SQL Developer で結果が返ってくることが確認できます。これは、1つ目のSELECT文実行で行レベルロックが実行され2つ目のSELECT文で排他処理されたことになります。

では、1つ目の SQL Developer で実行するSQL文に FOR UPDATE を付加し、2つ目の SQL Developer で実行する SELECT文には FOR UPDATE 無しで実行した場合はどうなるのでしょう？

-- 1つ目
SELECT ID,NAME FROM MST_ROLE WHERE ID = 2 FOR UPDATE;

-- 2つ目
SELECT ID,NAME FROM MST_ROLE WHERE ID = 2;

2つ目の SQL Developer でSQL文を実行すると今度は結果が返ってきます。

SELECT FOR UPDATE には、「WAIT」と「NOWAIT」のオプションを指定することができオプションを指定しない場合は「WAIT」として実行されます。次に「NOWAIT」を指定して実行してみます。

-- 1つ目
SELECT ID,NAME FROM MST_ROLE WHERE ID = 2 FOR UPDATE NOWAIT;

-- 2つ目
SELECT ID,NAME FROM MST_ROLE WHERE ID = 2 FOR UPDATE NOWAIT;

2つ目の SQL Developer でSQL文を実行すると　「 ORA-00054　リソース・ビジー 」が返され即座に処理が終了します。

排他しないときの問題

何も対策をしていないアプリケーションで同じデータをほぼ同時に編集した場合、後から編集を行った操作が採用されることになります。しかし、その処理で問題が起こるケースもあります。例えば、次の図のようなケースを考えてみます。

始めにユーザーAがボルトの在庫を照会し、続いてユーザーBも同じボルトを照会したとします。

-- ユーザーAの照会
SELECT ID,商品,在庫数 FROM 在庫 WHERE ID = 1;

-- ユーザーBの照会
SELECT ID,商品,在庫数 FROM 在庫 WHERE ID = 1;

ユーザーAが購入検討している隙にユーザーBが1個購入してしまいました。

-- ユーザーBの更新
UPDATE 在庫 SET 在庫数 = 在庫数 - 1 WHERE ID = 1;

この時、ボルトの在庫数は0個となるはずですがユーザーAの画面には更新される前の情報が表示されているはずです。

その後、ユーザーAもボルトの購入を決めますが実際には在庫がなく正しい処理とならないはずです。

-- ユーザーAの更新
UPDATE 在庫 SET 在庫数 = 在庫数 - 1 WHERE ID = 1;

更新後のデータは在庫数 -1 となりよくない結果となります。

このような処理が発生しないよう排他制御を行う必要があります。

楽観的排他制御の例 その1

上記例のようなアプリケーションを作るとき SELECT FOR UPDATE を使うと「ロックを取得したユーザが見ているレコードが他者から変更されないことを保障する」というメリットがありますが、参照したいだけなのに待ちが発生するなど利用者には不便な運用が予想されます。

そこで、 楽観的排他制御とよばれる排他制御を実装してみたいと思います。

まず、対象となるテーブルに排他制御用の 「MODIFY_COUNTER NUMBER」項目を用意します。（項目名は何でもいいです。）

データを取得する時、追加した MODIFY_COUNTER 項目の値も取得しておきます。

-- ユーザーAの照会
SELECT MODIFY_COUNTER,ID,商品,在庫数 FROM 在庫 WHERE ID = 1;
-- MODIFY_COUNTER は、 0 で取得されたとします

-- ユーザーBの照会
SELECT MODIFY_COUNTER,ID,商品,在庫数 FROM 在庫 WHERE ID = 1;
-- MODIFY_COUNTER は、 0 で取得されたとします

データを更新する時、取得済みの MODIFY_COUNTER を Where句に含め MODIFY_COUNTER をインクリメントしておきます。

-- ユーザーBの更新
UPDATE 在庫 SET 在庫数 = 在庫数 - 1,MODIFY_COUNTER = MODIFY_COUNTER + 1 
WHERE ID = 1 AND MODIFY_COUNTER = 取得済みのMODIFY_COUNTER[ 0 ];
-- WHERE 句により MODIFY_COUNTER = 0 のレコードが対象となり、更新後 MODIFY_COUNTER は 1 になる

MODIFY_COUNTER をインクリメントしたことにより後から更新に来るユーザーAが取得していた MODIFY_COUNTER とデータベース上の MODIFY_COUNTER が不一致となり更新が行われないことになります。

-- ユーザーAの更新
UPDATE 在庫 SET 在庫数 = 在庫数 - 1,MODIFY_COUNTER = MODIFY_COUNTER + 1 
WHERE ID = 1 AND MODIFY_COUNTER = 取得済みのMODIFY_COUNTER[ 0 ];
-- WHERE 句により MODIFY_COUNTER = 0 のレコードが対象となり、存在しないため更新されない

アプリケーション側は、更新レコード数を確認するなどして正しく更新されたかどうか確認します。例えば、ユーザーAの更新レコード数が 0 の場合、更新処理が失敗したとしてエラー表示などを行います。

楽観的排他制御の例 その2

Oracle のトリガー機能を使って楽観的排他制御を行ってみます。

「楽観的排他制御の例 その1」と同様に、対象となるテーブルに排他制御用の 「MODIFY_COUNTER NUMBER」項目を用意します。（項目名は何でもいいです。）

トリガーで使用できる OLD および NEW 疑似レコードを使用して MODIFY_COUNTER の更新状況を確認します。更新前の状態と更新時の状態が一致していれば他の処理で更新されていないと判断するよう次のトリガーを作成します。

CREATE OR REPLACE TRIGGER トリガー名
BEFORE INSERT OR UPDATE 
ON テーブル名
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
DECLARE
    BEGIN
        IF UPDATING THEN
            IF :OLD.MODIFY_COUNTER = :NEW.MODIFY_COUNTER THEN
                IF :OLD.MODIFY_COUNTER = 9999 THEN
                    :NEW.MODIFY_COUNTER := 1;
                ELSE
                    :NEW.MODIFY_COUNTER := :NEW.MODIFY_COUNTER + 1;
                END IF;
            ELSE
                RAISE_APPLICATION_ERROR(-20002,'他の端末で更新されています');
            END IF;
        END IF;
END;

アプリケーション側は、データを取得時に追加した MODIFY_COUNTER 項目の値も取得しておきます。

-- ユーザーAの照会
SELECT MODIFY_COUNTER,ID,商品,在庫数 FROM 在庫 WHERE ID = 1;
-- MODIFY_COUNTER は、 0 で取得されたとします

更新する時、取得済みの MODIFY_COUNTER の値で MODIFY_COUNTER を更新します。

-- ユーザーAの更新
UPDATE 在庫 SET 在庫数 = 在庫数 - 1,MODIFY_COUNTER =  取得済みのMODIFY_COUNTER[ 0 ] 
WHERE ID = 1;
-- SET に取得済みの MODIFY_COUNTER を指定することでトリガーが更新状況を比較します

もしほかの処理で同じレコードが更新されている場合、 OLD と NEW が一致しないためエラーとなります。一致している場合は、次の処理のため MODIFY_COUNTER をインクリメントしています。これで、排他制御が実装できたかと思います。