SQL Server 2008 R2: ロックを解除する
SQL Server 2008 R2 では、ロックの問題を解決するのが困難な場合と驚くほど簡単に解決できる場合があります。
William Stanek
データベースのトラブルシューティング (特にロックとブロックに関するもの) は、気弱な人には向きません。放置している重要な問題は、認識と異なっていることがあります。そのような誤認識が問題のトラブルシューティングを困難でストレスのたまる作業にしています。探し求めている答えが、目と鼻の先にある場合もあります。これは SQL Server 2008 R2 について言えることで、SQL Server 2008 R2 には、最新のサービス パックや更新プログラムを適用するだけで解決できるロックに関する既知の問題があります。
実行する操作によって、データベースではロックやブロックの問題が発生することがあります。ロックとブロックは回避できない問題です。この問題は、どのようなリレーショナル データベース管理システム (RDBMS) でも発生するもので、SQL Server 2008 R2 も例外ではありません。
ブロックは、1 つのログオンしているデータベース セッション (サーバー プロセス ID (SPID)) が特定のリソースをロックしているときに別の SPID が同じリソースに対して競合するロックを取得しようとすると発生します。通常、リソース ロックが長期に渡ることはありません。最初のユーザーがロックを解除すると、別の SPID でリソースをロックして、クエリの処理を続行できます。
ロックの詳細
SQL Server 2008 R2 では、ロックで粒度の細かいアプローチを使用しており、影響するレコードの数と他の実行中の処理に応じて適切なロックのレベルを選択しています。
既定では、ロックの範囲は、パフォーマンスを向上するために、必要に応じて "行レベルとページ レベルのロック" から "テーブル レベルのロック" に拡大されます。一般的にロックのエスカレーションは良いことですが、問題の原因となる場合もあります。たとえば、特定のサービス セット識別子 (SSID) がテーブル全体をロックしていて、他の SSID がテーブルに対して処理を行えない場合です。
行レベルのロックとページ レベルのロックのオプションは構成できます。既定では、ロックのオプションは、インデックスで有効になっています。SQL Server 2008 R2 では、テーブル分割もサポートされています。パーティション分割によりデータは個別のオブジェクトに分割されるので、テーブルを分割すると全体的なパフォーマンスが向上します。
クエリの種類によって、ロックの期間は異なります。クエリが複数のトランザクションにまたがって実行されており、ロック ヒントが使用されていない場合、SELECT ステートメントのロックは、リソースを読み取っている間だけ保持され、クエリを実行している間ずっと保持されるわけではありません。一方、INSERT、UPDATE、および DELETE ステートメントのロックは、クエリの実行中ずっと保持されます。この仕様により、データの整合性が確保され、SQL Server では必要に応じてクエリをロール バックできます。
クエリが 1 つのトランザクション内で実行される場合、ロックの期間は次の 3 つの要素によって決まります。
- クエリの種類
- トランザクションの分離レベル
- ロック ヒントがあるかどうか
ロックとブロックは RDBMS の特性です。ただし、特定の SPID が長期に渡ってリソースをロックすると、パフォーマンスが低下することがあります。また、SPID がロックしているリソースのロックを解除できなかった場合にもパフォーマンスに影響します。
1 つ目のブロックの問題は、いずれ SPID でロックが解除されるので、時間が経てば自然と解決します。ですが、長期に渡るロックによって生じるパフォーマンスの低下は、依然として問題になります。2 つ目のブロックの問題は、重大なパフォーマンスの低下を招きます。ですが、SQL Server でロックとブロックの問題を監視していれば、比較的簡単に見つけられます。
ブロック バスター
SQL Server 利用状況モニターは、ロックとブロックの問題を特定するのに役立ちます。表示されるプロセスの待機時間、待機の種類、待機リソース、ブロック元の値を注意深く観察します。
利用状況モニターに表示されるプロセス情報の大半は、次の動的管理ビューから取得されています。
- sys.dm_os_tasks
- sys.dm_os_waiting_tasks
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.dm_resource_governor_workload_group
sys.dm_tran_locks ビューでは、ロックとブロックの明確な状態を確認できます。このビューでは、許可されているアクティブなロック要求または許可されるのを待機しているロック要求に関する情報が返されます。sys.dm_exec_connections、sys.dm_exec_sessions、および sys.dm_exec_requests の各ビューでは、アクティブな接続、セッション、および要求に関する情報が返されます。
ここでは sys.dm_exec_requests ビューに集中します (詳細については、この MSDN ライブラリの記事を参照してください)。状態が "休止中" の要求は、実行が完了して、アプリケーションからのコマンドを待機しています。状態が "実行中" または "実行可能" の要求は、現在、クエリを処理しています。状態が "中断状態" の要求は、ロックやラッチなどのイベントを待機しています。
名前から推測できるように、wait_type 列には待機の種類が表示されます。値が 0 以上の場合、当該 SPID は現在待機している状態です。詳細については、wait_time 列と wait_resource 列を参照してください。要求がブロックされている場合、wait_time 列には待機時間 (ミリ秒単位) が表示され、wait_resource 列には、SPID が待機しているリソースが表示されます。blocking_session_id 列には、要求をブロックしているセッションの ID またはブロックしているリソースの所有者に関する情報が負の値で表示されることに注意してください。
適切に記述されていないフロントエンド アプリケーションが原因で、さまざまなブロックの問題が発生することがあります。アプリケーションで入れ子になったトランザクションのレベルを適切に管理できない場合、要求の wait_type 列の値が 0、状態が "休止中"、open_transaction_count 列の値が 0 以上というブロックの状態が生じます。
このアプリケーションでは、クエリのタイムアウトが発生したか、必要な数の ROLLBACK ステートメントや COMMIT ステートメントを発行せずに操作をキャンセルしたことが考えられます。この場合、ロックはアクティブな状態が維持されるので、他の SPID によるロックがブロックされます。SQL Server で、この動作を修正することはありません。ですから、アプリケーションは、入れ子になったトランザクションを適切に処理するように設計されている必要があります。
アプリケーションですべての結果行を完全にフェッチできない場合、要求の wait_type 列の値が 0、状態が "実行可能"、open_transaction_count 列の値が 0 以上というブロックの状態が生じます。このような状況が発生した場合、アプリケーションでは、すべての結果行をフェッチせずに、テーブルのロックを解除しなかったため、他の SPID によるロックがブロックされていることが考えられます。可能であれば、すべての結果をフェッチするようにアプリケーションを構成する必要があります。
問題がバックエンド アプリケーションで発生している場合は、SQL Server でデータベースに適切なインデックスが作成されるようにすることでブロックの問題を解決できます。また、必要に応じてクエリをチューニングすることをお勧めします。インデックスを作成すると、クエリでアクセスするレコードの数を削減できます。これは、データベース エンジンで実行する必要がある参照の回数を減らすことで実現しています。インデックスを作成する必要があるテーブルと列を特定するには、次の動的管理ビューを使用します。
- sys.dm_db_missing_index_groups
- sys.dm_db_missing_index_details
- sys.dm_db_missing_index_group_stats
インデックスを作成することでデータの変更に時間がかかる場合があることに注意してください (詳細については、「オンライン インデックス操作の動作原理」を参照してください)。そのため、インデックスを作成するときには、作成したインデックスが sys.dm_db_ index_usage_stats ビューで使用されるようにする必要があります。
インデックスを作成した結果、長期に渡ってテーブルがロックされるようになるとは限りません。オンライン インデックスでは、インデックス操作のメイン フェーズの間ソース テーブルで保持されるのは、インテント共有 (IS) ロックのみです。そのためクエリと更新の処理は続行できます。通常、共有のロックは、インデックス操作の最初の段階で短時間だけソース オブジェクトで保持されます (インデックス操作の最後の段階で保持されることもあります)。
実行時間が長い以外に問題がないクエリが原因でブロックが発生した場合は、クエリを最適化することで、問題を解決できることがあります。クエリを最適化する最も効果的な方法は、1 つのトランザクションでロックされるリソースの量を最小限に抑えることです。そのためには、トランザクションの規模をなるべく小さくし、重要でない処理は取り除いて、本当に必要な処理のみを実行するようにします。1 つのトランザクションを影響する行数が少ない複数のクエリに分割することも可能です。ただし、この結果、必要な結果を得られなくなる場合は、トランザクションを分割することを避けます。適切なトランザクションの分離レベルを使用するようにトランザクションを設計し、ロック ヒントを使用して適切な結果を得られて、かつロックの問題を軽減できるかどうかを検討します。
デッドロックに対処する
複数の SPID が別々のオブジェクトをロックしているときに、他の SPID がロックしているオブジェクトのロックを取得しようとすると、デッドロックという特殊な種類のブロックの問題が発生します。各 SPID では、他の SPID でロックが解除されるのを待機しますが、そのような状況が発生することはありません。デッドロックを最小限に抑えることは可能ですが、完全に回避することはできません。さいわい、SQL Server のロック マネージャーでは、デッドロックの確認が自動的に行われるようになっています。ロック マネージャーでデッドロックが検出されると、次の 3 つの処理が行われます。
- いずれかの SPID を強制終了する
- ロック要求を行ったクライアントに送信される 1205 エラー メッセージを生成する
- SPID を強制終了してロックされたリソースを解放し、他の SPID で処理を続行できるようにする
利用状況モニターと sys.dm_tran_locks ビューも、デッドロックを特定するのに役立ちます。利用状況モニターでは、待機時間 (経過時間がミリ秒単位で表示されます)、待機の種類 (処理が待機中かどうかを示します)、および待機リソース (待機リソースがある場合、プロセスが待機しているリソースが表示されます) を確認します。
フロントエンド アプリケーションは、デッドロックに対応できるように設計されている必要があります。適切に設計されたフロントエンド アプリケーションでは、1205 エラーをトラップして、SQL Server に再接続して、トランザクションが再実行されます。また、適切に設計されたフロントエンド アプリケーションでは、あるリソースの要求がクライアント レベルで行われ、もう一方の要求がサーバー レベルで行われている場合に発生する分散デッドロックも監視されます。
アプリケーションでは、クエリ タイムアウトやバインドされた接続を使用して分散デッドロックを解決できます。この場合、クエリのタイムアウトが発生すると、分散デッドロックは解消されます。接続が相互にブロックすることがないように、クライアントは単一のトランザクション領域にバインドする複数の接続を確立できます。
簡単に説明すると、こんな感じです。ロックのしくみとブロックの問題を特定して解決する方法の説明は以上です。多くの場合、SQL Server では、デッドロックを含む、多くのブロックに関する問題を解決できます。また、ブロックに関する問題は、適切な設計により軽減できます。まずは適切に設計したクエリと定評のあるフロントエンド アプリケーションを使用することから始めてみてください。それから、微調整を行いながらバックエンド アプリケーションにも同じように対応してみてください。
.jpg)
William R. Stanek は、先駆者的なテクノロジの専門家で、優秀なトレーナーや 100 冊以上もの優れた書籍の著者としても活躍しています。twitter.com/WilliamStanek、英語 で Stanek をフォローしてみてください。