次の方法で共有

トランザクションの持続性を制御する

SQL Server トランザクションのコミットには、完全持続性、SQL Server の既定値、遅延持続性 (遅延コミットとも呼ばれます) のいずれかを指定できます。

完全に永続的なトランザクション コミットは同期的であり、コミットを成功として報告し、トランザクションのログ レコードがディスクに書き込まれた後にのみ、制御をクライアントに返します。 遅延持続性トランザクションのコミットは非同期的であり、トランザクションのログ レコードがディスクに書き込まれる前にコミットが正常完了として報告されます。 トランザクションを持続可能にするためには、トランザクション ログ エントリをディスクに書き込む必要があります。 遅延持続性トランザクションは、トランザクション ログ エントリがディスクにフラッシュされる時点で持続的になります。

このトピックでは、遅延された永続化トランザクションについて詳しく説明します。

トランザクションの完全持続性と遅延トランザクションの持続性

完全および遅延トランザクションの持続性には、長所と短所があります。 アプリケーションには、完全な耐久性のあるトランザクションと遅延耐久性のあるトランザクションを混在させることができます。 ビジネス ニーズと、それぞれのニーズにどのように適合するかを慎重に検討する必要があります。

トランザクションの完全持続性

完全持続性トランザクションは、制御をクライアントに返す前に、トランザクション ログをディスクに書き込みます。 次の場合は常に、完全に永続的なトランザクションを使用する必要があります。

  • システムはデータ損失を許容できません。
    データの一部を 失う 可能性がある場合の詳細については、「データが失われる可能性がある場合」セクションを参照してください。

  • ボトルネックは、トランザクション ログの書き込み待機時間が原因ではありません。

トランザクションの持続性が遅延すると、トランザクション ログ レコードをメモリに保持し、トランザクション ログにバッチで書き込むことで、ログ I/O による待機時間が短縮されるため、必要な I/O 操作が少なくなります。 トランザクションの持続性が遅延すると、ログ I/O の競合が減り、システムでの待機が減る可能性があります。

完全なトランザクション持続性の保証

  • トランザクションのコミットが成功すると、トランザクションによって行われた変更は、システム内の他のトランザクションに表示されます。 詳細については、「 トランザクション分離レベル 」を参照してください。

  • 持続性はコミット時に保証されます。 対応するログ レコードは、トランザクションのコミットが成功し、制御がクライアントに返される前にディスクに保持されます。

トランザクションの持続性の遅延

遅延トランザクションの持続性は、ディスクへの非同期ログ書き込みを使用して実現されます。 トランザクション ログ レコードはバッファーに保持され、バッファーがいっぱいになるか、バッファー フラッシュ イベントが発生したときにディスクに書き込まれます。 トランザクションの持続性が遅延すると、システム内の待機時間と競合の両方が削減されます。理由は次のとおりです。

  • トランザクション コミット処理は、ログ IO の完了を待機せず、制御をクライアントに返します。

  • 同時実行トランザクションは、ログ IO に競合する可能性が低くなります。代わりに、ログ バッファーを大きなチャンクでディスクにフラッシュし、競合を減らし、スループットを向上させることができます。

    注

    コンカレンシーが高い場合は、ログ I/O の競合が引き続き発生する可能性があります。特に、ログ バッファーをフラッシュするよりも高速にログ バッファーを埋める場合です。

遅延トランザクションの持続性を使用する場合

遅延トランザクションの持続性を使用するとメリットが得られるケースの一部を次に示します。

一部のデータ損失を許容できます。
たとえば、ほとんどのデータがある限り個々のレコードが重要でない場合など、データ損失を許容できる場合は、遅延持続性を考慮する必要があります。 データ損失を許容できない場合は、遅延トランザクションの持続性を使用しないでください。

トランザクション ログの書き込みでボトルネックが発生しています。
パフォーマンスの問題がトランザクション ログの書き込みの待機時間に起因する場合、アプリケーションはトランザクションの持続性の遅延を使用することでメリットが得られる可能性があります。

ワークロードの競合率が高い。
システムに高い競合レベルのワークロードがある場合、ロックが解放されるまでの待ち時間が大幅に失われます。 トランザクションの持続性が遅延するとコミット時間が短縮され、ロックがより早く解除され、その結果スループットが向上します。

遅延トランザクションの持続性の保証

  • トランザクションのコミットが成功すると、トランザクションによって行われた変更は、システム内の他のトランザクションに表示されます。

  • トランザクションの持続性は、メモリ内トランザクション ログがディスクにフラッシュされた後でのみ保証されます。 メモリ内トランザクション ログは、次の場合にディスクにフラッシュされます。

    • 同じデータベース内の完全に永続的なトランザクションは、データベースに変更を加え、正常にコミットします。

    • ユーザーは、システム ストアド プロシージャ sp_flush_log を正常に実行します。

    • メモリ内トランザクション ログ バッファーがいっぱいになり、ディスクに自動的にフラッシュされます。

    完全に永続的なトランザクションまたはsp_flush_logが正常にコミットされた場合、以前にコミットされたすべての遅延耐久性トランザクションが永続化されます。

ログは定期的にディスクにフラッシュされる場合があります。 ただし、SQL Server では、永続的なトランザクションとsp_flush_log以外の持続性の保証は提供されません。

トランザクションの持続性を制御する方法

データベース レベルの制御

DBA は、次のステートメントを使用して、ユーザーがデータベースで遅延トランザクション持続性を使用できるかどうかを制御できます。 ALTER DATABASE で遅延持続性の設定を設定する必要があります。

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

DISABLED
[既定値] この設定では、データベース上でコミットされる全てのトランザクションが完全に耐久性を持ち、コミットレベルの設定 (DELAYED_DURABILITY=[ON | OFF]) には関係なく適用されます。 ストアド プロシージャの変更と再コンパイルは必要ありません。 これにより、遅延持続性によってデータが危険にさらされないようにすることができます。

ALLOWED
この設定では、各トランザクションの持続性はトランザクション レベルで決定されます - DELAYED_DURABILITY = { OFF |ON }。 詳細については、「 アトミック ブロック レベルコントロール - ネイティブ コンパイル ストアド プロシージャ と COMMIT レベル制御 - Transact-SQL 」を参照してください。

FORCED
この設定では、データベースでコミットされるすべてのトランザクションが遅延耐久性を持つことになります。 トランザクションが完全持続性 (DELAYED_DURABILITY = OFF) を指定するか、指定しないかにかかわらず、トランザクションは遅延持続性になります。 この設定は、遅延トランザクションの持続性がデータベースに役立ち、アプリケーション コードを変更しない場合に便利です。

アトミック ブロック レベルの制御 - ネイティブ コンパイル ストアド プロシージャ

次のコードは、アトミック ブロック内に入ります。

DELAYED_DURABILITY = { OFF | ON }

OFF
[既定値]データベース オプション DELAYED_DURABLITY = FORCED が有効な場合を除き、トランザクションは完全に永続的です。この場合、コミットは非同期であるため、持続性が遅延します。 詳細については、「 データベース レベルの制御 」を参照してください。

ON
データベース オプション DELAYED_DURABLITY = DISABLED が有効でない限り、トランザクションは永続的に遅延します。その場合、コミットは同期的であり、完全に永続的です。 詳細については、「 データベース レベルの制御 」を参照してください。

コード例:

CREATE PROCEDURE <procedureName> ...  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  
    DELAYED_DURABILITY = ON,  
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
    LANGUAGE = N'English'  
    ...  
)  
END

表 1: アトミック ブロックの持続性

アトミック ブロックの耐久性オプション 既存のトランザクションがない 処理中のトランザクション (完全耐久性または遅延耐久性)
DELAYED_DURABILITY = OFF アトミック ブロックは、新しい完全持続性トランザクションを開始します。 アトミック ブロックは、既存のトランザクションにセーブ ポイントを作成し、新しいトランザクションを開始します。
DELAYED_DURABILITY = ON アトミック ブロックは、新しい遅延持続性トランザクションを開始します。 アトミック ブロックは、既存のトランザクションにセーブ ポイントを作成し、新しいトランザクションを開始します。

COMMIT レベル制御 - (T-SQL)

COMMIT 構文が拡張されているため、遅延トランザクションの持続性を強制できます。 DELAYED_DURABILITYがデータベース レベルで DISABLED または FORCED の場合 (上記参照)、この COMMIT オプションは無視されます。

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

OFF
[既定値]データベース オプション DELAYED_DURABLITY = FORCED が有効でない限り、トランザクション COMMIT は完全に永続的です。この場合、COMMIT は非同期であるため、持続性が遅延します。 詳細については、「 データベース レベルの制御 」を参照してください。

ON
データベース オプション DELAYED_DURABLITY = DISABLED が有効でない限り、トランザクション COMMIT は永続的に遅延します。この場合、COMMIT は同期的であり、完全に永続的です。 詳細については、「 データベース レベルの制御 」を参照してください。

オプションとその相互作用の概要

次の表は、データベース レベルの遅延持続性設定とコミット レベル設定の間の相互作用をまとめたものです。 データベース レベルの設定は、コミット レベルの設定よりも常に優先されます。

COMMIT 設定/データベース設定 DELAYED_DURABILITY = DISABLED DELAYED_DURABILITY = 許可 DELAYED_DURABILITY = 強制的
DELAYED_DURABILITY = OFF データベース レベルのトランザクション。 トランザクションは完全に永続的です。 トランザクションは完全に永続的です。 トランザクションの遅延が持続します。
DELAYED_DURABILITY = ON データベース レベルのトランザクション。 トランザクションは完全に永続的です。 トランザクションは遅延持続性があります。 トランザクションは遅延持続性があります。
DELAYED_DURABILITY = OFF データベース間または分散トランザクション。 トランザクションは完全に永続的です。 トランザクションは完全に永続的です。 トランザクションは完全に永続的です。
DELAYED_DURABILITY = ON データベース間または分散トランザクション。 トランザクションは完全に永続的です。 トランザクションは完全に永続的です。 トランザクションは完全に永続的です。

トランザクション ログを強制的にフラッシュする方法

トランザクション ログを強制的にディスクにフラッシュする方法は 2 つあります。

  • 同じデータベースを変更する完全に永続的なトランザクションを実行します。 これにより、前にコミットされた遅延持続性のある全てのトランザクションのログレコードが強制的にディスクに書き込まれます。

  • システム ストアド プロシージャ sp_flush_logを実行します。 この手順では、前にコミットされたすべての遅延持続性トランザクションのログ レコードを強制的にディスクにフラッシュします。 詳細については、 sys.sp_flush_log (Transact-SQL) を参照してください。

遅延持続性とその他の SQL Server 機能

変更の追跡と変更データ キャプチャ
変更追跡を使用するすべてのトランザクションは、完全に永続的です。 変更追跡が有効になっているテーブルに対して書き込み操作を行う場合、トランザクションには変更追跡プロパティがあります。 変更データ キャプチャ (CDC) を使用するデータベースでは、遅延持続性の使用はサポートされていません。

クラッシュ復旧
一貫性は保証されますが、コミットされた遅延耐久トランザクションからの一部の変更が失われることがあります。

データベース間および DTC
トランザクションがデータベース間または分散されている場合、データベースまたはトランザクションのコミット設定に関係なく、完全に永続的である場合。

Always On 可用性グループとミラーリング
遅延持続性トランザクションでは、プライマリまたはセカンダリの持続性は保証されません。 また、セカンダリでのトランザクションに関する知識は保証されません。 コミット後、同期セカンダリから受信確認が受信される前に、制御がクライアントに返されます。

フェールオーバー クラスタリング
一部の遅延持続性トランザクション書き込みが失われる可能性があります。

トランザクション レプリケーション
トランザクション レプリケーションでは、遅延が発生する持続可能なトランザクションはサポートされていません。

ログ配布
永続化されたトランザクションのみが、転送されるログに含まれます。

ログ バックアップ
バックアップには、永続的に行われたトランザクションのみが含まれます。

データが失われるのはいつですか?

いずれかのテーブルに遅延持続性を実装する場合は、特定の状況がデータ損失につながる可能性があることを理解する必要があります。 データ損失を許容できない場合は、テーブルに遅延持続性を使用しないでください。

致命的なイベント

サーバーのクラッシュのような致命的なイベントが発生した場合、ディスクに保存されていないコミットされたすべてのトランザクションのデータが失われます。 遅延持続性トランザクションは、データベース内の任意のテーブル (永続メモリ最適化またはディスク ベース) に対して完全持続性トランザクションが実行されるたびにディスクに保存されるか、 sp_flush_log が呼び出されます。 遅延持続性トランザクションを使用している場合は、データベースに小さなテーブルを作成し、 sp_flush_log を定期的に更新または定期的に呼び出して、未処理のコミット済みトランザクションをすべて保存することができます。 トランザクション ログも満杯になるとフラッシュされますが、予測が難しく、制御できません。

SQL Server のシャットダウンと再起動

遅延持続性の場合、予期しないシャットダウンと SQL Server の予期されるシャットダウン/再起動に違いはありません。 致命的なイベントと同様に、データ損失を計画する必要があります。 計画的なシャットダウン/再起動では、ディスクに書き込まれていない一部のトランザクションが最初にディスクに保存される可能性がありますが、計画しないでください。 計画的または計画外のシャットダウンや再起動が発生した場合に、あたかも致命的なイベントでデータが失われたかのように計画を立ててください。

こちらもご覧ください

トランザクション分離レベル
Memory-Optimized テーブルでのトランザクション分離レベルのガイドライン