デッドロックの最小化

  • [アーティクル]

デッドロックを完全に回避することはできませんが、コーディング上の一定の規則に従うことにより、デッドロックが発生する可能性を最小限に抑えることができます。デッドロックの発生数を抑えると、以下の理由から、トランザクションのスループットが向上し、システムのオーバーヘッドが減少します。

  • ロールバック (トランザクションが実行したすべての処理の取り消し) の対象となるトランザクションの数が減少します。
  • デッドロック後にロールバックされたトランザクションをアプリケーションが再実行する場合、対象となるトランザクションの数が減少します。

デッドロックを最小限に抑えるには、次の手順を実行します。

  • 同じ順序でオブジェクトにアクセスします。
  • トランザクション内でのユーザーとのやり取りを避けます。
  • トランザクションを短くして 1 つのバッチ内に収めます。
  • 低い分離レベルを使用します。
  • 行のバージョン管理に基づく分離レベルを使用します。
    • READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定して READ COMMITTED トランザクションを有効にして、行のバージョン管理を使用できるようにします。
    • スナップショット分離を使用します。
  • バインドされた接続を使用します。

同じ順序でのオブジェクトへのアクセス

すべての同時実行トランザクションが同じ順序でオブジェクトにアクセスすると、デッドロックの発生する可能性は低くなります。たとえば、2 つの同時実行トランザクションが Supplier テーブルでロックを取得してから、Part テーブルでロックを取得する場合、一方のトランザクションは、もう一方のトランザクションが完了するまで Supplier テーブルでブロックされます。1 番目のトランザクションがコミットまたはロールバックされた後に 2 番目のトランザクションが続行されるので、デッドロックは発生しません。すべてのデータ変更にストアド プロシージャを使用すると、オブジェクトへのアクセス順序を統一できます。

デッドロック回避の図

トランザクション内でのユーザーとのやり取りの回避

ユーザーとのやり取りを含むトランザクションを避けます。ユーザーの介入なしにバッチを実行すれば、ユーザーが手動でクエリに応答する場合、つまりアプリケーションによるパラメータ入力要求に手動で応答する場合などよりはるかに高速に処理できます。たとえば、トランザクションがユーザーの入力を待機しているときに、ユーザーが昼食に出ていたり、週末で家に帰っていると、トランザクションの完了は大幅に延期されます。トランザクションが保持するロックを解除するにはトランザクションのコミットまたはロールバックが必要なので、このような状況ではシステムのスループットが低下してしまいます。デッドロックが発生しないとしても、トランザクションが完了するまで、同じリソースにアクセスしている他のトランザクションはブロックされます。

1 つのバッチ内でのトランザクションの短縮

デッドロックは主に、同じデータベースで長時間動作するトランザクションがいくつか同時に実行されている場合に発生します。トランザクションが長くなれば、排他ロックまたは更新ロックが長時間になり、他の処理をブロックしてしまうので、デッドロックが発生する可能性が高くなります。

トランザクションを 1 つのバッチ内に収めると、トランザクション実行時のネットワーク経由のやり取りを最小限に抑えられ、トランザクションの完了やロックの解除が遅延する可能性を低くすることができます。

低い分離レベルの使用

低い分離レベルでトランザクションが実行可能かどうかを調べます。READ COMMITTED を実装すると、トランザクションは、別のトランザクションが以前に読み取った (変更されていない) データを読み取ることができるので、最初のトランザクションが完了するまで待機する必要がありません。READ COMMITTED などの低い分離レベルを使用すると、SERIALIZABLE などの高い分離レベルの場合よりも共有ロックの保持時間が短くなります。これにより、ロックの競合が減少します。

行のバージョン管理に基づく分離レベルの使用

READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合、READ COMMITTED 分離レベルで実行されているトランザクションでは、読み取り操作中に、共有ロックではなく行のバージョン管理を使用します。

ms191242.note(ja-jp,SQL.90).gifメモ :
一部のアプリケーションは、READ COMMITTED 分離レベルのロックおよびブロックの動作に依存します。このようなアプリケーションについては、このオプションを使用する前に多少の変更が必要になります。

スナップショット分離レベルでも行のバージョン管理を使用します。行のバージョン管理では、読み取り操作中に共有ロックを使用しません。スナップショット分離レベルでトランザクションを実行する前に、ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定する必要があります。

これらの分離レベルを実装すると、読み取り操作と書き込み操作の間に発生する可能性があるデッドロックを最小限に抑えることができます。

バインドされた接続の使用

バインドされた接続を使用すると、同じアプリケーションによって開かれた複数の接続が相互に協調動作できます。最初の接続が取得したロックと同じように次の接続が取得したロックも保持されます。また、その逆の場合も同様に保持されます。したがって、相互にブロックすることはありません。

参照

概念

デッドロック
デッドロックの検出と終了
デッドロックの処理
行のバージョン管理に基づく分離レベルの使用
バインドされたセッションの使用

ヘルプおよび情報

SQL Server 2005 の参考資料の入手