トランザクションのロックおよび行のバージョン管理ガイド

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric の SQL データベース

どのようなデータベースであっても、トランザクションを正しく管理しないと、ユーザー数が多いシステムでは競合やパフォーマンスの問題を招くことがあります。 データにアクセスするユーザー数が多いほど、トランザクションが効率的に行われるアプリケーションを用意することが重要になります。 このガイドでは、各トランザクションの整合性の確保を目的としてデータベース エンジンで使用されるロックおよび行のバージョン管理のメカニズムと、アプリケーションで効率的にトランザクションを制御する方法について説明します。

トランザクションの基礎

トランザクションは、論理的な 1 つの作業単位として実行される一連の操作です。 論理的な 1 つの作業単位がトランザクションとして有効であるには、ACID と呼ばれる 4 つのプロパティ (原子性、一貫性、分離性、および持続性) を備えている必要があります。

原子性
トランザクションは、分離できない 1 つの作業単位であり、そのデータ変更がすべて実行されるか、まったく実行されないかのどちらかである必要があります。

一貫性
トランザクションの完了時に、すべてのデータが一貫した状態になければなりません。 リレーショナル データベースの場合、すべてのデータの整合性を維持するため、トランザクションの変更に対してすべてのルールが適用される必要があります。 B ツリー インデックスや二重リンク リストなどのすべての内部データ構造は、トランザクションの終了時に正しくなければなりません。

分離性
同時実行トランザクションによって行われる変更は、他の同時実行トランザクションによって行われる変更と相互に独立している必要があります。 トランザクションは、他の同時実行トランザクションが変更する前の状態のデータを認識するか、2 番目のトランザクションが完了した後のデータを認識するかのどちらかであり、中間の状態は認識しません。 これをシリアル化可能性と呼んでいます。最初のデータを再度読み込み、一連のトランザクションを実行しても、元のトランザクションを実行したときと同じ状態で終了できるからです。

永続性
完全持続性トランザクションの完了後、その結果がシステム内で持続されます。 システム障害が発生しても、変更結果は持続されます。 SQL Server 2014 (12.x) 以降では、遅延永続性トランザクションが有効になります。 遅延持続性トランザクションのコミットは、トランザクション ログ レコードがディスクに対して永続化される前に行われます。 トランザクションの遅延持続性について詳しくは、「トランザクションの永続性をコントロールする」を参照してください。

アプリケーションは、データの論理的な一貫性が確保されるように、トランザクションを開始および終了しなければなりません。 アプリケーションは、組織のビジネス ルールに合わせて、データが一貫した状態に保たれるようにデータ修正の順序を定義する必要があります。 アプリケーションは、これらの変更を 1 つのトランザクションで実行するため、データベース エンジンは、トランザクションの整合性を強制できます。

データベース エンジンのインスタンスなど、企業のデータベース システムは、各トランザクションの整合性を確保するメカニズムを用意する必要があります。 データベース エンジンは、次の機能を提供します。

• トランザクションの分離性を保持するロック機能。

• トランザクションの永続性を確保するためのログ機能。 完全持続性トランザクションでは、ログ レコードがディスクに書き込まれた後で、トランザクションがコミットされます。 したがって、サーバー ハードウェア、オペレーティング システム、またはデータベース エンジンのインスタンス自体で障害が発生した場合でも、インスタンスは再起動時にトランザクション ログを使用して、未完了のトランザクションをシステム障害が発生した時点まで自動的にロールバックします。 遅延持続性トランザクションのコミットは、トランザクション ログ レコードがディスクに書き込まれる前に行われます。 ログ レコードがディスクに書き込まれる前にシステム障害が発生した場合、このようなトランザクションは失われる可能性があります。 トランザクションの遅延持続性について詳しくは、「トランザクションの永続性をコントロールする」を参照してください。

• トランザクションの原子性と一貫性を設定するトランザクション管理機能。 トランザクションは開始したら、正常に完了する (コミットされる) 必要があります。または、データベース エンジンは、トランザクションが開始したら、トランザクションが修正したすべてのデータを取り消します。 データを変更前の状態に戻す操作であるため、これをトランザクションのロールバックといいます。

トランザクションを制御する

アプリケーションは、主にトランザクションの開始タイミングと終了タイミングを指定してトランザクションを制御します。 これは、Transact-SQL ステートメントまたはデータベース アプリケーション プログラミング インターフェイス (API) 関数を使用して指定できます。 また、トランザクションが完了せずに終了した場合、その原因となったエラーがシステムによって正しく処理される必要があります。 詳細については、「トランザクション」、「ODBC でトランザクションを実行する」、「SQL Server Native Client のトランザクション」を参照してください。

既定では、トランザクションは接続レベルで管理されます。 接続時にトランザクションが開始すると、その接続で実行されるすべての Transact-SQL ステートメントが、トランザクションが終了するまでそのトランザクションの一部になります。 ただし、複数のアクティブな結果セット (MARS) セッションでは、Transact-SQL の明示的なトランザクションまたは暗黙的なトランザクションは、バッチ レベルで管理されるバッチスコープ トランザクションになります。 バッチ完了時に、バッチスコープ トランザクションがコミットまたはロールバックされない場合、データベース エンジンが自動でトランザクションをロールバックします。 詳しくは、「複数のアクティブな結果セット (MARS)」をご覧ください。

トランザクションを開始する

API 関数と Transact-SQL ステートメントを使用すると、明示的、オートコミットまたは暗黙的トランザクションとしてトランザクションを開始できます。

明示的なトランザクション

明示的なトランザクションとは、API 経由または、Transact-SQL BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION または ROLLBACK WORK Transact-SQL ステートメント経由でトランザクションの開始と終了の両方を明示的に定義するトランザクションです。 トランザクションが終了すると、接続は明示的なトランザクションが開始される前のトランザクション モード (暗黙的なトランザクション モードまたはオートコミット モード) に戻ります。

明示的なトランザクションでは、次のステートメントを除くすべての Transact-SQL ステートメントを使用できます。

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• フルテキスト システム ストアド プロシージャ
• 明示的なトランザクションまたは暗黙的なトランザクション内の master データベースを修正するデータベース オプションまたはシステム プロシージャを設定する sp_dboption。

オートコミット トランザクション

オートコミット モードは、データベース エンジンの既定トランザクション管理モードです。 すべての Transact-SQL ステートメントは完了時にコミットされるか、ロールバックされます。 ステートメントが正常に完了した場合はコミットされ、エラーが検出された場合はロールバックされます。 この既定のモードが明示的トランザクションまたは暗黙的なトランザクションでオーバーライドされていなければ、データベース エンジンのインスタンスへの接続はオートコミット モードで動作します。 オートコミット モードは、SqlClient、ADO、OLE DB および ODBC でも規定モードです。

暗黙的なトランザクション

接続が暗黙のトランザクション モードで動作している場合、データベース エンジンのインスタンスでは、現在のトランザクションがコミットされるかロールバックされた後に新しいトランザクションが自動的に開始されます。 トランザクションの開始を指定する必要はありません。各トランザクションをコミットするかロールバックするだけです。 暗黙のトランザクション モードの場合、トランザクションが連鎖して生成されます。 暗黙的なトランザクション モードは、API 関数または Transact-SQL SET IMPLICIT_TRANSACTIONS ON ステートメントのいずれかを使用して設定します。 このモードは、オートコミット オフとも呼ばれます。「setAutoCommit メソッド (SQLServerConnection)」を参照してください。

接続に対して暗黙のトランザクション モードを有効にした後、データベース エンジンのインスタンスで次のステートメントのいずれかが最初に実行される際に、トランザクションが自動的に開始されます。

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

バッチスコープのトランザクション

複数のアクティブな結果セット (MARS) にのみ該当します。MARS セッションで開始された Transact-SQL の明示的または暗黙的なトランザクションは、バッチスコープのトランザクションになります。 バッチ完了時にコミットされていないまたはロールバックされていないバッチスコープのトランザクションは、データベース エンジンによって自動でロールバックされます。

分散トランザクション

分散トランザクションは、リソース マネージャーと呼ばれる複数のサーバーに展開されます。 トランザクションの管理はリソース マネージャー間で、トランザクション マネージャーと呼ばれるサーバー コンポーネントが調整する必要があります。 データベース エンジンの各インスタンスは、Microsoft Distributed Transaction Coordinator (MS DTC) や分散トランザクション処理の Open Group XA 仕様をサポートするその他のトランザクション マネージャーなどのトランザクション マネージャーが調整する分散トランザクションのリソース マネージャーとして操作できます。 詳細については、MS DTC のドキュメントを参照してください。

2 つ以上のデータベースに広がるデータベース エンジンの 1 つのインスタンス内のトランザクションは、分散トランザクションです。 ただし、SQL Server インスタンスは分散トランザクションを内部で処理するため、ユーザーにはローカル トランザクションとして動作しているように見えます。

アプリケーションでは、分散トランザクションは、ローカル トランザクションと同じように管理されます。 トランザクションの終了時に、アプリケーションがトランザクションのコミットまたはロールバックを要求します。 ただし、トランザクション マネージャーが分散コミットを別の方法で管理することによって、ネットワーク障害により一部のリソース マネージャーがトランザクションを正常にコミットし、その一方で他のリソース マネージャーがトランザクションをロールバックするという危険性を最小限に抑える必要があります。 これは、コミット処理を準備フェーズとコミット フェーズの 2 フェーズで管理することによって実現されます。これを 2 フェーズ コミットと呼びます。

• 準備フェーズ

  トランザクション マネージャーはコミット要求を受け取ると、そのトランザクションに関連するすべてのリソース マネージャーに準備コマンドを送ります。 その後、各リソース マネージャーは、トランザクションを永続的にするために必要なすべてを実行し、トランザクションのすべてのトランザクション ログ バッファーをディスクにフラッシュします。 リソース マネージャーの準備フェーズが完了すると、トランザクション マネージャーに対し、そのフェーズの成否が通知されます。 SQL Server 2014 (12.x) では、トランザクションの遅延永続性が導入されました。 遅延永続性トランザクションは、各リソース マネージャーのトランザクション ログ バッファーがディスクにフラッシュされる前にコミットされます。 トランザクションの遅延持続性について詳しくは、「トランザクションの永続性をコントロールする」を参照してください。

• コミット フェーズ

  トランザクション マネージャーは、すべてのリソース マネージャーから準備の正常完了通知を受け取ると、リソース マネージャーにコミット コマンドを送ります。 これにより、リソース マネージャーはコミットを完了できます。 すべてのリソース マネージャーがコミットの正常完了を報告した場合、トランザクション マネージャーは、アプリケーションに成功通知を送ります。 準備できなかったことを報告するリソース マネージャーがあった場合、トランザクション マネージャーはすべてのリソース マネージャーにロールバック コマンドを送り、アプリケーションにコミットできなかったことを報告します。

  データベース エンジン アプリケーションは、Transact-SQL またはデータベース API 経由で分散トランザクションを管理できます。 詳細については、「BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)」を参照してください。

トランザクションを終了する

トランザクションは、COMMIT ステートメント、ROLLBACK ステートメント、または対応する API 関数を使用して終了できます。

• Commit

  トランザクションが正常に完了した場合、そのトランザクションをコミットします。 COMMIT ステートメントは、トランザクションのすべての修正が、データベースの永続的な一部となることを保証します。 コミットは、トランザクションが使用するロックなどのリソースも解放します。

• ロールバック

  トランザクションでエラーが発生した場合、またはユーザーが、トランザクションをキャンセルした場合、トランザクションをロールバックします。 ROLLBACK ステートメントは、データをトランザクションの開始時点の状態に戻すことにより、トランザクションで行われたすべての修正を元に戻します。 ロールバックによって、トランザクションが保持していたリソースも解放されます。

トランザクション処理中のエラー

エラーによりトランザクションを正常に完了できない場合、データベース エンジンによってトランザクションが自動的にロールバックされ、そのトランザクションで保持されていたすべてのリソースが解放されます。 データベース エンジンのインスタンスへのクライアント ネットワーク接続が切断された場合、ネットワークからインスタンスにこの切断が通知されると、その接続に対する未処理のトランザクションがすべてロールバックされます。 クライアント アプリケーションが失敗した場合、またはクライアントのコンピューターが停止するか、再起動された場合も、接続が切断されます。ネットワークが切断を通知すると、データベース エンジンのインスタンスは、未処理のトランザクションをすべてロールバックします。 クライアントがデータベース エンジンから切断されると、未処理のトランザクションがすべてロールバックされます。

バッチでランタイム ステートメント エラー (制約違反など) が発生した場合、データベース エンジンの既定の動作として、エラーの原因となったステートメントだけがロールバックされます。 この動作を変更するには、SET XACT_ABORT ON ステートメントを使用します。 SET XACT_ABORT ON の実行後、実行時間ステートメント エラーにより、現在のトランザクションが自動的にロールバックされます。 構文エラーなどのコンパイル エラーは、SET XACT_ABORT の設定の影響を受けません。 詳細については、「SET XACT_ABORT (Transact-SQL)」を参照してください。

エラーが発生した場合は、適切なアイコン (COMMIT または ROLLBACK) をアプリケーション コードに含める必要があります。 トランザクションで発生するエラーなど、エラーを処理するための効果的なツールとして Transact-SQL TRY...CATCH 構造が挙げられます。 トランザクションを含む例の詳細については、「TRY...CATCH (Transact-SQL)」を参照してください。 SQL Server 2012 (11.x) 以降では、THROW ステートメントを使用して例外を発生させ、TRY...CATCH 構造の CATCH ブロックに実行を渡すことができます。 詳細については、「THROW (Transact-SQL)」を参照してください。

オートコミット モードでのコンパイル エラーと実行時エラー

オートコミット モードでは、データベース エンジンのインスタンスが 1 つの SQL ステートメントだけでなく、バッチ全体をロールバックしたように見える場合があります。 これは、検出されたエラーが実行時エラーではなくコンパイル エラーの場合に発生します。 コンパイル エラーが起こると、データベース エンジンが実行プランを構築できないため、バッチ内のどの処理も実行できません。 エラーを生成したステートメントよりも前にあるすべてのステートメントがロールバックされたように見えますが、エラーによりバッチ内のどのステートメントも実行されませんでした。 次の例では、3 番目のバッチ内のどの INSERT ステートメントも、コンパイル エラーにより実行されません。 最初の 2 つの INSERT ステートメントが実行されないので、ロールバックされたように見えます。

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

次の例では、3 番目の INSERT ステートメントによって、主キーが重複するという実行時エラーが生成されます。 最初の 2 つの INSERT ステートメントは正常に完了しコミットされるので、実行時エラーの生成後も有効です。

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

データベース エンジンでは、名前の遅延解決を採用しています。つまり、オブジェクト名はコンパイル時ではなく、実行時まで解決されません。 次の例では、最初の 2 つの INSERT ステートメントは正常に実行されてコミットされ、3 番目の TestBatch ステートメントで存在しないテーブルが参照され、実行時エラーになった後も、最初の 2 行は INSERT テーブル内に残ります。

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

ロックおよび行のバージョン管理の基礎

データベース エンジンでは、複数のユーザーが同時にアクセスしたときにトランザクションの整合性を保証し、データベースの一貫性を保つため、次のメカニズムを使用します。

• ロック

  トランザクションの要求があるたびに、そのトランザクションが依存する行、ページ、テーブルなどの各種リソースにロックがかかります。 ロックをかけると、ロックを要求したトランザクションにとって問題になるようなリソースの変更が行われないように、他のトランザクションがブロックされます。 各トランザクションは、ロックしたリソースに対する依存関係がなくなったときにロックを解放します。

• 行のバージョン管理

  行のバージョン管理に基づく分離レベルが使用されている場合、データベース エンジンは、変更されたそれぞれの行のバージョンを維持します。 アプリケーションは、すべての読み取りをロックで保護するのではなく、トランザクションで使用する行のバージョンを使い分けて、トランザクションまたはステートメントの開始時点の状態のデータを参照できるようにすることを指定できます。 行のバージョン管理を使用すると、読み取り操作によって他のトランザクションがブロックされる可能性を大幅に低下できます。

ロックおよび行のバージョン管理を実装することで、ユーザーがコミット前のデータを読み取ったり、複数のユーザーが同時に同一のデータを変更したりする危険性を回避できます。 ロックおよび行のバージョン管理を使用しなければ、クエリを実行してもデータベース内のコミット前のデータが返されて、予期しない結果になる場合があります。

アプリケーション側でトランザクション分離レベルを選択して、トランザクションを他のトランザクションによる変更から保護するレベルを定義できます。 個別の Transact-SQL ステートメントに対してテーブルレベルのヒントを指定し、アプリケーションの要件に合わせて動作を細かく調整することもできます。

同時実行データ アクセスを管理する

ユーザーが同じリソースに同時にアクセスすることを、リソースへのアクセスを同時実行しているといいます。 同時実行データ アクセスには、他のユーザーが現在使用しているリソースを複数のユーザーが変更しようとしたときの悪影響を回避するためのメカニズムが必要です。

コンカレンシーの影響

あるユーザーがデータを変更すると、同じデータを同時に読み取ったり変更している他のユーザーに影響します。 このようなユーザーを、データに同時アクセスしているユーザーと呼びます。 データベースに同時実行制御がない場合、次の副次的影響が発生する場合があります。

• 更新データの喪失

  この問題は、複数のトランザクションが同じ行を選択し、その行の元の値を更新すると発生します。 トランザクションは互いに相手を認識しません。 更新によって他のトランザクションによる更新データが上書きされれば、そのデータが失われてしまいます。

  たとえば、2 人の編集者が同じ文書のコピーを作成したとします。 2 人が自分のコピーを変更し、その変更内容を保存して、元の文書を上書きしたとします。 変更したコピーを最後に保存した編集者により、他の編集者が行った変更が上書きされます。 1 人の編集者が終了してトランザクションをコミットするまで、他の編集者がファイルにアクセスできないようにすれば、この問題を防ぐことができます。

• 非コミット依存 (ダーティ リード)

  非コミット依存は、トランザクションによって更新されている行を別のトランザクションが読み取る際に発生します。 2 番目のトランザクションでは、まだコミットされていないデータを読み取ります。このデータは、行を更新するトランザクションによって変更されることがあります。

  たとえば、ある編集者が電子文書を変更しているとします。 その間、他の編集者はそれまでの変更が反映された文書を受け取って、読者に配布します。 その後、最初の編集者がそれまでの変更を誤りと判断して取り消し、保存したとします。 配布されている文書には取り消した編集内容が含まれているため、既に存在しない文書として扱う必要があります。 最初の編集者が最後に変更を保存してトランザクションをコミットするまで、変更された文書をだれも読み取ることができないようにすれば、この問題を防ぐことができます。

• 不一致分析 (反復不能読み取り)

  不一致分析は、別のトランザクションが同じ行に数回アクセスし、それぞれの場合で異なったデータを読み取るときに発生します。 不一致分析は、あるトランザクションがデータを変更している間に別のトランザクションがそのデータを読み取るという点で非コミット依存と似ています。 ただし不一致分析の場合、別のトランザクションが読み取るデータは変更を行ったトランザクションによってコミットされています。 また、同じ行が複数回読み取られ、そのたびにトランザクションによって情報が変更されます。そのため、反復不能読み取りと呼ばれます。

  たとえば、編集者が同じ文書を 2 回読んだ場合に、1 回目と 2 回目の間に執筆者が文書を変更したとします。 編集者が 2 回目に文書を読んだときには、内容が大幅に変更されていました。 最初に読んだ内容と同じものは再現されません。 編集者が最終的に読み終わるまで執筆者が文書を変更できないようにすると、この問題を防ぐことができます。

• ファントム読み取り

  ファントム読み取りとは、2 つの同じクエリが実行されたときに 2 番目のクエリによって返された行一式が異なる場合に発生する状況です。 次の例は、これがどのように実行されるかを示しています。 2 つのトランザクションが同時に実行されているとします。 最初のトランザクションにある 2 つの SELECT ステートメントは、異なる結果を返す可能性があります。これは、これら 2 つのステートメントで使用されるデータが 2 番目のトランザクションの INSERT ステートメントで変更されるためです。

  --Transaction 1
  BEGIN TRAN;
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 AND ID < 10;
  
  --The INSERT statement from the second transaction occurs here.
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee (Id, Name)
  VALUES(6 ,'New');
  
  COMMIT;
  

• 行の更新による読み取りの欠落および重複

  • 更新された行の欠落または更新された行の複数回表示

    READ UNCOMMITTED レベル (または NOLOCK テーブルのヒントを使用したステートメント) で実行中のトランザクションは、現在のトランザクションによって読み取られたデータが他のトランザクションによって変更されるのを防ぐために、共有ロックを発行しません。 READ COMMITTED レベルで実行されるトランザクションでは共有ロックが取得されますが、行ロックまたはページ ロックは行が読み取られた時点で解放されます。 どちらの場合も、インデックスをスキャンしているときに、読み取り中の行のインデックス キー列が他のユーザーによって変更された場合は、このキーの変更によって行がスキャン前の位置に移動すると、その行は再び出現する可能性があります。 同様に、キーの変更によって、既に読み取ったインデックスの位置に行が移動すると、その行は一切読み取られない場合があります。 この問題を回避するには、SERIALIZABLE または HOLDLOCK のヒントや、行のバージョン管理を使用します。 詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

  • 更新の対象でなかった 1 行または複数行の欠落

    READ UNCOMMITTED を使用している場合、クエリで割り当て順序スキャン (IAM ページの使用) によって行を読み取っているときに、他のトランザクションによってページ分割が発生すると、行が欠落する可能性があります。 READ COMMITTED 分離レベルを使用する場合には、発生しません。

コンカレンシーの種類

複数のトランザクションが、同時にデータベース内のデータを変更しようとする場合、あるトランザクションによる変更が他のトランザクションの変更に悪影響を及ぼさないように、管理システムを実装する必要があります。 この制御機構をコンカレンシー制御と呼びます。

コンカレンシー制御の原理は、制御方法によって次の 2 種類に分類されます。

• 悲観的並行性制御

  ロック機構は、トランザクションが他のトランザクションに影響を与えるような形でデータを変更するのを防ぎます。 トランザクションがアクションを実行し、それが原因でロックが適用された後は、その所有者がロックを解放するまで、他のトランザクションはロックと競合するアクションを実行できません。 これは、通常、データの競合が高いシステムで使用されるため、これは悲観的な制御と呼ばれます。この場合、ロックを使用してデータを保護するコストは、コンカレンシーの競合が発生した場合にトランザクションをロールバックするコストよりも低くできます。

• 楽観的同時実行制御

  楽観的同時実行制御では、データ読み取り時、トランザクションはデータをロックしません。 ただし、トランザクションによるデータ更新時、システムは、データの読み取り前に別のトランザクションがそのデータを変更したかを確認します。 他のトランザクションがデータを更新していた場合、エラーが発生します。 通常は、エラーが報告されたトランザクションは、ロールバックされ、再実行されます。 これは、通常、データの競合が少なく、トランザクションをロールバックするコストが読み取り時にデータをロックするコストよりも低いシステムで使用されるため、楽観的と呼ばれます。

データベース エンジンは、両方の同時実行制御をサポートします。 接続のトランザクション分離レベルやカーソルのコンカレンシー オプションを選択することで、コンカレンシー制御の種類を指定できます。 これらの属性を定義するには、Transact-SQL ステートメントを使用するか、ADO、ADO.NET、OLE DB、ODBC などのデータベース API (アプリケーション プログラミング インターフェイス) のプロパティおよび属性を指定します。

データベース エンジンの 分離レベル

各トランザクションは、別のトランザクションによって行われたリソースまたはデータの変更から特定のトランザクションを独立させる際の程度を定義する分離レベルを指定します。 分離レベルは、ダーティ リードやファントム読み取りなど、コンカレンシーの副作用が許可されるのかという観点で定義されます。

トランザクション分離レベルでは次のことを制御しています。

• データの読み取り時にロックを取得するかどうか、要求されるロックの種類。
• 読み取りロックの保持期間。
• 別のトランザクションによって変更された行を参照している読み取り操作で、次のことを行うかどうか。
  • その行に対する排他ロックが解放されるまでブロックする。
  • ステートメントまたはトランザクションの開始時に存在していた行の、コミット済みのバージョンを取得する。
  • コミットされていないデータ変更を読み取る。

分離レベルが低いほど、多くのトランザクションが同時にデータにアクセスできるようになりますが、ダーティ リードや更新データの喪失などトランザクションが直面する同時実行性の影響も大きくなります。 反対に、分離レベルが高いほど、トランザクションで発生する可能性があるコンカレンシーの影響の種類は減りますが、必要なシステム リソースが増加し、あるトランザクションによって別のトランザクションがブロックされる状況も多くなります。 適切な分離レベルの選択は、アプリケーションのデータ整合性の要件と各分離レベルのオーバーヘッドとのバランスによって決まります。 最も高い分離レベルの SERIALIZABLE は、トランザクションが読み取り操作を繰り返し実行するたびに、取得されるデータが完全に同じになることを保証します。ただし、これを行うために、マルチユーザー システムにおいて他のトランザクションが影響を受ける可能性が高い レベルのロックが実行されています。 最も低い分離レベルの場合、READ UNCOMMITTED は、他のトランザクションが修正したが、まだコミットしていないデータを取得する場合があります。 同時実行性の副次的影響はすべて、READ UNCOMMITTED で発生しますが、読み取りロックやバージョン管理は行われないため、オーバーヘッドが最小限に抑えられます。

データベース エンジンの分離レベル

ISO 標準では、次に示す分離レベルが定義されています。それらのすべてがデータベース エンジンでサポートされます。

データベース エンジンでは、行のバージョン管理を使用する 2 つの追加トランザクション分離レベルがサポートされます。 1 つは、READ COMMITTED 分離レベルの実装向けで、もう 1 つは、SNAPSHOT トランザクション分離レベル向けです。

次の表に、各分離レベルで許容されているコンカレンシーの副作用を示します。

各トランザクション分離レベルが制御する特定のロックの種類または行のバージョン管理の詳細については、「トランザクション分離レベル (Transact-SQL) を設定する」を参照してください。

トランザクション分離レベルを設定するには、Transact-SQL または データベース API を使用します。

Transact-SQL
Transact-SQL スクリプトでは、SET TRANSACTION ISOLATION LEVEL ステートメントが使用されます。

ADO.NET
ADO アプリケーションは、Connection オブジェクトの IsolationLevel プロパティを、adXactReadUncommitted、adXactReadCommitted、adXactRepeatableRead、または adXactReadSerializable に設定します。

ADO.NET
System.Data.SqlClient マネージド名前空間を使用する ADO.NET アプリケーションは、SqlConnection.BeginTransaction メソッドを呼び出して、IsolationLevel オプションを Unspecified、Chaos、ReadUncommitted、ReadCommitted、RepeatableRead、Serializable、または Snapshot に設定できます。

OLE DB
トランザクションの開始時、OLE DB を使用するアプリケーションは、isoLevel が、ISOLATIONLEVEL_READUNCOMMITTED、ISOLATIONLEVEL_READCOMMITTED、ISOLATIONLEVEL_REPEATABLEREAD、ISOLATIONLEVEL_SNAPSHOT または ISOLATIONLEVEL_SERIALIZABLE に設定された ITransactionLocal::StartTransaction を呼び出します。

オートコミット モードでトランザクション分離レベルを指定する場合、OLE DB アプリケーションは、DBPROPSET_SESSION プロパティ DBPROP_SESS_AUTOCOMMITISOLEVELS を、DBPROPVAL_TI_CHAOS、DBPROPVAL_TI_READUNCOMMITTED、DBPROPVAL_TI_BROWSE、DBPROPVAL_TI_CURSORSTABILITY、DBPROPVAL_TI_READCOMMITTED、DBPROPVAL_TI_REPEATABLEREAD、DBPROPVAL_TI_SERIALIZABLE、DBPROPVAL_TI_ISOLATED または DBPROPVAL_TI_SNAPSHOT に設定できます。

ODBC
ODBC アプリケーションは、Attribute が SQL_ATTR_TXN_ISOLATION に設定され、ValuePtr が SQL_TXN_READ_UNCOMMITTED、SQL_TXN_READ_COMMITTED、SQL_TXN_REPEATABLE_READ または SQL_TXN_SERIALIZABLE に設定された SQLSetConnectAttr を呼び出します。

スナップショット トランザクションの場合、アプリケーションは属性が SQL_COPT_SS_TXN_ISOLATION に、ValuePtr が SQL_TXN_SS_SNAPSHOT に設定された SQLSetConnectAttr を呼び出します。 スナップショット トランザクションは、SQL_COPT_SS_TXN_ISOLATION または SQL_ATTR_TXN_ISOLATION のいずれかを使用して取得できます。

データベース エンジンのロック

ロックとは、データベース エンジンが使用するメカニズムで、複数のユーザーによる同じデータへの同時アクセスが同期されます。

トランザクションでは、データの読み取りや変更など、データの現在の状態に対する依存関係を取得する前に、そのトランザクションを、別のトランザクションで同じデータが変更される影響から保護する必要があります。 トランザクションでは、データのロックを要求することにより、この問題に対処しています。 ロックには、共有ロック (S) や排他ロック (X) など複数のモードがあります。 ロック モードは、データに対するトランザクションの依存関係の度合いを定義します。 別のトランザクションに既に許可されているロックのモードと競合するロックを、トランザクションに許可することはできません。 トランザクションで、あるデータに対して既に許可されたロックと競合するロック モードが要求された場合、データベース エンジンは、既に許可されたロックが解放されるまで、要求を行ったトランザクションを保留します。

トランザクションで一部のデータが修正されている場合、トランザクションが完了するまで特定のロックを保持して、データの変更を保護します。 トランザクションが Read 操作を保護するために取得したロックの保持期間は、トランザクション分離レベル設定と最適化されたロックが有効かどうかによって異なります。

• 最適化されたロックが有効になっていない場合、トランザクションの最後まで書き込みに必要な行とページのロックは保持されます。

• 最適化されたロックが有効な場合、トランザクションの終了までトランザクション ID (TID) ロックのみが保持されます。 既定の READ COMMITTED 分離レベルでは、トランザクションは、トランザクションの終了まで、書き込みに必要な行ロックとページ ロックを保持しません。 これにより、必要なロック メモリが減り、ロックのエスカレーションの必要性が軽減されます。 さらに最適化ロックが有効な場合、Lock After Qualification (LAQ) 最適化は、ロックを取得せずに、行の最新のコミット済みバージョンのクエリの述語を評価するため、同時実行性が向上します。

トランザクションで保持されているすべてのロックは、トランザクションが完了 (コミットまたはロールバック) した時点で解放されます。

通常、アプリケーションから、ロックが直接要求されることはありません。 ロックは、ロック マネージャーと呼ばれるデータベース エンジンの一部により内部管理されます。 データベース エンジンのインスタンスが、Transact-SQL ステートメントを処理する際、データベース エンジン クエリ プロセッサは、アクセスするリソースを判断します。 クエリ プロセッサでは、アクセスの種類とトランザクションの分離レベルの設定に基づいて、各リソースを保護するために必要なロックの種類が決定されます。 その後、クエリ プロセッサから、ロック マネージャーに適切なロックが要求されます。 ロック マネージャーでは、別のトランザクションで保持されているロックに競合するロックがない場合、要求されたロックを許可します。

ロックの粒度と階層

データベース エンジンには、複数粒度のロックがあり、これは、トランザクションがロックするさまざまな種類のリソースを許可します。 ロックのコストを最小限に抑えるため、データベース エンジンは、タスクに適切なレベルでリソースを自動的にロックします。 ロックの粒度を細かくすると (行単位など)、コンカレンシーが高くなります。ただし、多くの行をロックすると、ロック数が増えるのでオーバーヘッドが増大します。 ロックの粒度を粗くすると (テーブル単位など)、テーブル全体がロックされるので、他のトランザクションがそのテーブルにアクセスできなくなります。このため、コンカレンシーが低下します。 ただし、ロック数が減るので、オーバーヘッドは減少します。

多くの場合、データベース エンジンは、異なるレベルの粒度でロックを取得して、リソースを完全に保護します。 この複数レベルの粒度でのロックのグループを、ロック階層と呼びます。 たとえば、インデックスの読み取りを完全に保護するため、データベース エンジンのインスタンスは、行の共有ロックと、ページやテーブルのインテント共有ロックを取得しなければならない場合があります。

次の表に、データベース エンジンがロックできるリソースを示します。

¹HoBT および TABLE ロックは、ALTER TABLE の LOCK_ESCALATION オプションに影響される場合があります。

² XACT ロック リソースでは、追加のロック リソースを使用できます。「最適化されたロックの診断の追加」を参照してください。

ロック モード

データベース エンジンは、異なるロック モードを使用してリソースをロックします。このモードは、同時実行トランザクションがリソースにアクセスする方法を決定します。

次の表は、データベース エンジンが使用するリソース ロック モードを示しています。

共有ロック

共有 (S) ロックを使用すると、同時実行トランザクションは、楽観的同時実行制御でリソースを読み取ることができます。 リソースに共有 (S) ロックが既存している間は、その他のトランザクションは、データを修正できません。 トランザクション分離レベル REPEATABLE READ 以上に設定されていおり、ロック ヒントを使用して、トランザクションの期間中に共有 (S) ロックが維持されていない限り、リソースの共有 (S) ロックは、Read 操作が完了したらすぐに、解放されます。

更新ロック

更新 (U) ロックは、更新の実行準備をするため、データベース エンジンは、更新ロックを配置します。 U ロックは S ロックと互換性がありますが、特定のリソースで一度に 1 つの U ロックを保持できるトランザクションは 1 つだけです。 これは重要です。多くの同時実行トランザクションは S ロックを保持できますが、リソースに対して U ロックを保持できるトランザクションは 1 つだけです。 更新 (U) ロックは、最終的に排他 (X) ロックにアップグレードされ、行が更新されます。

更新 (U) ロックは、ステートメントで UPDLOCK テーブル ヒントが指定されている場合に、UPDATE 以外のステートメントでも実行できます。

• 一部のアプリケーションでは、「行を選択してから行を更新する」パターンを使用します。このパターンでは、読み取りと書き込みがトランザクション内で明示的に独立しています。 分離レベルが REPEATABLE READ または SERIALIZABLE の場合、次のように同時更新によってデッドロックが発生する可能性があります。

  トランザクションはデータを読み取り、リソースの共有 (S) ロックを取得してからデータを変更します。これには、排他 (X) ロックへのロックの変換が必要です。 2 つのトランザクションが 1 つのリソースで共有ロック (S) を取得し、データの同時更新を試行した場合、1 つのトランザクションは、ロックを 排他 (X) ロックに変換しようとします。 もう一方のトランザクションの排他 (X) ロックは、もう一方のトランザクションの共有 (S) ロックと互換性が無いため、共有から排他へのロック変換は待機状態になります。つまり、ロックの待機が発生します。 もう一方のトランザクションも更新のために排他 (X) ロックの取得を試みます。 この場合、両方のトランザクションが排他 (X) ロックへの変換を行っており、もう一方のトランザクションが共有 (S) ロックを解放するのを待っている状態なので、デッドロックが発生します。

  デフォルトの READ COMMITTED 分離レベルでは、S ロックは短い期間であり、使用されるとすぐに解放されます。 上記のデッドロックが引き続き発生する可能性はありますが、期間の短いロックの可能性ははるかに低くなります。

  この種類のデッドロックを回避するために、アプリケーションは、「UPDLOCK ヒントを持つ行を選択してから、行を更新する」パターンに従います。

• SNAPSHOT 分離の使用中に、UPDLOCK ヒントがが書き込みで使用される場合、トランザクションは、最新バージョンの行にアクセスできる必要があります。 最新バージョンが表示されなくなった場合は、Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict を受信できる場合があります。 例については、「スナップショット分離の操作」を参照してください。

排他的ロック

排他 (X) ロックは、同時に実行されている複数のトランザクションが同じリソースにアクセスすることを防ぎます。 排他 (X) ロックを使用すると、その他のトランザクションは、ロックで保護されたデータを修正できなくなります。Read 操作は、NOLOCK Hinnto または READ UNCOMMITTED 分離レベルが使用されている場合のみ実行できます。

INSERT、UPDATE、DELETE などのデータ変更ステートメントでは、Read 操作と Modification 操作が組み合わされて使用されます。 最初に読み取り操作でデータを取得してから、必要な変更操作を実行します。 したがって、一般的にデータ変更ステートメントには共有ロックおよび排他ロックの両方が必要です。 たとえば、UPDATE ステートメントが、他のテーブルとの結合に基づいてあるテーブルの行を修正する場合、 UPDATE ステートメントは、結合テーブルで読み取られる行の共有ロックと更新された行の排他ロックを要求します。

インテント ロック

データベース エンジンは、インテント ロックを使用して、リソースの共有 (S) ロックまたは排他 (X) ロックがロック階層の下位に配置されるのを防ぎます。 インテント ロックには、「intent locks」という名前が付けられています。これは、下位レベルのロックの前に、インテント ロックが取得され、下位レベルにロックを配置する意図があるためです。

インテント ロックの用途は次の 2 つです。

• 他のトランザクションが上位のリソースを変更することにより下位レベルのロックを無効にしてしまうことを防ぐ。
• より高い粒度でロックの競合を検出するデータベース エンジンの効率性を向上する。

たとえば、あるテーブルのページまたは行の共有 (S) ロックを要求する前に、テーブル レベルの共有インテント ロックを要求するとします。 テーブル レベルのインテント ロックを設定すると、それ以降、別のトランザクションによってそのページを含むテーブルに排他的 (X) ロックがかけられる状態を回避することができます。 インテント ロックを使用すればパフォーマンスが向上します。データベース エンジンは、トランザクションがテーブルに対して安全にロックをかけることができるかどうかを判断する際に、テーブル レベルのインテント ロックを調べるだけで済みます。 これにより、トランザクションがテーブル全体をロックできるかどうかを判断するために、テーブルの各行や各ページのロックを調べる必要がなくなります。

インテント ロックにはインテント共有 (IS)、インテント排他 (IX)、およびインテント排他付き共有 (SIX) があります。

スキーマ ロック

データベース エンジンは、カラムの追加やテーブルの削除などのテーブル データ定義言語 (DDL) 操作中に、スキーマ修正 (Sch-M) ロックを使用します。 保持されている間、Sch-M ロックは、テーブルへの同時アクセスを防ぎます。 つまり、Sch-M ロックは、ロックが解放されるまで、すべての外部操作をブロックします。

テーブルの切り捨てなど一部のデータ操作言語 (DML) は、Sch-M ロックを使用して、同時操作によって影響を受けるテーブルへのアクセスを防ぎます。

データベース エンジンは、クエリをコンパイルして実行する際に、スキーマ安定性 (Sch-S) ロックを使用します。 Sch-Sロックは、排他 (X) ロックを含めどのトランザクション ロックもブロックしません。 したがって、その他のトランザクション (テーブルで X ロックが指定されているトランザクションなど) は、クエリのコンパイル中も継続して実行されます。 ただし、同時実行 DDL 操作、および Sch-M ロックを取得する同時実行 DML 操作は、Sch-S ロックによってブロックされます。

一括更新ロック

一括更新 (BU) ロックを使用すると、複数のスレッドで同時にデータを同一のテーブルに一括で読み込むことができますが、データを一括読み込みしていない他のプロセスは、その間テーブルにアクセスできません。 次に示す両方の条件に該当する場合、データベース エンジンは、一括更新 (BU) ロックを使用します。

• Transact-SQL BULK INSERT ステートメントまたはOPENROWSET(BULK) 関数を使用するか、.NET SqlBulkCopy、OLEDB Fast Load API または ODBC Bulk Copy API などの Bulk Insert API コマンドのいずれかを使用して、テーブルにデータを一括コピーする。
• TABLOCK ヒントが指定されているか、sp_tableoption を使用して、table lock on bulk load テーブル オプションを設定する。

キー範囲ロック

キー範囲ロックは、SERIALIZABLE トランザクション分離レベルを使用する一方、Transact-SQL が読み取ったレコード一式に含まれる行の範囲を暗黙的に保護します。 キー範囲ロックを使用すると、ファントム読み取りを回避できます。 行間のキー範囲を保護することで、トランザクションからアクセスするレコード セットへのファントム挿入やファントム削除も回避されます。

ロックの互換性

ロックの互換性により、複数のトランザクションが同じリソースのロックを同時に獲得できるかどうかが制御されます。 リソースが別のトランザクションによって既にロックされている場合、要求されたロックのモードと既存のロックのモードに互換性がある場合のみ、新しいロック要求の許可が可能になります。 要求されたロックのモードと既存のロックとの互換性がない場合、新しいロックを要求しているトランザクションは、既存のロックが解除されるか、またはロックがタイムアウトするのを待機します。 たとえば、排他ロックと互換性があるロック モードはありません。 排他 (X) ロックが保持されている間は、その排他 (X) ロックが解除されるまで、他のトランザクションはこのリソースに対してどの種類のロック (共有、更新、排他) も取得できません。 反対に、共有 (S) ロックがリソースに適用された場合、最初のトランザクションが完了していなくても、その他のトランザクションも、リソースで共有ロックまたは 更新 (U) ロックを取得できます。 ただし、共有ロックが解除されないと、他のトランザクションは排他ロックを獲得できません。

次の表に、最も一般的に使用されるロック モードの互換性を示します。

以下のテーブルでは、データベース エンジンで使用できるすべてのロック モードの互換性を確認できます。

キー範囲ロック

キー範囲ロックは、SERIALIZABLE トランザクション分離レベルを使用する一方、Transact-SQL が読み取ったレコード一式に含まれる行の範囲を暗黙的に保護します。 SERIALIZABLE 分離レベルでは、トランザクション中に実行されるクエリは、そのトランザクション内で実行されるたびに同一の行セットを取得する必要があります。 キー範囲ロックは、その他のトランザクションが、SERIALIZABLE トランザクションが読み込んだキーの範囲に該当するキーを持つ新しい行を挿入できないようにすることで、この要件を満たします。

キー範囲ロックを使用すると、ファントム読み取りを回避できます。 各行のキー範囲を保護することで、トランザクションからアクセスされるレコード セットへのファントム挿入も回避されます。

キー範囲ロックは、キー範囲の開始値と終了値を指定して、インデックスに対して設定されます。 このロックでは、範囲内のキー値を持つ行を挿入、更新、または削除する操作がブロックされます。挿入操作、更新操作、または削除操作では、最初にインデックスに対するロックを取得する必要があるためです。 たとえば、SERIALIZABLE トランザクションは、キー値が条件 BETWEEN 'AAA' AND 'CZZ' を満たすすべての行を読み取る SELECT ステートメントを発行できます。 'AAA' から 'CZZ' の範囲内のキー値にキー範囲ロックをかけると、他のトランザクションからは 'ADG'、'BBD'、'CAL' など、その範囲内のキー値を持つ行は挿入されません。

キー範囲ロック モード

キー範囲ロックには、範囲-行形式で指定される範囲と行のコンポーネントが含まれています。

• 範囲は 2 つの連続したインデックス エントリ間の範囲を保護するロック モードを表します。
• 行はインデックス エントリを保護するロック モードを表します。
• モードは使用する組み合わされたロック モードを表します。 キー範囲ロック モードは 2 つの部分から成ります。 最初の部分はインデックス範囲 (RangeT) をロックするのに使用するロックの種類を表し、その次の部分は特定のキー (K) をロックするのに使用するロックの種類を表します。 RangeT-K のように、2 つの部分はハイフン (-) で連結されます。

各キー範囲ロック モードには、重なり合うキーと範囲に対して取得されるロックが、どのロックと互換性があるかを示す互換性マトリックスがあります。

変換ロック

変換ロックは、キー範囲ロックが別のロックと重なり合うときに作成されます。

変換ロックは、同時実行プロセスを実行しているときなど、さまざまな環境で短時間発生することがあります。

シリアル化可能な範囲スキャン、単一フェッチ、削除、および挿入

キー範囲ロックは、次の操作のシリアル化を保証します。

• 範囲スキャン クエリ
• 存在しない行の単一フェッチ
• Delete 操作
• Insert 操作

キー範囲ロックを実行する前に次の条件を満たしておく必要があります。

• トランザクション分離レベルを SERIALIZABLE に設定する。
• クエリ プロセッサではインデックスを使用して範囲フィルター述語を実装する必要があります。 たとえば、SELECT ステートメントの WHERE 句は、ColumnX BETWEEN N'AAA' AND N'CZZ' の述部で範囲条件を指定できます。 ColumnX がインデックス キーに含まれている場合、キー範囲ロックのみが取得されます。

例

次のテーブルとインデックスは、この後のキー範囲ロックの例の基準として使用されます。

範囲スキャン クエリ

範囲スキャン クエリを確実にシリアル化するには、同じトランザクション内で同じクエリを実行するたびに同じ結果が返されるようにします。 他のトランザクションによる範囲スキャン クエリ内に新しい行を挿入しないでください。これはファントム挿入になります。 たとえば、上の図のテーブルとインデックスを使用する次のクエリについて考えます。

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

名前が Adam と Dale の値の間にある行の範囲に対応するインデックス エントリにキー範囲ロックが設定され、前のクエリで指定された新しい行が追加されたり、削除されたりするのを防ぎます。 この範囲の最初の名前は Adam ですが、このインデックス エントリに対する RangeS-S モードのキー範囲ロックにより、確実に Abigail などの文字 A で始まる新しい名前を Adam の前に追加できなくなります。 同様に、Dale のインデックス エントリの RangeS-S キー範囲ロックにより、Clive などの文字 C で始まる新しい名前を Carlos の後に確実に追加できなくなります。

存在しないデータの単一フェッチ

トランザクション内のクエリで存在しない行を選択しようとする場合、同じトランザクション内で再度そのクエリを実行しても、同じ結果を返す必要があります。 どのトランザクションも、存在しない行を追加することはできません。 たとえば、次のクエリについて考えてみます。

SELECT name
FROM mytable
WHERE name = 'Bill';

名前 Ben は隣接するインデックス エントリである Bing と Bill の間に挿入されるため、この名前範囲に対応するインデックス エントリにキー範囲ロックが設定されます。 RangeS-S モードのキー範囲ロックは、インデックス エントリ Bing に設定されます。 これにより、Bill などの値がインデックス エントリの Ben と Bing の間に挿入されるのを防ぎます。

最適化されたロックを使用しない Delete 操作

トランザクション内で値を削除する場合、Delete 操作を実行するトランザクションの実行中、その行が入る範囲はロックする必要はありません。 シリアル化可能性を維持するには、削除するキー値をトランザクションの終了時までロックするだけで十分です。 たとえば、次の DELETE ステートメントについて考えてみます。

DELETE mytable
WHERE name = 'Bob';

名前 Bob に対応するインデックス エントリに、排他 (X) ロックを配置します。 他のトランザクションは、削除する値 Bob の行の前後に値を挿入したり、削除することができます。 ただし、Bob 値に一致する行の読み取り、挿入、または削除を試みる任意のトランザクションは、削除中のトランザクションがコミットまたはロールバックするまでブロックされます。 (READ_COMMITTED_SNAPSHOT データベース オプションと SNAPSHOT 分離レベルでは、以前にコミットされた状態の行バージョンからの読み取りも許可されます)。

範囲削除は、行ロック、ページ ロック、またはテーブル ロックの 3 つの基本的なロック モードを使用して実行できます。 行、ページ、テーブルのロック戦略は、Query Optimizer が決定するか、ROWLOCK、PAGLOCK または TABLOCK などの Query Optimizer ヒントを使用してユーザーが指定できます。 PAGLOCK または TABLOCK を使用した場合、すべての行をインデックス ページから削除すると、データベース エンジンによりすぐにインデックス ページの割り当てが解除されます。 対照的に、ROWLOCK を使用する場合、削除対象のすべての行には削除のマークが付けられるだけです。これらは、後でバックグラウンド タスクによってインデックス ページから削除されます。

最適化されたロックを使用する Delete 操作

トランザクション内の行を削除すると、行ロックとページ ロックは増分的に取得および解放され、トランザクションの期間中は保持されません。 たとえば、次の DELETE ステートメントについて考えてみます。

DELETE mytable
WHERE name = 'Bob';

TID ロックは、トランザクションの期間中、変更されたすべての行に対して配置されます。 値 Bobに対応するインデックス行の TID でロックが取得されます。 最適化されたロックでは、ページ ロックと行ロックは更新のために引き続き取得されますが、各行が更新されるとすぐに各ページと行ロックが解放されます。 TID ロックは、トランザクションが完了するまで行が更新されないように保護します。 Bob 値を持つ行の読み取り、挿入、または削除を試みる任意のトランザクションは、削除中のトランザクションがコミットまたはロールバックするまでブロックされます。 (READ_COMMITTED_SNAPSHOT データベース オプションと SNAPSHOT 分離レベルでは、以前にコミットされた状態の行バージョンからの読み取りも許可されます)。

それ以外の場合、Delete 操作のロックのしくみは、最適化されたロックなしの場合と同じです。

最適化されたロックを使用しない Insert 操作

トランザクション内で行を挿入する場合、Insert 操作を行うトランザクションの実行中、その行が含まれている範囲をロックする必要はありません。 シリアル化可能性を維持するには、挿入するキー値をトランザクションの終了時までロックするだけで十分です。 たとえば、次の INSERT ステートメントについて考えてみます。

INSERT mytable VALUES ('Dan');

RangeI-N モード キー範囲ロックは、範囲をテストするために、名前 David に対応するインデックス行に配置されます。 ロックが許可されると、値 Dan を持つ行が挿入され、挿入された行に排他的 (X) ロックが配置されます。 RangeI-N モードのキー範囲ロックは範囲のテストだけに必要で、挿入処理を行うトランザクションの実行中は保持されません。 他のトランザクションは、値 Dan が指定された挿入済み行の前後に値を挿入したり、前後の値を削除できます。 ただし、Dan 値を持つ行の読み取り、挿入、または削除を試みる任意のトランザクションは、挿入中のトランザクションがコミットまたはロールバックするまでブロックされます。

最適化されたロックを使用する Insert 操作

トランザクション内で行を挿入する場合、Insert 操作を行うトランザクションの実行中、その行が含まれている範囲をロックする必要はありません。 行ロックとページ ロックが取得されることはほとんどありません。オンライン インデックスの再構築が進行中の場合、または、同時 SERIALIZABLE トランザクションがある場合にのみ取得されます。 行ロックとページ ロックが取得されると、これらはすぐに解放され、トランザクションの期間中は保持されません。 直列可能性を維持するには、トランザクション終了まで、排他 TID ロックを挿入済みキー値に配置するだけで十分です。 たとえば、次の INSERT ステートメントについて考えてみます。

INSERT mytable VALUES ('Dan');

最適化されたロックでは、RangeI-N ロックは、インスタンスで SERIALIZABLE 分離レベルを使用しているトランザクションが 1 つ以上存在する場合にのみ取得されます。 RangeI-N モード キー範囲ロックは、範囲をテストするために、名前 David に対応するインデックス行に配置されます。 ロックが許可されると、値 Dan を持つ行が挿入され、挿入された行に排他的 (X) ロックが配置されます。 RangeI-N モードのキー範囲ロックは範囲のテストだけに必要で、挿入処理を行うトランザクションの実行中は保持されません。 他のトランザクションは、値 Dan が指定された挿入済み行の前後に値を挿入したり、前後の値を削除できます。 ただし、Dan 値を持つ行の読み取り、挿入、または削除を試みる任意のトランザクションは、挿入中のトランザクションがコミットまたはロールバックするまでブロックされます。

ロックのエスカレーション

ロックのエスカレーションとは、粒度が細かい多数のロックを粒度が粗い少数のロックに変換し、システム オーバーヘッドを削減するプロセスですが、その一方で同時実行性の競合が起こりやすくなります。

ロックのエスカレーションの動作は、最適化されたロックが有効になっているかどうかによって異なります。

最適化されたロックを使用しないロックのエスカレーション

データベース エンジンは、低いレベルのロックを使用するため、下位レベルのオブジェクトを含むオブジェクトにもインテント ロックが配置されます。

• 行またはインデックス キー範囲をロックする際、データベース エンジンは、行またはキーを含むページにインテント ロックを配置します。
• ページをロックする際、データベース エンジンは、ページを含む上位レベルのオブジェクトにインテント ロックを配置します。 オブジェクトのインテント ロックに加えて、次のオブジェクトでインテント ページ ロックが要求されます。
  • 非クラスター化インデックスのリーフレベル ページ
  • クラスター化インデックスのデータ ページ
  • ヒープ データ ページ

データベース エンジンは、ロックの数を最小限に抑え、ロックのエスカレーションが必要になる可能性を減らすために、同じステートメントに対して行とページのロックの両方を行う場合があります。 たとえば、データベース エンジンは、非クラスター化インデックスにページ ロックを配置し (インデックス ノード内でクエリの条件を満たすのに十分な連続するキーが選択されている場合)、クラスター化インデックスまたはヒープに行ロックを配置できます。

ロックをエスカレートするには、データベース エンジンは、テーブルのインテント ロックを対応する完全なロックに変更しようとします。たとえば、インテント排他 (IX) ロックを排他 (X) ロックに変更する、またはインテント共有 (IS) ロックを共有 (S) ロックに変更するなどです。 ロックのエスカレーション試行が成功し、完全なテーブル ロックが取得されると、ヒープまたはインデックスでトランザクションが保持するすべての HoBT、ページ (PAGE) または下位レベルの (RID、KEY) ロックが解放されます。 完全なロックを取得できない場合、その時点ではロックのエスカレーションは発生しません。データベース エンジンは行、キー、またはページのロックの取得を続行します。

データベース エンジンは、行ロックやキー範囲ロックを、ページ ロックにエスカレートしませんが、これらを直接テーブル ロックにエスカレートします。 同様に、ページ ロックは常にテーブル ロックにエスカレートされます。 パーティション テーブルのロックは、テーブル ロックではなく、関連するパーティションの HoBT レベルにエスカレートすることができます。 HoBT レベルのロックは、パーティションと調整された HoBT をかならずしもロックするわけではありません。

同時実行トランザクションが保持するロックの競合により、ロックのエスカレーションの試行が失敗した場合、データベース エンジンは、トランザクションが取得した追加の 1,250 の各ロックにロックのエスカレーションを再試行します。

各エスカレーション イベントは、主に 1 つの Transact-SQL ステートメントのレベルで操作されます。 イベント開始時、データベース エンジンは、エスカレーションのしきい値条件を満たすアクティブなステートメントが参照するテーブル内で同時実行トランザクションが所有するすべてのロックをエスカレートします。 ステートメントがテーブルにアクセスする前にエスカレーション イベントが開始された場合、そのテーブルのロックはエスカレートされません。 ロックのエスカレーションが成功しており、テーブルが現在のステートメントによって参照されエスカレーション イベントに含まれている場合は、前のステートメントでトランザクションによって取得されイベントの開始時に保持されていたロックは、いずれもエスカレートされます。

たとえば、セッションでこれらの操作が行われるとします。

• トランザクションを開始します。
• TableA を更新します。 これにより、トランザクションが完了するまで保持される TableA の排他的行ロックが生成されます。
• TableB を更新します。 これにより、トランザクションが完了するまで保持される TableB の排他的行ロックが生成されます。
• TableA を TableC と結合する SELECT を実行します。 クエリの実行プランによって、行が TableC から取得される前に TableA から取得される行が呼び出されます。
• SELECT ステートメントは、TableA の行の取得中、かつ、TableC がアクセスされる前に、ロックのエスカレーションがトリガーされます。

ロックのエスカレーションが成功した場合、TableA のセッションで保持されているロックのみがエスカレートされます。 これには、SELECT ステートメントからの共有ロックと、前の UPDATE ステートメントからの排他ロックの両方が含まれます。 ロックのエスカレーションを行う必要があるかどうかを判断するために、セッションにより SELECT ステートメントの TableA で取得されたロックのみがカウントされますが、エスカレーションが成功すると、TableA のセッションによって保持されているすべてのロックがテーブルの排他ロックにエスカレートされ、インテント ロックを含む TableA のその他のすべての細分性の低いロックが解放されます。

SELECT ステートメントの TableB へのアクティブな参照がなかったため、TableB のロックのエスカレートは試行されません。 同様に、TableC は、エスカレーションの発生時にまだアクセスされていなかったので、ロックのエスカレートは試行されません。

最適化されたロックを使用するロックのエスカレーション

最適化されたロックは、トランザクションの期間中に保持されるロックが非常に少ないため、ロック メモリを減らすのに役立ちます。 データベース エンジンが行ロックとページ ロックを取得すると、ロックのエスカレーションも同様に発生する可能性がありますが、その頻度は非常に低いです。 最適化されたロックは、通常、ロックのエスカレーションを回避し、ロックの数と必要なロック メモリの量を減らします。

最適化されたロックが有効なデフォルトの READ COMMITTED 分離レベルでは、データベース エンジンは、行の変更が完了すると直ちに行ロックとページ ロックを解放します。 1 つのトランザクション ID (TID) ロックを除き、トランザクションの期間中、行ロックとページ ロックは保持されません。 これにより、ロックのエスカレーションの可能性が低下します。

ロックのエスカレーションのしきい値

ロックのエスカレーションは、ALTER TABLE SET LOCK_ESCALATION オプションを使用してロックのエスカレーションをテーブルで無効にしていない場合、および次のいずれかの条件が存在する場合にトリガーされます。

• 1 つの Transact-SQL ステートメントにより、パーティション化されていない 1 つのテーブルまたはインデックスに対して少なくとも 5,000 のロックが取得されている。
• 1 つの Transact-SQL ステートメントにより、パーティション化されたテーブルの 1 つのパーティションで少なくとも 5,000 のロックが取得され、ALTER TABLE SET LOCK_ESCALATION オプションが [AUTO] に設定されている。
• データベース エンジンのインスタスのロック数が、メモリまたは構成しきい値を超えている。

ロックの競合によりロックをエスカレートできない場合、データベース エンジンは、新しい 1,250 のロックが取得されるたびにロックのエスカレーションをトリガーします。

Transact-SQL ステートメントのエスカレーションのしきい値

データベース エンジンにより、新たに 1,250 のロックが取得されるたびに発生する可能性があるエスカレーションが確認されると、Transact-SQL ステートメントでテーブルの 1 つの参照に対して少なくとも 5,000 のロックが取得された場合にのみ、ロックのエスカレーションが発生します。 Transact-SQL ステートメントでテーブルの 1 つの参照に対して少なくとも 5,000 のロックが取得されると、ロックのエスカレーションがトリガーされます。 たとえば、ステートメントが 1 つのインデックスで 3,000 のロックを取得し、同じテーブルの別のインデックスで 3,000 のロックを取得した場合、ロックのエスカレーションはトリガーされません。 同様に、ステートメントのテーブルの自己結合があり、テーブルへの各参照がテーブル内の 3,000 のロックのみを取得する場合、ロックのエスカレーションはトリガーされません。

ロックのエスカレーションは、エスカレーションがトリガーされた時点でアクセスされたテーブルに対してのみ発生します。 1 つの SELECT ステートメントが、TableA、TableB、TableC という 3 つのテーブルにこの順序でアクセスする結合であると仮定します。 このステートメントにより、TableA についてはクラスター化インデックスの 3,000 の行ロックが、TableB についてはクラスター化インデックスの少なくとも 5,000 の行ロックが取得されますが、TableC はまだアクセスされていません。 データベース エンジンによって、ステートメントで TableB に少なくとも 5,000 の行ロックが取得されたことが検出されると、TableB の現在のトランザクションで保持されているすべてのロックのエスカレーションが試行されます。 また、TableA の現在のトランザクションで保持されているすべてのロックのエスカレートが試行されますが、TableA のロック数が 5,000 未満なので、エスカレーションは成功しません。 TableC はエスカレーションの発生時にまだアクセスされていなかったので、エスカレーションが試行されません。

データベース エンジンのインスタンスのエスカレーションのしきい値

ロックのエスカレーションに関するメモリのしきい値よりもロック数が大きくなると、データベース エンジンは、必ずロックのエスカレーションをトリガーします。 メモリのしきい値は、locks 構成オプションの設定によって異なります。

• locks オプションがその既定の 0 に設定されている場合、AWE メモリを除き、ロック オブジェクトによって使用されるメモリがデータベース エンジンによって使用されるメモリの 24% になると、ロックのエスカレーションのしきい値に達します。 ロックを表すために使用されるデータ構造長は、約 100 バイトです。 データベース エンジンによって、変化するワークロードに合わせて調整するためにメモリが動的に確保および解放されるので、このしきい値は動的です。

• locks オプションの値が 0 以外の場合、ロックのエスカレーションのしきい値は locks オプションの値の 40% になります (メモリに負荷がかかっている場合は 40% 未満になります)。

データベース エンジンは、エスカレーション対象として、任意のセッションの任意のアクティブなステートメントを選択できます。また、インスタンスで使用されるロックのメモリがしきい値よりも大きい間は、1,250 の新しいロックごとに、エスカレーション対象のステートメントが選択されます。

さまざまな種類のロックがあるロックのエスカレーション

ロックのエスカレーションが発生すると、ヒープまたはインデックスに対して選択されたロックは、最も制限の厳しい下位レベルのロックの要件を満たすために十分強力です。

たとえば、次のようなセッションがあるとします。

• トランザクションを開始します。
• クラスター化インデックスを含むテーブルを更新します。
• 同じテーブルを参照する SELECT ステートメントを発行します。

UPDATE ステートメントは、次のロックを取得します。

• 更新されたデータ行の排他 (X) ロック。
• これらの行を含むクラスター化インデックス ページのインテント排他 (IX) ロック。
• クラスター化インデックスの IX ロックと、テーブル上のもう 1 つのロック。

SELECT ステートメントは、次のロックを取得します。

• UPDATE ステートメントの X ロックがすでに行を保護していない限り、共有 (S) ロックは、すべての読み取ったデータ行をロックします。
• IX ロックがすでにページを保護していない限り、インテント共有 (IS) は、これらの行を含むすべてのクラスター化インデックス ページでロックします。
• IX ロックがすでに、クラスター化インデックスまたはテーブルを保護しているため、これらでロックは行われません。

SELECT ステートメントがロックのエスカレーションをトリガーするために十分なロックを取得し、エスカレーションが成功した場合、テーブルの IX ロックは X ロックに変換され、すべての行、ページ、およびインデックス ロックが解放されます。 更新と読み取りの両方が、テーブルの X ロックによって保護されます。

ロックとロックのエスカレーションを削減する

ほとんどの場合、ロックおよびロックのエスカレーションの既定設定を使用して操作すると、データベース エンジンは最善のパフォーマンスを発揮します。

• 最適化されたロックを最大限に活用する。

  • 最適化されたロックは、ロック メモリの消費量を減らし、同時実行トランザクションでブロックする、強化されたトランザクションのロック メカニズムを提供します。 最適化されたロックが有効になっている場合、ロックのエスカレーションが発生する可能性ははるかに低くなります。
  • 最適化されたロックでテーブル ヒントの使用を避けます。 テーブル ヒントは、最適化されたロックの有効性を低下させる可能性があります。
  • 最適化されたロックの利点を最大限に活用するには、データベースで [READ_COMMITTED_SNAPSHOT] オプションを有効にします。 これは、Azure SQL データベースの既定値です。
  • 最適化されたロックでは、データベースで高速データベース復旧 (ADR) を有効にする必要があります。

データベース エンジンのインスタンスがたくさんのロックを生成し、頻繁にロックがエスカレートされる場合は、次の戦略をもとにロック数を減らすことを検討します。

• Read 操作で共有ろっくを生成しない分離レベルを使用する:

  • READ_COMMITTED_SNAPSHOT データベース オプションが ON の場合の READ COMMITTED 分離レベル。
  • SNAPSHOT 分離レベル。
  • READ UNCOMMITTED 分離レベル。 これは、ダーティ リードで動作できるシステムでのみ使用できます。

• データベース エンジンが、下位レベルのロックではなく、ページ、ヒープまたはインデックス ロックを使用するように PAGLOCK または TABLOCK テーブル ヒントを使用します。 ただし、このオプションを使用すると、同じデータにアクセスしようとする他のユーザーをブロックするユーザーの問題が増えるため、同時ユーザー数が少ないシステムでは使用しないでください。

• 最適化されたロックが利用できない場合、パーティション化されたテーブルでは、ALTER TABLE の LOCK_ESCALATION オプションを使用して、ロックをテーブルではなくパーティションにエスカレートするか、テーブルへのロックのエスカレーションを無効にします。

• 大きなバッチ操作をいくつかの小さな操作に分割します。 たとえば、次のクエリを実行して監査テーブルから数十万の古い行を削除してから、他のユーザーをブロックしたロックのエスカレーションが発生したことがわかったとします。

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

  これらの行を一度に数百個削除すると、トランザクションごとに蓄積されるロックの数を大幅に減らし、ロックのエスカレーションを防ぐことができます。 例えば次が挙げられます。

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• クエリを可能な限り効率的にして、クエリのロックの占有領域を減らします。 大規模なスキャンまたは多数の主要な検索では、ロックのエスカレーションの機会が増える場合があります。さらに、デッドロックの可能性が増え、通常は同時実行性とパフォーマンスに悪影響を及ぼします。 ロックのエスカレーションの原因となっているクエリを見つけた後、新しいインデックスを作成するか、既存のインデックスに列を追加して完全なインデックスまたはテーブルのスキャンを削除し、インデックス シークの効率を最大化できる可能性を探ります。 クエリで自動インデックス分析を実行する場合は、データベース エンジン チューニング アドバイザーの使用を検討してください。 詳細については、「チュートリアル:データベース エンジン チューニング アドバイザー」を参照してください。 この最適化の目的の 1 つは、主要な検索のコストを最小限に抑える (特定のクエリに対するインデックスの選択度を最大にする) ために、インデックス シークでできるだけ少ない行を返すようにすることです。 データベース エンジンが、主要な検索の論理演算子によって多数の行が返されることを予想する場合、プリフェッチ最適化を使用して検索を実行することができます。 データベース エンジンが、検索にプリフェッチを使用する場合は、クエリの一部のトランザクション分離レベルを REPEATABLE READ に上げる必要があります。 つまり、READ COMMITTED 分離レベルで SELECT ステートメントと同じように見えても、(クラスター化インデックスとクラスター化されていないインデックスの両方で) 何千もの主要なロックを取得する場合があり、これにより、そのようなクエリは、ロック エスカレーションしきい値を超える可能性があります。 これは、エスカレートされたロックが共有テーブル ロックであることがわかった場合に特に重要です。しかし、これは通常、既定の READ COMMITTED 分離レベルでは見られません。

  プリフェッチ最適化がある主要な検索が原因で、ロック エスカレーションが発生している場合は、クエリ プランの主要な検索論理演算しの下にある Index Seek または Index Scan 論理演算子に表示されるクラスター化されていないインデックスに列をさらに追加することを検討します。 SELECT 列リストにすべてを含むことが現実的ではない場合、カバリング インデックス (クエリで使用するテーブル内のすべての列を含むインデックス) を作成するか、少なくとも結合基準または WHERE 句で使用された列をカバーするインデックスを作成できます。 Nested Loop 結合でもプリフェッチ最適化を使用することがあります。これにより、同じロック動作が発生します。

• 異なる SPID で現在、互換性のないテーブル ロックが保持されている場合は、ロックのエスカレーションを行うことはできません。 ロックのエスカレーションは常にテーブル ロックに行われ、ページ ロックに行われることはありません。 また、別の SPID で互換性のないテーブル ロックが保持されているためにロックのエスカレーションの試行に失敗した場合、エスカレーションを試行したクエリでは、テーブル ロックを待機している間はブロックは行われません。 代わりに、元のより細かいレベル (行、キー、またはページ) でのロックの取得が続行され、定期的に追加のエスカレーションが試行されます。 したがって、特定のテーブルでのロックのエスカレーションを防ぐ方法の 1 つは、エスカレートされたロックの種類と互換性のない別の接続に対してロックを取得して保持することです。 テーブル レベルでのインテント排他 (IX) ロックによって、行やページはロックされませんが、それでもエスカレートされた共有 (S) または排他 (X) テーブル ロックとの互換性はありません。 たとえば、mytable テーブル内の多数の行を変更するバッチ ジョブを実行する必要があり、ロックのエスカレーションによってブロックが発生しているとします。 このジョブが常に 1 時間足らずで完了する場合は、次のコードを含む Transact-SQL ジョブを作成し、バッチ ジョブの開始時刻の数分前に新しいジョブを開始するようにスケジュールすることができます。

  BEGIN TRAN;
  
  SELECT *
  FROM mytable WITH (UPDLOCK, HOLDLOCK)
  WHERE 1 = 0;
  
  WAITFOR DELAY '1:00:00';
  
  COMMIT TRAN;
  

  このクエリによって、1 時間で mytable の IX ロックが取得され保持されます。これにより、その間のテーブルでのロックのエスカレーションを防ぐことができます。 このバッチは、データを変更したり、その他のクエリをブロックしたりしません (その他のクエリが TABLOCK ヒントを使用してテーブル ロックを強制したり、管理者が mytable のインデックスでページまたは行のロックを無効にしている場合を除きます)。

• トレース フラグ 1211 と 1224 を使用すると、すべてまたは一部のロックのエスカレーションを無効にすることもできます。 ただし、これらのトレース フラグを使用すると、データベース エンジン インスタンス全体のすべてのロックのエスカレーションが全体で無効になります。 ロックのエスカレーションは、数千個のロックを取得して解放するオーバーヘッドによってクエリが遅くなる場合に、その効率を最大化することで、データベース エンジン内で有用な意義を提供しています。 ロックのエスカレーションは、ロックを追跡するために必要なメモリを最小限に抑えるのにも役立ちます。 データベース エンジンが動的にロック構造を割り当てることができるメモリは有限であるため、ロックのエスカレーションを無効にし、ロック メモリのサイズが十分である場合は、クエリに対する追加ロックの配置に失敗する場合があり、Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration. のエラーが発生します。

  注

  MSSQLSERVER_1204 エラーが発生すると、現在のステータスの処理が停止され、アクティブなトランザクションがロールバックされます。 ロールバック自体で、ユーザーがブロックされたり、データベース サービスを再開した場合にデータベースの復旧時間が長くなったりすることがあります。

  注

  ROWLOCK などのロック ヒントを使用すると、最初のロック取得のみが変更されます。 ロック ヒントによってロックのエスカレーションが妨げられることはありません。

SQL Server 2008 (10.0.x) 以降、ロックのエスカレーションの動作は、LOCK_ESCALATION テーブル オプションの導入によって変更されました。 詳しくは、ALTER TABLE の LOCK_ESCALATION オプションを参照してください。

ロックのエスカレーションを監視する

次の例のようなlock_escalation 拡張イベントを使用してロックのエスカレーションを監視します。

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
    (
    SET collect_database_name=1,collect_statement=1
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
    )
ADD TARGET package0.histogram
    (
    SET source=N'sqlserver.database_id'
    )
GO

動的ロック

行ロックなど、レベルの低いロックを使用すると、2 つのトランザクションが同時にデータの同じ部分に対するロックを要求する可能性が低減し、コンカレンシーが高まります。 同時に、ロックの数も増えるので、ロックを管理するために多くのリソースが必要になります。 テーブルまたはページに対する高レベルのロックはオーバーヘッドが減りますが、コンカレンシーは低下します。

データベース エンジンは、動的ロック戦略を使用して、最も効率的なロックを判断します。 データベース エンジンは、クエリ実行時に、スキーマおよびクエリの特性に基づいて最適なロックを自動的に判断します。 たとえば、インデックス スキャンの実行時に、インデックス内でのページのロックが選択されます。これにより、ロックのオーバーヘッドを減少させることができます。

ロックのパーティション分割

大規模なコンピューター システムでは、頻繁に参照されるオブジェクトのロックがパフォーマンスのボトルネックになることがあります。これは、ロックの獲得と解放により、内部ロック リソースで競合が発生するためです。 ロックのパーティション分割を行うと、単一のロック リソースが複数のロック リソースに分割されるので、ロックのパフォーマンスが向上します。 この機能は、16 以上の論理 CPU を搭載しているシステムでのみ使用でき、自動的に有効になります。この機能を無効にすることはできません。 パーティション化できるのはオブジェクト ロックだけです。 サブタイプを持つオブジェクト ロックはパーティション化されません。 詳細については、「sys.dm_tran_locks (Transact-SQL)」を参照してください。

ロックのパーティション化について

ロック タスクでは、複数の共有リソースへのアクセスが行われます。これらのうち、次の 2 つがロックのパーティション分割によって最適化されます。

• スピンロック

  行やテーブルなどのロック リソースへのアクセスを制御します。

  ロックのパーティション分割を行わない場合は、1 つのスピンロックにより単一のロック リソースのすべてのロック要求が管理されます。 大量の処理が行われるシステムでは、スピンロックが使用できるようになるまでロック要求が待機するので、競合が発生する場合があります。 この状況では、ロックの獲得がボトルネックになり、パフォーマンスが低下することがあります。

  単一のロック リソースの競合を減らすには、ロックのパーティション分割によって単一のロック リソースを複数のロック リソースに分割し、複数のスピンロックに負荷を分散します。

• メモリ

  ロック リソースの構造を格納するために使用されます。

  スピンロックが獲得されると、ロック構造がメモリに格納されます。その後、ロック構造へのアクセスが行われ、場合によっては変更されることがあります。 ロックへのアクセスを複数のリソースに分散すると、CPU 間でメモリ ブロックを転送する必要がなくなり、パフォーマンスの向上に役立ちます。

ロックのパーティション化の実装と監視

ロックのパーティション分割は、16 基以上の CPU を搭載しているシステムでは既定で有効になっています。 ロックのパーティション化が有効になっていると、情報メッセージが SQL Server エラー ログに記録されます。

パーティション分割されたリソースのロックを獲得するときの規則を次に示します。

• 1 つのパーティションに対して取得されるロック モードは、NL、Sch-S、IS、IU、IX のみです。

• NL、Sch-S、IS、IU、IX モード以外のモードの共有 (S)、排他 (X)、およびその他のロックは、パーティション ID が 0 のパーティションから、パーティション ID 順に取得される必要があります。 これらのパーティション分割されたリソースのロックでは、パーティション化されていないリソースの同じモードのロックよりも、多くのメモリが使用されます。後者のリソースでは、各パーティションが効率的に分割されロックされるためです。 メモリの増加量は、パーティションの数によって決まります。 SQL Server ロック パフォーマンス カウンターには、パーティション分割のロックと非パーティションのロックで使用される、メモリに関する情報が表示されます。

トランザクションは、開始したときにパーティションに割り当てられます。 トランザクションでは、パーティション分割できるすべてのロック要求により、そのトランザクションに割り当てられたパーティションが使用されます。 この方法により、複数のトランザクションから同じオブジェクトのロック リソースへのアクセスが異なるパーティションに分散されます。

sys.dm_tran_locks 動的管理ビューの resource_lock_partition 列により、ロックがパーティション分割されたリソースのロック パーティション ID が提供されます。 詳細については、「sys.dm_tran_locks (Transact-SQL)」を参照してください。

ロックをパーティション化する

次に、ロックのパーティション分割の例を示します。 この例では、16 基の CPU を搭載しているコンピューター システムでのロックのパーティション分割の動作を示すために、2 つのトランザクションを 2 つの異なるセッションで実行します。

これらの Transact-SQL ステートメントは、以下の例で使用するテスト オブジェクトを作成します。

-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO

例 A

セッション 1:

あるトランザクションで SELECT ステートメントが実行されます。 HOLDLOCK ロックのヒントのため、このステートメントは、テーブルの意図共有 (IS) ロックを取得して保持します (この図では、行とページのロックは無視しています)。 IS ロックは、トランザクションに割り当てられているパーティションでのみ取得されます。 この例では、IS ロックが パーティション ID 7 で取得されることが前提です。

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

セッション 2:

トランザクションが開始され、このトランザクションで実行されている SELECT ステートメントは、テーブルの共有 (S) ロックを取得して保持します。 S ロックがすべてのパーティションで取得され、パーティションごとに 1 つずつ、複数のテーブル ロックが作成されます。 たとえば、16 の CPU を搭載しているシステムで、ロック パーティション ID 0 ～ 15 に 16 の S ロックが発行されるとします。 S ロックは、セッション 1 のトランザクションによりパーティション ID 7 に対して保持されている IS ロックと互換性があるので、トランザクション間のブロッキングは発生しません。

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

セッション 1:

セッション 1 において依然としてアクティブなトランザクションで次の SELECT ステートメントが実行されます。 排他的 (X) テーブル ロックのヒントのため、トランザクションはテーブルの X ロックの取得を試みます。 ただし、セッション 2 のトランザクションによって保持されている S ロックは、パーティション ID 0 で X ロックをブロックします。

SELECT col1
FROM TestTable
WITH (TABLOCKX);

例 B

セッション 1:

あるトランザクションで SELECT ステートメントが実行されます。 HOLDLOCK ロックのヒントのため、このステートメントは、テーブルの意図共有 (IS) ロックを取得して保持します (この図では、行とページのロックは無視しています)。 IS ロックは、トランザクションに割り当てられているパーティションでのみ取得されます。 この例では、IS ロックが パーティション ID 6 で取得されることが前提です。

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

セッション 2:

あるトランザクションで SELECT ステートメントが実行されます。 TABLOCKX ロック ヒントにより、このトランザクションではテーブルに対して排他 X) ロックの取得が試行されます。 X ロックはパーティション ID が 0 のパーティションから、パーティション ID 順に取得される必要があることに注意してください。 X ロックは、0 から 5 のすべてのパーティション ID で取得されますが、パーティション ID 6 で取得された IS ロックによってはブロックされます。

X ロックが取得されていないパーティション ID 7 ～ 15 に対しては、他のトランザクションがが引き続きロックを取得できます。

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

データベース エンジンの行のバージョン管理に基づく分離レベル

SQL Server 2005 (9.x) 以降、データベース エンジンでは、既存のトランザクション分離レベルである READ COMMITTED が実装されており、行のバージョン管理を使用してステートメント レベルのスナップショットを提供します。 データベース エンジンでは、行のバージョン管理を使用してトランザクション レベルのスナップショットを提供するトランザクション分離レベル SNAPSHOT も実装されています。

行のバージョン管理とは、行が変更または削除されると書き込み時コピーのメカニズムを起動する、SQL Server の一般的なフレームワークです。 このフレームワークでは、トランザクション内の一貫性に関する以前の状態を必要とするようなトランザクションの実行中に、行の古いバージョンをそのトランザクションで使用できることが求められます。 行のバージョン管理は、次の機能を実装するために使用されます。

• トリガーで inserted テーブルと deleted テーブルを構築する。 トリガーによって変更された行はすべて、バージョン化されます。 これには、トリガーによりデータが変更された行だけでなく、トリガーを起動したステートメントにより変更された行も含まれます。
• 複数のアクティブな結果セット (MARS) をサポートする。 アクティブな結果セットが存在するときに、MARS セッションでデータ変更ステートメント (INSERT、UPDATE、DELETE など) が実行された場合、その変更ステートメントの影響を受けた行はバージョン化されます。
• ONLINE オプションを指定するインデックス操作をサポートする。
• 行のバージョン管理ベースのトランザクション分離レベルをサポートする。
  • 行のバージョン管理を使用してステートメントレベルの読み取りの一貫性を保証する新しい READ COMMITTED 分離レベルの実装。
  • トランザクションレベルで読み取りの一貫性を確保する新しい分離レベル SNAPSHOT。

行バージョンはバージョン ストアに格納されます。 データベース 高速データベース復旧 (ADR) が有効になっている場合、そのデータベースにバージョン ストアが作成されます。 それ以外の場合は、tempdb データベースにバージョン ストアが作成されます。

データベースには、バージョン ストア用に十分な領域が必要です。 バージョン ストアが tempdb にあり、tempdb データベースがいっぱいな場合、更新操作はバージョンの生成を停止し正常に完了されますが、読み取り操作は、必要な特定の行バージョンが存在しないことが理由で失敗する可能性があります。 特定の行のバージョンが存在しないことにより、トリガー、MARS、オンラインのインデックス構築などの操作が影響を受けます。

ADR が使用され、バージョン ストアがいっぱいになると、読み取り操作は引き続き成功しますが、UPDATE や DELETE などのバージョンを生成する書き込み操作は失敗します。 データベースに十分なスペースがある場合、INSERT 操作は引き続き成功します。

READ COMMITTED および SNAPSHOT トランザクションの行のバージョン管理を使用するには、次の 2 段階のプロセスがあります。

1. READ_COMMITTED_SNAPSHOT および ALLOW_SNAPSHOT_ISOLATION データベース オプションの両方を ON に設定します。

2. 次の説明に従って、アプリケーションで適切なトランザクション分離レベルを設定します。

   • READ_COMMITTED_SNAPSHOT データベース オプションが ON の場合、READ COMMITTED 分離レベルが設定されているトランザクションは、行のバージョン管理を使用します。
   • ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON の場合、トランザクションは、SNAPSHOT 分離レベルを設定できます。

READ_COMMITTED_SNAPSHOT または ALLOW_SNAPSHOT_ISOLATION データベース オプションのいずれかが、ON に設定されている場合、データベース エンジンは、トランザクション シーケンス番号 (XNS) を、行のバージョン管理を使用するデータを操作する各トランザクションに割り当てます。 トランザクションは、BEGIN TRANSACTION ステートメントが実行されたときに開始されます。 ただし、トランザクション シーケンス番号が始まるのは、BEGIN TRANSACTION ステートメントの後に実行される最初の読み取り操作または書き込み操作からです。 トランザクション シーケンス番号は、トランザクションに割り当てられるたびに 1 ずつ増加します。

READ_COMMITTED_SNAPSHOT または ALLOW_SNAPSHOT_ISOLATION データベースのいずれかを ON に設定する場合、データベースで行われたすべてのデータ修正に対して、論理コピー (バージョン) が維持されます。 特定のトランザクションが行を修正するたびに、データベース エンジンのインスタンスは、行の以前コミットされたイメージのバージョンをバージョン ストアに格納します。 各バージョンには、その変更を行ったトランザクションのトランザクション シーケンス番号が付きます。 変更された行のバージョンは、リンク リストを使用して連結されます。 最新の行の値は、常に現在のデータベースに格納され、バージョン ストアにあるバージョン管理された行に連結されます。

バージョンストアに格納されているバージョンは、行のバージョン管理に基づく分離レベルで実行されるトランザクションで必要な限り保持されます。 データベース エンジンは、最も古い有用なトランザクション シーケンス番号を追跡し、最も古い有用なシーケンス番号よりも低いトランザクション シーケンス番号でスタンプされたすべての行バージョンを定期的に削除します。

両方のデータベース オプションを OFF に設定すると、トリガーまたは MARS セッションで変更された行、あるいは online index 操作で読み取られた行のみがバージョン管理されます。 これらの行のバージョンは、必要ではなくなった時点で解放されます。 バックグラウンド処理では、古い行バージョンが削除されます。

データ読み取り時の動作

行のバージョン管理に基づく分離レベルで実行されているトランザクションによりデータが読み取られるとき、読み取り操作では、読み取るデータに対して共有 (S) ロックが取得されないので、データを変更しているトランザクションはブロックされません。 また、リソースのロックによるオーバーヘッドは、獲得されるロックの数が少ないほど小さくなります。 行のバージョン管理を使用する READ COMMITTED 分離と SNAPSHOT 分離は、ステートメントレベルまたはトランザクションレベルで、バージョン管理されたデータの読み取りの一貫性を確保します。

行のバージョン管理に基づく分離レベルで実行されているトランザクションを含むすべてのクエリは、コンパイルおよび実行中にスキーマ安定性 (Sch-S) ロックを取得します。 このため、同時実行トランザクションがテーブルのスキーマ修正 (Sch-M) ロックを保持している場合、クエリはブロックされます。 たとえば、データ定義言語 (DDL) 操作では、テーブルのスキーマ情報を変更する前に Sch-M ロックを取得します。 行のバージョン管理に基づく分離レベルで実行されているトランザクションを含むトランザクションは、Sch-S ロックを取得しようとすると、ブロックされます。 一方、Sch-S ロックを保持するクエリは、Sch-M ロックを取得しようとする同時実行トランザクションをブロックします。

SNAPSHOT 分離レベルを使用するトランザクションが開始されると、データベース エンジンのインスタンスにより、現在アクティブなトランザクションがすべて記録されます。 SNAPSHOT トランザクションでバージョン チェーンを持つ行が読み取られると、データベース エンジンは、チェーンを追跡し、次のトランザクション シーケンス番号を持つ行を取得します。

• 行を読み取っているスナップショット トランザクションのシーケンス番号に最も近く、それよりも小さいトランザクション シーケンス番号。

• スナップショット トランザクションが開始されたときのアクティブなトランザクションの一覧にないトランザクション シーケンス番号。

SNAPSHOT トランザクションで実行される Read 操作は、SNAPSHOT トランザクションの開始時にコミットされた各行の最後のバージョンを取得します。 これにより、トランザクション内で一貫性を持つ、トランザクションが開始されたときのデータのスナップショットが提供されます。

行のバージョン管理を使用する READ COMMITTED トランザクションも、ほぼ同じように動作します。 ただし、READ COMMITTED トランザクションでは、行のバージョンを選択するときにトランザクション自体のトランザクション シーケンス番号が使用されないという点が異なります。 ステートメントが開始されるたびに、READ COMMITTED トランザクションは、データベース エンジン インスタンスに対して生成された最新のトランザクション シーケンス番号を読み取ります。 これは、そのステートメントの行のバージョンを選択するために使用されるトランザクション シーケンス番号です。 このトランザクション シーケンス番号により、READ COMMITTED トランザクションは、各ステートメント開始時のデータのスナップショットを参照できるようになります。

データ修正時の動作

データ書き込みの動作は、ロックの最適化が、有効になっている場合となっていない場合とで異なります。

最適化されたロックなしでデータを変更する

行のバージョン管理を使用する READ COMMITTED トランザクションでは、更新する行を選択するときにブロッキング スキャンが使用されます。ブロッキング スキャンでは、データ値を読み取るときにデータ行の更新 (U) ロックが取得されます。 これは、行のバージョン管理を使用しない READ COMMITTED トランザクションでも同様です。 データ行が更新基準を満たさない場合、その行の更新ロックが解除され、次の行がロックされてスキャンされます。

SNAPSHOT 分離レベルで実行されているトランザクションでは、制約を設定するためだけに変更を実行する前に、データのロックを取得することによって、データ変更に楽観的アプローチが使用されます。 それ以外の場合、データの変更が確定するまで、そのデータのロックは獲得されません。 データ行が更新基準を満たしている場合、SNAPSHOT トランザクションにより、その SNAPSHOT トランザクションの開始後にコミットされた同時実行トランザクションでそのデータ行が変更されていないかどうかが確認されます。 データ行が SNAPSHOT トランザクションの外部で変更された場合は、更新の競合が発生し、その SNAPSHOT トランザクションは終了します。 更新の競合はデータベース エンジンによって処理されるので、更新の競合が検出されないようにする方法はありません。

最適化されたロックでデータを変更する

READ_COMMITTED_SNAPSHOT (RCSI) データベース オプションが有効になっている最適化ロックが有効で、既定の READ COMMITTED 分離レベルを使用する場合、閲覧者はロックを取得せず、書き込み者は、トランザクションの終了時に期限切れになるロックではなく、短期間の低レベルのロックを取得します。

最適化されたロックを使用して最も効率よく行うには、RCSI を有効化することをお勧めします。 REPEATABLE READ や SERIALIZABLE などのより厳しい分離レベルを使用すると、データベース エンジンは、閲覧者と書き込み者に対して、トランザクションが終了するまで行ロックやページ ロックを保持するため、ブロックとロック メモリが増加します。

RCSI を有効にし、デフォルトの READ COMMITTED 分離レベルを使用すると、書き込み者は U ロックを取得せずに、行の最新のコミット済みバージョンに基づいて述語ごとに行を修飾します。 クエリは、行が該当し、その行またはページに別のアクティブな書き込みトランザクションがある場合にのみ待機します。 最新のコミット済みバージョンに基づいて修飾し、修飾された行のみをロックすると、ブロックが減り、同時実行性が向上します。

RCSI と既定の READ COMMITTED 分離レベルで更新の競合が検出された場合、お客様のワークロードに影響を与えずに自動的に処理され、再試行されます。

最適化されたロックが有効になっている場合に、SNAPSHOT 分離レベルを使用すると、更新の競合の動作は最適化されたロックがない場合と同じになります。 更新の競合は、アプリケーションによって処理され、再試行される必要があります。

動作のまとめ

次の表に、行のバージョン管理を使用する SNAPSHOT 分離レベルと READ COMMITTED 分離レベルの違いを要約します。

行のバージョン管理用リソースの使用状況

行のバージョン管理フレームワークは、次のデータベース エンジン機能をサポートします。

• トリガー
• 複数のアクティブな結果セット (MARS)
• オンラインのインデックス構築

行のバージョン管理フレームワークは、次の行のバージョン管理ベースのトランザクション分離レベルもサポートします。

• READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合、READ_COMMITTED トランザクションは、行のバージョン管理を使用して、ステートメントレベルの読み取りの一貫性を確保します。
• ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON に設定されている場合、SNAPSHOT トランザクションは、行のバージョン管理を使用して、トランザクションレベルの読み取りの一貫性を確保します。

行のバージョン管理に基づく分離レベルを使用すると、読み取り操作での共有ロックが排除され、トランザクションから取得するロック数が減少します。 その結果、ロックの管理に使用するリソースを節約できるので、システムのパフォーマンスが向上します。 また、他のトランザクションから取得したロックによりトランザクションがブロックされる回数が減少することも、パフォーマンスが向上する要因です。

行のバージョン管理に基づいて分離レベルを使用すると、データの変更に必要なリソースが増加します。 これらのオプションを有効にすると、データベースに対するすべてのデータ変更がバージョン管理されます。 行のバージョン管理に基づく分離を使用したアクティブなトランザクションが存在しない場合でも、変更前のデータのコピーはバージョン ストアに格納されます。 変更後のデータには、バージョン ストア内のバージョン管理されるデータへのポインターが含まれます。 ラージ オブジェクトの場合は、変更されたオブジェクトの一部のみバージョン ストアに格納されます。

tempdb で使用される領域

データベース エンジンの各インスタンスの場合、バージョン ストアに行のバージョンを保持するための十分なスペースが必要です。 データベース管理者は、tempdb およびその他のデータベース (ADR が有効な場合) に、バージョン ストアをサポートするための十分な領域があることを確認する必要があります。 次の 2 種類のバージョン ストアがあります。

• オンライン インデックス構築用バージョン ストアは、オンライン インデックス構築に使用されます。
• 共通バージョン ストアは、その他すべてのデータ修正操作に使用されます。

アクティブなトランザクションで行バージョンにアクセスする必要がある限り、その行バージョンを格納しておく必要があります。 バックグラウンドのスレッドは、定期的に不要になった行バージョンを削除するため、バージョン ストア内の領域が解放されます。 次の条件のいずれかに該当する場合、実行時間の長いトランザクションにより、バージョン ストアの領域の解放が妨げられます。

• トランザクションで、行のバージョン管理に基づく分離が使用されています。
• トランザクションで、トリガー、MARS、またはオンラインのインデックス構築のいずれかの操作が使用されています。
• トランザクションで、行バージョンが生成されます。

バージョン ストアが tempdb にあり、tempdb のスペースが不足すると、データベース エンジンはバージョン ストアを強制的に縮小します。 圧縮処理では、行バージョンをまだ生成していないトランザクションのうち、実行時間が最も長いトランザクションが圧縮対象になります。 圧縮対象のトランザクションごとに、メッセージ 3967 がエラー ログに記録されます。 あるトランザクションが圧縮の対象として設定されると、そのトランザクションではバージョン ストア内の行バージョンを読み取れなくなります。 そのトランザクションから行バージョンを読み取ろうとすると、メッセージ 3966 が生成され、そのトランザクションはロールバックされます。 縮小処理が成功すると、tempdb で領域が使用できるようになります。 失敗した場合は、tempdb の領域が不足し、次のいずれかの現象が発生します。

• 書き込み操作が続行および実行されますが、バージョンが生成されません。 情報メッセージ (3959) がエラー ログに記録されますが、データを書き込むトランザクションは影響を受けません。

• tempdb の完全なロールバックにより生成されなかった行バージョンにアクセスを試行するトランザクションが、エラー 3985 で終了します。

データ行で使用される領域

各データベース行では、行の終わりの最大 14 バイトを行のバージョン管理情報用に使用する場合があります。 行のバージョン管理情報には、そのバージョンでコミットしたトランザクションのトランザクション シーケンス番号と、バージョン管理される行へのポインターが含まれています。 次に示す条件のいずれかに該当する場合、行が最初に変更されたとき、または新しい行が追加されたときに、この 14 バイトが追加されます。

• READ_COMMITTED_SNAPSHOT または ALLOW_SNAPSHOT_ISOLATION オプションは ON に設定されます。
• テーブルにトリガーが含まれています。
• 複数のアクティブな結果セット (MARS) が使用されています。
• オンラインのインデックス構築操作が、現在そのテーブルで実行されています。
• 高速データベース復旧 (ADR) が有効になっています。

次のすべての条件に該当する状況で最初に行が変更されたときに、そのデータベース行からこの 14 バイトが削除されます。

• READ_COMMITTED_SNAPSHOT および ALLOW_SNAPSHOT_ISOLATION のオプションは、OFF に設定されます。
• テーブルに既にトリガーが存在しません。
• MARS が使用されていません。
• オンラインのインデックス構築操作が現在実行されていません。
• 高速データベース復旧 (ADR) が無効になっています。

行のバージョン管理機能を使用する場合は、データベースに追加のディスク領域を割り当て、各データベース行で 14 バイトを使用できるようにする必要があります。 行のバージョン管理用情報が追加された際に、現在のページ上に十分な空き領域がない場合、インデックス ページの分割や、新しいデータ ページの割り当てが発生します。 たとえば、行の平均の長さが 100 バイトの場合は、14 バイト追加されると既存のテーブルが最大 14% 大きくなります。

FILL FACTOR を小さくすると、インデックス ページの断片化を防止または低減するのに役立ちます。 テーブルまたはビューのデータとインデックスに関する最新のページ密度情報を表示するには、sys.dm_db_index_physical_stats を使用できます。

永続バージョン ストア (PVS) で使用される領域

ADR を有効にすると、変更前の行のサイズに応じて、次のいずれかの方法で行バージョンを永続バージョン ストア (PVS) に格納できます。

• サイズが小さい場合、古い行バージョン全体が変更された行の一部として格納されます。
• サイズが中間の場合、古い行バージョンと変更された行の違いは、変更された行の一部として格納されます。 この違いは、データベース エンジンが必要に応じて古い行バージョン全体を再構築できるように構築されています。
• サイズが大きい場合、古い行バージョン全体が別の内部テーブルに格納されます。

最初の2つのメソッドは、行内バージョンストレージのと呼ばれます。 最後の方法は、行外 バージョンストレージと呼ばれます。 行内バージョンが不要になると、ページ上の領域を解放するために削除されます。 同様に、行外バージョンが不要になった内部テーブル内のページは、バージョン クリーナーによって削除されます。

行の一部として行バージョンを格納すると、行バージョンを読み取る必要があるトランザクションによるデータ取得が最適化されます。 バージョンが行に格納されている場合、行外 PVS ページの個別の読み取りは必要ありません。

sys.dm_db_index_physical_stats DMV は、インデックスのパーティションの行内および行外に格納されているバージョンの数と種類を提供します。 行に格納されているバージョン データの合計サイズは、total_inrow_version_payload_size_in_bytes 列に報告されます。

行外バージョンのストレージのサイズは、persistent_version_store_size_kb DMV の 列で報告されます。

ラージ オブジェクトで使用される領域

データベース エンジンは、最大 2 GB の大きな文字列を保持できるいくつかのデータ型 (nvarchar(max)、varchar(max)、varbinary(max)、ntext、text、image など) をサポートします。 これらのデータ型で格納された大きなデータは、データ行にリンクされている一連のデータ フラグメントに格納されます。 行のバージョン管理情報は、これらの大きな文字列の格納に使用される各フラグメントに格納されます。 データ フラグメントは、テーブル内のラージ オブジェクト専用のページのセットに格納されます。

新しい大きな値がデータベースに追加されたときに、データ フラグメントには、1 つのフラグメントにつき最大 8,040 バイトのデータが割り当てられます。 データベース エンジンの以前のバージョンは、1 つのフラグメントにつき最大 8,080 バイトの ntext、text または image データを格納していました。

既存の ntext、text および image ラージ オブジェクト (LOB) データは、データベースが以前のバージョンの SQL Server から SQL Server にアップグレードされても、行のバージョン管理情報用の領域を確保するために更新されることはありません。 ただし、これらの LOB データが最初に変更されたときに、バージョン管理情報の領域を使用できるように動的にアップグレードされます。 これは、行のバージョンが生成されない場合でも発生します。 LOB データがアップグレードされた後、1 フラグメントに格納されている最大バイト数が 8,080 バイトから 8,040 バイトに減少します。 このアップグレード処理は、LOB 値を削除し、再度同じ値を挿入する処理に相当します。 LOB データは、1 バイトしか変更されない場合でもアップグレードされます。 このアップグレードは、ntext 型、text 型、または image 型の各列で 1 回だけ実行される操作ですが、LOB データのサイズによっては、大量のページが割り当てられたり、大量の I/O 処理が実行されたりする場合があります。 また、変更が完全にログに記録される場合、ログ処理が膨大になる場合があります。 WRITETEXT 操作および UPDATETEXT 操作を使用すると、データベース復旧モデルが [FULL] に設定されていない場合、ログ記録を最小限に抑えることができます。

この要件を満たすには、十分なディスク領域を割り当てる必要があります。

行のバージョン管理とバージョン ストアを監視する

データベース エンジンでは、パフォーマンスや問題について、行のバージョン管理、バージョン ストア、およびスナップショット分離のプロセスを監視するために、動的管理ビュー (DMV) とパフォーマンス カウンターというツールが用意されています。

DMV

次に示す DMV からは、行のバージョン管理を使用しているトランザクションについての情報だけではなく、tempdb の現在のシステム状態とバージョン ストアについての情報が提供されます。

• sys.dm_db_file_space_usage。 データベース内の各ファイルに関する使用領域の情報を返します。 詳細については、「sys.dm_db_file_space_usage (Transact-SQL)」を参照してください。

• sys.dm_db_session_space_usage。 データベースのセッション別に、ページの割り当てと割り当て解除の状態を返します。 詳細については、「sys.dm_db_session_space_usage (Transact-SQL)」を参照してください。

• sys.dm_db_task_space_usage。 データベースに対するタスクごとに、ページの割り当てと割り当て解除の処理に関する情報を返します。 詳細については、「sys.dm_db_task_space_usage (Transact-SQL)」を参照してください。

• sys.dm_tran_top_version_generators。 バージョン ストア内で最も高いバージョンを生成しているオブジェクトの仮想テーブルを返します。 集計済みのレコード長について、長いものから順に 256 位までを database_id と rowset_id でグループ化しています。 この関数を使用して、バージョン ストアを最も多く使用しているレコードを見つけます。 tempdb のバージョン ストアのみに適用します。 詳細については、「sys.dm_tran_top_version_generators (Transact-SQL)」を参照してください。

• sys.dm_tran_version_store。 共通バージョン ストア内のすべてのバージョン レコードを表す仮想テーブルを返します。 tempdb のバージョン ストアのみに適用します。 詳細については、「sys.dm_tran_version_store (Transact-SQL)」を参照してください。

• sys.dm_tran_version_store_space_usage。 各データベースのバージョン ストア レコードで使われている tempdb の合計スペースを表示する仮想テーブルを返します。 tempdb のバージョン ストアのみに適用します。 詳細については、「sys.dm_tran_version_store_space_usage (Transact-SQL)」を参照してください。

  注

  sys.dm_tran_top_version_generators と sys.dm_tran_version_store のクエリは、どちらも (大きなサイズとなり得る) バージョン ストア全体をスキャンするため、コストが高くなる場合があります。 sys.dm_tran_version_store_space_usage は、バージョン ストア レコードを個別にナビゲートせず、代わりにデータベースごとに tempdb で消費されるバージョン ストア スペースの集計を返すため、効率的で実行コストも高くありません。

• sys.dm_tran_active_snapshot_database_transactions。 データベース内のすべてのアクティブなトランザクションを表す仮想テーブルを返します。行のバージョン管理を使用する SQL Server インスタンス内のすべてのデータベースが対象です。 システム トランザクションは、この DMV には表示されません。 詳細については、「sys.dm_tran_active_snapshot_database_transactions (Transact-SQL)」を参照してください。

• sys.dm_tran_transactions_snapshot。 トランザクションごとに作成されたスナップショットを表す仮想テーブルを返します。 このスナップショットには、行のバージョン管理を使用するアクティブなトランザクションのシーケンス番号が含まれています。 詳細については、「sys.dm_tran_transactions_snapshot (Transact-SQL)」を参照してください。

• sys.dm_tran_current_transaction。 現在のセッションにおけるトランザクションの行のバージョン管理に関係した状態情報を表す 1 行を返します。 詳細については、「sys.dm_tran_current_transaction (Transact-SQL)」を参照してください。

• sys.dm_tran_current_snapshot。 現在のスナップショット分離トランザクションの開始時点でアクティブなすべてのトランザクションを表す仮想テーブルを返します。 現在のトランザクションでスナップショット分離が使用されている場合、この関数は行を返しません。 DMV sys.dm_tran_current_snapshot は sys.dm_tran_transactions_snapshot に似ていますが、現在のスナップショットのアクティブなトランザクションのみを返す点が異なります。 詳細については、「sys.dm_tran_current_snapshot (Transact-SQL)」を参照してください。

• sys.dm_tran_persistent_version_store_stats。 高速データベース復旧が有効な場合に使用される各データベースの永続バージョン ストアの統計を返します。 詳細については、「sys.dm_tran_persistent_version_store_stats (Transact-SQL)」を参照してください。

パフォーマンス カウンター

次に示すパフォーマンス カウンターでは、行のバージョン管理を使用しているトランザクションだけではなく、tempdb のバージョン ストアも監視します。 パフォーマンス カウンターは、SQLServer:Transactions パフォーマンス オブジェクトに含まれています。

• Free Space in tempdb (KB)。 tempdb データベースの空き領域を KB 単位で監視します。 tempdb には、スナップショット分離をサポートするバージョン ストアを処理できるだけの十分な空き領域が必要です。

  次の式を使用すると、バージョン ストアのサイズを概算することができます。 実行時間の長いトランザクションの場合、生成率とクリーンアップ率を監視してバージョン ストアの最大サイズを推定すると有益な場合があります。

  [共通バージョン ストアのサイズ] = 2 * [毎分生成されるバージョン ストア データ] * [トランザクションの最長実行時間 (分)]

  実行時間が極端に長いトランザクションには、オンラインのインデックス構築操作を含めないでください。 そのような場合のオンラインのインデックス構築操作は、非常に大きなテーブルでは時間がかかる場合があるので、別のバージョン ストアを使用します。 オンライン インデックス構築用のバージョン ストアの大まかなサイズは、オンラインのインデックス構築がアクティブになっている間にテーブル内で変更されたデータ (すべてのインデックスを含む) の量と同じです。

• Version Store Size (KB)。 tempdb のすべてのバージョン ストアのサイズを KB 単位で監視します。 この情報は、tempdb データベースに必要なバージョン ストア用の領域のサイズを判断する際に役立ちます。 このカウンターを長期間監視すると、tempdb に必要な領域を追加する際に役立つ推定値が得られます。

• Version Generation rate (KB/s)。 tempdb のすべてのバージョン ストアでバージョン生成率 (KB/秒) を監視します。

• Version Cleanup rate (KB/s)。 tempdb のすべてのバージョン ストアでバージョンクリーンアップ率 (KB/秒) を監視します。

  注

  Version Generation rate (KB/s) と Version Cleanup rate (KB/s) から得た情報を、tempdb に必要な領域の予測に利用できます。

• Version Store unit count。 バージョン ストア ユニットの数を監視します。

• Version Store unit creation。 インスタンスの開始以降に行バージョンを格納するために作成されたバージョン ストア ユニットの総数を監視します。

• Version Store unit truncation。 インスタンスの開始以降に切り捨てられたバージョン ストア ユニットの総数を監視します。 バージョン ストア ユニットは、バージョン ストア内に格納されているバージョン行が SQL Server によりアクティブなトランザクションの実行に不要と判断された場合に切り捨てられます。

• Update conflict ratio。 更新スナップショット トランザクションの総数に対し、更新に関して競合が発生している更新スナップショット トランザクションの割合を監視します。

• Longest Transaction Running Time。 行のバージョン管理を使用しているトランザクションの最長実行時間 (秒) を監視します。 これは、トランザクションの実行時間が想定外でないかどうかを判断するために使用します。

• Transactions。 アクティブなトランザクションの総数を監視します。 システム トランザクションは含まれません。

• Snapshot Transactions。 アクティブなスナップショット トランザクションの総数を監視します。

• Update Snapshot Transactions。 更新操作を実行するアクティブなスナップショット トランザクションの総数を監視します。

• NonSnapshot Version Transactions。 バージョン レコードを生成する、スナップショット以外のアクティブなトランザクションの総数を監視します。

  注

  Update Snapshot Transactions と NonSnapshot Version Transactions の合計は、バージョンの生成に関係するトランザクションの総数を表します。 Snapshot Transactions と Update Snapshot Transactions の差分は、読み取り専用のトランザクション数を表します。

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

以下の例は、SNAPSHOT 分離トランザクションと、行のバージョン管理を使用する READ COMMITTED トランザクションとの動作の違いを示しています。

A. スナップショット分離の作業

この例では、SNAPSHOT 分離で実行されているトランザクションが、その後、別のトランザクションが修正するデータを読み取ります。 SNAPSHOT トランザクションでは、別のトランザクションで実行される更新操作をブロックしないで、バージョン管理される行から引き続きデータを読み取り、データの変更が無視されます。 ただし、SNAPSHOT トランザクションが、別のトランザクションによって既に変更されているデータの変更を試みた場合は、その SNAPSHOT トランザクションがエラーを生成し、終了します。

セッション 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

セッション 2 :

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

セッション 1:

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

セッション 2 :

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

セッション 1:

-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

B: 行のバージョン管理を使用して READ COMMITTED 分離を操作する

この例では、行のバージョン管理を使用する READ COMMITTED トランザクションを、別のトランザクションと同時に実行しています。 READ COMMITTED トランザクションは、SNAPSHOT トランザクションとは異なる動作をします。 SNAPSHOT トランザクションのように、READ COMMITTED トランザクションは、別のトランザクションがデータを修正した後でもバージョン管理された行を読み取ります。 しかし、SNAPSHOT トランザクションとは異なり、READ COMMITTED トランザクションは以下を実行します。

• 別のトランザクションがデータ守勢をコミットした後に修正されたデータを読み取る。
• SNAPSHOT トランザクションが修正できなかったが他のトランザクションが修正したデータを更新できる。

セッション 1:

USE AdventureWorks2022;
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

セッション 2 :

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

セッション 1:

-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

セッション 2 :

-- Commit the transaction.
COMMIT TRANSACTION;
GO

セッション 1:

-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

行のバージョン管理に基づく分離レベルを有効化する

データベース管理者は、ALTER DATABASE ステートメントの READ_COMMITTED_SNAPSHOT と ALLOW_SNAPSHOT_ISOLATION データベース オプションを使用して、行のバージョンの設定をデータベースレベルで制御します。

READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合、このオプションのサポートに使用されるメカニズムが直ちにアクティブになります。 READ_COMMITTED_SNAPSHOT オプションを設定すると、ALTER DATABASE コマンドを実行する接続のみがデータベースで許可されます。 ALTER DATABASE が完了するまで、データベースでその他の解放された接続はあってはなりません。 データベースがシングル ユーザー モードになっている必要はありません。

次の Transact-SQL ステートメントは、READ_COMMITTED_SNAPSHOT を有効にします。

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON に設定されている場合、データベース エンジンのインスタンスは、修正されたデータがあるすべてのアクティブなトランザクションが完了するまで、修正されたデータの行バージョンを生成しません。 アクティブな修正トランザクションがある場合、データベース エンジンは、オプションの状態を PENDING_ON に設定します。 すべての修正トランザクションが完了したら、オプションの状態は ON に変わります。 ユーザーは、オプション が ON になるまで、データベースの SNAPSHOT トランザクションを開始できません。 同様に、データベース管理者が ALLOW_SNAPSHOT_ISOLATION オプションを OFF に設定すると、データベースは、PENDING_OFF 状態を渡します。

次の Transact-SQL ステートメントは、ALLOW_SNAPSHOT_ISOLATION を有効にします。

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

次の表で、ALLOW_SNAPSHOT_ISOLATION オプションの状態を一覧し、説明します。 ALLOW_SNAPSHOT_ISOLATION オプション付きの ALTER DATABASE を使用しても、現在データベース データにアクセスしているユーザーはブロックされません。

行のバージョン管理データベース オプションの両方の状態を判断するには、sys.databases カタログ ビューを使用します。

ユーザー テーブルと master と msdb に格納されている一部のシステム テーブルへのすべての更新は、行のバージョンを生成します。

ALLOW_SNAPSHOT_ISOLATION オプションは、master と msdb のデータベースで自動的に ON に設定され、無効にできません。

ユーザーは、master、tempdb または msdb で READ_COMMITTED_SNAPSHOT オプションを ON に設定できません。

行のバージョン管理ベースの分離レベルを使用する

行のバージョン管理フレームワークは、常に有効で複数の機能によって使用されます。 このフレームワークは、行のバージョン管理に基づく分離レベルを提供するだけでなく、トリガーと複数のアクティブな結果セット (MARS) セッションで行われた変更のサポートおよび online index 操作でのデータ読み取りのサポートに使用されます。

行のバージョン管理に基づく分離レベルは、データベース レベルで有効になっています。 この分離レベルが有効になっているデータベースのオブジェクトにアクセスするアプリケーションでは、次の分離レベルを使用してクエリを実行できます。

• 次のコード例で示されているように READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている行のバージョン管理を使用する READ COMMITTED。

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

  データベースで READ_COMMITTED_SNAPSHOT が有効になっている場合、READ COMMITTED 分離レベルで実行されているすべてのクエリで行のバージョン管理が使用されます。つまり、Read 操作により Update 操作がブロックされることはありません。

• 次のコード例に示すように、ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON に設定されている SNAPSHOT 分離。

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  データベース間クエリを使用する場合、SNAPSHOT 分離で実行されているトランザクションは、ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON に設定されているデータベース内のテーブルにアクセスできます。 ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON に設定されていないデータベースのテーブルにアクセスするには、分離レベルを変更する必要があります。 たとえば、次のコード例は、SNAPSHOT トランザクションで実行中に 2 つのテーブルを結合する SELECT ステートメントを示しています。 1 つのテーブルは、SNAPSHOT 分離が無効なデータベースに属しています。 SNAPSHOT分離で SELECT ステートメントを実行すると、実行に失敗します。

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

  次のコード例は、特定のテーブルにアクセスするときにトランザクション分離レベルを READ COMMITTED に変更するように修正された、同じ SELECT ステートメントを示しています。 この変更により、SELECT ステートメントは正常に実行されます。

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1 WITH (READCOMMITTED)
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

行のバージョン管理に基づく分離レベルを使用したトランザクションの制限事項

行のバージョン管理に基づく分離レベルを使用して作業する場合は、次の制限事項を考慮してください。

• READ_COMMITTED_SNAPSHOT は tempdb、msdb、または master で有効にすることはできません。

• グローバルな一時テーブルは tempdb に格納されます。 SNAPSHOT トランザクション内でグローバルな一時テーブルにアクセスする場合は、次のいずれかの操作を行う必要があります。

  • tempdb で、ALLOW_SNAPSHOT_ISOLATION データベース オプション を ON に設定します。
  • 分離ヒントを使用して、ステートメントの分離レベルを変更します。

• SNAPSHOT トランザクションは次の場合に失敗します。

  • SNAPSHOT トランザクションが開始してから SNAPSHOT トランザクションがデータベースにアクセスするまで、データベースが読み取り専用である場合。
  • 複数のデータベースからオブジェクトにアクセスする場合、SNAPSHOT トランザクションが開始してから、SNAPSHOT トランザクションがデータベースにアクセスする前にデータベースが回復するように、データベースの状態が変更された場合。 たとえば、データベースが OFFLINE に設定された後 ONLINE に設定され、AUTO_CLOSE オプションが ON に設定されたか、データベースが切断し、再接続されたことが原因で、データベースが自動でクローズまたはリオープンされた場合が挙げられます。

• 分散パーティション化データベースのクエリを含む分散トランザクションは、SNAPSHOT 分離でサポートされていません。

• データベース エンジンは、システム メタデータの複数のバージョンを保持しません。 テーブルおよび他のデータベース オブジェクト (インデックス、ビュー、データ型、ストアド プロシージャ、および共通言語ランタイム関数) のデータ定義言語 (DDL) ステートメントにより、メタデータが変更されます。 DDL ステートメントでオブジェクトを変更する場合、SNAPSHOT 分離のオブジェクトを同時に参照すると、SNAPSHOT トランザクションが失敗します。 READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合、READ COMMITTED トランザクションにこの制限は適用されません。

  たとえば、データベース管理者が、次の ALTER INDEX ステートメントを実行したとします。

  USE AdventureWorks2022;
  GO
  ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD;
  GO
  

  ALTER INDEX ステートメントの実行時にアクティブなスナップショット トランザクションでは、HumanResources.Employee ステートメントの実行後に ALTER INDEX テーブルを参照すると、エラーが発生します。 行のバージョン管理を使用する READ COMMITTED トランザクションは影響されません。

  注

  BULK INSERT 操作により、対象のテーブルのメタデータが変更される場合があります (例: 制約チェックを無効にする場合など)。 これが発生すると、一括挿入されたテーブルにアクセスする同時実行 SNAPSHOT 分離トランザクションは失敗します。

ロックと行のバージョン管理をカスタマイズする

ロック タイムアウトをカスタマイズする

別のトランザクションがすでに、リソースで競合ロックを所有していることが原因で、データベース エンジンのインスタンスがトランザクションのロックを許可できない場合、最初のトランザクションは、ブロックされ、既存のロックが解放されるまで待機状態になります。 既定では、ロック待機のタイムアウト期間は存在しないため、トランザクションは無期限にブロックされる場合があります。

LOCK_TIMEOUT 設定により、ブロックされたリソースをステートメントが待機する最大時間をアプリケーションから設定できます。 ステートメントが LOCK_TIMEOUT 設定よりも長く待機している場合、ブロックされたステートメントは、自動的にキャンセルされ、エラー メッセージ 1222 (Lock request time-out period exceeded) が返されます。 ただし、ステートメントを含むトランザクションは、ロールバックされません。 したがって、アプリケーションはエラー メッセージ 1222 をトラップできるエラー ハンドラーを備えている必要があります。 アプリケーションがエラーをトラップしない場合、アプリケーションは、トランザクション内の個々のステートメントがキャンセルされたことに気づかないまま処理を続行できますが、トランザクションはアクティブなままです。 トランザクションの後半にあるステートメントが、実行されなかったステートメントに依存している場合があるため、エラーが発生することがあります。

エラー メッセージ 1222 をトラップするエラー ハンドラーを実装すると、アプリケーションでタイムアウト状況を処理し、ブロックされたステートメントを自動的に再実行したりトランザクション全体をロールバックするなどの救済措置を講じることができます。

現在の LOCK_TIMEOUT 設定を調べるには、@@LOCK_TIMEOUT 関数を実行します。

SELECT @@LOCK_TIMEOUT;
GO

トランザクション分離レベルをカスタマイズする

READ COMMITTED は、データベース エンジンの既定の分離レベルです。 アプリケーションを異なる分離レベルで動作させる必要がある場合、次の方法を使用して分離レベルを設定できます。

• SET TRANSACTION ISOLATION LEVEL ステートメントを実行します。
• System.Data.SqlClient 名前空間を使用する ADO.NET アプリケーションは、SqlConnection.BeginTransaction メソッドを使用して IsolationLevel オプションを指定できます。
• ADO を使用するアプリケーションでは、Autocommit Isolation Levels プロパティを設定できます。
• トランザクションの開始時、OLE DB を使用するアプリケーションは、isoLevel が目的のトランザクション分離レベルに設定されている状態の ITransactionLocal::StartTransaction を呼び出せます。 オートコミット モードで分離レベルを指定する場合、OLE DB を使用するアプリケーションは、DBPROPSET_SESSION[プロパティ]DBPROP_SESS_AUTOCOMMITISOLEVELS を目的のトランザクション分離レベルに設定できます。
• ODBC を使用するアプリケーションは、SQLSetConnectAttr を使用して SQL_COPT_SS_TXN_ISOLATION 属性を設定できます。

分離レベルを指定すると、セッション内のすべてのクエリとデータ操作言語 (DML) ステートメントに対するロック動作がその分離レベルで動作します。 分離レベルは、セッションが終了するか、または分離レベルが別のレベルに設定されるまで有効です。

次の例では、SERIALIZABLE 分離レベルを設定します。

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

分離レベルは、必要に応じて個別のクエリまたは DML ステートメントでテーブル レベルのヒントを指定することによりオーバーライドできます。 テーブル レベルのヒントを指定しても、セッション内の他のステートメントに影響はありません。

現在設定されているトランザクション分離レベルを特定するには、次の例に示すように、DBCC USEROPTIONS ステートメントを使用します。 次に示す結果セットは、使用中のシステムの結果セットとは異なる場合があります。

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

結果セットは次のとおりです。

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

ロック ヒント

SELECT、INSERT、UPDATE、DELETE、MERGE の各ステートメント内で参照する個別のテーブルにロック ヒントを指定できます。 ロック ヒントは、データベース エンジンのインスタンスがテーブル データに使用するロックの種類または行のバージョン管理を指定します。 テーブルレベルのロック ヒントは、オブジェクトにかけるロックの種類を詳細に制御する場合に使用できます。 これらのロック ヒントは、セッションの現在のトランザクション分離レベルをオーバーライドします。

特定のロック ヒントとその動作の詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

データ読み取り時に共有ロックの要求を回避するロック ヒントが指定されたステートメントを処理していても、データベース エンジンは、メタデータの読み取り時にロックを取得する必要がある場合があります。 たとえば、READ UNCOMMITTED 分離レベルで実行中、または NOLOCK ヒントを使用する SELECT ステートメントは、データ読み取り時に共有ろっくを取得しませんが、システム カタログ ビューの読み取り時にロックを要求することがあります。 つまり、同時実行トランザクションがテーブルのメタデータを修正時に、このような SELECT ステートメントがブロックされる場合があります。

次の例で示すように、トランザクション分離レベルが SERIALIZABLE に設定され、テーブルレベルのロック ヒント NOLOCK が SELECT ステートメントで使用されている場合、一般的に SERIALIZABLE トランザクションの維持に使用されるキー範囲ロックは、取得されません。

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
       resource_subtype,
       request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

-- End the transaction.
ROLLBACK;
GO

取得されるロックのうち、HumanResources.Employee を参照するロックは、スキーマ安定性 (Sch-S) ロックのみです。 この場合、シリアル化可能性は保証されません。

ロックのエスカレーション中、ALTER TABLE の LOCK_ESCALATION オプションは、テーブル ロックを回避し、パーティション化テーブルで、HoBT (パーティション) ロックを有効化します。 このオプションは、ロック ヒントではありませんが、ロックのエスカレーションを減らすために使用できます。 詳細については、「ALTER TABLE (Transact-SQL)」を参照してください。

インデックスのロックをカスタマイズする

データベース エンジンは、ほとんどの場合クエリに最適なロック粒度を自動選択する動的ロック戦略を使用します。 テーブルまたはインデックス アクセス パターンをよく理解していない場合、一貫性が無い場合、解決しなければならないリソースの競合がある場合、既定のロック レベルをオーバーライドしないことをお勧めします。 ロック レベルをオーバーライドすると、テーブルまたはインデックスへの同時アクセスのパフォーマンスが大きく低下することがあります。 たとえば、ユーザーが頻繁にアクセスする大きなテーブルに対してテーブルレベルのロックのみを指定すると、ボトルネックが発生します。これは、ユーザーがテーブルにアクセスする前に、テーブルレベルのロックが解除されるのを待たなければならなくなるためです。

アクセス パターンが一定していることがわかっている場合、ページまたは行のロックを禁止することが効果的なケースもいくつかあります。 たとえば、あるデータベース アプリケーションでバッチ処理により週単位で更新される参照テーブルを使用しているとします。 共有 (S) ロックが指定されているテーブルに、閲覧者が同時にアクセスし、排他 (X) ロックが指定されているテーブルに週次バッチ更新がアクセスします。 テーブル上でページと行のロックをオフにし、読み取り側が共有テーブル ロックを通過して同時にテーブルにアクセスできるようにすることで、週を通したロック オーバーヘッドを小さくできます。 バッチ ジョブの実行時には、排他テーブル ロックを獲得できるので、効率的に更新を完了できます。

ページと行のロックを無効にすることは、一部のケースでは許容されるかもしれませんが、週単位のバッチ更新が実行されている間に同時閲覧者がテーブルにアクセスできなくなるため、許容されない場合もあります。 バッチ ジョブによっていくつかの行またはページのみが変更される場合は、行またはページ レベルのロックを許可するようにロック レベルを変更できます。この場合、他のセッションはブロックされることなくテーブルから読み出しを行うことができます。 バッチ ジョブで大量の更新を扱う場合は、バッチ ジョブを効率的に実行するためにテーブル上で排他ロックを取得することが最善の方法です。

一部のワークロードでは、2 つの同時実行操作が同じテーブルの行ロックを取得し、ページをロックする必要があるため、互いにブロックしたときに、デッドロックの種類が発生することがあります。 行ロックを禁止することで、どちらかの処理が待機するようになるため、デッドロックを回避できます。 デッドロックの詳細については、「デッドロックのガイド」を参照してください。

インデックスで使用するロックの粒度は、CREATE INDEX ステートメントまたは ALTER INDEX ステートメントを使用して設定できます。 また、CREATE TABLE ステートメントと ALTER TABLE ステートメントを使用して、PRIMARY KEY 制約および UNIQUE 制約によるロック粒度を設定することもできます。 下位互換性の場合、sp_indexoption システム ストアド プロシージャは、粒度も設定できます。 特定のインデックスの現在のロックのオプションを表示するには、INDEXPROPERTY 関数を使用します。 特定のインデックスに対して、ページレベルのロック、行レベルのロック、またはその両方のロックを禁止することができます。

詳細なトランザクション情報

トランザクションを入れ子する

明示的なトランザクションは入れ子にすることができます。 これは、トランザクション内の既存のプロセスからでもアクティブ トランザクションがないプロセスからでも呼び出せるストアド プロシージャ内のトランザクションをサポートすることを主な目的としています。

次の例では、入れ子されたトランザクションの使用法を示しています。 トランザクションがアクティブな際に、TransProc を呼び出す場合、TransProc で入れ子されたトランザクションの成果は、外側のトランザクションによって制御され、その INSERT ステートメントは、外側のトランザクションのコミットまたはロールバックにもとづいて、コミットまたはロールバックされます。 TransProc が、未処理のトランザクションがないプロセスによって実行される場合、プロシージャの最後にある COMMIT TRANSACTION は、INSERT ステートメントをコミットします。

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

外側のトランザクションがアクティブな場合、データベース エンジンは、内側のトランザクションのコミットを無視します。 トランザクションは、もっとも外側のトランザクションの最後に、コミットまたはロールバックにもとづいてコミットまたはロールバックされます。 外側のトランザクションがコミットされると、入れ子の内側のトランザクションもコミットされます。 外側のトランザクションがロールバックされると、内側のトランザクションも、個々がコミットされたかどうかに関係なくすべてロールバックされます。

COMMIT TRANSACTION または COMMIT WORK への各呼び出しは、最後に実行された BEGIN TRANSACTION に適用されます。 BEGIN TRANSACTION ステートメントが入れ子になっている場合、最後の入れ子になっているトランザクション、つまり最も内側のトランザクションだけに COMMIT ステートメントが適用されます。 入れ子されたトランザクション内の COMMIT TRANSACTION transaction_name ステートメントが外側のトランザクションのトランザクション名を参照していても、コミットは、最も内側のトランザクションのみに適用されます。

ROLLBACK TRANSACTION ステートメントの transaction_name パラメータは、名前付きの入れ子されたトランザクションの一式の内側のトランザクションを参照できません。 transaction_name は、最も外側のトランザクションのトランザクション名のみを参照できます。 入れ子された一連のトランザクションの任意レベルで、外側のトランザクション名を使用する ROLLBACK TRANSACTION transaction_name ステートメントが実行された場合、入れ子されたトランザクションはすべてロールバックされます。 入れ子されたトランザクションの任意のレベルで、transaction_name パラメータなしで ROLLBACK WORK または ROLLBACK TRANSACTION ステートメントを実行した場合、もっとも外側のトランザクションを含めすべてのトランザクションがロールバックされます。

@@TRANCOUNT 関数は、現在のトランザクションの入れ子レベルを記録します。 @@TRANCOUNT の値は、BEGIN TRANSACTION ステートメントが実行されるごとに 1 ずつ増加します。 COMMIT TRANSACTION ステートメントまたは COMMIT WORK ステートメントが実行されると、@@TRANCOUNT が 1 ずつ減少します。 トランザクション名がない ROLLBACK WORK または ROLLBACK TRANSACTION ステートメントは、入れ子されたすべてのトランザクションをロールバックし、@@TRANCOUNT を 0 にデクリメントします。 ROLLBACK TRANSACTION で、入れ子構造になっている一連のトランザクションの最も外側のトランザクションの名前を指定すると、入れ子構造の内側のトランザクションすべてがロールバックされ、@@TRANCOUNT は 0 まで減少します。 すでにトランザクションを実行中であるかどうかを確認するには、SELECT @@TRANCOUNT が 1 かそれ以上であるかを確認します。 @@TRANCOUNT が 0 であれば、トランザクションの実行中ではありません。

バインドされたセッションを使用する

バインドされたセッションを使用すると、同一サーバー上の複数のセッションにまたがるアクションの調整が容易になります。 バインドされたセッションでは、2 つ以上のセッションで同じトランザクションとロックを共有できます。また、ロックの競合が発生しないで同じデータを操作できます。 バインドされたセッションは、同じアプリケーション内の複数のセッションからも、セッションが異なる複数のアプリケーションからも作成できます。

バインドされたセッションに参加するには、セッションから (Open Data Services 経由で) sp_getbindtoken または srv_getbindtoken を呼び出し、バインド トークンを取得します。 バインド トークンは、バインドされたトランザクションをそれぞれ一意に識別する文字列です。 取得したバインド トークンは、現在のセッションにバインドされる他のセッションに送信されます。 その他のセッションは、最初のセッションから受け取ったバインド トークンを使用して、sp_bindsession を呼び出してトランザクションにバインドします。

最初のセッションを作成するアプリケーション コードから、その後最初のセッションに他のセッションをバインドするアプリケーション コードに、バインド トークンが転送される必要があります。 別のプロセスが開始したトランザクション向けにバインド トークンを取得するためにアプリケーションを使用できる Transact-SQL または API 関数はありません。 バインド トークンは、次に示す方法を使用して転送できます。

• バインドされるセッションがすべて同じアプリケーション プロセスから開始されている場合、バインド トークンをグローバル メモリに格納するか、パラメーターとして関数に渡すことができます。

• バインドされるセッションが異なるアプリケーション プロセスで作成されている場合、RPC (リモート プロシージャ コール) や DDE (動的データ交換) などの IPC (プロセス間通信) を使用してバインド トークンを転送できます。

• バインド トークンは、データベース エンジンのインスタンス内のテーブルに格納でき、最初のセッションにバインドするプロセスが読み取ることができます。

バインドされたセッションのうち、一度にアクティブにできるのは 1 つだけです。 あるセッションが、インスタンスでステートメントを実行している、またはインスタンスからの結果が保留中の場合、同じトークンにバインドされているその他のセッションは、現在のセッションがプロセスを終了するか、現在のステータスをキャンセルするまでそのインスタンスにアクセスできません。 そのインスタンスでバインドされた別のセッションからのステートメントが処理されていてビジー状態の場合、トランザクション領域が使用中なのでそのセッションを後で再試行する必要があることを示すエラーが発生します。

セッションをバインドするときに、各セッションの分離レベル設定が保持されます。 SET TRANSACTION ISOLATION LEVEL を使用して、1 つのセッションの分離レベル設定を変更しても、同じトークンにバインドされている他のセッションの設定には影響しません。

バインドされたセッションの種類

バインドされたセッションには "ローカル" と "分散" の 2 種類があります。

• バインドされたローカル セッション バインドされたセッションは、データベース エンジンの 1 つのインスタンスにある 1 つのトランザクションのトランザクション領域を共有できます。

• バインドされた分散セッション Microsoft 分散トランザクション コーディネーター (MS DTC) を使用してトランザクション全体がコミットまたはロールバックされるまで、バインドされたトランザクションは、2 つ以上のインスタンス間で同じトランザクションを共有します。

バインドされた分散セッションは、文字列のバインド トークンによって識別されるのではなく、分散トランザクション ID 番号によって識別されます。 バインドされたセッションがローカル トランザクションに関係していて、リモート サーバーで SET REMOTE_PROC_TRANSACTIONS ON を指定して RPC を実行している場合、MS DTC により、バインドされたローカル トランザクションがバインドされた分散トランザクションに自動的に昇格し、MS DTC セッションが開始します。

バインドされたセッションの用途

以前のバージョンの SQL Server では、バインドされたセッションは、主に特定の拡張ストアド プロシージャの開発に使用されていました。このような拡張ストアド プロシージャでは、セッションを呼び出すプロセスに代わって Transact-SQL ステートメントを実行する必要があります。 呼び出しプロセスをバインド トークンで拡張ストアド プロシージャのパラメーターの 1 つとして渡せば、プロシージャは呼び出し側プロセスのトランザクション領域を結合できます。これにより、拡張ストアド プロシージャを呼び出し元プロセスに統合できます。

データベース エンジンでは、CLR を使用して記述されたストアド プロシージャは、拡張ストアド プロシージャよりも安全性、拡張性、安定性が高くなります。 CLR ストアド プロシージャでは、sp_bindsession ではなく SqlContext オブジェクトを使用して、呼び出し元セッションのコンテキストを結合します。

また、バインドされたセッションは、1 つのビジネス トランザクションで個別のプログラムが連携するようなビジネス ロジックを持つ、3 層構造のアプリケーションの開発に使用できます。 このようなプログラムでは、データベースへのアクセス調整に注意を払う必要があります。 2 つのセッションで同じロックを共有するので、その 2 つのプログラムで同じデータを同時に変更しないでください。 トランザクションの一部として機能するセッションはどの時点においても 1 つだけです。並列実行はできません。 すべての DML ステートメントが完了しそれらの結果が取得された時点など、セッション間のトランザクション切り替えは明確な降伏点でのみ行えます。

コードの効率性が良いトランザクション

トランザクションはできるだけ短くすることが重要です。 トランザクションが開始されると、終了するまでの間、トランザクションの ACID プロパティ (原子性、一貫性、分離性、および持続性) を損なわないよう、多数のリソースを DBMS (データベース管理システム) で確保する必要があります。 データを変更する場合、他のトランザクションによる読み取りを防ぐために変更する行に排他ロックをかけて保護する必要があり、排他ロックは、トランザクションがコミットされるかロールバックされるまでかけておく必要があります。 トランザクションの分離レベルの設定によっては、トランザクションのコミットまたはロールバックまで解除できないロックが SELECT ステートメントによってかけられる場合があります。 特に、ユーザー数が多いシステムの場合、コンカレント接続どうしによるリソースのロックの競合を減らす目的で、トランザクションをできるだけ短くする必要があります。 実行時間が長く、効率の悪いトランザクションでもユーザー数が少なければあまり問題にはなりませんが、ユーザー数が多い場合は、システムで高確率で問題となります。 SQL Server 2014 (12.x) 以降、データベース エンジンは、遅延永続性トランザクションをサポートします。 遅延永続性トランザクションは、拡張性とパフォーマンスを向上させる場合がありますが、永続性は保証されません。 詳しくは、「トランザクションの持続性の制御」をご覧ください。

コードのガイドライン

効率的なトランザクションをコーディングするためのガイドラインは次のとおりです。

• トランザクション中にユーザーによる入力を求めないようにします。 トランザクションを開始する前に、必要なすべての入力をユーザーが終えるようにします。 トランザクション中に追加のユーザー入力が必要になった場合は、現在のトランザクションをロールバックし、ユーザーが入力を終えてからトランザクションを再度開始します。 ユーザーの反応が早くても、人間の反応はコンピューターの処理速度に比べるとはるかに低速です。 トランザクションが確保しているすべてのリソースが長時間確保されることにより、ブロッキングの問題が発生する場合があります。 ユーザーが反応しない場合、応答が (場合によっては数分後か数時間後に) あるまでトランザクションはアクティブな状態で、重要なリソースをロックし続けます。

• データの参照中は、できるだけトランザクションを開かないようにします。 トランザクションは、事前のすべてのデータ分析が完了するまで開始しないでください。

• トランザクションはできるだけ短くします。 どのような変更が必要なのか把握した上でトランザクションを開始し、変更ステートメントを実行し、すぐにコミットまたはロールバックします。 トランザクションは必要になってから開きます。

• ブロックを減らすため、読み取り専用のクエリには行のバージョン管理に基づく分離レベルの使用を検討します。

• 低いトランザクション分離レベルを賢く利用します。 多くのアプリケーションは、READ COMMITTED トランザクション分離レベルを使用するようにコーディングできます。 SERIALIZABLE トランザクション分離レベル必要とするトランザクションはほとんどありません。

• 楽観的同時実行オプションを有効活用します。 同時に更新が行われる確率が低いシステムの場合、めったに発生しない "ユーザーがデータを読み取った後に他のユーザーがそのデータを変更した" というエラーを処理するオーバーヘッドは、読み取る行を常にロックするオーバーヘッドに比べて小さくできます。

• トランザクション中は、アクセスするデータ量をできるだけ少なくします。 アクセスするデータ量が少なければ、ロックされる行数が減るので、トランザクション間の競合が減少します。

• 可能な限り、HOLDLOCK などの悲観的ロック ヒントは避けるようにします。 HOLDLOCK や SERIALIZABLE 分離レベルなどのヒントを使用すると、共有ロックでもプロセスが待機状態となり、同時実行性が低下する可能性があります

• 可能な限り、暗黙的トランザクションは避けるようにします。 暗黙的トランザクションは、その性質上、想定外の動作を引き起こす場合があります。 「暗黙的トランザクションと同時実行性の問題」を参照してください。

暗黙のトランザクションおよびコンカレンシーとリソースの問題の回避

コンカレンシーおよびリソースの問題を防ぐには、暗黙のトランザクションを注意深く管理します。 暗黙的トランザクションを使用する場合、COMMIT または ROLLBACK 後の Transact-SQL ステートメントは、自動で新しいトランザクションを開始します。 その結果、アプリケーションでデータが参照されている間や、ユーザーからの入力を要求している間にも新しいトランザクションが開くことができます。 データの変更を防ぐことが必要な最後のトランザクションが完了した後、データの変更を防ぐことが必要な次のトランザクションまでは暗黙のトランザクションを無効にしてください。 このプロセスによって、データベース エンジンは、アプリケーションでデータが参照されている間やユーザーが入力している間に、オートコミット モードを使用できます。

さらに、SNAPSHOT 分離レベルが有効な場合、新しいトランザクションがロックを保有しなくても、実行時間の長いトランザクションは、バージョン ストアから古いバージョンが削除されるのを防ぎます。

実行時間の長いトランザクションを管理する

実行時間の長いトランザクションは、タイムリーにコミットまたはロールバックされていないアクティブなトランザクションです。 たとえば、トランザクションの開始と終了をユーザーが制御する場合、トランザクションの実行時間が長くなる一般的な原因は、トランザクションを開始したユーザーが、トランザクションがユーザーからの応答を待っているにもかかわらず、席を外してしまうことです。

トランザクションの実行時間が長くなると、次のように、データベースへの深刻な問題が発生する可能性があります。

• アクティブなトランザクションがコミットされていない修正を行った後に、サーバー インスタンスがシャットダウンされた場合、後続の再起動の復旧フェーズは、recovery interval サーバー構成オプションまたはALTER DATABASE ... SET TARGET_RECOVERY_TIME オプションが指定する時間よりもかなり長くかかることがあります。 これらのオプションは、アクティブなチェックポイントと間接的なチェックポイントをそれぞれ制御します。 チェックポイントの種類の詳細については、「データベースのチェックポイント (SQL Server)」を参照してください。

• さらに重要な注意事項として、待機状態のトランザクション自体によって生成される可能性のあるログ量はわずかですが、ログの切り捨てが無期限に停止されるため、トランザクション ログが大きくなり、満杯になる可能性があります。 トランザクション ログがいっぱいになると、データベースではそれ以降、書き込みができなくなります。 詳細については、「SQL Server トランザクション ログ アーキテクチャおよび管理ガイド」、「いっぱいになったトランザクション ログをトラブルシューティングする (SQL Server エラー 9002)」、「トランザクション ログ」を参照してください。

実行時間の長いトランザクションの検出

実行時間の長いトランザクションを検索するには、以下のいずれかの方法を使用します。

• sys.dm_tran_database_transactions

  この動的管理ビューは、データベース レベルでのトランザクションに関する情報を返します。 実行時間の長いトランザクションの場合、特に関心がある列として、初回ログ記録の時刻 (database_transaction_begin_time)、トランザクションの現在の状態 (database_transaction_state)、トランザクション ログ (database_transaction_begin_lsn) 内の begin レコードのログ シーケンス番号 (LSN) が挙げられます。

  詳細については、「sys.dm_tran_database_transactions (Transact-SQL)」を参照してください。

• DBCC OPENTRAN

  このステートメントを使用すると、トランザクション所有者のユーザー ID を特定できます。これにより、コミットやロールバックなど適切にトランザクションを終了するためのトランザクションのソースを追跡できる場合があります。 詳細については、「DBCC OPENTRAN (Transact-SQL)」を参照してください。

トランザクションを終了します。

特定のセッションでトランザクションを終了するには、KILL ステートメントを使用します。 ただし、重要なプロセスが実行中の場合は特に、このステートメントの使用には十分注意してください。 詳細については、「KILL (Transact-SQL)」を参照してください。

デッドロック

デッドロックはロックに関連する複雑なトピックですが、ブロックとは異なります。

• 監視、診断、サンプルなどのデッドロックの詳細については、「デッドロック ガイド」を参照してください。
• Azure SQL Database 特有のデッドロックの詳細については、「Azure SQL Database でデッドロックを分析および防止する」を参照してください。

関連するコンテンツ

• SQL Server のブロックの問題を理解して解決する
• Azure SQL Database のブロックに関する問題のを理解して解決する
• トランザクション関連の動的管理ビューと関数 (Transact-SQL)
• 行のバージョン管理のオーバーヘッド
• sys.dm_tran_locks (Transact-SQL)