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

  • [アーティクル]

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

適用対象: 別途記載がなければ SQL Server 2005 から SQL Server 2012。

このガイドの内容

Transaction Basics

Locking and Row Versioning Basics

Locking in the Database Engine

Row Versioning-based Isolation Levels in the Database Engine

Customizing Locking and Row Versioning

Advanced Transaction Information

トランザクションの基礎

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

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

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

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

  • 持続性
    トランザクションの完了後、その結果がシステム内で持続されます。 システム障害が発生しても、変更結果は持続されます。

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

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

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

  • トランザクションの持続性を設定するログ機能。 サーバー ハードウェア、オペレーティング システム、またはデータベース エンジンのインスタンス自体で障害が発生した場合でも、データベース エンジンのインスタンスは再起動時にトランザクション ログを使用して、中断したトランザクションをシステム障害が発生した時点まで自動的にロールバックします。

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

トランザクションの制御

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

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

トランザクションの開始

SQL Server データベース エンジンのインスタンスでは、API 関数や Transact-SQL ステートメントを使用して、明示的、自動コミット、または暗黙のいずれかのトランザクションとしてトランザクションを開始できます。

  • 明示的なトランザクション
    明示的なトランザクションとは、API 関数または Transact-SQL ステートメント (BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION、または ROLLBACK WORK) の使用により、トランザクションの開始と終了を明示的に定義したものです。 トランザクションが終了すると、接続は明示的なトランザクションを開始する前のトランザクション モード、つまり暗黙のトランザクション モードまたは自動コミット モードに戻ります。

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

    ALTER DATABASE

    CREATE DATABASE

    DROP FULLTEXT INDEX

    ALTER FULLTEXT CATALOG

    CREATE FULLTEXT CATALOG

    RECONFIGURE

    ALTER FULLTEXT INDEX

    CREATE FULLTEXT INDEX

    RESTORE

    BACKUP

    DROP DATABASE

    フルテキスト システム ストアド プロシージャ

    CREATE DATABASE

    DROP FULLTEXT CATALOG

    明示的なトランザクションでも暗黙のトランザクションでも、データベース オプションを設定する sp_dboption および master データベースを変更するシステム プロシージャは使用できません。

    注意

    UPDATE STATISTICS は、明示的なトランザクションで使用できます。ただし、UPDATE STATISTICS は、このステートメントを含むトランザクションとは別にコミットされ、ロールバックすることはできません。

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

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

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

    ALTER TABLE

    FETCH

    REVOKE

    CREATE

    GRANT

    SELECT

    DELETE

    INSERT

    TRUNCATE TABLE

    DROP

    OPEN

    UPDATE

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

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

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

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

    • 準備フェーズ
      トランザクション マネージャーはコミット要求を受け取ると、そのトランザクションに関連するすべてのリソース マネージャーに準備コマンドを送ります。 各リソース マネージャーは、トランザクションを持続的にするために必要な処理をすべて実行し、そのトランザクションのログ イメージを含むすべてのバッファーをディスクにフラッシュします。 リソース マネージャーの準備フェーズが完了すると、トランザクション マネージャーに準備の成否が通知されます。

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

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

トランザクションの終了

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

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

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

注意

複数のアクティブな結果セット (MARS) をサポートできる接続では、保留中の実行要求がある間は、API 関数を使用して開始された明示的なトランザクションをコミットできません。実行中の未処理の操作があるときに、このようなトランザクションをコミットしようとするとエラーが発生します。

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

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

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

エラーが発生した場合は、修正措置 (COMMIT または ROLLBACK) をアプリケーション コードに含める必要があります。 トランザクションで発生するエラーなど、エラーを処理するための効果的なツールには Transact-SQL TRY…CATCH 構造があります。 トランザクションを含む例の詳細については、「TRY...CATCH (Transact-SQL)」を参照してください。 SQL Server 2012 以降では、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 番目の INSERT ステートメントで存在しないテーブルが参照され、実行時エラーになった後も、最初の 2 行は TestBatch テーブル内に残ります。

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

[トップに戻る] リンクで使用される矢印アイコン[Top]

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

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

  • ロック

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

  • 行のバージョン管理

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

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

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

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

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

同時実行の影響

あるユーザーがデータを変更すると、同じデータを同時に読み取ったり変更している他のユーザーに影響します。 このようなユーザーを、データに同時アクセスしているユーザーと呼びます。 データ ストレージ システムで同時実行が制御されていない場合は、次のような副作用が生じることがあります。

  • 更新データの喪失

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

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

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

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

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

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

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

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

  • ファントム読み取り

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

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

    --Transaction 2
BEGIN TRAN;
INSERT INTO dbo.employee
    SET name = 'New' WHERE ID = 5;
COMMIT;

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

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

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

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

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

同時実行の種類

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

同時実行制御の原理は、制御方法によって次の 2 種類に分類されます。

  • ペシミスティック同時実行制御

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

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

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

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

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

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

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

  • データの読み取り時にロックを獲得するかどうか、要求されるロックの種類。

  • 読み取りロックの保持期間。

  • 別のトランザクションによって変更された行を参照している読み取り操作で、次のことを行うかどうか。

    • その行に対する排他ロックが解放されるまでブロックする。

    • ステートメントまたはトランザクションの開始時に存在していた行の、コミット済みのバージョンを取得する。

    • コミットされていないデータ変更を読み取る。

重要

トランザクション分離レベルを選択しても、データ変更を保護するために獲得したロックは影響を受けません。トランザクションでは、設定されたトランザクション分離レベルに関係なく、常に、そのトランザクションで変更するデータについて排他ロックを獲得し、トランザクションが完了するまでそのロックを保持します。トランザクション分離レベルでは主に、読み取り操作に対して、他のトランザクションによって行われる変更の影響からの保護レベルを定義します。

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

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

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

分離レベル

定義

READ UNCOMMITTED

物理的に破損したデータを読み取らないことのみが保証されるようにトランザクションを分離する、最も低い分離レベル。 このレベルではダーティ リードが許可されるため、トランザクションで行われたコミットされていない変更を、他のトランザクションで読み取ることが可能です。

READ COMMITTED

トランザクションは、別のトランザクションが以前に読み取った (変更されていない) データを読み取ることができるので、最初のトランザクションが完了するまで待機する必要がありません。 データベース エンジンは、(選択されたデータに対して取得された) 書き込みロックをトランザクションの終わりまで保持しますが、読み取りロックは SELECT 操作の実行が終わると解放します。 これはデータベース エンジンの既定のレベルです。

REPEATABLE READ

データベース エンジンは、選択されたデータに対して取得された読み取り/書き込みロックをトランザクションの終わりまで保持します。 ただし、範囲ロックが管理されないため、ファントム読み取りが発生する可能性はあります。

Serializable

各トランザクションが完全に分離される、最も高い分離レベル。 データベース エンジンは、選択されたデータに対して取得された読み取り/書き込みロックを保持し、トランザクションの終わりに開放します。 範囲指定付きの WHERE 句を SELECT 操作に使用する場合には、特にファントム読み取りを回避するために範囲ロックが取得されます。

重要

SERIALIZABLE 分離レベルが要求された場合、レプリケートされたテーブルの DDL 操作やトランザクションは失敗することがあります。レプリケーションのクエリで使用されるヒントは、SERIALIZABLE 分離レベルと互換性がない可能性があるためです。

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

行のバージョン管理分離レベル

定義

READ COMMITTED SNAPSHOT

READ_COMMITTED_SNAPSHOT データベース オプションが ON に設定されている場合、READ COMMITTED 分離では、行のバージョン管理を使用して読み取りの一貫性をステートメント レベルで維持します。 読み取り操作にはテーブル レベルの SCH-S ロックだけが必要であり、ページ ロックや行ロックは不要です。 つまりデータベース エンジンでは行のバージョン管理が使用され、各ステートメントでは、トランザクション全体で一貫性のあるデータのスナップショットが使用されます。このスナップショットは、ステートメント開始時点に存在したデータのスナップショットです。 ただし、他のトランザクションによるデータ更新を防ぐためのロックは使用されません。

READ_COMMITTED_SNAPSHOT データベース オプションが OFF (既定) に設定されている場合は、READ_COMMITTED 分離に共有ロックが使用されます。これにより、現在のトランザクションでの読み取り操作中に他のトランザクションによって行が変更されるのを防ぐことができます。 また、ステートメントが他のトランザクションで変更された行を読み取ろうとしても、そのトランザクションが完了するまでステートメントはブロックされます。 どちらの実装も READ COMMITTED 分離の ISO 定義に準拠しています。

スナップショット

スナップショット分離レベルでは、行のバージョン管理を使用して読み取りの一貫性をトランザクション レベルで維持します。 読み取り操作では、ページ ロックも行ロックも獲得しません。テーブル レベルの SCH-S ロックだけを獲得します。 別のトランザクションによって変更された行を読み取るときは、トランザクションの開始時に存在していた行のバージョンを取得します。 データベースに対してスナップショット分離を使用できるのは、ALLOW_SNAPSHOT_ISOLATION データベース オプションが ON に設定されている場合のみです。 既定では、ユーザー データベースのこのオプションは OFF に設定されています。

注意

SQL Server では、メタデータのバージョン管理はサポートされません。そのため、スナップショット分離下で実行されている明示的なトランザクションでは、実行できる DDL 操作に制限があります。スナップショット分離下では、BEGIN TRANSACTION ステートメントの後に、ALTER TABLE、CREATE INDEX、CREATE XML INDEX、ALTER INDEX、DROP INDEX、DBCC REINDEX、ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME などの DDL ステートメントを実行することはできません。共通言語ランタイム (CLR) の DDL ステートメントも同様です。暗黙的なトランザクション内でスナップショット分離を使用している場合は、これらのステートメントが許可されます。暗黙的なトランザクションとは、原則的に、DDL ステートメントでもスナップショット分離のセマンティックを適用することのできる単一のステートメントをいいます。この原則に反した場合、エラー 3961 が発生し、"データベース '%.*ls' でスナップショット分離トランザクションが失敗しました。ステートメントからアクセスされるオブジェクトが、このトランザクションの開始後に別の同時トランザクションの DDL ステートメントで変更されました。メタデータはバージョン管理されないため、この操作は許可されません。メタデータに対する同時更新は、スナップショット分離と組み合わせると一貫性を損なう結果になる可能性があります。" というメッセージが表示されます。

次の表に、各分離レベルで許容されている同時実行の副作用を示します。

分離レベル

ダーティ リード

反復不可能読み取り

ファントム

READ UNCOMMITTED

可

可

可

READ COMMITTED

可

可

可

REPEATABLE READ

可

可

可

スナップショット

可

可

可

Serializable

可

可

可

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

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

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

  • ADO
    ADO アプリケーションでは、Connection オブジェクトの IsolationLevel プロパティが adXactReadUncommitted、adXactReadCommitted、adXactRepeatableRead、または adXactReadSerializable に設定されます。

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

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

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

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

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

[トップに戻る] リンクで使用される矢印アイコン[Top]

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

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

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

トランザクションでデータが変更される場合、そのトランザクションでは、トランザクションが完了するまでロックを保持して、データの変更を保護します。 トランザクションが読み取り操作を保護するために取得したロックの保持期間は、トランザクションの分離レベルの設定により異なります。 トランザクションで保持されているすべてのロックは、トランザクションが完了 (コミットまたはロールバック) した時点で解放されます。

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

ロックの粒度と階層

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

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

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

Resource

説明

RID

ヒープ内の 1 行をロックするのに使用される行識別子 (ROWID)。

KEY

シリアル化可能なトランザクションのキー範囲の保護に使用されるインデックス内の行ロック。

PAGE

データ ページやインデックス ページなど、データベース内の 8 KB のページ。

EXTENT

データ ページやインデックス ページなど、連続した 8 ページのグループ。

HoBT

ヒープまたは B ツリー。 B ツリー (インデックス)、またはクラスター化インデックスのないテーブルのヒープ データ ページを保護するロックです。

TABLE

すべてのデータとインデックスを含むテーブル全体。

FILE

データベース ファイル。

APPLICATION

アプリケーションにより指定されたリソース。

METADATA

メタデータのロック。

ALLOCATION_UNIT

アロケーション ユニット。

DATABASE

データベース全体。

注意

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

ロック モード

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

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

ロック モード

説明

共有 (S)

SELECT ステートメントなど、データの変更や更新を伴わない読み取り操作で使用します。

更新 (U)

更新可能なリソースに使用します。 複数のセッションがリソースを読み取り、ロックして、後で更新する可能性がある場合に発生する一般的な形式のデッドロックを防ぎます。

排他 (X)

INSERT、UPDATE、DELETE などのデータ変更操作に使用します。 複数の更新操作により 1 つのリソースを同時に更新しないようにするためのロック モードです。

インテント

ロック階層を設定するのに使用します。 インテント ロックの種類にはインテント共有 (IS)、インテント排他 (IX)、およびインテント排他付き共有 (SIX) があります。

スキーマ

テーブルのスキーマに依存する操作を行うときに使用します。 スキーマ ロックの種類には、スキーマ修正 (Sch-M) およびスキーマ安定度 (Sch-S) があります。

一括更新 (BU)

データを一括でテーブルにコピーするときに TABLOCK ヒントを指定して使用します。

キー範囲

トランザクション分離レベルが SERIALIZABLE のとき、クエリにより読み取られる行の範囲を保護します。 シリアル化可能トランザクションのクエリを再度実行した場合に対象となるような行を、他のトランザクションは挿入できなくなります。

共有ロック

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

更新ロック

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

このような潜在的なデッドロックの問題を解決するには、更新 (U) ロックを使用します。 更新 (U) ロックでは、1 つのリソースを一度にロックできるトランザクションは、1 つだけです。 トランザクションがリソースを変更する場合に更新 (U) ロックが排他 (X) ロックに変換されます。

排他ロック

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

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

インテント ロック

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

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

  • 他のトランザクションが上位のリソースを変更することにより下位レベルのロックを無効にしてしまうことを防ぐ。

  • データベース エンジンによって高い粒度でロックの競合を検出する効率を向上する。

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

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

ロック モード

説明

インテント共有 (IS)

下位の階層に位置するリソースの (すべてではなく) 一部に対し、要求されているかかけられている共有ロックを保護します。

インテント排他 (IX)

下位の階層に位置するリソースの (すべてではなく) 一部に対し、要求されているかかけられている排他ロックを保護します。 IX は IS のスーパーセットです。また、下位のリソースに対する共有ロックの要求を保護します。

インテント排他付き共有 (SIX)

下位の階層に位置するすべてのリソースに対し、要求されているかかけられている共有ロックを保護し、下位のリソースの (すべてではなく) 一部のインテント排他ロックを保護します。 上位リソースで同時実行している IS ロックは可能です。 たとえば、テーブルに対し SIX ロックをかけると、変更中のページにインテント排他ロックが、変更中の行に排他ロックがかかります。 1 つのリソースに対しては、一度に 1 つの SIX ロックしかかけられません。その結果、他のトランザクションによってリソースが更新されることはなくなりますが、他のトランザクションはテーブル レベルの IS ロックをかけることで下位のリソースを読み取ることができます。

インテント更新 (IU)

下位の階層に位置するすべてのリソースに対し、要求または取得された更新ロックを保護します。 IU ロックはページ リソースに対してのみ使用します。 更新操作が発生すると、IU ロックは IX ロックに変換されます。

共有インテント更新 (SIU)

S ロックと IU ロックを個別にかけるか、同時にかけるかして組み合わせたものです。 たとえば、トランザクションで PAGLOCK ヒントを指定してクエリを実行してから更新操作を実行するとします。 PAGLOCK ヒントを指定したクエリで S ロックをかけ、更新操作で IU ロックをかけます。

更新インテント排他 (UIX)

U ロックと IX ロックを個別にかけるか、同時にかけるかして組み合わせたものです。

スキーマ ロック

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

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

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

一括更新ロック

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

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

  • TABLOCK ヒントを指定した場合または sp_tableoption を使用して table lock on bulk load テーブル オプションを設定した場合。

ヒント

より制限の少ない一括更新ロックを保持する BULK INSERT ステートメントとは異なり、TABLOCK ヒントが指定された INSERT INTO...SELECT は、テーブルに対する排他的な (X) ロックを保持します。したがって、並列挿入操作を使用して行を挿入することはできません。

キー範囲ロック

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

ロックの互換性

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

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

 

既に許可されているモード

 

 

 

 

 

要求されたモード

IS

S

U

IX

SIX

×

インテント共有 (IS)

可

可

可

可

可

可

共有 (S)

可

可

可

可

可

可

更新 (U)

可

可

可

可

可

可

インテント排他 (IX)

可

可

可

可

可

可

インテント排他付き共有 (SIX)

可

可

可

可

可

可

排他 (X)

可

可

可

可

可

可

注意

インテント排他 (IX) はすべての行ではなく一部の行を更新することを指定するものなので、IX ロックは IX ロック モードと互換性があります。一部の行を読み取ったり更新したりする他のトランザクションも、他のトランザクションによって更新されている同じ行でない限り、許可されます。また、2 つのトランザクションが同じ行を更新しようとする場合は、両方のトランザクションにテーブル レベルとページ レベルの IX ロックが許可されます。ただし、行レベルの X ロックは一方のトランザクションに許可されるため、もう一方のトランザクションは、行レベルのロックが解除されるまで待機する必要があります。

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

ロックの互換性を示す表の図

バージョンの違い

先入れ先出し (FIFO) 許可アルゴリズムは、SQL Server 2000 と比較すると SQL Server 2005 では大幅に緩和されました。 この緩和により、保持されているすべてのモードや保留中のすべてのモードと互換性がある要求は影響を受けました。 このような場合、保留中の要求をすべて渡すことにより、新しいロックはすぐに許可されることがあります。 保留中のすべての要求と互換性があるため、新しく要求されたモードでは、スタベーションになることはありません。 SQL Server 2000 では、新しい要求が許可されません。これは、FIFO の実装がより厳しくなり、それまでに作成されたすべての要求が許可されるまで新しい要求を許可できないためです。 次の例では、接続 1 と接続 3 は、指定された順に SQL Server 2005 に対して実行すると許可されます。 SQL Server 2000 では、接続 1 のみが許可されます。

/* Conn 1 */
BEGIN TRAN
EXEC sp_getapplock 'amalgam-demo', 'IntentExclusive';
/* Conn 2 */
BEGIN TRAN
EXEC sp_getapplock 'amalgam-demo', 'Shared';
/* Conn 3 */
BEGIN TRAN
EXEC sp_getapplock 'amalgam-demo', 'IntentShare;

キー範囲ロック

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

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

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

キー範囲ロック モード

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

  • 範囲は 2 つの連続したインデックス エントリ間の範囲を保護するロック モードを表します。

  • 行はインデックス エントリを保護するロック モードを表します。

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

    範囲

    行

    モード

    説明

    RangeS

    S

    RangeS-S

    共有範囲。共有リソース ロック、シリアル化可能範囲スキャン。

    RangeS

    U

    RangeS-U

    共有範囲。更新リソース ロック、直列化可能更新スキャン。

    RangeI

    Null

    RangeI-N

    挿入範囲。NULL リソース ロック。新しいキーをインデックスに挿入する前に範囲をテストするのに使用します。

    RangeX

    ×

    RangeX-X

    排他範囲。排他リソース ロック。範囲内のキーを更新するのに使用します。

注意

内部 NULL ロック モードは、他のすべてのロック モードと互換性があります。

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

 

既に許可されているモード

 

 

 

 

 

 

要求されたモード

S

U

×

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

×

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 がインデックス キーに含まれている場合、キー範囲ロックだけを取得できます。

使用例

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

インデックス B-Tree を持つデータベース テーブルの図

範囲スキャン クエリ

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

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

名前が Adam と Dale の間に含まれるデータ行の範囲に対応するインデックス エントリにキー範囲ロックを設定し、上のクエリで指定した範囲内に新しい行を追加、削除することを禁止します。 この範囲の最初の名前は Adam ですが、このインデックス エントリに対する RangeS-S モードのキー範囲ロックによって、Abigail など英字 A で始まる新しい名前を Adam の前に追加することが禁止されます。 同様に、インデックス エントリ Dale に対する RangeS-S キー範囲ロックによって、Clive など英字 C で始まる新しい名前を Carlos の後に追加することが禁止されます。

注意

保持される RangeS-S ロック数は n + 1 個です。n はクエリに該当する行数です。

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

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

SELECT name
    FROM mytable
    WHERE name = 'Bill';

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

削除操作

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

DELETE mytable
    WHERE name = 'Bob';

Bob に対応するインデックス エントリに排他 (X) ロックを設定します。 他のトランザクションは、削除する値 Bob の前後に値を挿入したり、削除することができます。 ただし、値 Bob の読み取り、挿入、または削除を試みるトランザクションは、削除を実行中のトランザクションがコミットまたはロールバックするまでブロックされます。

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

挿入操作

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

INSERT mytable VALUES ('Dan');

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

動的ロック

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

コストと粒度を示す図

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

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

  • データベースの管理が簡略化されます。 データベース管理者がロック エスカレーションのしきい値を調整する必要はありません。

  • パフォーマンスが高まります。 データベース エンジンによりタスクに適したロックが使用されるので、システム オーバーヘッドが最小になります。

  • アプリケーション開発者が開発に専念できます。 ロックはデータベース エンジンにより自動的に調整されます。

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

デッドロック

デッドロックは、複数のタスクが永続的に相互ブロックすることで発生します。つまり、一方のタスクがロックを試みているリソースに他方のタスクがロックを獲得していて、これが相互に行われるとデッドロックが発生します。 次にその例を示します。

  • トランザクション A が行 1 の共有ロックを取得します。

  • トランザクション B が行 2 の共有ロックを取得します。

  • トランザクション A が行 2 の排他ロックを要求しますが、トランザクション B が完了し、B が保持している行 2 の共有ロックが解放されるまで A はブロックされます。

  • このとき、トランザクション B が行 1 の排他ロックを要求すると、トランザクション A が完了し、A が保持している行 1 の共有ロックが解放されるまで B はブロックされます。

トランザクション A は、トランザクション B が完了するまで完了できませんが、トランザクション B もトランザクション A によってブロックされます。 この状態は、循環依存関係とも呼ばれます。 トランザクション A がトランザクション B に依存し、トランザクション B がトランザクション A に依存するため、依存関係が循環します。

デッドロックになったどちらのトランザクションも、外部処理からデッドロックを解除されない限り、永久的に待機を続けます。 SQL Server データベース エンジンのデッドロック モニターでは、デッドロックになったタスクがあるかどうかの確認が定期的に行われます。 モニターによって循環依存関係が検出されると、一方のタスクがデッドロックの犠牲者として選択され、そのトランザクションはエラーで終了されます。 その結果、もう一方のタスクのトランザクションを完了できます。 トランザクションがエラーで終了したアプリケーションは、そのトランザクションを再試行できます。通常は、デッドロックの一方のトランザクションが完了してからこのトランザクションも完了します。

デッドロックが、通常のブロッキングと混同されることがあります。 あるトランザクションが、別のトランザクションによってロックされているリソースのロックを要求すると、ロックを要求したトランザクションはロックが解放されるまで待機します。 既定では、LOCK_TIMEOUT を設定しない限り、SQL Server のトランザクションはタイムアウトになりません。 この場合、ロックを要求したトランザクションはブロックされているだけで、デッドロックが発生しているわけではありません。つまり、ロックを要求したトランザクションは、ロックを所有しているトランザクションをブロックする操作を行っていません。 最終的には、ロックを所有しているトランザクションが完了してロックが解放され、ロックを要求したトランザクションがロックを取得し、続行されます。

デッドロックは、「破壊的な支配」と呼ばれることもあります。

デッドロックの状態は、リレーショナル データベース管理システムだけでなく、複数のスレッドを使用していれば、どのようなシステムでも発生する可能性があります。また、データベース オブジェクトのロック以外でも発生する可能性があります。 たとえば、マルチスレッド オペレーティング システムの 1 つのスレッドが、メモリのブロックなど、1 つ以上のリソースを取得するとします。 取得しようとしているリソースが別のスレッドに所有されている場合、最初のスレッドはリソースを所有しているスレッドがそのリソースを解放するまで待機することになります。 このとき、待機しているスレッドのことを「そのリソースについて、所有側のスレッドに対する依存関係がある」といいます。 データベース エンジンのインスタンスでは、メモリやスレッドなど、データベース以外のリソースを取得するときにデッドロックが発生する可能性があります。

トランザクションのデッドロック発生の図

この例では、トランザクション T1 は Part テーブルのロック リソースに関して、トランザクション T2 に依存関係があります。 同様に、Supplier テーブルのロック リソースに関しては、トランザクション T2 がトランザクション T1 に対する依存関係を持っています。 これらの依存関係は相互に働くため、トランザクション T1 と T2 の間でデッドロックが発生します。

デッドロックは、テーブルがパーティション分割されており、ALTER TABLE の LOCK_ESCALATION 設定が AUTO に設定されている場合にも発生することがあります。 LOCK_ESCALATION を AUTO に設定すると、データベース エンジンが TABLE レベルではなく HoBT レベルでテーブル パーティションをロックできるようになるため、同時実行性が向上します。 ただし、個々のトランザクションがテーブルのパーティション ロックを保持し、他のトランザクション パーティションのどこかをロックする必要がある場合、デッドロックが発生します。 このタイプのデッドロックは、LOCK_ESCALATION を TABLE に設定することで回避できますが、この設定を行うと、パーティションに対して大規模な更新を行う際にテーブル ロックを獲得するまで待機しなければならなくなるため、同時実行性が低下します。

デッドロックの検出と終了

デッドロックは、複数のタスクが永続的に相互ブロックすることで発生します。つまり、一方のタスクがロックを試みているリソースに他方のタスクがロックを獲得していて、これが相互に行われるとデッドロックが発生します。 次の図に、デッドロック状態の概要を示します。

  • タスク T1 は、リソース R1 のロックを所有し (R1 から T1 への矢印で表しています)、リソース R2 のロックを要求しました (T1 から R2 への矢印で表しています)。

  • タスク T2 は、リソース R2 のロックを所有し (R2 から T2 への矢印で表しています)、リソース R1 のロックを要求しました (T2 から R1 への矢印で表しています)。

  • どちらのタスクもリソースが使用できるようになるまで続行できず、どちらのリソースもタスクが続行するまで解放できないため、デッドロック状態が発生します。

デッドロック状態のタスクを示す図

SQL Server データベース エンジンは、SQL Server 内のデッドロック サイクルを自動的に検出します。 データベース エンジンがセッションの 1 つをデッドロックの対象として選択すると、現在のトランザクションはエラーで終了し、デッドロックが解除されます。

デッドロックの原因となるリソース

各ユーザー セッションに含まれている 1 つ以上のタスクが、そのセッションのためにさまざまなリソースを取得したり、リソースを取得するために待機状態にある場合があります。 次のような種類のリソースは、デッドロックの原因となるブロッキングを発生させる可能性があります。

  • ロック。 オブジェクト、ページ、行、メタデータ、およびアプリケーションなどのリソースに対してロック取得のために待機していると、デッドロックが発生する場合があります。 たとえば、トランザクション T1 では、行 r1 の共有 (S) ロックを所有しており、r2 に排他 (X) ロックがかかるのを待機しているとします。 トランザクション T2 では、r2 の共有 (S) ロックを所有しており、行 r1 に排他 (X) ロックがかかるのを待機しているとします。 この結果、T1 と T2 では、互いにロックされているリソースが解放されるのを待機するロック サイクルが発生します。

  • ワーカー スレッド。 キューに登録されたタスクが利用可能なワーカー スレッドを待機していると、デッドロックが発生する場合があります。 キューに登録されたタスクが、ワーカー スレッドをすべてブロックしているリソースを所有している場合、デッドロックが発生します。 たとえば、セッション S1 で、トランザクションが開始され、行 r1 に共有 (S) ロックがかけられ、スリープ状態になったとします。 使用可能なすべてのワーカー スレッドで実行中のアクティブなセッションは、行 r1 に排他 (X) ロックをかけようとします。 セッション S1 ではワーカー スレッドを取得できないので、トランザクションをコミットして行 r1 のロックを解放することができません。 この結果、デッドロックが発生します。

  • メモリ : 同時要求で使用できるメモリ量以上のメモリ許可を待機している場合、デッドロックが発生することがあります。 たとえば、2 つの同時実行クエリ Q1 と Q2 は、それぞれ 10 MB と 20 MB のメモリを取得するユーザー定義関数として実行されます。 各クエリで 30 MB が必要でも、使用できるメモリの合計が 20 MB の場合、Q1 および Q2 では、互いにメモリが解放されるまで待機する必要があります。その結果、デッドロックが発生します。

  • 並列クエリ実行関連のリソース。交換ポートに関連付けられたコーディネーター、プロデューサー、またはコンシューマーのスレッドが互いをブロックし、デッドロックを発生させることがあります。通常、この現象は、並列クエリに含まれていない別のプロセスを 1 つ以上含めようとした場合に発生します。 また、並列クエリの実行が開始されると、SQL Server は、現在のワークロードに基づいて並列処理の次数やワーカー スレッドの数を決定します。 たとえば、サーバーで新しいクエリの実行が開始されたり、システムのワーカー スレッドが不足したりするなど、システムのワークロードが予期せず変更される場合は、デッドロックが発生する可能性があります。

  • 複数のアクティブな結果セット (MARS) のリソース。 これらのリソースは、MARS でアクティブな複数の要求のインターリーブを制御する際に使用します。 詳細については、「SQL Server 2005 の複数のアクティブな結果セット (MARS)」を参照してください。

    • ユーザー リソース。 ユーザー アプリケーションで制御されている可能性のあるリソースをスレッドが待機している場合、そのリソースは、外部リソースまたはユーザー リソースと見なされ、ロックと同様に処理されます。

    • セッション ミューテックス。 1 つのセッションで実行中のタスクはインターリーブされます。つまり、セッションでは、一度に 1 つのタスクしか実行できません。 タスクを実行する前に、セッション ミューテックスに排他でアクセスする必要があります。

    • トランザクション ミューテックス。 1 つのトランザクションで実行中のすべてのタスクはインターリーブされます。つまり、トランザクションでは、一度に 1 つのタスクしか実行できません。 タスクを実行する前に、トランザクション ミューテックスに排他でアクセスする必要があります。

    タスクを MARS で実行するには、セッション ミューテックスを取得する必要があります。 タスクがトランザクションで実行されている場合は、トランザクション ミューテックスを取得する必要があります。 これにより、そのセッションやトランザクションでは、一度に 1 つのタスクだけがアクティブになります。 必要なミューテックスを取得後に、タスクを実行できます。 タスクが終了するか、または要求の途中で中断されると、取得とは逆の順序で、最初にトランザクション ミューテックスが解放され、次にセッション ミューテックスが解放されます。 ただし、これらのリソースでデッドロックが発生する場合があります。 次に、ユーザーの要求 U1 と U2 という 2 つのタスクが同じセッション内で実行されているコード例を示します。

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
U2:    Rs2=Command2.Execute("select colA from sometable");

    ユーザーの要求 U1 で実行されているストアド プロシージャで、セッション ミューテックスが取得されています。 このストアド プロシージャの実行に時間がかかると、データベース エンジンにより、ストアド プロシージャではユーザーからの入力を待機していると見なされます。 ユーザーの要求 U2 ではセッション ミューテックスが解放されるのを待機しているのに対し、ユーザーは U2 の結果セットが返されるのを待機しています。さらに、U1 では、ユーザー リソースが解放されるのを待機しています。 次の図に、このデッドロック状態を論理的に示します。

ユーザー プロセスのデッドロックを示す論理図

デッドロックの検出

上記のすべてのリソースには、データベース エンジンのデッドロック検出方式が適用されます。 デッドロックの検出は、データベース エンジン インスタンスのすべてのタスクの検索を定期的に開始するロック モニター スレッドにより実行されます。 検索プロセスは次のとおりです。

  • 既定の間隔は 5 秒です。

  • ロック モニター スレッドでデッドロックが検出されると、デッドロック検出の間隔は、デッドロックの頻度に応じて、5 秒より短くなります。最短の間隔は 100 ミリ秒です。

  • ロック モニター スレッドでデッドロックが検出されなくなると、データベース エンジンにより、検索の間隔は 5 秒に引き上げられます。

  • デッドロックが検出されると、ロックが解除されるのを待機する必要のある次のスレッドがデッドロック サイクルに入っていることが想定されます。 デッドロックが検出されると、最初の 2 つのロック待機では、次のデッドロックの検出間隔まで待機せず、すぐにデッドロックの検索が開始されます。 たとえば、検索の間隔が 5 秒に設定されている場合にデッドロックが検出されると、次のロック待機により、直ちにデッドロックの検出が開始されます。 このロック待機がデッドロックの一部である場合は、このロック待機は、次のデッドロックの検索時ではなく、直ちに検出されます。

通常、データベース エンジンでは、定期的なデッドロックの検出のみが実行されます。 システムでデッドロックが発生することはまれであるため、デッドロック検出を定期的に実行することにより、システムのデッドロック検出のオーバーヘッドを削減できます。

ロック モニターで、特定のスレッドに対するデッドロック検出が開始されると、スレッドが待機中のリソースが特定されます。 その後、ロック モニターでは、その特定のリソースを所有するスレッドが検出され、相互の従属性を検出するまで、スレッドのデッドロック検出が繰り返されます。 このように検出された相互の従属性により、デッドロックが発生します。

デッドロックが検出されると、データベース エンジンでは、スレッドの 1 つをデッドロックの対象として選択することによりデッドロックを終了します。 データベース エンジンでは、スレッドに対して現在実行中のバッチを終了し、デッドロックの対象のトランザクションをロールバックして、アプリケーションに 1205 エラーを返します。 デッドロックの対象のトランザクションをロールバックすると、そのトランザクションで保持されていたすべてのロックが解放されます。 ロックが解放されると、他のスレッドのトランザクションのブロックは解除され、処理を続行することができるようになります。 デッドロックの対象を示す 1205 エラーにより、デッドロックに関係するスレッドおよびリソースに関する情報がエラー ログに記録されます。

既定では、データベース エンジンにより、ロールバックに最もコストのかからないトランザクションを実行しているセッションがデッドロックの対象として選択されます。 また、ユーザーは、SET DEADLOCK_PRIORITY ステートメントを使用して、デッドロックが発生した場合のセッションの優先度を指定することもできます。 DEADLOCK_PRIORITY には、LOW、NORMAL、または HIGH を設定するか、あるいは -10 ～ 10 の範囲の整数値を設定することができます。 DEADLOCK_PRIORITY の既定値は NORMAL です。 2 つのセッションのデッドロックの優先度が異なる場合、優先度の低いセッションがデッドロックの対象として選択されます。 2 つのセッションのデッドロックの優先度が同じ場合、ロールバックに最もコストのかからないトランザクションを含むセッションがデッドロックの対象として選択されます。 デッドロック サイクルに関連するセッションのデッドロックの優先度とコストが同じ場合、対象はランダムに選択されます。

CLR を使用して作業する場合、デッドロック モニターでは、マネージ プロシージャ内でアクセスされる同期リソース (モニター、リーダー ロックとライター ロック、およびスレッド結合) のデッドロックが自動的に検出されます。 ただし、デッドロックは、デッドロックの対象として選択されたプロシージャに例外をスローすることによって解決されます。 デッドロックの対象が現在所有しているリソースは、この例外により自動的に解放されないことに注意してください。つまり、リソースは明示的に解放する必要があります。 例外の動作と一貫性があるため、デッドロックの対象の特定に使用された例外は、キャッチおよび破棄できます。

デッドロック情報ツール

デッドロック情報を表示するために、データベース エンジンには 2 種類のトレース フラグ形式での監視ツールと SQL Server Profiler の Deadlock Graph イベントが用意されています。

トレース フラグ 1204 およびトレース フラグ 1222

デッドロックが発生すると、トレース フラグ 1204 およびトレース フラグ 1222 は SQL Server のエラー ログでキャプチャされる情報を返します。 トレース フラグ 1204 では、デッドロックに関係するノードごとに形式が設定されたデッドロック情報をレポートします。 トレース フラグ 1222 では、最初にプロセスごとに、次にリソースごとにデッドロック情報の形式を設定します。 両方のトレース フラグを有効にして、同じデッドロック イベントを 2 種類の表示方法で取得することも可能です。

次の表は、トレース フラグ 1204 および 1222 のプロパティの定義の他に、類似点および相違点についても示しています。

プロパティ

トレース フラグ 1204 およびトレース フラグ 1222

トレース フラグ 1204 のみ

トレース フラグ 1222 のみ

出力形式

出力は SQL Server のエラー ログにキャプチャされます。

デッドロックに関係するノードだけが表示されます。 各ノードには専用のセクションがあり、最後のセクションではデッドロック対象が示されます。

XML スキーマ定義 (XSD) スキーマには準拠していない、XML に似た形式で情報を返します。 この形式には、3 つの主要なセクションがあります。 最初のセクションでは、デッドロック対象が宣言されます。 2 番目のセクションでは、デッドロックに関係する各プロセスが示されます。 3 番目のセクションでは、トレース フラグ 1204 のノードと同義のリソースが示されます。

識別属性

SPID:<x> ECID:<x>。並列処理を行う場合に、システム プロセス ID のスレッドを識別します。 エントリ SPID:<x> ECID:0 (<x> は SPID 値に置き換えられます) は、メイン スレッドを表します。 エントリ SPID:<x> ECID:<y> (<x> は SPID 値に置き換えられ、<y> は 0 よりも大きくなります) は、同じ SPID のサブスレッドを表します。

BatchID (トレース フラグ 1222 の sbid)。 コードの実行でロックを要求または保持しているバッチを識別します。 複数のアクティブな結果セット (MARS) が無効になっている場合、BatchID の値は 0 になります。 MARS が有効になっている場合、アクティブなバッチの値は 1 から n になります。 セッションにアクティブなバッチが存在しない場合、BatchID は 0 になります。

Mode。 スレッドによって要求、許可、または待機される特定のリソースに対して、ロックの種類を指定します。 モードには、IS (インテント共有)、S (共有)、U (更新)、IX (インテント排他)、SIX (インテント排他付き共有)、および X (排他) があります。

Line # (トレース フラグ 1222 の行)。 デッドロックが発生したときに実行されていた、現在のステートメントのバッチの行番号が表示されます。

Input Buf (トレース フラグ 1222 の inputbuf)。 現在のバッチに含まれるステートメントをすべて表示します。

Node。 デッドロック チェーンに含まれるエントリ番号を表します。

Lists。 次の一覧にロックの所有者が含まれる場合があります。

  • Grant List。 リソースの現在の所有者を列挙します。

  • Convert List。 ロックを高いレベルに変換しようとしている現在の所有者を列挙します。

  • Wait List。 リソースに対する現在の新しいロック要求を列挙します。

Statement Type。 権限を持つスレッドにおける DML ステートメントの種類 (SELECT、INSERT、UPDATE、または DELETE) を示します。

Victim Resource Owner。 SQL Server がデッドロック サイクルを解除する対象として選択する、参加スレッドを指定します。 選択したスレッドと既存のすべてのサブスレッドを終了します。

Next Branch。 デッドロック サイクルに関係する、同じ SPID からの 2 つ以上のサブスレッドを表します。

deadlock victim。 デッドロックの対象として選択されたタスクの物理メモリ アドレス (「sys.dm_os_tasks (Transact-SQL)」を参照) を表します。 デッドロックが未解決の場合は、0 になることがあります。 ロールバックを実行中のタスクは、デッドロックの対象として選択できません。

executionstack。 デッドロックの発生時に実行されている Transact-SQL コードを表します。

priority。 デッドロックの優先度を表します。 データベース エンジンでは、同時実行性を向上させるために、短期間でデッドロックの優先度が変更されることがあります。

logused。 タスクで使用されているログ領域です。

owner id。 要求を制御するトランザクションの ID です。

status。 タスクの状態。 次の値のいずれかになります。

  • pending。 ワーカー スレッドを待機しています。

  • runnable。 実行できる状態ですが、クォンタムを待機しています。

  • running。 スケジューラで現在実行中です。

  • suspended。 実行は中断されます。

  • done。 タスクが完了しました。

  • spinloop。 スピンロックが解放されるのを待機しています。

waitresource。 タスクで必要なリソースです。

waittime。 リソースを待機する時間 (ミリ秒単位) です。

schedulerid。 このタスクに関連付けられたスケジューラです。 「sys.dm_os_schedulers (Transact-SQL)」を参照してください。

hostname。 ワークステーション名です。

isolationlevel。 現在のトランザクション分離レベルです。

Xactid。 要求を制御するトランザクションの ID です。

currentdb。 データベースの ID です。

lastbatchstarted。 クライアント プロセスで最後にバッチ実行が開始した時刻です。

lastbatchcompleted。 クライアント プロセスで最後にバッチ実行が完了した時刻です。

clientoption1 と clientoption2。 このクライアント接続にオプションを設定します。 これは、通常 SET NOCOUNT や SET XACTABORT などの SET ステートメントで制御されているオプションに関する情報を含むビットマスクです。

associatedObjectId。 HoBT (ヒープまたは B-Tree) の ID を表します。

リソース属性

RID。 ロックが保持または要求されているテーブル内の単一行を識別します。 RID は RID: db_id:file_id:page_no:row_no で表されます。 たとえば、RID: 6:1:20789:0 のようにします。

OBJECT。 ロックが保持または要求されているテーブルを識別します。 OBJECT は OBJECT: db_id:object_id で表されます。 たとえば、TAB: 6:2009058193 のようにします。

KEY。 ロックが保持または要求されているインデックス内のキー範囲を識別します。 KEY は KEY: db_id:hobt_id (index key hash value) で表されます。 たとえば、KEY: 6:72057594057457664 (350007a4d329) のようにします。

PAG。 ロックが保持または要求されているページ リソースを識別します。 PAG は PAG: db_id:file_id:page_no で表されます。 たとえば、PAG: 6:1:20789 のようにします。

EXT。 エクステント構造を識別します。 EXT は EXT: db_id:file_id:extent_no で表されます。 たとえば、EXT: 6:1:9 のようにします。

DB。 データベース ロックを識別します。 DB は次のいずれかで表されます。

  • DB: db_id

  • DB: db_id[BULK-OP-DB]。データベースのバックアップに使用されたデータベース ロックを識別します。

  • DB: db_id[BULK-OP-LOG]。特定のデータベースのバックアップ ログに使用されたロックを識別します。

APP。 アプリケーション リソースに使用されたロックを識別します。 APP は APP: lock_resource で表されます。 たとえば、APP: Formf370f478 のようにします。

METADATA。 デッドロックに関係するメタデータ リソースを表します。 METADATA には多数のサブリソースがあるため、返される値はデッドロックされたサブリソースに依存します。 たとえば、METADATA.USER_TYPE は user_type_id = <integer_value> を返します。 METADATA のリソースおよびサブリソースの詳細については、「sys.dm_tran_locks (Transact-SQL)」を参照してください。

HOBT。 デッドロックに関係するヒープまたは B-Tree を表します。

このトレース フラグに限定されるリソース属性はありません。

このトレース フラグに限定されるリソース属性はありません。

トレース フラグ 1204 の例

次の例は、トレース フラグ 1204 がオンになっている場合の出力を示しています。 この場合、ノード 1 のテーブルはインデックスのないヒープ、ノード 2 のテーブルは非クラスター化インデックスのあるヒープになります。 ノード 2 のインデックス キーは、デッドロックの発生時に更新されます。

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
BEGIN TRANSACTION
   EXEC usp_p2
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0 
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
     BEGIN TRANSACTION
       EXEC usp_p1
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

トレース フラグ 1222 の例

次の例は、トレース フラグ 1222 がオンになっている場合の出力を示しています。 この場合、一方のテーブルがインデックスのないヒープになり、他方のテーブルが非クラスター化インデックスのあるヒープになります。 2 番目のテーブルでは、デッドロックの発生時にインデックス キーが更新されます。

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868 
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444 
   transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0 
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54 
   sbid=0 ecid=0 priority=0 transcount=2 
   lastbatchstarted=2005-09-05T11:22:42.733 
   lastbatchcompleted=2005-09-05T11:22:42.733 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310444 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2012.dbo.usp_p1 line=6 stmtstart=202 
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1     
    inputbuf
      BEGIN TRANSACTION
       EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380 
   waitresource=KEY: 6:72057594057457664 (350007a4d329)   
   waittime=5015 ownerId=310462 transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U 
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0 
   priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077 
   lastbatchcompleted=2005-09-05T11:22:44.077 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310462 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2012.dbo.usp_p2 line=6 stmtstart=200 
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2     
    inputbuf
      BEGIN TRANSACTION
        EXEC usp_p2    
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2012.dbo.T2 
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2012.dbo.T1 
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X 
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

Profiler の Deadlock Graph イベント

デッドロックに関係するタスクおよびリソースをグラフィカルに表す SQL Server Profiler のイベントです。 次の例は、Deadlock Graph イベントがオンになっている場合の SQL Server Profiler からの出力を示しています。

ユーザー プロセスのデッドロックを示す論理フロー図

SQL Server Profiler Deadlock Graph の実行方法の詳細については、「Deadlock Graph の保存 (SQL Server Profiler)」を参照してください。

デッドロックの処理

SQL Server データベース エンジンのインスタンスにより、デッドロックの対象となっているトランザクションが判断されると、現在のバッチが終了され、そのトランザクションがロールバックされます。また、このインスタンスからエラー メッセージ 1205 がアプリケーションに返されます。

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Transact-SQL クエリを送信するアプリケーションはどれも、デッドロックの対象と判断される可能性があるため、アプリケーションには、エラー メッセージ 1205 をトラップできるエラー ハンドラーを実装する必要があります。 このエラーがアプリケーションでトラップされない場合、アプリケーションでは自身のトランザクションがロールバックされたことが認識されず、エラーが発生することがあります。

エラー メッセージ 1205 をトラップするエラー ハンドラーを実装すると、アプリケーションでデッドロックの状況に対応し、デッドロックと関連のあるクエリの自動再送信などの救済措置を講じることができます。 クエリを自動的に再送信すると、ユーザーにデッドロックが発生したことを通知する必要はありません。

アプリケーションは、クエリを再送信する前に少しの間停止する必要があります。 この一時停止により、デッドロックにかかわっている他のトランザクションでは、処理を完了し、デッドロックのサイクルの一因となっているロックを解放することができます。 このようにすることで、再送信されたクエリからロックが要求されたときに、デッドロックが再発する可能性を最小限に抑えられます。

デッドロックの最小化

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

  • ロールバック (トランザクションが実行したすべての処理の取り消し) の対象となるトランザクションの数が減少します。

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

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

  • 同じ順序でオブジェクトにアクセスします。

  • トランザクション内でのユーザーとのやり取りを避けます。

  • トランザクションを短くして 1 つのバッチ内に収めます。

  • 低い分離レベルを使用します。

  • 行のバージョン管理に基づく分離レベルを使用します。

    • READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定して READ COMMITTED トランザクションを有効にして、行のバージョン管理を使用できるようにします。

    • スナップショット分離を使用します。

  • バインドされた接続を使用します。

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

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

デッドロック回避の図

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

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

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

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

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

低い分離レベルの使用

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

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

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

注意

一部のアプリケーションは、READ COMMITTED 分離レベルのロックおよびブロックの動作に依存します。このようなアプリケーションについては、このオプションを使用する前に多少の変更が必要になります。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL Server Profiler の Locks イベントでは、BigintData1 列により、ロックがパーティション分割されたリソースのロック パーティション ID が提供されます。

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

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

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

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

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

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

例 A

セッション 1:

あるトランザクションで SELECT ステートメントが実行されます。 HOLDLOCK ロック ヒントにより、このステートメントではテーブルのインテント共有 (IS) ロックが獲得および保持されます (ここでは、説明のため、行ロックとページ ロックは無視します)。 IS ロックは、トランザクションに割り当てられたパーティションに対してのみ獲得されます。 この例では、パーティション ID 7 に対して IS ロックが獲得されるものとします。

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

セッション 2:

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

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

セッション 1:

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

    SELECT col1
        FROM TestTable
        WITH (TABLOCKX);

例 B

セッション 1:

あるトランザクションで SELECT ステートメントが実行されます。 HOLDLOCK ロック ヒントにより、このステートメントではテーブルのインテント共有 (IS) ロックが獲得および保持されます (ここでは、説明のため、行ロックとページ ロックは無視します)。 IS ロックは、トランザクションに割り当てられたパーティションに対してのみ獲得されます。 この例では、パーティション ID 6 に対して IS ロックが獲得されるものとします。

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

セッション 2:

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

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

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

[トップに戻る] リンクで使用される矢印アイコン[Top]

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

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

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

  • トリガーに inserted テーブルと deleted テーブルを構築します。 トリガーにより変更された行はすべて、バージョン化されます。 これには、トリガーによりデータが変更された行だけでなく、トリガーを起動したステートメントにより変更された行も含まれます。

  • 複数のアクティブな結果セット (MARS) をサポートします。 アクティブな結果セットが存在するときに、MARS セッションでデータ変更ステートメント (INSERT、UPDATE、DELETE など) が実行された場合、その変更ステートメントの影響を受けた行はバージョン化されます。

  • ONLINE オプションを指定するインデックス操作をサポートします。

  • 行のバージョン管理に基づく次のトランザクション分離レベルをサポートします。

    • 行のバージョン管理を使用してステートメントレベルの読み取りの一貫性を保証する新しい READ COMMITTED 分離レベルの実装。

    • トランザクションレベルの読み取りの一貫性を保証する新しいスナップショット分離レベル。

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

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

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

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

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

    • ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定すると、トランザクションでスナップショット分離レベルを設定できます。

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

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

注意

ラージ オブジェクト (LOB) の変更については、変更された部分のみが tempdb のバージョン ストアにコピーされます。

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

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

注意

トランザクションの実行時間が短い場合、変更された行のバージョンは、tempdb データベースのディスク ファイルに書き込まれずにバッファー プールにキャッシュされる場合があります。バージョン管理された行が必要とされる時間が短い場合、その行のバージョンは単純にバッファー プールから削除されるので、I/O のオーバーヘッドが発生しない場合もあります。

データ読み取り時の動作

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

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

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

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

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

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

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

注意

行のバージョン管理を使用する READ COMMITTED トランザクションが、トランザクション全体で一貫性のあるデータのビューをステートメント レベルで提供しても、この種類のトランザクションにより生成またはアクセスされる行バージョンは、トランザクションが完了するまで保持されます。

データ変更時の動作

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

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

注意

スナップショット分離レベルで実行されている更新操作は、スナップショット トランザクションにより次のアイテムへのアクセスが行われたときに、内部的に READ COMMITTED 分離レベルで実行されます。

FOREIGN KEY 制約が適用されたテーブル。

別のテーブルの FOREIGN KEY 制約で参照されるテーブル。

複数のテーブルを参照するインデックス付きビュー。

ただしこのような状況でも、更新操作では、データが別のトランザクションにより変更されていないかどうかが引き続き確認されます。データが別のトランザクションで変更されている場合は、更新の競合が発生し、スナップショット トランザクションは終了します。

動作のまとめ

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

プロパティ

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

スナップショット分離レベル

必要なサポートを有効にするために ON に設定されている必要があるデータベース オプション

READ_COMMITTED_SNAPSHOT

ALLOW_SNAPSHOT_ISOLATION

セッションが特定の種類の行のバージョン管理を要求する方法

既定の READ COMMITTED 分離レベルを使用するか、または SET TRANSACTION ISOLATION LEVEL ステートメントを実行して READ COMMITTED 分離レベルを指定します。 この操作は、トランザクションの開始後に実行できます。

トランザクションの開始前に、SET TRANSACTION ISOLATION LEVEL を実行してスナップショット分離レベルを指定するように要求します。

ステートメントにより読み取られるデータのバージョン

各ステートメントの開始前にコミットされたすべてのデータ。

各トランザクションの開始前にコミットされたすべてのデータ。

更新の処理方法

行のバージョンを実際のデータに戻して更新する行を選択し、選択したデータ行に対して更新ロックをかけます。 また、変更する実際のデータ行の排他ロックを獲得します。 更新の競合検出は行われません。

行バージョンを使用して更新する行を選択します。 変更する行の実際のデータに対する排他ロックの獲得を試行します。このデータが別のトランザクションにより変更されている場合は、更新の競合が発生し、スナップショット トランザクションは終了します。

更新の競合検出

ありません。

組み込みによるサポート。 この機能は無効にできません。

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

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

  • トリガー

  • 複数のアクティブな結果セット (MARS)

  • オンラインのインデックス構築

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

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

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

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

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

tempdb で使用される領域

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

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

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

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

  • トランザクションで、行のバージョン管理に基づく分離が使用されています。

  • トランザクションで、トリガー、MARS、またはオンラインのインデックス構築のいずれかの操作が使用されています。

  • トランザクションで、行バージョンが生成されます。

注意

トランザクションの内部でトリガーが呼び出されるときに、トリガーによって作成される行バージョンは、トリガーの完了後に不要になる行バージョンであっても、トランザクションの終了時まで保持されます。これは、行のバージョン管理を使用する READ COMMITTED トランザクションにも適用されます。この種類のトランザクションを使用すると、トランザクション内の各ステートメントに対してだけ、トランザクション全体で一貫性のあるデータベース ビューが必要です。つまり、トランザクションでステートメントに対して作成される行バージョンは、ステートメントが完了した後には不要になります。ただし、トランザクションで各ステートメントによって作成される行バージョンは、トランザクションが完了するまで保持されます。

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

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

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

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

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

  • READ_COMMITTED_SNAPSHOT オプションまたは ALLOW_SNAPSHOT_ISOLATION オプションが ON になっています。

  • テーブルにトリガーが含まれています。

  • 複数のアクティブな結果セット (MARS) が使用されています。

  • オンラインのインデックス構築操作が、現在そのテーブルで実行されています。

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

  • READ_COMMITTED_SNAPSHOT オプションと ALLOW_SNAPSHOT_ISOLATION オプションが OFF になっています。

  • テーブルに既にトリガーが存在しません。

  • MARS が使用されていません。

  • オンラインのインデックス構築操作が現在実行されていません。

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

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

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

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

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

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

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

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

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

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

DMV

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

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

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

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

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

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

注意

sys.dm_tran_top_version_generators と sys.dm_tran_version_store では、非常に大きくなる可能性があるバージョン ストア全体に対してクエリが実行されるので、これらの関数を実行すると非常に多くのリソースが使用される可能性があります。

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

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

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

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

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

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

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

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

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

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

注意

Version Generation rate (KB/s) と Version Cleanup rate (KB/s) の情報は、tempdb の必要な空き領域の予測に使用できます。

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

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

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

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

Longest Transaction Running Time。 行のバージョン管理を使用しているトランザクションの最長実行時間 (秒) を監視します。 このパフォーマンス カウンターを使用して、トランザクションの実行時間が不適切でないかどうかを判断できます。

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

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

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

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

注意

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

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

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

A.スナップショット分離の使用

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

セッション 1 :

USE AdventureWorks2012;  -- Or the 2008 or 2008R2 version of the AdventureWorks database.
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2012
    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 AdventureWorks2012;
GO

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

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

セッション 1 :

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

セッション 2 :

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

セッション 1 :

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

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

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

B.行のバージョン管理を使用する Read Committed

この例では、行のバージョン管理を使用する Read Committed トランザクションを、別のトランザクションと同時に実行しています。 Read Committed トランザクションは、スナップショット トランザクションとは異なる動作をします。 スナップショット トランザクションと同様に Read Committed トランザクションも、別のトランザクションがデータを変更した後でも、バージョン管理される行を読み取ります。 ただし、スナップショット トランザクションとは異なり、Read Committed トランザクションは以下のように動作します。

  • 別のトランザクションがデータの変更をコミットした後は、変更されたデータを読み取ります。

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

セッション 1 :

USE AdventureWorks2012;  -- Or any earlier version of the AdventureWorks database.
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2012
-- database.
ALTER DATABASE AdventureWorks2012
    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 AdventureWorks2012;
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 AdventureWorks2012
    SET READ_COMMITTED_SNAPSHOT ON;

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

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

ALTER DATABASE AdventureWorks2012
    SET ALLOW_SNAPSHOT_ISOLATION ON;

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

現在のデータベースのスナップショット分離フレームワークの状態

説明

OFF

スナップショット分離トランザクションのサポートはアクティブになりません。 スナップショット分離トランザクションは許可されません。

PENDING_ON

スナップショット分離トランザクションのサポートが遷移中の状態 (OFF から ON) です。 開いているトランザクションが完了する必要があります。

スナップショット分離トランザクションは許可されません。

ON

スナップショット分離トランザクションのサポートがアクティブになります。

スナップショット トランザクションが許可されます。

PENDING_OFF

スナップショット分離トランザクションのサポートが遷移中の状態 (ON から OFF) です。

これ以降に開始されるスナップショット トランザクションは、このデータベースにアクセスできません。 このデータベースのバージョン管理の負荷は、依然として更新トランザクションが担っています。 既存のスナップショット トランザクションからは、このデータベースに引き続き問題なくアクセスできます。 PENDING_OFF の状態は、データベースのスナップショット分離の状態が ON のときにアクティブであったすべてのスナップショット トランザクションが完了するまで OFF になりません。

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

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

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

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

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

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

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

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

    ALTER DATABASE AdventureWorks2012
    SET READ_COMMITTED_SNAPSHOT ON;

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

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

    ALTER DATABASE AdventureWorks2012
    SET ALLOW_SNAPSHOT_ISOLATION ON;

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

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

    次に、トランザクション分離レベルを Read Committed に変更するように変更した同じ SELECT ステートメントのコード例を示します。 この変更により、SELECT ステートメントは正常に実行されます。

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

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

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

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

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

    • tempdb で ALLOW_SNAPSHOT_ISOLATION データベース オプションを ON に設定します。

    • 分離ヒントを使用して、ステートメントの分離レベルを変更します。

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

    • スナップショット トランザクションが開始してからデータベースにアクセスするまで、データベースが読み取り専用になっている場合。

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

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

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

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

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

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

    注意

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

[トップに戻る] リンクで使用される矢印アイコン[Top]

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

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

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

注意

SQL Server では、sys.dm_os_waiting_tasks 動的管理ビューを使用して、特定のプロセスがブロックされているかどうか、またどのプロセスがブロックしているかを判断できます。以前のバージョンの SQL Server では、sp_who システム ストアド プロシージャを使用していました。

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

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

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

SELECT @@lock_timeout;
GO

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

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

  • SET TRANSACTION ISOLATION LEVEL ステートメントを実行します。

  • System.Data.SqlClient マネージ名前空間を使用している ADO.NET アプリケーションでは、SqlConnection.BeginTransaction メソッドを使用して IsolationLevel オプションを指定できます。

  • ADO を使用するアプリケーションでは、Autocommit Isolation Levels プロパティを設定できます。

  • トランザクションを開始するとき、OLE DB を使用しているアプリケーションでは、isoLevel を必要なトランザクション分離レベルに設定して ITransactionLocal::StartTransaction を呼び出すことができます。 OLE DB を使用するアプリケーションでは、自動コミット モードの分離レベルを指定するときに、DBPROPSET_SESSION プロパティの DBPROP_SESS_AUTOCOMMITISOLEVELS を必要なトランザクション分離レベルに設定できます。

  • ODBC を使用するアプリケーションでは、SQLSetConnectAttr を使用して SQL_COPT_SS_TXN_ISOLATION 属性を設定できます。

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

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

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

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

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

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

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

以下に結果セットを示します。

Set Option Value

---------------------------- -------------------------------------------

textsize 2147483647

language us_english

dateformat mdy

datefirst 7

... ...

Isolation level repeatable read

(14 row(s) affected)

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

ロック ヒント

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

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

注意

データベース エンジンのクエリ オプティマイザーでは、ほとんどの場合、適切なロック レベルが選択されます。必要な場合に限り、テーブルレベルのロック ヒントを使用して既定のロック動作を変更することをお勧めします。ロック レベルを禁止すると、同時実行性が低下することがあります。

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

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

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

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

-- End the transaction.
ROLLBACK;
GO

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

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

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

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

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

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

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

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

禁止されるロック

インデックスにかけられるロック

ページレベルのロック

行レベルおよびテーブルレベルのロック

行レベルのロック

ページレベルおよびテーブルレベルのロック

ページレベルおよび行レベルのロック

テーブルレベルのロック

[トップに戻る] リンクで使用される矢印アイコン[Top]

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

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

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

次の例は、入れ子構造のトランザクションの使用方法を示しています。 プロシージャ TransProc は、プロセスのトランザクション モードに関係なくトランザクションを実行します。 トランザクションがアクティブであるときに TransProc を呼び出すと、TransProc 内の入れ子になっているトランザクションは概して無視され、外側のトランザクションに対して行った最終的な操作に基づいて INSERT ステートメントがコミットまたはロールバックされます。 未完了のトランザクションがないプロセスが TransProc を実行した場合は、プロシージャの最後に INSERT ステートメントが COMMIT TRANSACTION によって有効にコミットされます。

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
               Colb CHAR(3) NOT NULL);
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc;
GO
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
EXECUTE TransProc 3,'bbb';
GO
/* The following SELECT statement shows only rows 3 and 4 are 
   still in the table. This indicates that the commit
   of the inner transaction from the first EXECUTE statement of
   TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans;
GO

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

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

ROLLBACK TRANSACTION ステートメントの transaction_name パラメーターで、入れ子構造になっている一連の名前付きトランザクションのうち、内側のトランザクションを参照するのは正しくない操作です。 transaction_name で参照できるのは、最も外側のトランザクションの名前のみです。 入れ子構造になっている一連のトランザクションの任意のレベルで、外側のトランザクションの名前を使用して ROLLBACK TRANSACTION transaction_name ステートメントで実行すると、入れ子構造のトランザクションすべてがロールバックされます。 入れ子構造になっている一連のトランザクションの任意のレベルで、transaction_name パラメーターを指定せずに ROLLBACK WORK ステートメントまたは ROLLBACK TRANSACTION ステートメントを実行すると、最も外側のトランザクションを含めて、入れ子構造のトランザクションすべてがロールバックされます。

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

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

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

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

注意

sp_getbindtoken または srv_getbindtoken が成功するには、セッションにアクティブなユーザー トランザクションが含まれている必要があります。

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

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

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

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

バインドされたセッションのうち、一度にアクティブにできるのは 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 つとして渡せば、プロシージャは呼び出し側プロセスのトランザクション領域を結合できます。これにより、拡張ストアド プロシージャを呼び出し元プロセスに統合できます。

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

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

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

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

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

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

  • トランザクション中にユーザーによる入力を求めないようにします。

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

  • データの参照中は、できるだけトランザクションを開かないようにします。

    トランザクションは、事前のすべてのデータ分析が完了するまで開始しないでください。

  • トランザクションはできるだけ短くします。

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

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

  • 低いトランザクション分離レベルを賢く利用します。

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

  • オプティミスティック同時実行制御オプションなど、カーソルの同時実行性が低いオプションを賢く利用します。

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

  • トランザクション中は、アクセスするデータ量をできるだけ少なくします。

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

同時実行とリソースの問題の回避

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

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

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

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

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

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

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

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

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

  • sys.dm_tran_database_transactions

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

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

  • DBCC OPENTRAN

    このステートメントを使用すると、トランザクション所有者のユーザー ID を特定できます。これにより、トランザクションの実行元を特定して、より規則正しくトランザクションを終了する (トランザクションをロールバックするのではなくコミットする) ことができます。 詳細については、「DBCC OPENTRAN (Transact-SQL)」を参照してください。

トランザクションの停止

KILL ステートメントの使用が必要になる場合もあります。 ただし、重要なプロセスが実行中の場合は特に、このステートメントの使用には十分注意してください。 詳細については、「KILL (Transact-SQL)」を参照してください。

[トップに戻る] リンクで使用される矢印アイコン[Top]

関連項目

その他の技術情報

SQL Server 2005 での行のバージョン管理ベースのトランザクション分離

行のバージョン管理のオーバーヘッド

SQL Server 2008 で自律トランザクションを作成する方法