SE の雑記

トランザクションの同時実行性を低下させる原因の一因としてロックの競合によるブロッキングとデッドロックの発生があります。
これらの事象は瞬間で発生するため、事前に後追いをできる仕組みを考えておかないと、どのような要因で発生したかを確認することが難しいです。

今回の投稿ではブロッキングとデッドロックの発生状況をあとから確認できるようにするための設定をまとめてみたいと思います。

■ブロッキングを確認するための設定

ブロッキングは互換性のないロックが競合を起こした場合に発生します。
ロックの互換性 (データベース エンジン)

以下のような処理を複数のセッションを使用して、トラン A → トラン B の順番で実行したとします。

更新中のレコードには排他ロック (X) がかかります。
この状態で共有ロック (S) が取得される検索をした場合、排他ロックと共有ロックには競合しますので、トラン B でトラン A により更新を行っているレコードが検索された場合、ロックによる待ちが発生します。

この状態がブロッキングとなります。
ブロッキングはデッドロックとは異なり、待っていれば現象が解決される状態ですので強制的にどちらかのトランザクションを終了させるという動作はしません。
実際のアプリケーションではクエリのタイムアウトにより処理が終了すると思います。
SSMS で実行されたクエリは通常、クエリのタイムアウトは無制限になっていますので、クエリのタイムアウトではなくトラン A のロックが解放されるまで待つ必要があります。

リアルタイムでブロッキングの状態を見るときは

• sp_who2 を実行
  
• 利用状況モニターで確認
  

等の方法があると思います。

SQL Server 2005 および SQL Server 2000 のブロッキングを監視する方法 で紹介されているストアドを作成して実行するという方法もありますね。
# [SQL Troubleshooting] 第6回:ブロッキング情報を採取する (SQL Server 2000 ～ 2008 R2) でも紹介されていますね。

これらの方法はリアルタイムでロックの状態を確認する際には有効ですが、あとからどうだったかを確認するためには不向きだと思います。
SQL Server 2005 以降では Blocked Process Report イベント クラス を使用することができます。

これは blocked process threshold サーバー構成オプション と組み合わせて使用することになるのですが、ブロッキングが特定の秒数発生した場合にイベントを発生することができるようになります。
たとえば、以下のように設定をした場合は 10 秒間ブロッキングが発生した場合にイベントを発生させることができます。

このレポートには競合が発生しているクエリが出力されますので、どのクエリ間で競合が発生したのかを確認することができます。

このイベントを SQL トレース でロギングすればあとから状態を確認することができます。
SQL トレースについては以下の記事がとてもわかりやすいと思います。
SQL トレーススクリプトの作成、実行 (SQL Server 2000)
SQL トレーススクリプトの作成、実行 (SQL Server 2005, 2008, 2008 R2)

SQL Server 2012 であれば、拡張イベントの設定が GUI で簡単にできますのでこちらで取得しまってもよいかもしれないですね。

■デッドロックを確認するための設定

次はデッドロックについてみていきたいと思います。
デッドロックはブロッキングと異なりロックモニターにより検知され片方のトランザクションが強制的に終了されます。
デッドロックの検出と終了

デッドロックが発生した場合にログを出力したい場合にはトレースフラグを設定する必要があります。
SQL Server 技術情報 – デッドロックの解決方法
デッドロック調査用の情報採取

SQL Server 2000 では、1204, SQL Server 2005 以降は追加で 1222  を設定するのが一般的でしょうか

トレースフラグを設定していない場合、プロファイラで Deadlock Graph イベント クラス を取得することでリアルタイムで確認をすることができます。

トレースフラグを設定することで、デッドロック発生時に SQL Server の ERRORLOG に情報が出力されます。

SQL Server 2012 の場合は、デフォルトで設定されている system_health セッション の拡張イベントでデッドロックが取得されるように設定がされています。
# 独自に xml_deadlock_report を取得する拡張イベントを作成することも可能です。

これらの設定は起動中でも柔軟に変更することができますので、必要に応じて特定の期間のみ有効にしておくとう設定することが可能です。
# トレースフラグに関しては起動時のパラメーターではなく DBCC TRACEON で設定した場合、再起動されると設定がクリアされてしまいますが。

問題発生時にリアルタイムで見れればよいですが、いつ発生するかわからないものに関してはログに出力するための方法を覚えておくとよいかと。