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

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

前回は、ロックの重要性などを説明した。
今回は、ロックが悪さをする場合について説明しようと思う。

悪さというと少し語弊があるかもしれないが、例えば、以下のように行ロック待ちになるようなSQL文を発行してみる。

セッションA

SQL> UPDATE A SET ENAME = 'OSMAU';

12行が更新されました。

セッションB

SQL> UPDATE A SET ENAME = 'OSMAU';
「待たされているよ~~~~~~~~~~」

この時の、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
--------- -- --------- --------- --------- --------- --------- ---------
        8 TX    589835       111         6         0       314         1
        8 TM      5526         0         3         0       314         0
       11 TM      5526         0         3         0       298         0
       11 TX    589835       111         0         6       298         0

これは、SID(セッションID)=11がSID=8に待たされていることを示めす。
REQUESTにO以外の数字が入っているものがロックを獲得できずに待たされているもの、
LMODEにO以外の数字が入っているものがロックを獲得しているものを示す。
LMODE,REQUESTの数字の意味については、以下のV$LOCKの項目説明を参照してほしい。

さらに、BLOCK=1からも、「こいつが待たせている原因のロック」ということがわかる。
ここでのBLOCK列の意味は、「オラクルブロック」の意味ではなく、「障害」という意味であろう。

この例ではデータを保護しているだけで、「悪さをしている」とは言えないが、
例えば、処理が終わっているのに、いつまでもSID=8のロックを解放しないようなときを、
今回は「悪さをしている」と表現している。

では、「悪さをしている」状態を再現してみよう。

その前に、通常はSQL*PLUSで接続するとpsコマンドで以下のように、親プロセスと子プロセスを確認できる。

$ ps -ef | grep 13277
     UID   PID  PPID  C    STIME TTY      TIME CMD
  ora803 17711 17172  1 15:54:04 pts/7    0:00 grep 13277
  ora803 13277 28182  0 15:32:00 pts/6    0:01 sqlplus sys/manager
  ora803 13280 13277  0 15:32:03 ?        0:02 oracleora803 (DESCRIPTION=
  (LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

以下に項目の説明を記述する(OSコマンドmanより)。

UID    プロセス所有者のユーザ ID 番号
PID    プロセスのプロセス ID(このデータはプロセスを停止させるために必要)
PPID   親プロセスのプロセス ID
C      スケジューリングのためのプロセッサ利用率(旧式)
       -c オプションを使用した場合は出力されません
STIME  時間、分、秒で示されるプロセスの開始時間(ps が実行される 24 時
       間以上前に開始したプロセスは、月および日で示されます)
TTY    プロセスを制御している端末(制御端末がない場合は、メッセージ ?
       が出力)
TIME   プロセスの累積実行時間
CMD    コマンド名(-f オプションでは、完全なコマンド名と引数を最大 80
       文字まで出力)

sqlplus sys/manager は oracleora803 (DESCRIPTION=(LOCAL=YES)…の親プロ
セスである。

通常、親プロセスをKillすると子プロセスもkillされるが、「悪さをしている」状態というのは、
親プロセスがkillされたときに子プロセスが残ってしまう状態である。

<再現手順>

1.teratermにOSユーザーでログイン
2.sqlplusよりscottで、update a set ename = 'OSAMU';を実行
3.!を入力して、再度sqlplusによりupdate a set ename = 'OSAMU';を実行
    この時点でこのupdate文はロック待ちになる。
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
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
         7 TM      20201          0          3          0    1347369          0
         7 TX     327698       5948          0          6    1347369          0
         9 TX     327698       5948          6          0    1347378          1
         9 TM      20201          0          3          0    1347378          0
4.この状態でteratermのウインドウを強制終了(右上の×ボタンを押す)さ
    せると、以下のようなプロセスが確認できる。
$ ps -ef | grep oracleora803
  ora803  3380     1  0   4月 17 ?        0:01 oracleora803 (DESCRIPTION=(LOCAL=YES)
(ADDRESS=(PROTOCOL=beq)))
  ora803  3383     1  0   4月 17 ?        0:04 oracleora803 (DESCRIPTION=(LOCAL=YES)
(ADDRESS=(PROTOCOL=beq)))
  ora803 23372 22541  1 10:38:55 pts/4    0:00 grep oracleora803

親プロセスIDが1になっている子プロセスは、親なしプロセスである。

今回は、意図的に発生させたので原因は明白であるが、以下のSQL文を用いて、
待たせている原因のセッション情報やSQL文などが確認できる。

SQL> SELECT SES.SID,SES.SERIAL#,SES.TERMINAL,SES.PROGRAM,SES.OSUSER,SES.USERNAME,
    SES.MACHINE,SQL.SQL_TEXT
    FROM V$SESSION SES ,V$SQL  SQL
    WHERE  SES.SQL_ADDRESS=SQL.ADDRESS AND 
    SID IN (SELECT SID FROM V$LOCK WHERE BLOCK = 1);

      SID    SERIAL# TERMINAL   PROGRAM                   OSUSER   USERNAME MACHI
--------- ---------- ---------- ------------------------- -------- -------- -----
SQL_TEXT
----------------------------------------------------------------------------------
       9        896  pts/2      sqlplus@sol (TNS V1-V3)   ora803   SCOTT    sol
 UPDATE A SET ENAME = 'OSAMU'

では、上記の SID、SERIAL#を用いて、Oracleのコマンドでセッションを切ってみよう。

SQL> ALTER SYSTEM KILL SESSION '9,896';

この後、V$LOCKを確認してみると以下のようにデータが検索されなくなるが、
これはロックが解放されたことを示す。

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

レコードが選択されませんでした。

psコマンドでも以下の通りである。これで、正常な状態に戻ったといえる。

$ ps -ef | grep oracleora803
  ora803   160 21744  1 11:13:39 pts/4    0:00 grep oracleora803

ロックが「悪さをしている」状態というのは、強制終了などを行なったときに発生する場合が多い。
以前、エンドユーザがクライアントからデータを更新中に、誤ってPCのコンセントを
足でひっこ抜いてしまい、サーバ側にセッションが残っていてロックを取得したままだったというような
現象が起きたこともある。

おかしなセッションの状況を確認したら、ALTER SYSTEM KILL SESSIONか、OSコマンドのkillで対応しよう。

以上 ロックちゃん 茅ヶ崎にて