この特集のトップページへ

9.7.2 デッドロック

　アプリケーションで不適切なロックを実行すると，デッドロックが生じることもある。すでに述べたように，デッドロックとはアプリケーションが互いに利用するリソースを握り合ってしまった状況を指す。このような現象が生じると，アプリケーションは互いの処理の終了を待って待機状態に陥るため，処理が完全に停止してしまう。
　SQL Serverは，ロック要求で待機しているセッションを定期的にスキャンし，最初のスキャンで待機状態にあるセッションにフラグを立てる。最初のスキャンでフラグを立てたセッションが次のスキャンにおいても待機状態のままであれば，回帰的なデッドロック探索が開始される。この探索によってロック要求の循環チェーンが検出されると，SQL Serverはいずれかのセッションを自動的に選択し，セッションを強制終了させる。
　このときSQL Serverは，終了させたセッションのトランザクションをロールバックする一方，ユーザーアプリケーションにエラーメッセージ1205を返し，ユーザーの要求をキャンセルして，残されたもう片方のトランザクション処理を続行できるようにする。一般的にロールバックされたトランザクションを処理していたユーザーアプリケーションは，エラーメッセージを受信した時点でトランザクションを再実行するように実装すべきだろう。

Fig.9-49 デッドロック発生メッセージ

　デッドロックが生じているか否かを確認するには，パフォーマンスモニタのSQL Server:LocksオブジェクトにあるNumber of Deadlocksカウンタを監視すればよい。このカウンタには，デッドロックになったロック要求数が記録されている。
　別の方法として，SQL Serverプロファイラでデッドロックおよびデッドロックチェーン（ロック要求の循環チェーン）を監視する方法もある。
　この場合にも，サーバー名，データベースID，オブジェクトIDでグループ化し，開始時刻，イベントクラス，アプリケーション名，テキストなどを設定すればよいであろう。
　デッドロックが記録されている場合には，どのアプリケーションがロックをブロックしているのか，アプリケーションのどの部分でロックしているのかを調べ，デッドロックの原因を取り除かなければならない。

Fig.9-50 SQL Serverプロファイラのデッドロックイベント

　デッドロックにかかわる詳細な情報を調査するには，DBCC TRACEONステートメントの実行時にトレースフラグ1204を設定する。このとき，同時にトレースフラグ3605も指定し，エラーログファイルにトレース記録を出力させるとよいだろう（Fig.9-51）。

　　DBCC TRACEON(3605, 1204)

　トレースフラグは，SQL Serverの起動時に設定することもできる。SQL Serverの起動時に-Tオプションを指定すると，エラーログファイルにトレース情報が記録される。

　　　sqlservr.exe -T1204

　SQL Serverを起動させるときのパラメータは，SQL Server Enterprise Managerを利用して設定する（Fig.9-52）。

Fig.9-51 DBCC TRACEONステートメントの実行

Fig.9-52 起動時パラメータの指定

　SQL Serverの起動時にトレースフラグ1204が指定されている場合，デッドロックを検出すると，エラーログファイルには次のようなレポートが出力される。

Fig.9-53 デッドロックレポート（クリックすると拡大）

　このほかにも，SQL Server Enterprise Manager，DBCCステートメント，SQL Serverプロファイラなどを利用して，デッドロックの原因となっているプロセスや問題箇所（コード）を特定することができる。具体的には，次のような手順を踏む。

1. デッドロックは，特定のプロセスが長時間ロックを保持したときに発生することが多い。そこでまず，デッドロックチェーンの先頭にあるサーバープロセスID（SPID）を確認する。具体的な方法としては，次のようなものがある。

○デッドロックチェーンの先頭にあるサーバープロセスIDを探索する最も手軽な方法は，SQL Serverプロファイラを利用する方法である。SQL Serverプロファイラの選択条件としてデッドロックイベント（Lock:DeadLock Chain）を指定し，アプリケーション名，テキスト，SPIDデータ列を選択する。SQL Serverプロファイラはロック情報をファイルまたはテーブルに出力する

Fig.9-54 デッドロックイベント

○SQL Serverプロファイラを利用しない場合は，下記のようなクエリを使用して，デッドロックチェーンの先頭にある各サーバープロセスIDの値を取得する

　　　CREATE PROCEDURE sp_blocker AS
　　　DECLARE @statement char(255) 
　　　　IF EXISTS (SELECT * FROM sysprocesses WHERE spid IN (SELECT blocked
　　　　　FROM sysprocesses))
　　　　　SELECT spid, status, loginame=substring(suser_name(suid),1,12),
　　　　　　hostname=substring(hostname,1,12),blk=convert(char(3), blocked), 
　　　　　　dbname=substring(db_name(dbid),1,10), cmd, waittype
　　　　　　FROM sysprocesses
　　　　　　WHERE spid IN (SELECT blocked FROM sysprocesses) AND blocked = 0
　　　　　ELSE
　　　　　　SELECT 'ブロッキングプロセスがみつかりません' 　

  • 上記の方法で取得したサーバープロセスIDでDBCC INPUTBUFFER(spid)ステートメントを実行し，デッドロックチェーンの先頭にあるサーバープロセスIDで動作しているクエリを見つける

  • あるいは上記の方法で取得したサーバープロセスIDでSQL Server Enterprise Managerのコンソールツリーから［管理］−［現在の利用状況］−［プロセス情報］をクリックし，デッドロックチェーンの先頭にあるサーバープロセスIDを右クリックし，表示されるメニューから［プロパティ］を選択する。最後に実行したクエリが［プロセスの詳細］ダイアログボックスに表示される

Fig.9-55 プロセス情報の詳細表示

２．デッドロックチェーンの先頭にあるサーバープロセスIDのロックタイプを調べる。具体的には，sp_lockシステムストアドプロシージャを実行するか，masterデータベースのsyslockinfoテーブルを検索する。

３．ブロックしているプロセスのトランザクションネストレベルとステータスを調べる。トランザクションネストレベルは，本質的に@@TRANCOUNTと同じ数値である。

４．デッドロックの原因となったテーブルおよびアプリケーションを特定し，デッドロックが生じないようにアプリケーションを修正する。

前へ Chapter 9　25/46 次へ