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

適用対象:SQL ServerAzure SQL データベースAzure SQL Managed Instance

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

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

この記事では、遅延持続性トランザクションについて詳しく説明します。

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

トランザクションの完全持続性と遅延持続性には、いずれも長所と短所があります。 アプリケーションには、完全持続性トランザクションと遅延持続性トランザクションを混在させることができます。 ビジネス ニーズを慎重に考慮したうえで、それぞれのタイプのトランザクションの適合性を検討する必要があります。

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

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

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

• ボトルネックの原因がトランザクション ログの書き込み待機時間ではない場合。

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

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

• トランザクションのコミットが成功した場合、トランザクションによる変更は、システム内の他のトランザクションから認識できる状態になります。 トランザクション分離レベルの詳細については、「 SET TRANSACTION ISOLATION LEVEL (Transact-SQL) 」または「 Memory-Optimized テーブルを使用したトランザクション」を参照してください。

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

遅延されたトランザクションの永続性

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

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

• 同時実行トランザクション間でログ IO の競合が発生する可能性は高くありません。ログ バッファーは大きな単位でディスクにフラッシュできるため、競合が軽減され、スループットを向上できます。

  注

  同時実行性が高い場合、特にログ バッファーをフラッシュするよりも速く埋めてしまうと、依然としてログ I/O の競合が発生する可能性があります。

トランザクションの遅延持続性の利用が適したケース

次のような場合は、トランザクションの遅延持続性の利用が適しています。

ある程度のデータ損失を許容できます。
ある程度のデータ損失を許容できる場合 (データの大部分を確保できていれば個々のレコードがそれほど重要ではない場合など) は、遅延持続性の使用を検討することができます。 一切のデータ損失を許容できない場合は、トランザクションの遅延持続性は使用しないでください。

トランザクション ログの書き込み処理でボトルネックが発生しています。
パフォーマンスの問題がトランザクション ログの書き込みにおける待機時間によるものであれば、トランザクションの遅延持続性を使用することがアプリケーションにとってのメリットになる可能性があります。

お客様のワークロードの競合率は高いです。
競合レベルの高いワークロードがシステムに存在する場合、ロックの解放待ちに多くの時間が消費されます。 トランザクションの遅延持続性を使用すると、コミット時間を短縮できるため、早くロックを解放でき、結果として高いスループットにつながります。

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

• トランザクションのコミットが成功した場合、トランザクションによる変更は、システム内の他のトランザクションから認識できる状態になります。

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

  • 完全持続性トランザクションによって、同じデータベース内で変更が行われ、正常にコミットされた場合。

  • ユーザーがシステム ストアド プロシージャ sp_flush_log を正常に実行した場合。

    完全持続性トランザクションまたは sp_flush_log によって正常コミットされた場合、それより前にコミットされた遅延持続性トランザクションはすべて、持続可能な状態になっていることを保証されます。

  • SQL Server では、すべてのトランザクションが遅延持続性であっても、ログ生成とタイミングの両方に基づいて、ディスクへのログのフラッシュを試みます。 通常、IO デバイスが稼働状態を保っている場合、これは成功します。 ただし、SQL Server では持続性トランザクションおよび sp_flush_log 以外にハード持続性は保証されません。

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

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

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

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

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

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

強制的
この設定では、データベースにコミットされるすべてのトランザクションに遅延持続性が適用されます。 トランザクションで完全持続性 (DELAYED_DURABILITY = OFF) が指定された場合も、指定がまったく行われていない場合も、遅延持続性トランザクションになります。 データベースに対してトランザクションの遅延持続性が役立ち、アプリケーション コードの変更を行わない場合に、この設定を使用できます。

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

次のコードは、ATOMIC ブロック内で使用します。

DELAYED_DURABILITY = { OFF | ON }

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

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

コード例:

CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
/* procedure body goes here */
END

表 1: アトミックブロックの耐久性

COMMIT レベルの制御 - Transact-SQL

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

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

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

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

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

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

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

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

• 同じデータベースを変更する完全持続性トランザクションを実行する。 これにより、それまでにコミット済みの遅延持続性トランザクションのログ レコードがすべて強制的にディスクにフラッシュされます。

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

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

トランザクション レプリケーション、変更の追跡、および変更データ キャプチャ

• トランザクション レプリケーションまたは変更データ キャプチャ (CDC) がに有効なデータベースの場合、遅延持続性は使用できません。

• 遅延耐久性を伴う変更追跡がサポートされています。 Change Tracking を使用するすべてのトランザクションは、完全に永続化されます。 変更の追跡が有効なテーブルに対して書き込み操作を行うトランザクションには、変更追跡プロパティがあります。

SQL Server 2022 CU 2 および SQL Server 2019 CU 20 以降では、以下が確認されることがあります:

• Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability set 遅延持続性を有効にしたデータベースでトランザクション レプリケーションまたは変更データ キャプチャを有効にしようとした場合。

• Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabled トランザクション レプリケーションまたは変更データ キャプチャを使用して構成されているデータベースで遅延持続性を有効にしようとするとします。

クラッシュ後の復旧
一貫性は保証されますが、コミット済みの遅延持続性トランザクションから変更内容が失われる場合があります。

クロスデータベースと DTC
複数データベース間トランザクションまたは分散トランザクションの場合、データベースまたはトランザクションのコミット設定に関係なく、トランザクションには完全持続性が適用されます。

AlwaysOn 可用性グループとミラーリング
遅延持続性トランザクションでは、プライマリまたはいずれかのセカンダリに関する持続性は保証されません。 また、セカンダリ側でのトランザクションに関する情報があることは保証されません。 コミット後、同期セカンダリからの受信確認を受信する前に、制御がクライアントに返されます。 プライマリでディスクへのフラッシュが行われる際にも、セカンダリ レプリカへのレプリケーションは引き続き行われます。

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

Azure Synapse Link for SQL
遅延永続トランザクションは、Azure Synapse Link for SQL ではサポートされていません。

ログ配布
配信されるログに含まれるのは、持続可能な状態になったトランザクションのみです。

トランザクション ログ バックアップ
バックアップに含まれるのは、持続可能な状態になったトランザクションのみです。

データが失われる可能性がある場合

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

重大なイベント

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

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

遅延持続性の場合、SQL Server の予期しないシャットダウンと予期されたシャットダウン/再起動に違いはありません。 重大なイベントと同様に、データ損失に対する計画を立てる必要があります。 計画されたシャットダウン/再起動では、ディスクに書き込まれていない一部のトランザクションがシャットダウン前にディスクに保存される場合がありますが、それを予期することはできません。 計画されているかどうかに関係なく、シャットダウン/再起動によって重大なイベントと同様にデータが失われるものとして計画してください。

次のステップ

• メモリ最適化テーブルでのトランザクション