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

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

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

トランザクションの基礎

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL Server データベース エンジンのインスタンスでは、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
• フルテキスト システム ストアド プロシージャ
• 明示的なトランザクションでも暗黙のトランザクションでも、データベース オプションを設定する sp_dboption および master データベースを変更するシステム プロシージャは使用できません。

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

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

接続に対して暗黙のトランザクション モードをオンに設定した後、SQL Server データベース エンジンのインスタンスで次のステートメントのうちのどれかが最初に実行されると、トランザクションが自動的に開始されます。

• ALTER TABLE

• 作成

• DELETE

• DROP

• FETCH

• GRANT

• INSERT

• OPEN

• REVOKE

• 選択

• TRUNCATE TABLE

• UPDATE

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

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

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

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

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

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

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

トランザクションの終了

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

• コミット トランザクションが正常に完了した場合、そのトランザクションをコミットします。 COMMIT ステートメントは、トランザクションのすべての変更をデータベースの一部として組み込みます。 また、COMMIT により、トランザクションで使用されていたロックなどのリソースが解放されます。

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

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

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

バッチでランタイム ステートメント エラー (制約違反など) が発生した場合、SQL Server データベース エンジンの既定の動作として、エラーの原因となったステートメントだけがロールバックされます。 この動作を変更するには、SET XACT_ABORT ステートメントを使用します。 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)」をご覧ください。

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

自動コミット モードの場合、SQL Server データベース エンジンのインスタンスが 1 つの SQL ステートメントだけでなく、バッチ全体をロールバックしたように見える場合があります。 これは、検出されたエラーが実行時エラーではなくコンパイル エラーの場合に発生します。 コンパイル エラーが起きると、SQL Server データベース エンジンの実行プランが構築できず、バッチ内のどの処理も実行されません。 エラーを生成したステートメントよりも前にあるすべてのステートメントがロールバックされたように見えますが、エラーによりバッチ内のどのステートメントも実行されませんでした。 次の例では、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

SQL Server データベース エンジンでは、名前の遅延解決を採用しています。つまり、オブジェクト名は実行時まで解決されません。 次の例では、最初の 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

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

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

• ロック

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

• 行のバージョン管理

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

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

アプリケーション側でトランザクション分離レベルを選択して、トランザクションを他のトランザクションによる変更から保護するレベルを定義できます。 個別の 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 レベルで実行されるトランザクションでは共有ロックが取得されないため、現在のトランザクションで読み取り中のデータが他のトランザクションで変更されることがあります。 READ COMMITTED レベルで実行されるトランザクションでは共有ロックが取得されますが、行ロックまたはページ ロックは行が読み取られた時点で解放されます。 どちらの場合も、インデックスをスキャンしているときに、読み取り中の行のインデックス キー列が他のユーザーによって変更された場合は、このキーの変更によって行がスキャン前の位置に移動すると、その行は再び出現する可能性があります。 同様に、キーの変更によって、既に読み取ったインデックスの位置に行が移動すると、その行は出現しない可能性があります。 この問題を回避するには、SERIALIZABLE または HOLDLOCK のヒントや、行のバージョン管理を使用します。 詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

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

    READ UNCOMMITTED を使用している場合、クエリで割り当て順序スキャン (IAM ページの使用) によって行を読み取っているときに、他のトランザクションによってページ分割が発生すると、行が欠落する可能性があります。 READ COMMITTED を使用している場合は、ページ分割時にテーブル ロックが保持されているため、この問題は発生しません。また、クラスター化インデックスを含まないテーブルの場合は、更新時にページ分割が行われることはないため、この問題は発生しません。

コンカレンシーの種類

多くのユーザーが同時にデータベースのデータを変更する場合、あるユーザーによる変更が別のユーザーによる変更に悪影響を及ぼすことを防ぐために、何らかの制御機構を実装する必要があります。 この制御機構をコンカレンシー制御と呼びます。

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

• ペシミスティックコンカレンシー制御

  ロック機構により、ユーザーが他のユーザーに影響するデータの変更を行うことを防ぎます。 あるユーザーによる操作でロックがかかると、そのユーザーがロックを解放するまで他のユーザーはロックと競合する操作を実行できません。 この方式は、データの競合が多く、コンカレンシーによる競合が発生した場合にトランザクションをロールバックするコストに比べデータをロックして保護するコストの方が低い環境で主に使用されるので、ペシミスティック コンカレンシー制御と呼ばれます。

• オプティミスティック同時実行制御

  オプティミスティック コンカレンシーでは、データを読み取る時点ではロックがかかりません。 データを更新するときに、そのユーザーが読み取ってから他のユーザーによる変更がなかったかが確認されます。 他のユーザーがデータを更新していた場合、エラーが発生します。 通常は、エラーが報告されたユーザーがトランザクションをロールバックして再実行します。 この方式は、データの競合が少なく、データを読み取るときにロックして保護するコストに比べトランザクションを必要に応じてロールバックするコストの方が低い環境で主に使用されるので、オプティミスティック同時実行制御と呼ばれます。

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

SQL Server データベース エンジンにおける分離レベル

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

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

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

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

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

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

Isolation Level	Definition
READ UNCOMMITTED	物理的に破損したデータを読み取らないことのみが保証されるようにトランザクションを分離する、最も低い分離レベル。 このレベルではダーティ リードが許可されるため、トランザクションで行われたコミットされていない変更を、他のトランザクションで読み取ることが可能です。
READ COMMITTED	トランザクションは、別のトランザクションが以前に読み取った (変更されていない) データを読み取ることができるので、最初のトランザクションが完了するまで待機する必要がありません。 SQL Server データベース エンジンは、(選択されたデータに対して取得された) 書き込みロックをトランザクションの終わりまで保持しますが、読み取りロックは SELECT 操作の実行が終わると解放します。 これは、SQL Server データベース エンジンの既定のレベルです。
REPEATABLE READ	SQL Server データベース エンジンは、選択されたデータに対して取得された読み取り/書き込みロックをトランザクションの終わりまで保持します。 ただし、範囲ロックが管理されないため、ファントム読み取りが発生する可能性はあります。
SERIALIZABLE	各トランザクションが完全に分離される、最も高い分離レベル。 SQL Server データベース エンジンは、選択されたデータに対して取得された読み取り/書き込みロックを保持し、トランザクションの終わりに開放します。 範囲指定付きの WHERE 句を SELECT 操作に使用する場合には、特にファントム読み取りを回避するために範囲ロックが取得されます。 注意: SERIALIZABLE 分離レベルが要求された場合、レプリケートされたテーブルの DDL 操作やトランザクションは失敗することがあります。 レプリケーションのクエリで使用されるヒントは、SERIALIZABLE 分離レベルと互換性がない可能性があるためです。

また、SQL Server では、行のバージョン管理を使用する 2 つの追加トランザクション分離レベルがサポートされます。 1 つは、READ COMMITTED 分離の実装であり、1 つはトランザクション分離レベルである "スナップショット" です。

行のバージョン管理分離レベル	Definition
READ COMMITTED SNAPSHOT (RCSI)	READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合、READ COMMITTED 分離では、行のバージョン管理を使用して読み取りの一貫性をステートメント レベルで維持します。 読み取り操作にはテーブル レベルの SCH-S ロックだけが必要であり、ページ ロックや行ロックは不要です。 つまり SQL Server データベース エンジンでは行のバージョン管理が使用され、各ステートメントでは、トランザクション全体で一貫性のあるデータのスナップショットが使用されます。このスナップショットは、ステートメント開始時点に存在したデータのスナップショットです。 ただし、ロックは、他のトランザクションがデータを更新するのを防ぐために使用されることはありません。 ユーザー定義関数から返されるデータは、そのユーザー定義関数を含んでいるステートメントの開始後にコミットされたものである可能性があります。 READ_COMMITTED_SNAPSHOT データベース オプションが OFF (既定) に設定されている場合は、READ_COMMITTED 分離に共有ロックが使用されます。これにより、現在のトランザクションでの読み取り操作中に他のトランザクションによって行が変更されるのを防ぐことができます。 また、ステートメントが他のトランザクションで変更された行を読み取ろうとしても、そのトランザクションが完了するまでステートメントはブロックされます。 どちらの実装も READ COMMITTED 分離の ISO 定義に準拠しています。
スナップショット	スナップショット分離レベルでは、行のバージョン管理を使用して読み取りの一貫性をトランザクション レベルで維持します。 読み取り操作では、ページ ロックも行ロックも獲得しません。テーブル レベルの SCH-S ロックだけを獲得します。 別のトランザクションによって変更された行を読み取るときは、トランザクションの開始時に存在していた行のバージョンを取得します。 データベースに対してスナップショット分離を使用できるのは、ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON に設定されている場合のみです。 既定では、ユーザー データベースのこのオプションは OFF に設定されています。 注: SQL Server では、メタデータのバージョン管理はサポートされません。 そのため、スナップショット分離下で実行されている明示的なトランザクションでは、実行できる DDL 操作に制限があります。 スナップショット分離下では、BEGIN TRANSACTION ステートメントの後に、ALTER TABLE、CREATE INDEX、CREATE XML INDEX、ALTER INDEX、DROP INDEX、DBCC REINDEX、ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME などの DDL ステートメントを実行することはできません。共通言語ランタイム (CLR) の DDL ステートメントも同様です。 暗黙のトランザクション内でスナップショット分離を使用しているときには、これらのステートメントは許可されます。 暗黙的なトランザクションとは、原則的に、DDL ステートメントでもスナップショット分離のセマンティックを適用することのできる単一のステートメントをいいます。 この原則に反した場合、エラー 3961 が発生し、Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.。

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

分離レベル	ダーティ リード	反復不可能読み取り	ファントム
READ UNCOMMITTED	はい	イエス	はい
READ COMMITTED	いいえ	イエス	はい
REPEATABLE READ	いいえ	番号	はい
スナップショット	いいえ	番号	いいえ
SERIALIZABLE	いいえ	番号	いいえ

各トランザクション分離レベルによって制御される特定のロックまたは行のバージョン管理の種類について詳しくは、「SET TRANSACTION ISOLATION LEVEL (Transact-SQL)」をご覧ください。

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

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

ADO
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 ののいずれかを使用して取得できます。

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

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

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

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

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

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

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

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

ロックの粒度と階層

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

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

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

リソース	説明
RID	ヒープ内の 1 行をロックするのに使用される行識別子 (ROWID)。
KEY	シリアル化可能なトランザクションのキー範囲の保護に使用されるインデックス内の行ロック。
ページ	データ ページやインデックス ページなど、データベース内の 8 KB のページ。
EXTENT	データ ページやインデックス ページなど、連続した 8 ページのグループ。
HoBT 1	ヒープまたは B ツリー。 B ツリー (インデックス)、またはクラスター化インデックスのないテーブルのヒープ データ ページを保護するロックです。
表: 1	すべてのデータとインデックスを含むテーブル全体。
FILE	データベース ファイル。
アプリケーション	アプリケーションにより指定されたリソース。
METADATA	メタデータのロック。
ALLOCATION_UNIT	アロケーション ユニット。
DATABASE	データベース全体。
XACT 2	最適化されたロックで使用されるトランザクション ID (TID) のロック。 トランザクション ID (TID) のロックを参照してください。

¹ HoBT ロックおよび TABLE ロックは、ALTER TABLE の LOCK_ESCALATION オプションの影響を受ける可能性があります。

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

ロック モード

SQL Server データベース エンジンは、さまざまなロック モードを使用してリソースをロックします。これにより、同時に実行されている複数のトランザクションがリソースにアクセスする方法が決まります。

次の表に、SQL Server データベース エンジンのリソース ロック モードを示します。

ロック モード	説明
共有 (S)	SELECT ステートメントなど、データの変更や更新を伴わない読み取り操作で使用します。
更新 (U)	更新可能なリソースに使用します。 複数のセッションがリソースを読み取り、ロックして、後で更新する可能性がある場合に発生する一般的な形式のデッドロックを防ぎます。
排他 (X)	INSERT、UPDATE、DELETE などのデータ変更操作に使用します。 複数の更新操作により 1 つのリソースを同時に更新しないようにするためのロック モードです。
インテント	ロック階層を設定するのに使用します。 インテント ロックの種類にはインテント共有 (IS)、インテント排他 (IX)、およびインテント排他付き共有 (SIX) があります。
[スキーマ]	テーブルのスキーマに依存する操作を行うときに使用します。 スキーマ ロックの種類には、スキーマ修正 (Sch-M) およびスキーマ安定度 (Sch-S) があります。
一括更新 (BU)	データを一括でテーブルにコピーするときに TABLOCK ヒントを指定して使用します。
キー範囲	トランザクション分離レベルが SERIALIZABLE のとき、クエリにより読み取られる行の範囲を保護します。 シリアル化可能トランザクションのクエリを再度実行した場合に対象となるような行を、他のトランザクションは挿入できなくなります。

共有ロック

共有 (S) ロックを設定すると、同時に実行されている複数のトランザクションがペシミスティック コンカレンシー制御の下でリソースの読み取り (SELECT) を行います。 他のトランザクションは、リソースに共有 (S) ロックがかけられている間はデータを変更できません。 リソースにかけられている共有 (S) ロックは、読み取りが完了するとすぐに解除されます。ただし、トランザクションの分離レベルが REPEATABLE READ 以上に設定されている場合や、トランザクションの間、ロック ヒントを使用して共有 (S) ロックを保つ場合を除きます。

更新ロック

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

更新 (U) ロックは、クエリで UPDLOCK テーブル ヒントが指定されている場合、UPDATE を実行しないクエリでも実行できます。 アプリケーションでは、「行を選択してから行を更新する」パターンを使用するのが一般的です。このパターンでは、読み取りと書き込みがトランザクション内で明示的に分離されます。 この場合、分離レベルが反復可能な読み取りまたはシリアル化可能な場合、同時更新がデッドロックする可能性があります。 代わりに、アプリケーションは「UPDLOCK ヒントを使用して行を選択し、行を更新する」パターンに従うことができます。

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

• 既定の読み取りコミット済み分離レベルでは、S ロックは短い期間であり、使用されるとすぐに解放されます。 短い期間のロックによってデッドロックが発生する可能性は低くなります。

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

排他ロック

排他 (X) ロックは、同時に実行されている複数のトランザクションが同じリソースにアクセスすることを防ぎます。 排他 (X) ロックをかけたトランザクション以外はデータを変更できませんが、NOLOCK ヒントまたは READ UNCOMMITTED 分離レベルが指定されている場合に限り、読み取り操作は行うことができます。

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

インテント ロック

SQL Server データベース エンジンではインテント ロックを使用して、下位のロック階層に位置するリソースに共有 (S) ロックまたは排他的 (X) ロックがかかるのを保護します。 「インテント ロック」の命名の由来は、ロックのタイミングが下位レベルのロックよりも前であり、下位レベルでロックをかける意図 (intent) を示すためです。

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

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

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

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

ロック モード	説明
インテント共有 (IS)	下位の階層に位置するリソースの (すべてではなく) 一部に対し、要求されているかかけられている共有ロックを保護します。
インテント排他 (IX)	下位の階層に位置するリソースの (すべてではなく) 一部に対し、要求されているかかけられている排他ロックを保護します。 IX は IS のスーパーセットです。また、下位のリソースに対する共有ロックの要求を保護します。
インテント排他付き共有 (SIX)	下位の階層に位置するすべてのリソースに対し、要求されているかかけられている共有ロックを保護し、下位のリソースの (すべてではなく) 一部のインテント排他ロックを保護します。 上位リソースで同時実行している IS ロックは可能です。 たとえば、テーブルに対し SIX ロックをかけると、変更中のページにインテント排他ロックが、変更中の行に排他ロックがかかります。 1 つのリソースに対しては、一度に 1 つの SIX ロックしかかけられません。その結果、他のトランザクションによってリソースが更新されることはなくなりますが、他のトランザクションはテーブル レベルの IS ロックをかけることで下位のリソースを読み取ることができます。
インテント更新 (IU)	下位の階層に位置するすべてのリソースに対し、要求または取得された更新ロックを保護します。 IU ロックはページ リソースに対してのみ使用します。 更新操作が発生すると、IU ロックは IX ロックに変換されます。
共有インテント更新 (SIU)	S ロックと IU ロックを個別にかけるか、同時にかけるかして組み合わせたものです。 たとえば、トランザクションで PAGLOCK ヒントを指定してクエリを実行してから更新操作を実行するとします。 PAGLOCK ヒントを指定したクエリで S ロックをかけ、更新操作で IU ロックをかけます。
更新インテント排他 (UIX)	U ロックと IX ロックを個別にかけるか、同時にかけるかして組み合わせたものです。

スキーマ ロック

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

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

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

一括更新ロック

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

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

キー範囲ロック

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

ロックの互換性

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

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

既に許可されているモード	IS	S	U	IX	SIX	X
要求されたモード
インテント共有 (IS)	はい	イエス	イエス	イエス	はい	いいえ
共有 (S)	はい	イエス	はい	いいえ	番号	いいえ
更新 (U)	はい	はい	いいえ	番号	番号	いいえ
インテント排他 (IX)	はい	いいえ	番号	有効	いいえ	いいえ
インテント排他付き共有 (SIX)	はい	いいえ	番号	番号	番号	いいえ
排他 (X)	いいえ	番号	番号	番号	番号	無効

次の表を使用すると、SQL Server で使用できるすべてのロック モードの互換性を確認できます。

キー範囲ロック

キー範囲ロックは、SERIALIZABLE トランザクション分離レベルの状態で、Transact-SQL ステートメントで読み取っているレコード セットに含まれている行の範囲を暗黙的に保護します。 SERIALIZABLE 分離レベルでは、トランザクション中に実行されるクエリは、そのトランザクション内で実行されるたびに同一の行セットを取得する必要があります。 キー範囲ロックではこの要件を満たすために、新しい行のキーが SERIALIZABLE トランザクションで読み取られるキー範囲内にある場合に、他のトランザクションが新しい行を挿入できないようにします。

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

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

キー範囲ロック モード

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

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

Range	行	モード	説明
RangeS	S	RangeS-S	共有範囲。共有リソース ロック、シリアル化可能範囲スキャン。
RangeS	U	RangeS-U	共有範囲。更新リソース ロック。シリアル化可能更新スキャン。
RangeI	[Null]	RangeI-N	挿入範囲。NULL リソース ロック。新しいキーをインデックスに挿入する前に範囲をテストするのに使用します。
RangeX	x	RangeX-X	排他範囲。排他リソース ロック。範囲内のキーを更新するのに使用します。

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

既に許可されているモード	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
要求されたモード
共有 (S)	はい	はい	いいえ	イエス	イエス	はい	いいえ
更新 (U)	はい	いいえ	番号	有効	いいえ	有効	いいえ
排他 (X)	いいえ	番号	番号	番号	番号	有効	いいえ
RangeS-S	はい	はい	いいえ	イエス	はい	いいえ	いいえ
RangeS-U	はい	いいえ	番号	有効	いいえ	番号	いいえ
RangeI-N	はい	イエス	はい	いいえ	番号	有効	いいえ
RangeX-X	いいえ	番号	番号	番号	番号	番号	いいえ

変換ロック

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

ロック 1	ロック 2	変換ロック
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
x	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

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

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

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

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

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

• トランザクション分離レベルを 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 mytable
WHERE name = 'Bob';

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

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

最適化されたロックを使用した削除操作

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

DELETE mytable
WHERE name = 'Bob';

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

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

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

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

INSERT mytable VALUES ('Dan');

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

最適化されたロックを使用した挿入操作

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

INSERT mytable VALUES ('Dan');

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

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

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

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

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

SQL Server データベース エンジンによって、次のように、低いレベルのロックが取得されると、下位レベルのオブジェクトを含むオブジェクトにもインテント ロックが設定されます。

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

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

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

データベース エンジンによって、行またはキー範囲ロックがページ ロックにエスカレートされず、テーブル ロックに直接エスカレートされます。 同様に、ページ ロックは常にテーブル ロックにエスカレートされます。 パーティション テーブルのロックは、テーブル ロックではなく、関連するパーティションの HoBT レベルにエスカレートすることができます。 HoBT レベルのロックでは、パーティションのアラインされた HoBT が必ずしもロックされるとは限りません。

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

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

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

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

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

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

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

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

最適化されたロックが有効になっており、既定の 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 ステートメントは、次のロックを取得します。

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

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

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

ほとんどの場合、で、ロックおよびロックのエスカレーションをその既定値を使用して行うと、最高のパフォーマンスを得ることができます。

• 最適化されたロックを利用します。

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

データベース エンジンのインスタンスによって多数のロックが生成され、頻繁にロックのエスカレーションが確認される場合は、次の戦略でロックの量を減らすことを検討してください。

• 読み取り操作で共有ロックを生成しない分離レベルを使用する場合:

  • READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合の READ COMMITTED 分離レベル。

  • SNAPSHOT 分離レベル。

  • READ UNCOMMITTED 分離レベル。 これは、ダーティ リードで動作できるシステムでのみ使用できます。

    Note

    分離レベルを変更すると、データベース エンジンのインスタンスのすべてのテーブルが影響を受けます。

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

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

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

  DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  

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

  SET ROWCOUNT 500
  delete_more:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  IF @@ROWCOUNT > 0 GOTO delete_more
  SET ROWCOUNT 0
  

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

  Bookmark Lookup の WITH PREFETCH 句が原因でエスカレーションが発生する場合は、クエリ プランの Bookmark Lookup 論理演算子の下にある Index Seek または Index Scan 論理演算子に表示される非クラスター化インデックスに列をさらに追加することを検討してください。 select 列リストにすべてを含めることが実用的でない場合は、カバー インデックス (クエリで使用されたテーブル内のすべての列を含むインデックス)、または結合条件あるいは WHERE 句で使用された列をカバーする 1 つ以上のインデックスを作成できることがあります。 入れ子になったループ結合でも PREFETCH を使用することがあります。これにより、同じロック動作が発生します。

• 異なる SPID で現在、互換性のないテーブル ロックが保持されている場合は、ロックのエスカレーションを行うことはできません。 ロックのエスカレーションは常にテーブル ロックに行われ、ページ ロックに行われることはありません。 また、別の SPID で互換性のない TAB ロックが保持されているためにロックのエスカレーションの試行に失敗した場合、エスカレーションを試行したクエリでは、TAB ロックを待機している間はブロックは行われません。 代わりに、元のより細かいレベル (行、キー、またはページ) でのロックの取得が続行され、定期的に追加のエスカレーションが試行されます。 したがって、特定のテーブルでのロックのエスカレーションを防ぐ方法の 1 つは、エスカレートされたロックの種類と互換性のない別の接続に対してロックを取得して保持することです。 テーブル レベルでの IX (インテント排他) ロックによって、行やページはロックされませんが、それでもエスカレートされた S (共有) または X (排他) TAB ロックとの互換性はありません。 たとえば、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 ヒントでテーブル ロックが強制される場合、または管理者が sp_indexoption ストアドプロシージャを使用して、ページまたは行のロックを無効にした場合を除く)。

• トレース フラグ 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.

  Note

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

  Note

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

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

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

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

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

動的ロックには次の利点があります。

• データベースの管理が簡略化されます。 データベース管理者がロック エスカレーションのしきい値を調整する必要はありません。
• パフォーマンスの向上。 SQL Server データベース エンジンによりタスクに適したロックが使用されるので、システム オーバーヘッドが最小になります。
• アプリケーション開発者が開発に専念できます。 SQL Server データベース エンジンは、ロックを自動的に調整します。

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

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

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

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

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

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

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

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

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

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

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

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

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

• 単一のパーティションに対して獲得されるロック モードは、NL、SCH-S、IS、IU、および IX のみです。

• 共有 (S) ロック、排他 (X) ロック、および NL、SCH-S、IS、IU、IX 以外のモードの他のロックは、パーティション ID が 0 のパーティションから、パーティション ID 順に獲得される必要があります。 パーティション分割されたリソースでは、パーティションごとに別のロックが獲得されます。そのため、これらのパーティション分割されたリソースのロックでは、パーティション分割されていないリソースの同じモードのロックよりも多くのメモリが使用されます。 メモリの増加量は、パーティションの数によって決まります。 Windows パフォーマンス モニターの 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 ロックは、トランザクションに割り当てられたパーティションに対してのみ獲得されます。 この例では、パーティション ID 7 に対して IS ロックが獲得されるものとします。

-- 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 ロックは、トランザクションに割り当てられたパーティションに対してのみ獲得されます。 この例では、パーティション ID 6 に対して IS ロックが獲得されるものとします。

-- 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 ロックはパーティション ID 0 ～ 5 のパーティションに対して獲得されますが、パーティション ID 6 に対して獲得された IS ロックによりブロックされます。

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

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCKX, HOLDLOCK);

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

SQL Server 2005 (9.x) 以降のSQL Server データベース エンジンでは、既存の READ COMMITTED トランザクション分離レベルで、行のバージョン管理によるステートメント レベルのスナップショットを使用できます。 SQL Server データベース エンジンでは、同じく行のバージョン管理によりトランザクション レベルのスナップショットを提供する SNAPSHOT トランザクション分離レベルも使用できます。

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

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

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

READ COMMITTED トランザクションとスナップショット トランザクションで行のバージョン管理を使用するには、次の 2 つの手順を実行します。

1. READ_COMMITTED_SNAPSHOT データベース オプションと ALLOW_SNAPSHOT_ISOLATION データベース オプションのいずれかまたは両方を ON に設定します。

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

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

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

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

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

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

データ読み取り時の動作

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

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

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

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

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

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

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

データ変更時の動作

データ書き込みの動作は、最適化されたロックが存在する場合としない場合とで大きく異なります。

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

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

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

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

最適化されたロックを有効にし、READ_COMMITTED_SNAPSHOT (RCSI) データベース オプションを有効にし、既定の READ COMMITTED 分離レベルを使用すると、リーダーはロックを取得せず、ライターはトランザクションの終了時に期限切れになるロックではなく、短時間の低レベルロックを取得します。

RCSI の有効化は、最適化されたロックを使用して最も効率よく行うことをお勧めします。 反復可能な読み取りやシリアル化可能などのより厳密な分離レベルを使用する場合、データベース エンジンは、リーダーとライターの両方に対して、トランザクションの最後まで行ロックとページ ロックを保持することを強制され、ブロックとロックのメモリが増加します。

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

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

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

動作のまとめ

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

プロパティ	行のバージョン管理を使用する READ COMMITTED 分離レベル	スナップショット分離レベル
必要なサポートを有効にするために ON に設定されている必要があるデータベース オプション。	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
セッションが特定の種類の行のバージョン管理を要求する方法。	既定の READ COMMITTED 分離レベルを使用するか、または SET TRANSACTION ISOLATION LEVEL ステートメントを実行して READ COMMITTED 分離レベルを指定します。 この操作は、トランザクションの開始後に実行できます。	トランザクションの開始前に、SET TRANSACTION ISOLATION LEVEL を実行してスナップショット分離レベルを指定するように要求します。
ステートメントにより読み取られるデータのバージョン。	各ステートメントの開始前にコミットされたすべてのデータ。	各トランザクションの開始前にコミットされたすべてのデータ。
更新の処理方法。	最適化されたロックなし: 行のバージョンを実際のデータに戻して更新する行を選択し、選択したデータ行に対して更新ロックをかけます。 また、変更する実際のデータ行の排他ロックを獲得します。 更新の競合検出は行われません。 最適化されたロックの場合: ロックを取得せずに、最後にコミットされたバージョンに基づいて行が選択されます。 行が更新対象の場合、排他行ロックまたはページ ロックが取得されます。 更新の競合が検出されると、それらは自動的に処理および再試行されます。	行バージョンを使用して更新する行を選択します。 変更する行の実際のデータに対する排他ロックの獲得を試行します。このデータが別のトランザクションにより変更されている場合は、更新の競合が発生し、スナップショット トランザクションは終了します。
更新の競合検出	最適化されたロックなしの場合: なし。 最適化されたロックの場合: 更新の競合が検出された場合、それらは自動的に処理および再試行されます。	組み込みによるサポート。 この機能は無効にできません。

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

行のバージョン管理フレームワークでは、SQL Server で使用可能な次の機能がサポートされます。

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

また、行のバージョン管理フレームワークでは、次に示すように、行のバージョン管理に基づいたトランザクション分離レベルもサポートされます。これは既定では無効になっています。

• READ_COMMITTED_SNAPSHOT データベース オプションが ON になっている場合、READ_COMMITTED トランザクションで行のバージョン管理を行うことにより、ステートメント レベルでの読み取り操作を一貫性を保った状態で実行できます。
• ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON になっている場合、SNAPSHOT トランザクションで行のバージョン管理を行うことにより、トランザクション レベルでの読み取り操作を一貫性を保った状態で実行できます。

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

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

tempdb で使用される領域

SQL Server データベース エンジンの各インスタンスの tempdb には、インスタンス内の各データベースで生成される行バージョンを保持できるだけの十分な領域が必要です。 データベース管理者は、バージョン ストアをサポートできるだけの十分な領域を tempdb 内に確保する必要があります。 tempdb には次に示す 2 つのバージョン ストアがあります。

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

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

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

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

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

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

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

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

• READ_COMMITTED_SNAPSHOT オプションまたは ALLOW_SNAPSHOT_ISOLATION オプションの少なくとも一方が ON です。
• テーブルにトリガーが含まれています。
• 複数のアクティブな結果セット (MARS) が使用されています。
• オンラインのインデックス構築操作が、現在そのテーブルで実行されています。

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

• READ_COMMITTED_SNAPSHOT オプションと ALLOW_SNAPSHOT_ISOLATION オプションの両方が OFF です。
• テーブルに既にトリガーが存在しません。
• MARS が使用されていません。
• オンラインのインデックス構築操作が現在実行されていません。

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

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

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

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

新しい大きな値がデータベースに追加されたときに、データ フラグメントには、1 つのフラグメントにつき最大 8,040 バイトのデータが割り当てられます。 以前のバージョンの SQL Server データベース エンジンでは、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 に設定されている場合、ログ記録を最小限に抑えることができます。

nvarchar(max)、varchar(max)、および varbinary(max) の各データ型は、以前のバージョンの SQL Server では使用できません。 したがって、これらのデータ型についてはアップグレードの問題は発生しません。

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

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

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

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 でグループ化しています。 この関数を使用して、バージョン ストアを最も多く使用しているレコードを見つけます。 詳しくは、「sys.dm_tran_top_version_generators (Transact-SQL)」をご覧ください。

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

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

  Note

  両方ともこれは非常に大きくなる可能性があるバージョン ストア全体にクエリを実行するため、システム オブジェクト 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)」をご覧ください。

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

SQL Server のパフォーマンス カウンターからは、SQL Server プロセスによって影響を受けるシステム パフォーマンスについての情報が提供されます。 次に示すパフォーマンス カウンターでは、行のバージョン管理を使用しているトランザクションだけではなく、tempdb とそのバージョン ストアを監視します。 パフォーマンス カウンターは SQLServer:Transactions パフォーマンス オブジェクトに含まれています。

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

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

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

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

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

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

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

  Note

  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。 バージョン レコードを生成する、スナップショット以外のアクティブなトランザクションの総数を監視します。

  Note

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

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

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

A. スナップショット分離を使用した作業

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

セッション 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 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 トランザクションは、スナップショット トランザクションとは異なる動作をします。 スナップショット トランザクションと同様に Read Committed トランザクションも、別のトランザクションがデータを変更した後でも、バージョン管理される行を読み取ります。 ただし、スナップショット トランザクションとは異なり、Read Committed トランザクションは以下のように動作します。

• 別のトランザクションがデータの変更をコミットした後は、変更されたデータを読み取ります。
• 別のトランザクションが変更したデータを変更できます。スナップショット トランザクションでは、このような変更は実行できませんでした。

セッション 1:

USE AdventureWorks2022;  -- Or any earlier version of the AdventureWorks database.
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 であれば、SQL Server データベース エンジンのインスタンスは、データベース内のデータを変更したアクティブなトランザクションがすべて完了するまで、変更されたデータの行に対応するバージョンを生成しません。 アクティブな変更トランザクションが存在すると、SQL Server によってオプションの状態が PENDING_ON に設定されます。 すべての変更トランザクションが完了してから、このオプションの状態が ON に変更されます。 ユーザーは、オプションが完全に ON になるまで、そのデータベースでのスナップショット トランザクションを開始できません。 データベース管理者が ALLOW_SNAPSHOT_ISOLATION オプションを OFF に設定すると、データベースは PENDING_OFF の状態を経てから OFF に設定されます。

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

ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

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

現在のデータベースのスナップショット分離フレームワークの状態	説明
OFF	スナップショット分離トランザクションのサポートはアクティブになりません。 スナップショット分離トランザクションは許可されません。
PENDING_ON	スナップショット分離トランザクションのサポートが遷移中の状態 (OFF から ON) です。 開いているトランザクションが完了する必要があります。 スナップショット分離トランザクションは許可されません。
ON	スナップショット分離トランザクションのサポートがアクティブになります。 スナップショット トランザクションが許可されます。
PENDING_OFF	スナップショット分離トランザクションのサポートが遷移中の状態 (ON から OFF) です。 これ以降に開始されるスナップショット トランザクションは、このデータベースにアクセスできません。 このデータベースのバージョン管理の負荷は、依然として更新トランザクションが担っています。 既存のスナップショット トランザクションからは、このデータベースに引き続き問題なくアクセスできます。 PENDING_OFF の状態は、データベースのスナップショット分離の状態が ON のときにアクティブであったすべてのスナップショット トランザクションが完了するまで OFF になりません。

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

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

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

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

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

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

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

• Read Committed。次のコード例に示すように、READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定して行のバージョン管理を使用します。

  ALTER DATABASE AdventureWorks2022
      SET READ_COMMITTED_SNAPSHOT ON;
  

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

• 次のコード例に示すように、ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定することによってスナップショット分離を有効にします。

  ALTER DATABASE AdventureWorks2022
      SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  スナップショット分離レベルで実行中のトランザクションでは、スナップショットが有効になっているデータベースのテーブルにアクセスできます。 スナップショットが有効になっていないテーブルにアクセスするには、分離レベルを変更する必要があります。 たとえば、次のコード例では、スナップショット トランザクションで実行中に 2 つのテーブルを結合する SELECT ステートメントを示します。 1 つのテーブルは、スナップショット分離が無効なデータベースに属しています。 スナップショット分離レベルで SELECT ステートメントを実行すると、実行に失敗します。

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  BEGIN TRAN
      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 TRAN
      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 に格納されます。 スナップショット トランザクション内でグローバルな一時テーブルにアクセスする場合は、次のいずれかの操作を行う必要があります。

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

• スナップショット トランザクションは、次の場合に失敗します。

  • スナップショット トランザクションが開始してからデータベースにアクセスするまで、データベースが読み取り専用になっている場合。
  • 複数のデータベースのオブジェクトにアクセスするときに、スナップショット トランザクションが開始してからデータベースにアクセスするまでの間にデータベースの復旧が行われるようにデータベースの状態が変更された場合。 たとえば、データベースが OFFLINE に設定されてから ONLINE に設定された場合、データベースを自動終了して開く場合、またはデータベースをデタッチ後にアタッチする場合などがあります。

• 分散パーティション データベースのクエリなど、分散トランザクションはスナップショット分離ではサポートされていません。

• SQL Server では、複数バージョンのシステム メタデータは保持されません。 テーブルおよび他のデータベース オブジェクト (インデックス、ビュー、データ型、ストアド プロシージャ、および共通言語ランタイム関数) のデータ定義言語 (DDL) ステートメントにより、メタデータが変更されます。 DDL ステートメントでオブジェクトを変更する場合、スナップショット分離では、オブジェクトへの同時参照が原因で、スナップショット トランザクションが失敗します。 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 トランザクションは影響を受けません。

  Note

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

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

ロック タイムアウトのカスタマイズ

別のトランザクションが競合するロックをリソースで既に所有しているために Microsoft SQL Server データベース エンジンで同じリソースへのロックをトランザクションに許可できない場合、そのトランザクションはブロックされ、既存のロックが解放されるまで待機状態になります。 既定では、強制タイムアウト時間は設定されないので、ロック前にリソースがロックされているかどうかを調べる方法はデータにアクセスする以外にありません。そして、データにアクセスすると無期限にブロックされる可能性があります。

LOCK_TIMEOUT 設定により、ブロックされたリソースをステートメントが待機する最大時間をアプリケーションから設定できます。 待機時間が LOCK_TIMEOUT の設定を超えると、ブロックされているステートメントが自動的に取り消され、エラー メッセージ 1222 (Lock request time-out period exceeded) がアプリケーションに返されます。 ただし、このステートメントが含まれているトランザクションは、SQL Server によってロールバックされたり取り消されたりしません。 したがって、アプリケーションはエラー メッセージ 1222 をトラップできるエラー ハンドラーを備えている必要があります。 アプリケーションでこのエラーをトラップしないと、トランザクション内の各ステートメントが取り消されたことが認識されません。トランザクション内のこれ以降のステートメントが、実行されなかったステートメントに依存している可能性があるので、エラーが発生することがあります。

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

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

SELECT @@lock_timeout;
GO

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

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

• 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 属性を設定できます。

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

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

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID
    FROM HumanResources.Employee;
GO

分離レベルは、必要に応じて個別のクエリまたは DML ステートメントでテーブル レベルのヒントを指定することによりオーバーライドできます。 テーブル レベルのヒントを指定しても、セッション内の他のステートメントに影響はありません。 テーブル レベルのヒントを使用して既定の動作を変更する操作は、どうしても必要な場合にのみ行うことをお勧めします。

データの読み取り時に共有ロックが要求されない分離レベルを設定した場合でも、SQL Server データベース エンジンではメタデータの読み取り時にロックの取得が必要になる場合があります。 たとえば、トランザクションが READ UNCOMMITTED 分離レベルで実行されている場合、データの読み取り時には共有ロックが取得されませんが、システム カタログ ビューの読み取り時にはロックが要求されることがあります。 つまり、READ UNCOMMITTED 分離レベルで実行されているトランザクションでは、同時実行トランザクションでテーブルのメタデータが変更されているときに、そのテーブルに対してクエリが実行されると、ブロッキングを発生させることがあります。

現在設定されているトランザクション分離レベルを特定するには、次の例に示すように、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 の各ステートメント内で参照する個別のテーブルにロック ヒントを指定できます。 ロック ヒントでは、SQL Server データベース エンジンのインスタンスがテーブル データに使用するロックの種類や行のバージョン管理が指定されます。 テーブルレベルのロック ヒントは、オブジェクトにかけるロックの種類を詳細に制御する場合に使用できます。 これらのロック ヒントは、セッションの現在のトランザクション分離レベルをオーバーライドします。

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

SQL Server データベース エンジンでは、データの読み取り時に共有ロックの要求を回避するロック ヒントが指定された SELECT を処理している場合でも、メタデータの読み取り時にロックの取得が必要になる場合があります。 たとえば、NOLOCK ヒントを使用する SELECT では、データの読み取り時には共有ロックが取得されませんが、システム カタログ ビューの読み取り時にはロックが要求される場合があります。 そのため、NOLOCK を使用する SELECT ステートメントはブロックされる可能性があります。

次の例のように、トランザクションの分離レベルを SERIALIZABLE に設定し、テーブルレベルのロック ヒントとして NOLOCK を SELECT ステートメントで使用すると、シリアル化可能なトランザクションの管理に通常使用されるキー範囲ロックが取得されません。

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) ロックのみです。 この場合、シリアル化可能性は保証されません。

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

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

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

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

週に 1 回のバッチ更新による更新の実行中は同時読み取りによるテーブルへのアクセスがブロックされるため、ページと行のロックをオフにすることが適切かどうかは状況によって異なります。 バッチ ジョブによっていくつかの行またはページのみが変更される場合は、行またはページ レベルのロックを許可するようにロック レベルを変更できます。この場合、他のセッションはブロックされることなくテーブルから読み出しを行うことができます。 バッチ ジョブで大量の更新を扱う場合は、バッチ ジョブを効率的に完了するためにテーブル上で排他ロックを取得することが最善の方法です。

2 つの同時処理が同じテーブル上で行ロックを取得した後にブロックすると、どちらもページをロックする必要があるため、デッドロックが発生する場合があります。 行ロックを禁止することで、どちらかの処理が待機するようになるため、デッドロックを回避できます。 デッドロックについて詳しくは、「デッドロック ガイド」をご覧ください。

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

禁止されるロック	インデックスにかけられるロック
ページ レベル	行レベルおよびテーブルレベルのロック
行レベルのロック	ページレベルおよびテーブルレベルのロック
ページレベルおよび行レベルのロック	テーブルレベルのロック

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

入れ子構造のトランザクション

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

次の例は、入れ子構造のトランザクションの使用方法を示しています。 プロシージャ TransProc は、プロセスのトランザクション モードに関係なくトランザクションを実行します。 トランザクションがアクティブであるときに 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 rollback. */
SELECT * FROM TestTrans;
GO

SQL Server データベース エンジンでは、入れ子の内側のトランザクションのコミットが無視されます。 内側のトランザクションは、最も外側にあるトランザクションの最後に行われた操作に基づいてコミットまたはロールバックされます。 外側のトランザクションがコミットされると、入れ子の内側のトランザクションもコミットされます。 外側のトランザクションがロールバックされると、内側のトランザクションも、個々がコミットされたかどうかに関係なくすべてロールバックされます。

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 つ以上のセッションで同じトランザクションとロックを共有できます。また、ロックの競合が発生しないで同じデータを操作できます。 バインドされたセッションは、同じアプリケーション内の複数のセッションからも、セッションが異なる複数のアプリケーションからも作成できます。

バインドされたセッションに参加するには、セッションから sp_getbindtoken または srv_getbindtoken(オープン データ サービス経由) を呼び出して、バインド トークンを取得します。 バインド トークンは、バインドされたトランザクションをそれぞれ一意に識別する文字列です。 取得したバインド トークンは、現在のセッションにバインドされる他のセッションに送信されます。 他のセッションは、最初のセッションから受信したバインド トークンを使用して sp_bindsession を呼び出すことにより、トランザクションにバインドされます。

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

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

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

• SQL Server データベース エンジンのインスタンス内の、最初のセッションにバインドを試みるプロセスから読み取れるテーブルに、バインド トークンを格納します。

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

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

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

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

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

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

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

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

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

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

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

効率的なトランザクションのコーディング

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

コーディング ガイドライン

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

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

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

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

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

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

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

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

• 可能な限り、holdlock などのペシミスティック ロック ヒントは避けてください。 HOLDLOCK や SERIALIZABLE 分離レベルのようなヒントを使用すると、共有ロックでもプロセスが待機状態となり、コンカレンシーが低下する可能性があります

• 可能な限り、暗黙のトランザクションの使用は避けてください。暗黙のトランザクションを使用すると、その性質により、予期しない動作となる可能性があります。 暗黙のトランザクションとコンカレンシーの問題に関する記述を参照してください

• FILL FACTOR を減らしてインデックスを設計します。FILL FACTOR を減らすと、インデックス ページの断片化を防いだり、減らしたりすることができるため、特にディスクから取得した場合にインデックスのシーク時間を短縮するのに役立つことがあります。 テーブルまたはビューのデータとインデックスに関する断片化情報を表示するために、sys.dm_db_index_physical_stats を使用できます。

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

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

スナップショット分離レベルが有効である場合、新しいトランザクションがロックをかけることはありませんが、実行時間の長いトランザクションを実行する間はそれ以前のトランザクションが tempdb から削除されません。

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

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

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

• アクティブなトランザクションにより多くの変更が加えられ、これをコミットせずにサーバー インスタンスをシャットダウンした場合、次にシステムを再起動したときの復旧フェーズは 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)、トランザクション ログ内の開始レコードのログ シーケンス番号 (LSN) (database_transaction_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 でデッドロックを分析および防止する」を参照してください。

関連するコンテンツ

• 行のバージョン管理のオーバーヘッド
• 拡張イベントの概要
• sys.dm_tran_locks (Transact-SQL)
• システム動的管理ビュー
• トランザクション関連の動的管理ビューおよび関数 (Transact-SQL)
• SQL Server のブロックの問題の理解と解決
• Azure SQL Database のブロックの問題の概要と解決策