トランケートとORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [SQL・DDL]
台風１９号が過ぎ去り被害の報道にこの台風の爪痕の大きさに驚くとともに被災された方々には１日も早い生活の復旧を願うばかりである。
ORA-54は一般的にロック中のテーブル（レコード）に対し、SELECT ... FOR UPDATE NOWAIT等でロックを獲得しようとした場合に発生するエラーである。ロック中のオブジェクトに対しロックをかけようとしているため、このオブジェクトへのロック解除後に再実行すれば解消される。この際、NOWAITではなく、SELECT ... FOR UPDATE WAITでタイムアウトを設定すると、指定された秒数までロック獲得を試み、失敗した場合にエラーとなる（この場合はORA-30006)。ロックとの競合で即時エラーを回避したい場合に有効である。
このORA-54、DML実行中のテーブルにトランケートを実行しても発生する。これはトランケートは該当オブジェクト（セグメント）に対しTMエンキューを排他モードで獲得するためである。alter table ... truncate patitionで複数のパーティションにトランケートを順に実行するとき、たまにORA-54が返って失敗するのは、多くの場合DML（オンライン処理などの業務アプリケーション）との競合が発生し、トランケートがロック獲得に失敗したためである。なお、SELECT実行中のテーブルにトランケートを実行した場合、トランケートでORA-54は発生せず処理されてしまう。SELECTは（Oracleの場合読み取り一貫性のメカニズムにより）ロックを獲得しないためである。トランケートの結果、data_object_idが変わってしまうため、SELECT側にORA-8103: object no longer existsが返る。
トランケートのORA-54を回避するには、当たり前であるがDML実行中にトランケートを実行しないことである。そもそもDML実行中にトランケートを実行しなければならないこと自体に疑問はありつつも、現実問題として、トランケート実行タイミングに影響ある業務APを完全に制御し切れないケースもあるだろう。このような場合に有効なのが、11gから導入されたDDL_LOCK_TIMEOUTパラメータである。例えばこれを以下のようにセッションレベルで設定すると、指定された秒数までDDLのロック獲得を試みることができる。多少のDMLとの競合であれば、DML実行の間にロック獲得を試みて、トランケートの成功確率を高めてくれるので便利である。なお、このデフォルトは0(秒)なので、ロック獲得に失敗したら即ORA-54が返却される。
alter session set ddl_lock_timeout=60;
TMエンキューに限って言えば、トランケートはセグメントレベルでロックを獲得する。つまり、パーティション表において、ORA-54が発生するのは、あくまで別トランザクションがそのタイミングで該当パーティションにDML操作を行っていた場合に限られる。したがって、例えば日付レンジパーティション表で、使われなくなった過去パーティションをトランケートする場合には、ORA-54の発生確率は低いだろう。逆に、ハッシュパーティションやリストパーティションで、どのパーティションも頻繁にDML操作される状況においては、いくつかのパーティションでORA-54が発生しトランケートが失敗したりする。このような場合に、上記DDL_LOCK_TIMEOUTは非常に有効である。
ただし、パーティションの競合が無いからといってオン中のトランケートを実行しても大丈夫という訳ではない。ORA-54は避けられても、トランケートにより当該テーブルにアクセスするSQLのカーソルのinvalidation、およびそれに伴うハードパース、それがトランケートによるテーブルレベルのlibrary cache lockと競合することは避けられないためである。このあたりの挙動の詳細は記事「トランケートについて知っておくべきこと」を参照されたい。
なお、DDL_LOCK_TIMEOUTはトランケートに限らず、ALTER TABLE・INDEX等のパーティションメンテナンスのDDLにも有効である。例えば、ローカルインデックスリビルド(alter index ... rebuild partition ...)、未使用エクステントの開放(alter table ... modify partition ... deallocate unused)など、パーティションメンテナンスの実行時にORA-54が発生した場合、回避策の１つとして考慮するとよいだろう。
以上
ORA-54は一般的にロック中のテーブル（レコード）に対し、SELECT ... FOR UPDATE NOWAIT等でロックを獲得しようとした場合に発生するエラーである。ロック中のオブジェクトに対しロックをかけようとしているため、このオブジェクトへのロック解除後に再実行すれば解消される。この際、NOWAITではなく、SELECT ... FOR UPDATE WAITでタイムアウトを設定すると、指定された秒数までロック獲得を試み、失敗した場合にエラーとなる（この場合はORA-30006)。ロックとの競合で即時エラーを回避したい場合に有効である。
このORA-54、DML実行中のテーブルにトランケートを実行しても発生する。これはトランケートは該当オブジェクト（セグメント）に対しTMエンキューを排他モードで獲得するためである。alter table ... truncate patitionで複数のパーティションにトランケートを順に実行するとき、たまにORA-54が返って失敗するのは、多くの場合DML（オンライン処理などの業務アプリケーション）との競合が発生し、トランケートがロック獲得に失敗したためである。なお、SELECT実行中のテーブルにトランケートを実行した場合、トランケートでORA-54は発生せず処理されてしまう。SELECTは（Oracleの場合読み取り一貫性のメカニズムにより）ロックを獲得しないためである。トランケートの結果、data_object_idが変わってしまうため、SELECT側にORA-8103: object no longer existsが返る。
トランケートのORA-54を回避するには、当たり前であるがDML実行中にトランケートを実行しないことである。そもそもDML実行中にトランケートを実行しなければならないこと自体に疑問はありつつも、現実問題として、トランケート実行タイミングに影響ある業務APを完全に制御し切れないケースもあるだろう。このような場合に有効なのが、11gから導入されたDDL_LOCK_TIMEOUTパラメータである。例えばこれを以下のようにセッションレベルで設定すると、指定された秒数までDDLのロック獲得を試みることができる。多少のDMLとの競合であれば、DML実行の間にロック獲得を試みて、トランケートの成功確率を高めてくれるので便利である。なお、このデフォルトは0(秒)なので、ロック獲得に失敗したら即ORA-54が返却される。
alter session set ddl_lock_timeout=60;
TMエンキューに限って言えば、トランケートはセグメントレベルでロックを獲得する。つまり、パーティション表において、ORA-54が発生するのは、あくまで別トランザクションがそのタイミングで該当パーティションにDML操作を行っていた場合に限られる。したがって、例えば日付レンジパーティション表で、使われなくなった過去パーティションをトランケートする場合には、ORA-54の発生確率は低いだろう。逆に、ハッシュパーティションやリストパーティションで、どのパーティションも頻繁にDML操作される状況においては、いくつかのパーティションでORA-54が発生しトランケートが失敗したりする。このような場合に、上記DDL_LOCK_TIMEOUTは非常に有効である。
ただし、パーティションの競合が無いからといってオン中のトランケートを実行しても大丈夫という訳ではない。ORA-54は避けられても、トランケートにより当該テーブルにアクセスするSQLのカーソルのinvalidation、およびそれに伴うハードパース、それがトランケートによるテーブルレベルのlibrary cache lockと競合することは避けられないためである。このあたりの挙動の詳細は記事「トランケートについて知っておくべきこと」を参照されたい。
なお、DDL_LOCK_TIMEOUTはトランケートに限らず、ALTER TABLE・INDEX等のパーティションメンテナンスのDDLにも有効である。例えば、ローカルインデックスリビルド(alter index ... rebuild partition ...)、未使用エクステントの開放(alter table ... modify partition ... deallocate unused)など、パーティションメンテナンスの実行時にORA-54が発生した場合、回避策の１つとして考慮するとよいだろう。
以上
2019-10-19 21:13
nice!(0)
コメント(0)
コメント 0