【ORACLE】FOR UPDATEの勘違いと真実（今さら）、あと実験

IT
Page content

Oracleの「FOR UPDATE OF TABLE.COLUMN」は、指定した項目を基準としてレコードの排他をかける構文だと思ってたが、

SELECTの対象が複数存在する場合にどのテーブルをロックするかを指定するもとの情報らしくて、

SELECT対象が１テーブルだけだとあまり意味がないらしい。

また、仮に複数あったとしても、「そのテーブルを（ロックする）」という意味しか持たず、

項目を指定することによる、その項目に関連する排他の意味合いはないようだ。（じゃあ書かせんなよ…FOR UPDATE OF TABLEだけ（COLUMN書く必要ない）でいいやんけ）

要するに項目Aと項目Bがあったら「FOR UPDATE OF 項目A」でも「FOR UPDATE OF 項目B」でも効果は同じ、ということらしい。



なお、前提として、ここでいう「そのテーブルを」というのは、FOR UPDATEを付けているSELECT側の条件（WHERE句等）に左右される。

つまり「テーブル全体をロックする」という意味ではなくて指定された条件に該当するレコードのみがロックされる。



まあでも確かに冷静に考えると、

指定した項目によっては「（その項目を指定したからって）だから何？」っていう気になるのもわかる。

これは、自身のプロジェクトにおいて、「FOR UPDATE OF TABLE.COLUMN」の「COLUMN」に指定される項目が、

得てしてKEY項目であることが多いことに由来するのだろう。

つまり「そのKEYのレコード全体をロックする」という意味合いとして使っている（書いている）と、

結果的には勝手に”勘違いしていた”ことになるのだが、

そもそも、もともとFOR UPDATE OF TABLE.COLUMNの".COLUMN"には、

Oracle的には、項目自体に対する排他等の意味や目的はなくて、

「そのテーブルを」の判断基準にしか使ってないんだから、

指定されたからといって、例えばフラグ的意味合いの項目だったとすると”値が1のレコードだけロックするの？”とか、

様々疑問が出てくることは確かである。

それに、KEY以外の項目を指定したとしたら（あるいはKEYなしテーブルだったとしたら）、

排他ができない無意味な構文になるかというと、それはそれで本末転倒であるし、

そういうことも考えると「よく考えればわかるだろ」と言われるとそうでもある。



結合してデータを取得する際に同じ名前の項目が一度のSELECT内で複数存在する場合に、

「どのテーブルの」項目かを区別する指定を明記しないと実行できずに怒られる仕様がある。

このFOR UPDATEの項目指定もそれとよく似ていて、その理由というか、経緯も多分同じなんだろう。

「Aテーブル.項目B」と「Cテーブル.項目B」が存在するとき、

単に「FOR UPDATE OF 項目B」とだけ書いても「どっちのだよ？」という気になるから、テーブル名から書かせるようになっている…

そんなところなんだろう。

だから「テーブルどころか項目名まで書かせている」というよりは「項目を書かせるためにテーブルまで指定させている」というほうが正しい気がする。

でも項目を書くことの意味がない（指定する項目によって排他の効果は変わらない）なら出発点がまず間違っていると思うのだ。

（これは俺の予想に過ぎないから事実がどうかは知らない…）



ちょっと簡単なサンプルを交えて俺の勘違いを説明させていただくと
★店舗コード★処理年月売上金額処理済フラグ
0012014/01\10,000,0001
0012014/02\15,000,0001
0012014/03\17,500,0001
0012014/04\15,500,0000
0022014/01 \9,500,0001
0022014/02 \9,750,0001
0022014/03\10,750,0001
0032014/04\11,750,0000
というテーブル（これを本項では「店舗別実績データ」テーブルと呼ぶことにする。KEYは★がついてる２項目）があって、
  
select 店舗コード,処理年月,売上金額  
from   店舗別実績データ  
where  処理済フラグ='0'  
order by 店舗コード,処理年月  
for update of 店舗コード
↑としてフェッチしていく場合、
ロックの単位はあくまで「店舗コード」だから、（←この時点で勘違いしている。ロックの「単位」という指定ではないからだ）
例えば店舗コード001を処理中は002はまだロックしていない（先に別セッションで掴もうと思えば掴める）と思っていた。
（勝手にそういうイメージで使われていると思い込んでいた）

でもよく考えたら別にFOR UPDATEの指定先にKEYを指定しなきゃいけないなんてルールや規約はないし、
やってなかっただけで別に↓のような書き方をしても同じ排他の効果を得られるわけだ。 
  
select 店舗コード,処理年月,売上金額  
from   店舗別実績データ  
where  処理済フラグ='0'  
order by 店舗コード,処理年月  
for update of 売上金額
知らなかった。というかややこしくないかこれ。
上にも書いたがなんでわざわざ項目まで書かせる仕様なんだ。
Oracleの人の考えてることはわからないことが多い。


これを確認する目的の簡単な実験をしてみたので、それも載せておくこととしよう。
この実験には以下の登場人物が存在する。

（１）TEST_PARENTテーブル
（２）TEST_TRNテーブル
（３）test_trn_for_updateプロシージャ
（４）update文（他セッションからの排他要求）


（１）のDDL
  
create table TEST_PARENT  
(  
     TEST_CD nvarchar2(10)   
    ,TEST_NAME nvarchar2(100)  
)
（１）のデータ
TEST_CDTEST_NAME
0000000001テスト１番
0000000002テスト２番
0000000003テスト３番


（２）のDDL
  
create table TEST_TRN   
(  
     TEST_CD nvarchar2(10)   
    ,TEST_RECORD_NO number(10)   
    ,TEST_VALUE number(10)  
    ,TEST_FLG number(1)  
)
（２）のデータ
TEST_CDTEST_RECORD_NOTEST_VALUETEST_FLG
00000000011100
00000000012200
00000000013300
00000000014-11
00000000015-21
00000000016400
000000000211000
000000000221500
000000000232000
000000000242500
0000000003110001
0000000003220001


（３）のDDL（ソースコード）
  
CREATE OR REPLACE PROCEDURE test_trn_for_update(arg nvarchar2) as  

  cursor cur_test_for_update_of_flg is  
  select  
     a.TEST_CD  
    ,a.TEST_NAME  
    ,b.TEST_RECORD_NO  
    ,b.TEST_VALUE  
    ,b.TEST_FLG  
  from TEST_PARENT a  
       ,TEST_TRN b  
  where a.TEST_CD = b.TEST_CD  
    and b.TEST_FLG = 0  
  order by  
        a.TEST_CD  
       ,b.TEST_RECORD_NO  
  for update of b.TEST_FLG  
  ;  

  cursor cur_test_for_update_of_cd is  
  select  
     a.TEST_CD  
    ,a.TEST_NAME  
    ,b.TEST_RECORD_NO  
    ,b.TEST_VALUE  
    ,b.TEST_FLG  
  from TEST_PARENT a  
       ,TEST_TRN b  
  where a.TEST_CD = b.TEST_CD  
    and b.TEST_FLG = 0  
  order by  
        a.TEST_CD  
       ,b.TEST_RECORD_NO  
  for update of b.TEST_CD  
  ;  

  row_test_data cur_test_for_update_of_flg%ROWTYPE;  

  chr_test_cd        TEST_TRN.TEST_CD%TYPE;  
  chr_test_nm        TEST_PARENT.TEST_NAME%TYPE;  
  nmb_test_record_no TEST_TRN.TEST_RECORD_NO%TYPE;  



begin


  DBMS_OUTPUT.PUT_LINE(to_char(sysdate , 'yyyy/MM/dd Hh24:mi:ss') || ' START.');  

  if arg = '1' then  
       open cur_test_for_update_of_flg;  
  else  
       open cur_test_for_update_of_cd;  
  end if;    
  --open cur_test_data_for_update_of_test_flg;  
  DBMS_OUTPUT.PUT_LINE(to_char(sysdate , 'yyyy/MM/dd Hh24:mi:ss') || ' CURSOR OPEN FINISH.');  
  loop   
       if arg = '1' then  
            fetch cur_test_for_update_of_flg into row_test_data;  
            exit when cur_test_for_update_of_flg%NOTFOUND;  
       else  
            fetch cur_test_for_update_of_cd into row_test_data;  
            exit when cur_test_for_update_of_cd%NOTFOUND;  
       end if;  

       chr_test_cd := row_test_data.TEST_CD;  
       chr_test_nm := row_test_data.TEST_NAME;  
       nmb_test_record_no := row_test_data.TEST_RECORD_NO;  

       DBMS_OUTPUT.PUT_LINE(to_char(sysdate , 'yyyy/MM/dd Hh24:mi:ss') || ' [CD,NAME,RECORD_NO]=[' || chr_test_cd || ',' || chr_test_nm || ',' || to_char(nmb_test_record_no));  

       DBMS_LOCK.SLEEP(10);  

       update TEST_TRN  
       set    TEST_FLG = 1  
       where TEST_CD = chr_test_cd  
         and TEST_RECORD_NO = nmb_test_record_no  
       ;  

  end loop;  

  DBMS_OUTPUT.PUT_LINE(to_char(sysdate , 'yyyy/MM/dd Hh24:mi:ss') || ' LOOP FINISH.');  

  rollback;  

  DBMS_OUTPUT.PUT_LINE(to_char(sysdate , 'yyyy/MM/dd Hh24:mi:ss') || ' FINISH.');  



end;

/





（４）のSQL

  
update test_trn set test_value = test_value + 1 where test_cd = '0000000002';

簡単にいえば、（１）がマスタ、（２）がトラン、（３）が未処理データ（TEST_FLG=0）を更新する（TEST_FLG=1）処理といった感じか。
ただ業務処理のような意味合いは全くなく、実験用にそれっぽくこしらえたに過ぎないので、単なるイメージである。

実は（１）のTEST_PARENTテーブルにはあまり意味はない。
「複数テーブルがある場合に」というのを実現するため無理矢理登場してもらっただけである。
（３）処理内でも参照しかしていないし、ロックもしない。
逆に言えば処理中でもこいつは更新できるはずだが、そこまでは今回は見ない。（面倒になった）

（３）は与えられる引数によって使うカーソルを内部で切り分ける。
"1"が与えられたら「TEST_FLG」によるFOR UPDATE、それ以外なら「TEST_CD」によるFOR UPDATEだ。
取得項目と取得条件はどちらも同じで、これ自体にあまり意味はない。
それぞれが同一の排他効果を持つかを検証する目的だ。

（３）の実行中に別セッションから（４）を実行し、排他要求が受け付けられるかを確認する。
（３）のSELECTによるデータ取得範囲内に、（４）で更新をかけようとする test_cd = '0000000002'が含まれるので、
（３）が先にロックしている場合（４）は（FOR UPDATEの項目に寄らず）（３）の終了まで更新できないはずである。
ただ、先に（３）が完了してしまうと実験にならないので、ループの途中、（３）は強制的に10秒待機してもらう（DBMS_LOCK.SLEEPによる）。

（３）のところどころに入っているDBMS_OUTPUTは実行結果を標準出力して確認するためのもんであるが、
DBMS_OUTPUTのフラッシュの仕様の関係で全て完了した後でないと標準出力されない。
が、途中で排他がかかった（ことにより処理が停滞した）場合は、
実行後にそのことがわかるように各PUT_LINEには全てSYSDATEを含めている。
かつ、上述したように「途中で強制的に10秒待機」することで
次処理までに10秒以上かかっていたら「なんかおかしいぞ」と気づくきっかけにもなる。
※もちろんできれば経過はリアルタイムにほしい。（そういう仕様のDBMS_OUTPUTが使いづらいとは昔から感じているのだ）
　javaとかで組めばできるんだが今回はプロジェクトのとある案件の調査のための情報収集も兼ねているので
　プロジェクトでの実装状況に合わせてPROCEDUREで組んだ次第だ。

なお、（３）は処理最終部でROLLBACKをかけて処理内で行った更新を全て元に戻す。
これは実験の都合上、何度も連続実行できるようにするためである。


実験はsqlplusから行う。
異なる端末（TeraTerm）からそれぞれ同じDBにsqlplusで入り込み、
片方は（３）を実行もう片方は（４）を実行する。
順番は（３）⇒（４）の順。
（３）は実行時の引数を変えて、TEST_FLG及びTEST_CDそれぞれの排他を試みる。
結果をまとめると以下のようになる。

実験パターン（３）側標準出力（４）側標準出力
引数1（TEST_FLGによるFOR UPDATE） 
  
20:14:41 SQL> execute :a := '1';  

PL/SQLプロシージャが正常に完了しました。


経過: 00:00:00.00

20:14:47 SQL> select to_char(:a) from dual;


TO_CHAR(:A)


1


経過: 00:00:00.00

20:14:50 SQL>

20:15:15 SQL>

20:15:15 SQL>

20:15:16 SQL> exec test_trn_for_update(:a);

2014/10/10 20:15:19 START.

2014/10/10 20:15:19 CURSOR OPEN FINISH.

2014/10/10 20:15:19 [CD,NAME,RECORD_NO]=[0000000001,テスト１番,1

2014/10/10 20:15:29 [CD,NAME,RECORD_NO]=[0000000001,テスト１番,2

2014/10/10 20:15:39 [CD,NAME,RECORD_NO]=[0000000001,テスト１番,3

2014/10/10 20:15:49 [CD,NAME,RECORD_NO]=[0000000001,テスト１番,6

2014/10/10 20:15:59 [CD,NAME,RECORD_NO]=[0000000002,テスト２番,1

2014/10/10 20:16:09 [CD,NAME,RECORD_NO]=[0000000002,テスト２番,2

2014/10/10 20:16:19 [CD,NAME,RECORD_NO]=[0000000002,テスト２番,3

2014/10/10 20:16:29 [CD,NAME,RECORD_NO]=[0000000002,テスト２番,4

2014/10/10 20:16:39 LOOP FINISH.

2014/10/10 20:16:39 FINISH.


PL/SQLプロシージャが正常に完了しました。


経過: 00:01:20.03



  
20:15:08 SQL> update test_trn set test_value = test_value + 1 where test_cd = '0000000002';  

4行が更新されました。


経過: 00:01:14.09

20:16:39 SQL>

引数0（TEST_CDによるFOR UPDATE） 
  
20:18:11 SQL> execute :a := '0';  

PL/SQLプロシージャが正常に完了しました。


経過: 00:00:00.00

20:18:17 SQL> select to_char(:a) from dual;


TO_CHAR(:A)


0


経過: 00:00:00.00

20:18:20 SQL>

20:18:26 SQL>  exec test_trn_for_update(:a);

2014/10/10 20:18:27 START.

2014/10/10 20:18:27 CURSOR OPEN FINISH.

2014/10/10 20:18:27 [CD,NAME,RECORD_NO]=[0000000001,テスト１番,1

2014/10/10 20:18:37 [CD,NAME,RECORD_NO]=[0000000001,テスト１番,2

2014/10/10 20:18:47 [CD,NAME,RECORD_NO]=[0000000001,テスト１番,3

2014/10/10 20:18:57 [CD,NAME,RECORD_NO]=[0000000001,テスト１番,6

2014/10/10 20:19:07 [CD,NAME,RECORD_NO]=[0000000002,テスト２番,1

2014/10/10 20:19:17 [CD,NAME,RECORD_NO]=[0000000002,テスト２番,2

2014/10/10 20:19:27 [CD,NAME,RECORD_NO]=[0000000002,テスト２番,3

2014/10/10 20:19:37 [CD,NAME,RECORD_NO]=[0000000002,テスト２番,4

2014/10/10 20:19:47 LOOP FINISH.

2014/10/10 20:19:47 FINISH.


PL/SQLプロシージャが正常に完了しました。


経過: 00:01:20.04



  
20:18:28 SQL> update test_trn set test_value = test_value + 1 where test_cd = '0000000002';  

4行が更新されました。


経過: 00:01:16.04

20:19:47 SQL>


↑だけ見ると、引数１のモードでは、（４）が（３）よりも前に開始しているように見えるかもしれないが、
update～のSQLをコピって貼り付けるまでの間に若干のタイムラグがあるためで、
実際には（３）より後に（４）を実行しているのだ。

この結果を見る限りでは、
TEST_FLGによるFOR UPDATEも、TEST_CDによるFOR UPDATEも同じ排他効果を持っていることが確認できる。
いずれのパターンに関しても、（３）終了まで（４）が完了されないからだ。

だからこそ余計に項目まで書かせることへの疑問符が付く。
テーブルまででよかったんじゃないのか。