Oracle デッドロックの原因を追いかけてみる

突然ですが「ORA-00060」。何のことだかわかりますか？そう、デッドロックですね。
簡単に言えば「お互いのセッションがロックの解放待ちになり、以降の処理を継続できない」状態を指します。
開発中ならまだしも本番運用が始まってからは見たくないエラーの一つです。

あいにくSI Object Browser(以下、OB)では「事前対策」を行うのは難しいのですが、「事後調査」であれば画面操作で簡単に確認を行うことが可能です。

OBを使ってデッドロックの発生原因となったSQLを追いかけていきましょう。

デッドロックを発生させる

まずは調査を行うためにデッドロックを発生させます。
いくつか方法はありますが、今回は以下の手順でデッドロックを発生させることにします。

1. セッション1でテーブル1をロックする
2. セッション2でテーブル2をロックする
3. セッション1でテーブル2にUPDATE文を発行する
4. セッション2でテーブル1にUPDATE文を発行する

まずは手順1と2です。
OBを2つ起動し、セッション1(左側)とセッション2(右側)に見立ててそれぞれテーブルをロックします。

Oracle デッドロックの原因を追いかけてみる 1

- 図1. SQL実行画面 – 排他ロック -

セッション1ではT5テーブルを、セッション2ではT6テーブルを排他ロックしました。

続けて手順3、4を行います。
セッション1からT6テーブルにUPDATE文を発行するとセッション2の排他ロックにより待機状態となり、その後セッション2からT5テーブルにUPDATE文を発行したタイミングでデッドロックが検出されます。

Oracle デッドロックの原因を追いかけてみる 2

- 図2. デッドロック発生 -

デッドロックの原因を追いかける

以上の操作でデッドロックが発生しましたので、OBを使って原因を追いかけていきます。
OB上の作業では、DBAロールが付与されていることを前提として進めていきますのでご注意ください。

また、今回はデッドロックが発生したDBサーバがリモートの環境にありますので、OBの使用端末にトレースファイル一式をコピーしておきます。
アラートログとトレースファイルそれぞれが必要になりますので、全部まとめてコピーしておくとよいでしょう。

Oracle デッドロックの原因を追いかけてみる 3

- 図3. トレースファイル一式の配置 -

なお、OBの利用端末とOracle Databaseの端末が同一であればコピーの必要はありません。
アラートログ、トレースファイルが出力されるフォルダを確認しておきましょう。

準備ができたらOBの[管理]メニューから[ロック情報]を開きます。

Oracle デッドロックの原因を追いかけてみる 4

- 図4. ロック情報 -

[ロック情報]画面の上段、赤枠部分に[デッドロック履歴を表示]ボタンがあります。
このボタンを押下すると、これまでにOBで一度もトレースファイルの格納フォルダを設定したことがない場合に限り、同フォルダを指定するためのダイアログが表示されます。
(ログフォルダの設定は[SQLキャッチ]機能、[テーブルアクセス表]機能と共有されます)

Oracle デッドロックの原因を追いかけてみる 5

- 図5. トレースファイル格納フォルダの選択 -

ここでは図3で示した通り、トレースファイル一式を格納したフォルダ(C:\temp\trace)を指定します。
(OBの利用端末とOracle Databaseの端末が同一である場合は、確認しておいたアラートログ、トレースファイルが出力されるフォルダを指定します。)

デッドロックの履歴がある場合は画面下段にデッドロックの発生日時と、その際に出力されたトレースファイル名がリストで表示されます。

Oracle デッドロックの原因を追いかけてみる 6

- 図6. デッドロックの履歴 -

デッドロックの履歴リストで、詳細を確認したい行をダブルクリックすると[トレース]画面が呼び出されます。

Oracle デッドロックの原因を追いかけてみる 7

- 図7.[トレース]画面 -

初期状態では[オプション]タブが選択され、Oracle付属のトレースファイル整形ユーティリティである「TKPROF」の使用要否およびその詳細設定項目が表示されます。

今回は整形を行いませんので設定は不要ですが、上部の[ファイル名]欄がDBサーバであるLinux上のパスになっています。
このパスを図3で示した C:\temp\traceにあるファイルを参照するよう変更してから[トレース結果]タブに移動します。
[トレース結果]タブに表示されるトレース内容を見ていくと・・・

Oracle デッドロックの原因を追いかけてみる 8

- 図8.デッドロックの発生時のSQL -

無事、デッドロックが発生した際のSQLに到達しました。

まとめ

いかがでしたか？
今回はOBを使ってデッドロックの発生原因を追いかける手順をご紹介しました。

もちろん手動で追いかけることは可能ですが、デッドロックが複数回発生していてそれぞれの原因を追いかけようと思うとなかなか大変です。
その点、OBでは履歴をリストに表示してくれますので簡単に原因にたどり着くことができます。

あまり発生してほしくはないシチュエーションですが、デッドロックが頻発するような状況においてきっとOBが役に立つと思いますので、ぜひ記憶の片隅に入れてみてください。