効率的なトランザクションのコーディング
トランザクションはできるだけ短くすることが重要です。トランザクションが開始されると、終了するまでの間、トランザクションの ACID プロパティ (原子性、一貫性、分離性、および持続性) を損なわないよう、多数のリソースを DBMS (データベース管理システム) で確保する必要があります。データを変更する場合、他のトランザクションによる読み取りを防ぐために変更する行に排他ロックをかけて保護する必要があり、排他ロックは、トランザクションがコミットされるかロールバックされるまでかけておく必要があります。トランザクションの分離レベルの設定によっては、トランザクションのコミットまたはロールバックまで解除できないロックが SELECT ステートメントによってかけられる場合があります。特に、ユーザー数が多いシステムの場合、同時に発生している接続どうしによるリソースのロックの競合を減らす目的で、トランザクションをできるだけ短くする必要があります。実行時間が長く、効率の悪いトランザクションでもユーザー数が少なければ問題になりにくいですが、ユーザー数が数千にも及ぶシステムでは容認できません。
コーディングのガイドライン
効率的なトランザクションをコーディングするためのガイドラインは次のとおりです。
- トランザクション中にユーザーによる入力を求めないようにします。
トランザクションを開始する前に、必要なすべての入力をユーザーが終えるようにします。トランザクション中に追加のユーザー入力が必要になった場合は、現在のトランザクションをロールバックし、ユーザーが入力を終えてからトランザクションを再度開始します。ユーザーの反応が早くても、人間の反応はコンピュータの処理速度に比べるとはるかに低速です。トランザクションが確保しているすべてのリソースが長時間確保されることにより、ブロッキングの問題が発生する場合があります。ユーザーが反応しない場合、応答が (場合によっては数分後か数時間後に) あるまでトランザクションはアクティブな状態で、重要なリソースをロックし続けます。 - データの参照中は、できるだけトランザクションを開かないようにします。
トランザクションは、事前のすべてのデータ分析が完了するまで開始しないでください。 - トランザクションはできるだけ短くします。
どのような変更が必要なのか把握した上でトランザクションを開始し、変更ステートメントを実行し、すぐにコミットまたはロールバックします。トランザクションは必要になってから開きます。 - ブロックを減らすため、読み取り専用のクエリには行のバージョン管理に基づく分離レベルの使用を検討します。詳細については、「行のバージョン管理に基づく分離レベルの使用」を参照してください。
- 低いトランザクション分離レベルを賢く利用します。
多くのアプリケーションは、READ COMMITTED トランザクション分離レベルを使用するように簡単にコーディングできます。すべてのトランザクションで SERIALIZABLE トランザクション分離レベルが必要なわけではありません。 - オプティミスティック同時実行制御オプションなど、カーソルの同時実行性が低いオプションを賢く利用します。
同時に更新が行われる確率が低いシステムの場合、めったに発生しない "ユーザーがデータを読み取った後に他のユーザーがそのデータを変更した" というエラーを処理するオーバーヘッドは、読み取る行を常にロックするオーバーヘッドに比べて小さくできます。 - トランザクション中は、アクセスするデータ量をできるだけ少なくします。
アクセスするデータ量が少なければ、ロックされる行数が減るので、トランザクション間の競合が減少します。
同時実行とリソースの問題の回避
同時実行およびリソースの問題を防ぐには、暗黙のトランザクションを注意深く管理します。暗黙のトランザクションを使用する場合、COMMIT または ROLLBACK の直後の Transact-SQL ステートメントから新しいトランザクションが自動的に開始されます。その結果、アプリケーションでデータが参照されている間や、ユーザーからの入力を要求している間にも新しいトランザクションが開くことができます。データの変更を防ぐことが必要な最後のトランザクションが完了した後、データの変更を防ぐことが必要な次のトランザクションまでは暗黙のトランザクションを無効にしてください。そうすることで、アプリケーションでデータが参照されている間やユーザーが入力している間は、SQL Server データベース エンジンが自動コミット モードになります。
スナップショット分離レベルが有効である場合、新しいトランザクションがロックをかけることはありませんが、実行時間の長いトランザクションを実行する間はそれ以前のトランザクションが tempdb から削除されません。