重要なポイント

複数ユーザーが同時にデータ更新したときに不整合がおきないようにレコードをロック！

解説

通常の更新

下記の図のように、「Aさん」と「Bさん」が同時にレコード更新をした場合、どちらも在庫があると判断してしまい、更新することができてしまいます。その場合、「40」 もしくは 「60」という値に更新されてしまい、想定する処理結果ではなくなってしまいます。

排他制御（ロック）

同時に更新できないように「（レコード）ロック」をすることで、他のユーザーからの処理を受け付けなくすることができます。

  1. Aさん、ロックする。
    在庫があれば〈-50〉し、在庫数「50」に更新する。
  2. Aさん、ロック解除する。
  3. Bさん、ロック解除待ち。
  4. Bさん、ロックする。
    ロックが解除されたら、処理が始まる。
    在庫があれば〈-60〉しようとするが、在庫が足りないので更新しない。
  5. Bさん、ロック解除する。

ロックトラブル

レコードロックしたが、解除することを忘れてしまうと、次の処理が止まってしまいます。必ず、ロック解除するようにしましょう。

デッドロック

下記のような流れで、それぞれの処理の中で、レコードロックを解除せずに他のレコードを更新しようとすると「デッドロック」と呼ばれる状態となり、「Aさん」「Ｂさん」ともに処理が継続できない状態になります。レコードロックを利用する場合には、注意しましょう。

構文

レコードロックするためには、トランザクション処理開始の宣言「begin」を入れることで行うことができます。また、Select文の最後に「for update」をセットすることでレコードをロックすることが可能です。

for update（レコードロック）

-- トランザクション処理開始（※必須事項）
begin;
    select * from [テーブル名]
     where [カラム名] = [値] for update;
-- 処理を確定（※ロック解除）
commit;

＜例題＞

  • トランザクション処理を実行すること。
  • 処理１：「commodity_mst」を下記の条件でロックすること。
    • 商品NO（commodity_no）が〈1〉
  • コミット（commit）し、データ確定しロック解除こと。
--トランザクション処理開始
begin;

--処理１
select * from commodity_mst cm
 where cm.commodity_no = 1 for update;

--コミット（確定）
commit;

（応用）ロックして更新

-- トランザクション処理開始（※必須事項）
begin;
    select * from [テーブル名]
     where [カラム名] = [値] for update;

    update [テーブル名] set
     where [カラム名] = [値];
-- 処理を確定（※ロック解除）
commit;

＜例題＞

  • トランザクション処理を実行すること。
  • 処理１：「commodity_mst」を下記の条件でロックすること。
    • 商品NO（commodity_no）が〈1〉
  • 処理２：「commodity_mst」テーブルの値を更新すること。
    • 抽出条件
      • 商品NO（commodity_no）が〈1〉
    • 更新する値
      • 在庫数（stock）を〈9〉に更新
  • コミット（commit）し、データ確定しロック解除こと。
--トランザクション処理開始
begin;

--処理１
select * from commodity_mst cm
 where cm.commodity_no = 1 for update;

--処理２
update commodity_mst set
 stock = 9
 where commodity_no = 1;

--コミット（確定）
commit;

参考：カーソルの場合

ストアドプロシージャなどで使われる「カーソル」にも排他制御が可能です。
ここで取得した値をもとにレコード更新や削除などを行う場合には、レコードロックを行うことを推奨します。

delimiter //

drop procedure if exists pro036_cursor;
create procedure pro036_cursor(in int_start_commodity_no int,in int_end_commodity_no int)
begin

    -- レコードから値を受け取るための変数宣言
    declare rec_commodity_no int default 0;
    declare rec_commodity_name varchar(255) default '';
    declare rec_stock int default 0;

    -- 表示用の変数（初期値は空白）
    declare str_text varchar(8000) default '';
   
    -- カーソルがデータセットの最後かどうかを判定するための変数宣言（done=false）
    declare done int default false;

    -- ここで取得したいデータセット用ＳＥＬＥＣＴ文を発行
    declare datCursor cursor for
        select
             cm.commodity_no
            ,cm.commodity_name
            ,cm.stock
         from commodity_mst cm
         where cm.commodity_no >= int_start_commodity_no
         and cm.commodity_no <= int_end_commodity_no for update;

    -- レコードの最終行に到達したかどうかを判定「done=true」に変更
    declare continue handler for not found set done = true;

    open datCursor;
        read_loop :loop

            -- 1行ずつレコードを読み出す
            fetch datCursor into rec_commodity_no, rec_commodity_name, rec_stock;

            -- カーソルからの読み出しが最後に達していればループを抜ける
            if done = true then
                leave read_loop;
            end if;

            set str_text = concat(str_text,rec_commodity_name,'在庫：',rec_stock,',');
        
        end loop;
    close datCursor;

    set str_text = left(str_text, char_length(str_text) - 1);
    select str_text;
end;
//

delimiter ;

/* ストアドプロシージャを呼び出す */
call pro036_cursor(1,5);