| エンタープライズ：ニュース | 2002/07/05 19:34:00 更新 |
SQL Serverのデットロックを防ぎ同時実行性を向上させよう
Microsoft Tech・Ed Yokohama 2002の3日目、「SQL Serverでのブロック、ロック、デッドロックの主な原因と対策」をテーマにしたセッションが行われた。
Microsoft Tech・Ed Yokohama 2002の3日目となる7月5日、「SQL Serverでのブロック、ロック、デッドロックの主な原因と対策」をテーマにしたセッションが行われた。データベース（DB）のデータ整合性を保持するための機能である「ロック」が引き起こす、パフォーマンスやシステムリソース不足の問題、引いてはシステムのカットオーバーの延期や工数増加という悲劇を回避するための解決策について、「同時実行性を確保する」というDB設計の観点から解説されている。
複数のユーザーでデータベース（DB）を利用する場合、データを更新するタイミングによってテーブルのデータの整合性が崩れることがないように、テーブルがロックされる。これにより、アプリケーションに処理の待ち時間が発生する状態、「ブロッキング」が発生してしまう。
さらに、別々の2つのアプリケーションが、商品テーブル、在庫テーブルなど、2つのテーブルを逆の順序で更新するケースなどでは、処理の継続が難しくなる「デットロック」が発生してしまうわけだ。
講演したマイクロソフトのアジアリミテッド、プロフェッショナルサポート本部のデータベース開発技術部のエスカレーションエンジニア、柴田貴子氏は、「デットロックとは、時刻表を持っているがペンは持っていない人と、ペンを持っているが時刻表は持っていない人が、それぞれ同時に、自分の持っていないものを渡すように相手に要求すること」と例えている。
ここで言えば、2人の要求をなるべく同時に処理していくことが、同時実行性の確保ということになる。
データの一貫性を保ちながら、同時実行性を確保するためにはまず、ロックによる待ち時間を短縮して、ブロッキングの発生を防ぐことが第一になる。
ブロッキングからの考慮点としては、トランザクションの期間をなるべく短くすること、操作するデータを最小限にする、ロックのタイムアウトを設定する、不必要にカーソルを利用しないなどが挙げられている。
トランザクションを短くするためには、ロックを保持するクエリーはなるべく最後に持ってくるべきという。さらに、処理対象とするデータをSQLのWhere文による条件設定によってなるべく絞り込み、ループを回してサーチ処理するトランザクション数を減らすことも大事としている。
また、クエリーにエラーが発生した場合の処理については、コミットするかロールバックするかをしっかりと設定し、コネクションをクローズするように設計するべきだという。エラーが発生したままの状態では、クエリーの処理対象となるテーブルはロックされたまま状態が保有されていまい、別のアプリケーションが同テーブルにアクセスしようとした場合にブロッキングが発生する可能性が高いからだ。
さらに柴田氏は、ブロッキングやデッドロックの問題調査についても触れた。
ブロッキングが発生した場合、CPUやディスクI/Oなどのシステムにかかる負担は低いという。クライアント側では、タイムアウトによりクエリーが処理を行えず、キャンセルが発生している。ターンアラウンドタイムは急激に低下するという。
一方、デッドロックが発生した場合、データはロールバックされているため、すぐには状況を把握できないという。発生の有無を確認する場合は1204トレースフラグを確認したり、SQL Profiler Lock:Deadlockイベントで、クエリーの内容や、関わったリソースを確認できるとしている。
デッドロック解決へのステップは、1．発生の確認、2．デッドロックの種類の特定、3．クエリーの特定、3はさらに、3-1デッドロックに関与したクエリーの特定、3-2トランザクションの開始点の特定、4．原因の特定、4-1ブロッキングの発生原因の特定、4-2アプリケーションの修正箇所の特定、となっている。
まとめとして同氏は、アプリエケーションおよびDB設計の観点で開発時から十分にロックを考慮すること、最大ユーザー数や最大トランザクション数を想定して、開発中に十分テストを行うことが必須だとコメントした。
関連記事
Tech・Ed 2002 Yokohamaレポート関連リンク
SQL Server Developer CenterPASSJ & SQL Server Developer Center ジョイントコラムSQL Server 2000 における Web アプリケーション開発、第1章データベース トランザクション処理第2章 ロックアーキテクチャの理解第3章 トランザクション分離レベルの選択とデッドロックの問題マイクロソフト[怒賀新也，ITmedia]
