Oracleエラー対応（ORA-00060）：デッドロックした時の対処法について

現役DBAが解説～Oracleエラー対応～

2021.01.22

デッドロックした際に発生するORA-00060について、原因の調査方法及び解決策を解説します。

Contents

エラーメッセージ
原因
調査方法
- ①エラーが発生したセッションを調べる
- ②セッションが保持／待機しているロックの種類を調べる
解決策
- 1. テーブルをロックする順序を変更する
- 2. 共有テーブルロックを使用しないようにする
最後に

エラーメッセージ

ORA-00060: リソース待機の間にデッドロックが検出されました。

原因

ORA-00060はデッドロックが検出されたときに発生するエラーです。
デッドロックとは、複数のセッションがお互いにロックの解放を待つ状態になり、以降の処理が進まなくなることです。
例えば、次の図をご覧ください。

トランザクションAでは、まず、処理1でデータ1をロックし、処理2でデータ2を参照します。
トランザクションBでは、まず、処理1でデータ2をロックし、処理2でデータ1を参照します。
しかし、トランザクションAが処理2でデータ2を参照しようとしても、トランザクションBの処理1でデータ2がロックされているためにできません。

同様に、トランザクションBが処理2でデータ1を参照しようとしても、トランザクションAの処理1でデータ1がロックされているためにできません。
このように、お互いがお互いをロックしあい処理が停止してしまう状態がデッドロックです。

調査方法

それでは、ORA-00060が発生した場合の対処法についてご紹介します。

①エラーが発生したセッションを調べる

まずは原因を突き止める必要があります。
デッドロックが発生するとalert.logにエラーとトレースファイル名が記録されます。そこでトレースファイルを確認します。
トレースファイルは初期化パラメータUSER_DUMP_DESTで指定した場所に出力されます。
SQL文でUSER_DUMP_DESTを調べ、トレースファイルがどこにあるかを確認します。

◆SQL例

SQL> show parameter user_dump_dest;

②セッションが保持／待機しているロックの種類を調べる

トレースファイルを開き、デッドロックが発生したセッションやDeadlock graphからデッドロックの詳細を確認します。SESSION ID を見ることでデッドロックが発生したSIDは299であることがわかります。

Deadlock graphのsessionの項目から299と687でお互いにロックしあっていることが分かります。

Rows Waited onでデッドロックの対象レコード情報を確認します。
例えば、session 299についてrowidがAAAVQ5AAHAAAACGAAAであることがわかります。
また、dictionary objnは待機しているオブジェクトのIDのことで87097であることが読み取れます。

オブジェクトのIDが分かれば、このようにSQL文からオブジェクトの所有者や名前を知ることもできます。

◆SQL例

SQL> select owner, object_name from dba_objects where object_id in ([オブジェクトのID]);

また、rowidから具体的にテーブルのどの行であるかを知ることもできます。
上で判明したテーブル名を用いて次のSQL文を実行します。

◆SQL例

SQL> select * from [上で判明したテーブル名] where rowid = '[rowid]';

Information for the OTHER waiting sessionでデッドロックしている相手の対象レコード情報を確認します。
今回はsession 687のことです。current SQLの項目からデッドロックで中断されたSQL文を知ることができます。

以上のように、トレースファイルを確認して具体的なデッドロックの原因を分析してください。

解決策

まずはロールバックを行います。ロールバックは以下のSQL文を実行すればできます。

◆SQL例

SQL> roll back

それでは調査方法②でデッドロックについての詳細が判明したので、具体的にデッドロックが発生している部分を修正します。
修正には次のような方法があります。

1. テーブルをロックする順序を変更する

まずはシンプルな考え方ですが、デッドロックが発生しないように処理の順番を変えるという方法です。
あるトランザクションがデータをロックした時に、他のトランザクションがそのデータを参照しないように順番を工夫します。

先ほどのデッドロックの例でいえば、データをロックする処理とデータを参照する処理の順番を入れ替えてみます。

データをロックする処理を後回しにしたので、データを参照する処理が問題なく行われます。

また、処理の順番を変更するのが難しい場合は、適切なCOMMIT発行も対処法の1つです。
COMMITとはトランザクションを終了させ、トランザクションによる処理を確定させることです。
INSERT文、UPDATE文、DELETE文といったデータの操作を行うSQL文では、実行時からデータのロックが行われ、COMMITするまでロックされた状態が保持されます。
そこで、COMMITを行いデータのロックが解除されるようにすることでデッドロックを解消するという方法です。

先ほどのデッドロックの例でいえば、トランザクションAが終わった段階でCOMMITを行い、その後トランザクションBに移行します（逆でもよい）。つまり、トランザクションAとトランザクションBが並列処理にならないようにしています。

2. 共有テーブルロックを使用しないようにする

こちらは、いわゆる楽観的ロックを用いた対処法です。

楽観的ロックとはデータを更新する時、データの取得時と同じであることが確認できれば更新するという排他制御の方法です。
楽観的ロックでは、まず、データの更新状態を識別できるようなバージョンカラムを用意します。
このバージョンカラムはデータが更新されるたびに書き換えられるようにします。
例えば、最初にバージョンカラムを「1」として、データが更新されたらバージョンカラムを「2」と書き換えるようにします。こうすることでデータに対する更新があったかどうか知りたい時に、バージョンカラムが「1」のままならデータの更新はなく、「2」となっていればデータが更新されたのだと判断することができます。

楽観的ロックは、データに対するロックを行わずバージョンカラムの確認によってデータの更新を行います。
したがって、デッドロックの解消が期待できます。

ただし、楽観的ロックではデータの更新を行っている最中は無防備であるため、同時更新が起こるとデータの整合性に問題が生じる可能性があります。
同一のデータに対する同時更新が起きないであろう、もしくは起きてもやり直しがきくといった状況であれば楽観的ロックの導入を検討してください。