他にロックがないのに ORA-00054 エラーが発生する理由と対処

本業のシステムで ORA-00054 ってエラーがたまに発生します。アプリケーション側でエラーを捕捉して適切なエラーを出しているので大事にはならないのですが、そもそも格好悪い。そもそも ORA-00054 とは何か？

というエラーが示しているとおり、

「SELECT 〜 FOR UPDATE NOWAIT;　を発行した際に、該当するレコードロックが取得できなかった場合に、NOWAIT 指定によりロック取得を待たずにエラーで返しました。」

って意味になります。通常このエラーが発生する場合は、以下のような場合です。

なのですが、今回のケースはコレじゃないんです。ってか、上記のようなケースはテスト時に見つかるはず。実は、ORA-00054 はこれ以外の原因でも発生します。

運用を続けていく中でしか発生しにくいパターンなのですが、ORA-54エラーは、ITL （Interested Transaction List） が不足した場合にも発生します。

Oracle のデータブロックの内部構造から考えてみる

Oracleでは、同時トランザクションによるアクセスを管理するために、各データブロック内に制御情報として、トランザクションエントリ（ITL）という領域を確保しますし、ロックを取得します。ITL は表に対してINITRANSパラメータで静的に確保されています（デフォルト値は１）。また、ITL 領域が不足した場合には、ブロック内の PCTFREE による空き領域に空きがあれば MAXTRANSで設定されている値(10g では常に 255) まで動的に確保されます。

つまり、図で表すと以下の感じ。黄色の部分が Oracle のヘッダ情報になる部分。

PCTFREE デフォルト値１０％と INITRANS のデフォルト値１がマズイ点について

一般には PCTFREE パラメータを適切に設定していれば、複数の ITL を静的に確保しておく必要がないと言われていますが、データ更新により頻繁にレコード長が変わるテーブルで、かつ各レコード長が小さい場合には PCTFREE を使い切ってしまう場合があります。

PCTFREE パラメータはデフォルト値１０％です。例えば、８Kのブロックサイズのデータベースだとすると、PCTFREE で確保される空き領域は僅かに 800 byte です。varchar2 型のデータをもつテーブルで頻繁に更新がある場合は PCTFREE を使い切ってしまう場合が実際あります。とすると、ITL はデフォルトの１しか確保できないので、

PCTFREE に空きがないデータブロックに対しては、トランザクションが１つしか同時実行できなくなる可能性があるのです。

ORA-00054 を解決する方法する２つの方法

まず１つめは、Oracle 9i 以降にサポートされた SELECT 〜 FOR UPDATE WAIT 秒数;　を使って、デッドロックを回避しつつトランザクションを待つ SQL に変更することです。8i までは WAIT に秒数が指定できなかったので、デッドロックになってしまうのですが、秒数が指定できるので、５秒まってダメならエラーなんて感じにできます。完全な対処ではなく、ORA-54 を減らすという解決方法なのですが、アプリケーションの改変も SQL １つ変更するだけで、効果も十分にあります。

２つめが、INITRANS を増やし、PCTFREE をチューニングする方法です。但し、INITRANS は既に存在するデータブロックに対しては効果がありません。次に確保するデータブロックから効果がある設定（と思う。既存のデータブロックも再配置される？）なので、今まさに発生している ORA-54 が消えるというわけではないかもしれませんが、将来は発生しなくなるでしょう。変更する構文は、

です。完全に解決したいという場合は、INITRANS と PCTFREE を適切にチューニングしてテーブルを再作成することになるでしょう。今回は、１つめの対処方法で切り抜けましたが、エラーはその後発生しなくなりました。

ITL を更に深追い。データブロックのダンプをとってみる

さらにディープな領域へいくなら、ダンプを採る方法があります。ダンプの取り方は

です。ダンプの出力先は、user_dump_dest パラメータで指定された場所です。管理者ユーザで sqlplus に接続して、

で user_dump_dest の場所は取得できますが、通常はアラートログとかがあるディレクトリと近い場所で、xxxx/DB名/udump って場所にあるはずです。データファイル番号とブロック番号は、ITL が不足しているテーブルの該当レコードに対して、以下の SQL を実行することで一覧が取得できます。

実行例は以下の通り。

     FILE#     BLOCK#
---------- ----------
         9     190106
         9     224717
         9     208066
         9     218346
         9     220914
         9     230474

データファイル番号とブロック番さえわかればダンプができます。ダンプすると、udump ディレクトリに、DB名_ora_0001.trc みたいなファイルができていると思います。比較的サイズがおおきいやつがそうです。中身はこんな感じになってます。正しくデータブロックのダンプそのものをひとが読めるようにした感じのものです。

/app/oracle/admin/TESTDB/udump/TESTDB_ora_0001.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options
ORACLE_HOME = /app/oracle/product/10.2.0/db
System name:    Linux
Node name:      TESTDB2
Release:        2.6.9-34.ELsmp
Version:        #1 SMP Fri Feb 24 16:56:28 EST 2006
Machine:        x86_64
Instance name: TESTDB
Redo thread mounted by this instance: 2
Oracle process number: 33
Unix process pid: 8021, image: oracle@TESTDB (TNS V1-V3)

*** 2006-08-04 17:42:06.005
*** SERVICE NAME:(SYS$USERS) 2006-08-04 17:42:05.991
*** SESSION ID:(284.50151) 2006-08-04 17:42:05.991
Start dump data blocks tsn: 6 file#: 9 minblk 236899 maxblk 236899
buffer tsn: 6 rdba: 0x02439d63 (9/236899)
scn: 0x0000.034ac382 seq: 0x01 flg: 0x06 tail: 0xc3820601
frmt: 0x02 chkval: 0x9e71 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000005E83E00 to 0x0000000005E85E00
005E83E00 0000A206 02439D63 034AC382 06010000  [....c.C...J.....]
005E83E10 00009E71 00000001 00003AD9 034ABFDE  [q........:....J.]

〜中略〜

005E85DE0 04086A78 07010101 04086C78 FF010101  [xj......xl......]
005E85DF0 36300AFF 6C633830 52443430 C3820601  [..0608cl04DR....]
Block header dump:  0x02439d63
 Object id on Block? Y
 seg/obj: 0x3ad9  csc: 0x00.34abfde  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x2438422 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.002.00008c14  0x00800d76.0b52.21  --U-    1  fsc 0x0000.034ac382
0x02   0x0004.012.00008bb8  0x008007c5.0ca4.38  C---    0  scn 0x0000.034abf59

data_block_dump,data header at 0x5e83e64
===============
tsiz: 0x1f98
hsiz: 0x114
pbl: 0x05e83e64
bdba: 0x02439d63
     76543210
flag=--------
ntab=1
nrow=129
frre=-1
fsbo=0x114
fseo=0x3b0
avsp=0x343
tosp=0x343
0xe:pti[0]      nrow=129        offs=0
0x12:pri[0]     offs=0x1f64

〜中略〜

0x110:pri[127]  offs=0x422
0x112:pri[128]  offs=0x3ea
block_row_dump:
tab 0, row 0, @0x1f64
tl: 52 fb: --H-FL-- lb: 0x0  cc: 9
col  0: [ 7]  c6 12 01 08 20 56 45
col  1: [ 6]  67 6c 61 79 38 38
col  2: [ 1]  30
col  3: [ 2]  c2 02
col  4: [ 7]  78 6a 08 04 01 01 01
col  5: [ 7]  78 6c 08 04 01 01 01
col  6: *NULL*
col  7: *NULL*
col  8: [10]  30 36 30 38 63 6c 30 34 44 52
tab 0, row 1, @0x1f31
tl: 51 fb: --H-FL-- lb: 0x0  cc: 9

〜中略〜

end_of_block_dump
End dump data blocks tsn: 6 file#: 9 minblk 236899 maxblk 236899

赤字の部分が ITL 制御情報になります。この例では ITL が２つ分確保されていることがわかります。ORA-54 が発生した SQL を元に、該当するデータブロックを全て見ていくことで、どのブロックで ITL が不足したかを知ることができます。この手の話を更に知りたい方は、おらおらオラクルの P119 ページが参考になります。

おら！オラ！オラクル

posted with amazlet on 06.08.06

木脇 高太郎
翔泳社 (2003/05/27)
売り上げランキング: 51,170

Amazon.co.jp で詳細を見る