レベルエンター山本大のブログ

面白いプログラミング教育を若い人たちに

BLOCKVROCKリファレンス目次はこちら

SQLServerProfilerでデッドロックの図示

DB

現場で、デッドロックが発生してその対処をしている。
RDBMSによって分析・対処の方法は異なるが、今回はSQLServer2005で行った。
SQLServer2005およびSQLServerProfilerの強力さを改めて思い知った。

デッドロックの検知

デッドロックが発生したことは、アプリケーションのExceptionで判明したのだが、
具体的にどの操作と、どの操作が関連しているか調べなくては再現ができない。
それを調べるために、SQLServerのエラーログにDeadLockの情報をトレースする仕組みがある。
以下のトレースフラグ「1204」と「3605」をOnにするのだ。

トレースフラグをOnにするコマンドは以下。

DBCC TRACEON(1204, 3605, -1)	--1204と3605のトレースフラグ(-1:Global)をOnにする
go
DBCC TRACESTATUS(-1)	--[-1:Global]がたっているトレースフラグの状態を表示する
go

ちなみに後始末として、Offにするコマンドは以下。

DBCC TRACEOFF(1204, 3605, -1)	--1204と3605のトレースフラグ(-1:Global)をOffにする
go
DBCC TRACESTATUS (1205, 3605)	--1204と3605のトレースフラグを表示する
go

デッドロックの分析

デッドロックの発生が検知でき、再現可能となれば
次はSQLServerProfilerで具体的な原因を探る。

SQLServerProfilerは、SQLServerに流れるSQLを横取りして
すべて記録して行ってくれる。しかもSqlserverの実行を邪魔しないし
SQLServerの再起動などもいらない。

以下のように設定してプロファイラを起動すれば、
Deadlockの状況を図示をしてくれる。



以下、Deadlockを図示している箇所。
それぞれのアイコンにマウスを当てると、
流れているSQLを表示することもできる。

また、EventClassが「Deadlock graph」の行をコピーすると、それらの情報がXML形式でコピーできて
後の加工に便利だ。

対処

MSのケーススタディーサイトに私のケースと同じような現象が紹介されている。

【ケーススタディー】デッドロックの解決方法の推奨
UPDATE ステートメントはクラスタ化インデックスに対して "クラスタ化インデックスの更新" を実行しています。
そのため、非クラスタ化インデックスとクラスタ化インデックスの両方が更新される必要があります。
クラスタ化インデックスは ix_tblQueuedEvents であり、非クラスタ化インデックスは PK_tblQueuedEvents です。
UPDATE ステートメントは、更新を実行するために両方のインデックスの排他ロックを取得する必要があります。
これら 2 つのインデックスは、デッドロックに関連するインデックスです。SQL プロファイラ トレースを確認すると、
WHERE 句で ResynchDate を使用しているクエリはありません。
どのステートメントも非常に特殊なステートメントであり、WHERE 句では EventSID を使用していました。
クラスタ化インデックスでは EventSID を使用する方が適切と考えられます。
この情報と、ユーザーとの話し合いを基に、ResynchDate インデックスは古いため不要であると判断しました。
そこで、ユーザーに ResynchDate の ix_tblQueuedEvents インデックスを削除し、PK_tblQueuedEvent をクラスタ化インデックスにするように勧めました。
このようにして、このデッドロックを解決しました。

http://support.microsoft.com/kb/832524/ja

私のケースでも、クラスタ化インデックスの更新と参照がデッドロックの原因だった。
Select時に、主キーに設定されたClusterd Indexに対して共有ロック(RangeS-S)をかけてしまう。
※RangeS-Sは、検索範囲の共有ロックと、範囲末尾のキーの共有ロック。
その主キーを更新するクエリが、上記Selectの別の検索項目を更新してしまうためにデッドロックとなった。

非クラスタドインデックスを張って、対象のクラスタ化インデックスを検索しないようにした。
このあたりは、ケースバイケースの対応が必要だろう。


■デッドロックの解決方法
http://support.microsoft.com/kb/832524/ja

■DBCC TRACEON (Transact-SQL)
http://msdn2.microsoft.com/ja-jp/library/ms187329.aspx

■トレース フラグ (Transact-SQL)
http://msdn2.microsoft.com/ja-jp/library/ms188396.aspx

Microsoft SQL Server 2005 Performance Optimization and Tuning Handbook

Microsoft SQL Server 2005 Performance Optimization and Tuning Handbook