ロックに関する検証 その8

~ロックに関する検証 その8~
ペンネーム ちゃむ

前回は、ビットマップ・インデックスの構造をDUMPを用いて説明した。
今回は、ビットマップ・インデックスの構造を理解した上で、ビットマップ・
インデックスが作成してあるテーブルに対して更新処理を発行してみる。

では、早速データを1件更新してみよう。

SQL> UPDATE T10MAN_COPY_4 SET MGR = ' 0000002'
     WHERE EMPNO = 1 ;

1行が更新されました。

では、別のセッションから別の行を更新してみよう。

ああーっ!!別の行を更新したのに、ロック待ちになってしまった!!

SQL> UPDATE T10MAN_COPY_4 SET MGR = ' 0000004'
     WHERE EMPNO = 2 ;
「待たされているよ~~~~~~~~~~」

では、V$LOCKからそのロックの状態を確認してみよう。

SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK FROM V$LOCK
     WHERE TYPE IN ('TM','TX') ;

      SID TY       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK
--------- -- --------- --------- --------- --------- --------- ---------
        9 TX    458752       291         6         0        27         1
        9 TM      5712         0         3         0        27         0
       13 TX    524299       404         6         0         9         0
       13 TM      5712         0         3         0         9         0
       13 TX    458752       291         0         4         9         0

SID=9は、始めに更新処理をしたセッションで、SID=13は、後に更新処理をした
セッション、つまり、待たされているセッションである。一番下の行がREQUEST=4
になっているが、これは、ビットマップ・インデックスを更新する際のロック
が取得できず、待ちが発生してしまっている様子を表している。maxtrans=1に
設定した際の、トランザクション・エントリの待ちに状態はよく似ている。

このときの、ロックがかかっているビットマップ・インデックスの状態を見て
みよう。
B-Treeインデックスと同様に、1つのUPDATE文で2つのブロックが更新される。

SQL> SELECT ROWID,MGR FROM T10MAN_COPY_4 WHERE EMPNO = 1 ;

ROWID              MGR
------------------ --------
AAABZQAAEAAAQjUAAA  0000001

SQL> UPDATE T10MAN_COPY_4 SET MGR = ' 0000002'
     WHERE EMPNO = 1 ;

MGRを’ 0000001’から’ 0000002’に変更。

このときの、更新された’ 0000002’の値を持つビットマップのブロックの様子
を以下に示す。

このときの、更新された’ 0000001’の値を持つビットマップのブロックの様子
を以下に示す。

勉強熱心な読者の方々であれば、以下の様なことを耳にしたことがあるであろう。

「1つのエントリあたりのビットマップセグメントは最大でも、DB_BLOCK_SIZE
の1/2までである。」

これは、ビットマップ・セグメントを更新するとき、図(ダンプ)の場合であ
れば、「col 3; len 853; (853):」⇒ 853バイトであるが、この値がDB_BLOCK_SIZE
(この環境では2048バイト)の1/2を超えてしまうと、更新時に、以前のビット
マップ・セグメントを取り消して(実態は残っている)、新しいビットマップ・
セグメントを同一ブロック内に格納できなくなってしまうからではないであろ
うか?

始めに、次の更新処理が待たされている様子を確認したが、実際には、どこの
行まで待たされるのであろうか?
これは、当然、Start RowidとEnd Rowidに依存する。

先ほどの2つの図(ダンプ)で、
‘ 0000002’のエントリのStart RowidとEnd Rowidと、
‘ 0000001’のStart RowidとEnd Rowidの値は一致している。

col 0; len 8; (8):  20 30 30 30 30 30 30 32 ← 0000002という値
col 1; len 6; (6):  01 01 08 d4 00 00       ← Start Rowid
col 2; len 6; (6):  01 01 09 4d 00 27       ← End Rowid

col 0; len 8; (8):  20 30 30 30 30 30 30 31 ← 0000001という値
col 1; len 6; (6):  01 01 08 d4 00 00       ← Start Rowid
col 2; len 6; (6):  01 01 09 4d 00 27       ← End Rowid

さて、この「01 01 08 d4 00 00」は、どのようにROWIDを表わしているのであ
ろうか?
EMPNO = 1のROWIDは「AAABZQAAEAAAQjUAAA」であったが、
これでは「01 01 08 d4 00 00」とどのような関係があるのかわからない。

これを、ROWIDからファイル番号、ブロック番号、行番号を取得するためのパッ
ケージ DBMS_ROWID を用いて求めてみよう。

SQL> SELECT ROWID,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FNO,
     DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) B_NO,
     DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) R_NO
     FROM T10MAN_COPY_4
     WHERE EMPNO = 1 ;

ROWID                    FNO      B_NO      R_NO
------------------ --------- --------- ---------
AAABZQAAEAAAQjUAAA         4     67796         0

「01 01 08 d4 00 00」を「010」「108d4」「0000」のように分けて、16進数の
「108d4」を10進数に直すと67796になる。この値は、上記のブロック番号と一
致している。

では、End Rowid「01 01 09 4d 00 27」を「010」「1094d」「0027」のように
分解して、16進数の「1094d0」を10進数に直すと67917になる。つまり、ブロッ
ク番号 = 67917ということである。
このStart Rowidのブロック番号とEnd Rowidのブロック番号を利用して、次の
ようなSQL文をなげてみよう。

SQL> SELECT ROWID,EMPNO,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FNO,
     DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) B_NO,
     DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) R_NO
     FROM T10MAN_COPY_4
     WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BETWEEN 67796 AND 67917 ;

ROWID                  EMPNO       FNO      B_NO      R_NO
------------------ --------- --------- --------- ---------
AAABZQAAEAAAQjUAAA         1         4     67796         0
AAABZQAAEAAAQjUAAB         2         4     67796         1
        :                  :         :       :           :
        :                  :         :       :           :
AAABZQAAEAAAQjUAAi        35         4     67796        34
AAABZQAAEAAAQjUAAj        36         4     67796        35
AAABZQAAEAAAQjUAAk        37         4     67796        36
        :                  :         :       :           :
        :                  :         :       :           :
AAABZQAAEAAAQlNAAi      4394         4     67917        34
AAABZQAAEAAAQlNAAj      4395         4     67917        35

このSQL文により、EMPNO = 1から4395までの範囲のビットマップ・セグメント
が格納されているのが分かる。

SQL> UPDATE T10MAN_COPY_4 SET MGR = ' 0000002'
     WHERE EMPNO = 1 ;

1行が更新されました。

上記の状態で、別セッションから以下のSQL文を流して確認してみよう。

EMPNO = 4396を指定すると...

SQL> UPDATE T10MAN_COPY_4 SET MGR = ' 0000002'
     WHERE EMPNO = 4396 ;

1行が更新されました。

EMPNO = 4395を指定すると...

SQL> UPDATE T10MAN_COPY_4 SET MGR = ' 0000002'
     WHERE EMPNO = 4395 ;

「待たされているよ~~~~~~~~~~」

これにより、ロックの範囲が明確にわかるであろう。

SQL> UPDATE T10MAN_COPY_4 SET MGR = ' 0000002'
     WHERE EMPNO = 1 ;

上記のUPDATEでは、EMPNO = 1のMGR列は’ 0000001’(更新前)なので、
カラム値が’ 0000001’と’ 0000002’(更新後)のStart RowidからEnd Rowidま
でのロックを取得する必要がある。
しかし、残り2つのカラム値’ 0000003’と’ 0000004’に関しては、Start Rowid
からEnd Rowidまでのロックは取得されていない。

つまり、Start RowidからEnd Rowidの範囲内にあるデータでも、’ 0000003’か
ら’ 0000004’には変更できるのである。

SQL> SELECT MGR FROM T10MAN_COPY_4 WHERE EMPNO = 4395 ;

MGR
--------
 0000003

SQL> UPDATE T10MAN_COPY_4 SET MGR = ' 0000004'
     WHERE EMPNO = 4395 ;

1行が更新されました。

また、以下のように値が同じであれば、ロックの影響を受けることはない。

‘ 0000002’ ⇒ ‘ 0000002’

SQL> SELECT MGR FROM T10MAN_COPY_4 WHERE EMPNO = 2 ;

MGR
--------
 0000002

SQL> UPDATE T10MAN_COPY_4 SET MGR = ' 0000002'
     WHERE EMPNO = 2 ;

1行が更新されました。

このような例外もあるが、行レベルロックができないのがビットマップ・イン
デックスの大きな弱点である。複数セッションから、ビットマップ・インデッ
クスを作成してある列の値を変更するという処理が多いようなテーブルには、
このビットマップ・インデックスを使用するべきではないだろう。
ただし、ビットマップ・インデックスは、検索中心の処理であれば大きな効果
が望めるだろう。

以上 ちょっと長すぎたか? 茅ヶ崎にて