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

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

トランザクションの基礎

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

明示的なトランザクションとは、API 関数を通じて、または Transact-SQL、BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION、または ROLLBACK WORK Transact-SQL ステートメントを発行することによって、トランザクションの開始と終了の両方を明示的に定義するトランザクションです。 トランザクションが終了すると、接続は明示的なトランザクションを開始する前のトランザクション モード、つまり暗黙のトランザクション モードまたはオートコミット モードに戻ります。

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

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• フルテキスト システム ストアド プロシージャ
• 明示的なトランザクションでも暗黙のトランザクションでも、データベース オプションを設定する sp_dboption および master データベースを変更するシステム プロシージャは使用できません。

自動コミット トランザクション

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

暗黙のトランザクション

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

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

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

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

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

分散トランザクション

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

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

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

• 準備フェーズ

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

• コミット フェーズ

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

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

トランザクションの終了

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

• コミット

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

• ロールバック

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• ロック

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

• 行のバージョン管理

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

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

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

同時実行データ アクセスの管理

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

コンカレンシーの影響

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

• 更新データの喪失

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

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

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

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

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

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

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

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

• ファントム読み取り

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

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

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

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

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

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

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

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

コンカレンシーの種類

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

行のバージョン管理分離レベル	Definition
Read Committed Snapshot (RCSI)	READ_COMMITTED_SNAPSHOT データベース オプションが、Azure SQL データベースのデフォルトの設定である ON に設定されている場合、READ COMMITTED 分離レベルでは、行のバージョン管理を使用して、ステートメント レベルの読み取り整合性が提供されます。 読み取り操作には、スキーマ安定性 (Sch-S) テーブル レベルのロックのみが必要で、ページ ロックや行ロックは不要です。 つまりデータベース エンジンでは行のバージョン管理が使用され、各ステートメントでは、トランザクション全体で一貫性のあるデータのスナップショットが使用されます。このスナップショットは、ステートメント開始時点に存在したデータのスナップショットです。 ただし、ロックは、他のトランザクションがデータを更新するのを防ぐために使用されることはありません。 ユーザー定義関数から返されるデータは、そのユーザー定義関数を含んでいるステートメントの開始後にコミットされたものである可能性があります。 READ_COMMITTED_SNAPSHOT データベース オプションが OFF (SQL Server および Azure SQL Managed Instance のデフォルト設定) に設定されている場合、READ COMMITTED 分離では共有ロックが使用され、現在のトランザクションによる読み取り操作の実行中に、その他のトランザクションが行を変更するのを防ぎます。 また、ステートメントが他のトランザクションで変更された行を読み取ろうとしても、そのトランザクションが完了するまでステートメントはブロックされます。 どちらの実装も、READ COMMITTED 分離の ISO 定義に準拠しています。
SNAPSHOT	スナップショット分離レベルでは、行のバージョン管理を使用して読み取りの一貫性をトランザクション レベルで維持します。 読み取り操作では、ページ ロックも行ロックも取得しません。スキーマ安定性 (Sch-S) テーブル ロックだけを取得します。 別のトランザクションによって変更された行を読み取る場合、読み取り操作では、トランザクションの開始時に存在していた行のバージョンを取得します。 ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON に設定されている場合、SNAPSHOT 分離のみを使用できます。 既定では、このオプションは SQL Server と Azure SQL Managed Instance のユーザー データベースの OFF に設定され、Azure SQL データベースでデータベースの ON に設定されます。 注: データベースエンジンでは、メタデータのバージョン管理はサポートされません。 そのため、スナップショット分離下で実行されている明示的なトランザクションでは、実行できる DDL 操作に制限があります。 次の DDL ステートメントは、BEGIN TRANSACTION ステートメントステートメント (ALTER TABLE、CREATE INDEX、CREATE XML INDEX、ALTER INDEX、DROP INDEX、DBCC REINDEX、ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME)、または共通言語ランタイム (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	いいえ	番号	有効
SNAPSHOT	いいえ	いいえ	いいえ
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 ののいずれかを使用して取得できます。

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

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

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

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

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

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

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

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

ロックの粒度と階層

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

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

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

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

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

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

ロック モード

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

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

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

共有ロック

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

更新ロック

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

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

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

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

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

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

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

排他ロック

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

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

インテント ロック

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

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

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

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

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

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

スキーマ ロック

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

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

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

一括更新ロック

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

• Transact-SQL BULK INSERT ステートメントまたは OPENROWSET(BULK) 関数などを使用するか、.NET SqlBulkCopy、OLEDB Fast Load API、ODBC Bulk Copy API などの一括挿入 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)	いいえ	いいえ	いいえ	いいえ	いいえ	無効

次の表を使用すると、データベース エンジンで使用できるすべてのロック モードの互換性を確認できます。

キー	説明
N	競合なし
I	Illegal
C	競合
NL	ロックなし
SCH-S	スキーマの安定性ロック
SCH-M	スキーマ変更ロック
S	共有済み
U	更新する
x	排他的
IS	意図共有
IU	意図の更新
IX	意図排他
SIU	意図の更新で共有する
SIX	意図を排他的に共有する
UIX	意図を排他的に更新する
BU	一括更新
RS-S	共有範囲共有
RS-U	共有範囲の更新
RI-N	範囲 null を挿入する
RI-S	範囲共有の挿入
RI-U	範囲更新の挿入
RI-X	範囲排他の挿入
RX-S	排他範囲共有
RX-U	排他範囲更新
RX-X	排他範囲排他

キー範囲ロック

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

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

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

キー範囲ロック モード

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

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

Range	行	モード	説明
RangeS	S	RangeS-S	共有範囲。共有リソース ロック、SERIALIZABLE 範囲スキャン。
RangeS	U	RangeS-U	共有範囲。更新リソース ロック。SERIALIZABLE 更新スキャン。
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 を使用した場合、すべての行をインデックス ページから削除すると、データベース エンジンにより直ちにインデックス ページの割り当てが解除されます。 対照的に、ROWLOCK を使用した場合、削除対象のすべての行には削除のマークが付けられるだけです。これらは、後でバックグラウンド タスクによってインデックス ページから削除されます。

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

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

DELETE mytable
WHERE name = 'Bob';

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

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

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

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

INSERT mytable VALUES ('Dan');

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

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

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

INSERT mytable VALUES ('Dan');

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

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

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

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

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

データベース エンジンは、低レベルのロックを取得すると、下位レベルのオブジェクトを含むオブジェクトにもインテント ロックを設定します。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

複数の種類があるロックのエスカレーション

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Note

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

  Note

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

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

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

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

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

動的ロック

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

データベース エンジンは、動的ロック戦略によって最も効果の高いロックを判断します。 データベース エンジンはクエリの実行時に、スキーマとクエリの特性に基づいて、どのロックが最も適切であるかを自動的に判断します。 たとえば、ロックのオーバーヘッドを軽減するために、オプティマイザーはインデックス スキャンの実行時にインデックス内のページ ロックを選択することがあります。

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

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

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

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

• Spinlock

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

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

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

• メモリ

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

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

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

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

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

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

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

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

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

ロックのパーティション分割を使用した作業

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

これらの Transact-SQL ステートメントにより、その後の例で使用するテスト オブジェクトが作成されます。

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

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

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

例 A

セッション 1:

あるトランザクションで SELECT ステートメントが実行されます。 HOLDLOCK ロック ヒントにより、このステートメントではテーブルのインテント共有 (IS) ロックが取得され、保持されます (この図では、行ロックとページ ロックは無視します)。 IS ロックは、トランザクションに割り当てられたパーティションに対してのみ取得されます。 この例では、パーティション 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 で始まるすべてのパーティションで取得する必要があることに注意してください。 X ロックはパーティション ID 0 ～ 5 のパーティションに対して取得されますが、パーティション ID 6 に対して取得された IS ロックによってブロックされます。

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

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

データ読み取り時の動作

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

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

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

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

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

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

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

データ変更時の動作

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

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

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

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

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

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

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

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

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

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

動作のまとめ

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

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

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

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

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

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

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

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

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

tempdb で使用される領域

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

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

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

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

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

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

• tempdb の完全ロールバックによって生成されなかった行バージョンにアクセスしようとしたトランザクションは、エラー 3958 で終了します。

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

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

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

バージョン ストアが tempdb にある場合、次のすべての条件下で行が初めて変更されたときに、これらの 14 バイトがデータベース行から削除されます。

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

高速データベース復旧が有効でなく、上記の条件が満たされている場合、行が変更されると、14 バイトも削除されます。

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

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

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

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

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

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

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

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

行のバージョン管理、バージョン ストア、およびスナップショット分離プロセスのパフォーマンスと問題を監視するために、データベース エンジンは動的管理ビュー (DMV) およびパフォーマンス カウンターの形式でツールを提供します。

DMV

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

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

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

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

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

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

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

  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)」をご覧ください。

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

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

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

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

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

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

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

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

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

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

  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 分離トランザクションと、行のバージョン管理を使用する READ COMMITTED トランザクションとのビヘイビアーの違いを示しています。

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

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

セッション 1:

USE AdventureWorks2022;
GO

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

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

BEGIN TRANSACTION;

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

セッション 2 :

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

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

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

セッション 1:

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

セッション 2 :

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

セッション 1:

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

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

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

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

この例では、行のバージョン管理を使用する READ COMMITTED トランザクションが、別のトランザクションと同時に実行されます。 READ COMMITTED トランザクションは、SNAPSHOT トランザクションとは異なる動作をします。 SNAPSHOT トランザクションと同様に READ COMMITTED トランザクションも、その他のトランザクションがデータを変更した後でも、バージョン管理される行を読み取ります。 ただし、SNAPSHOT トランザクションとは異なり、READ COMMITTED トランザクションは次のように動作します。

• その他のトランザクションがデータの変更をコミットした後、変更されたデータを読み取ります。
• その他のトランザクションが変更したデータを更新できます。SNAPSHOT トランザクションではできませんでした。

セッション 1:

USE AdventureWorks2022;
GO

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

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

BEGIN TRANSACTION;

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

セッション 2 :

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

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

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

セッション 1:

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

セッション 2 :

-- Commit the transaction.
COMMIT TRANSACTION;
GO

セッション 1:

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

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

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

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

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

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

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

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

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

次の Transact-SQL ステートメントにより、ALLOW_SNAPSHOT_ISOLATION が可能になります。

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

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

現在のデータベースの SNAPSHOT 分離の状態	説明
OFF	SNAPSHOT 分離トランザクションのサポートはアクティブになりません。 SNAPSHOT 分離トランザクションは許可されません。
PENDING_ON	SNAPSHOT 分離トランザクションのサポートは遷移中の状態 (OFF から ON) です。 開いているトランザクションが完了する必要があります。 SNAPSHOT 分離トランザクションは許可されません。
ON	SNAPSHOT 分離トランザクションのサポートがアクティブになります。 SNAPSHOT トランザクションは許可されません。
PENDING_OFF	SNAPSHOT 分離トランザクションのサポートは遷移中の状態 (ON から OFF) です。 これ以降に開始される SNAPSHOT トランザクションは、このデータベースにアクセスできません。 既存の SNAPSHOT トランザクションからは、このデータベースに引き続きアクセスできます。 既存の書き込みトランザクションでは、このデータベースのバージョン管理が引き続き使用されます。 状態 PENDING_OFF は、データベースの SNAPSHOT 分離状態の ON 終了時に開始されたすべての SNAPSHOT トランザクションが完了するまで OFF になりません。

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

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

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

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

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

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

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

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

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

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

• 次のコード例に示すように、ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定することによる SNAPSHOT 分離:

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

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

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

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

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

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

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

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

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

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

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

  • SNAPSHOT トランザクションの開始後、かつ SNAPSHOT トランザクションがデータベースにアクセスする前に、データベースが読み取り専用になっている場合。
  • 複数のデータベースからオブジェクトにアクセスする際に、SNAPSHOT トランザクションの開始後、かつ SNAPSHOT トランザクションがデータベースにアクセスする前に、データベースの回復を行うためにデータベースの状態を変更した場合。 たとえば、データベースが OFFLINE、続いて ONLINE に設定され、AUTO_CLOSE オプションが ON に設定されていたか、データベースがデタッチされて再アタッチされたため、データベースが自動的に閉じられ、再度開かれた場合。

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

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

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

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

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

  Note

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

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

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

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

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

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

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

SELECT @@LOCK_TIMEOUT;
GO

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

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

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

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

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

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

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

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

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

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

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

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

(14 row(s) affected)

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

ロック ヒント

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

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

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

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

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

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

-- End the transaction.
ROLLBACK;
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

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

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

BEGIN TRANSACTION InProc;

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

COMMIT TRANSACTION InProc;
GO

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

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

EXECUTE TransProc 3,'bbb';
GO

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

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

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

ROLLBACK TRANSACTION ステートメントの transaction_name パラメーターが、名前付きの入れ子構造のトランザクション セット内の内部トランザクションを参照することは許可されていません。 transaction_name は、最も外側のトランザクションのトランザクション名のみを参照できます。 外部トランザクションの名前を使用する ROLLBACK TRANSACTION transaction_name ステートメントが、入れ子構造のトランザクション セットの任意のレベルで実行されると、入れ子構造のトランザクションはすべてロールバックされます。 transaction_name パラメータのない ROLLBACK WORK または ROLLBACK TRANSACTION ステートメントが入れ子構造のトランザクションセットの任意のレベルで実行されると、最も外側のトランザクションを含むすべての入れ子構造のトランザクションがロールバックされます。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• 可能な限り、暗黙のトランザクションは使用しないでください。 暗黙のトランザクションは、その性質上、想定外のビヘイビアーを引き起こす可能性があります。 「暗黙のトランザクションとコンカレンシーの問題」を参照してください。

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

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

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

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

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

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

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

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

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

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

• sys.dm_tran_database_transactions

  この動的管理ビューは、データベース レベルでのトランザクションに関する情報を返します。 長時間実行されるトランザクションの場合、特に重要な列として、最初のログ レコードの時刻 (database_transaction_begin_time)、トランザクションの現在の状態 (database_transaction_state)、およびトランザクション ログ (database_transaction_begin_lsn) 内の開始レコードのログ シーケンス番号 (LSN) などがあります。

  詳しくは、「sys.dm_tran_database_transactions (Transact-SQL)」をご覧ください。

• DBCC OPENTRAN

  このステートメントを使用すると、トランザクションのオーナーのユーザー ID を識別できるため、適切な終了 (コミットまたはロールバック) のためのトランザクションのソースを追跡できる可能性があります。 詳しくは、「DBCC OPENTRAN (Transact-SQL)」をご覧ください。

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

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

デッドロック

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

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

関連するコンテンツ

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