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

[アーティクル]
03/16/2023

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

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

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

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

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

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

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

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

システムで、データの損失が許容されない場合。データの一部が失われるケースについては、「データが失われるケース」のセクションを参照してください。
ボトルネックの原因がトランザクションログの書き込み待機時間ではない場合。

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

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

トランザクションのコミットが成功した場合、トランザクションによる変更は、システム内の他のトランザクションから認識できる状態になります。トランザクション分離レベルの詳細については、「SET TRANSACTION ISOLATION LEVEL (Transact-SQL)」または「メモリ最適化テーブルでのトランザクション」を参照してください。
持続性はコミット時に保証されます。対応するログレコードがディスクに保存されてからトランザクションのコミットが成功となり、制御がクライアントに返されます。

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

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

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

Note

ただしログバッファーがフラッシュされる前にいっぱいになる場合など、コンカレンシーが高い場合は、ログ 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 }

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

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

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

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

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

DELAYED_DURABILITY = { OFF | ON }

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

ON
データベースオプション 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: ATOMIC ブロックの持続性

ATOMIC ブロックの持続性オプション	既存のトランザクションが存在しない場合	処理中の (完全持続性または遅延持続性) トランザクションが存在する場合
DELAYED_DURABILITY = OFF	ATOMIC ブロックで、新しい完全持続性トランザクションが開始されます。	ATOMIC ブロックで、既存のトランザクションにセーブポイントが作成され、新しいトランザクションが開始されます。
DELAYED_DURABILITY = ON	ATOMIC ブロックで、新しい遅延持続性トランザクションが開始されます。	ATOMIC ブロックで、既存のトランザクションにセーブポイントが作成され、新しいトランザクションが開始されます。

COMMIT レベルの制御 - Transact-SQL

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

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

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

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

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

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

COMMIT の設定/データベースの設定	DELAYED_DURABILITY = DISABLED	DELAYED_DURABILITY = ALLOWED	DELAYED_DURABILITY = FORCED
DELAYED_DURABILITY = OFF データベースレベルトランザクション。	トランザクションに完全持続性が適用されます。	トランザクションに完全持続性が適用されます。	トランザクションに遅延持続性が適用されます。
DELAYED_DURABILITY = ON データベースレベルトランザクション。	トランザクションに完全持続性が適用されます。	トランザクションに遅延持続性が適用されます。	トランザクションに遅延持続性が適用されます。
DELAYED_DURABILITY = OFF 複数データベース間トランザクションまたは分散トランザクション。	トランザクションに完全持続性が適用されます。	トランザクションに完全持続性が適用されます。	トランザクションに完全持続性が適用されます。
DELAYED_DURABILITY = ON 複数データベース間トランザクションまたは分散トランザクション。	トランザクションに完全持続性が適用されます。	トランザクションに完全持続性が適用されます。	トランザクションに完全持続性が適用されます。

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

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

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

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

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

トランザクションレプリケーションまたは変更データキャプチャ (CDC) がに有効なデータベースの場合、遅延持続性は使用できません。
遅延持続性を使用した Change Tracking がサポートされています。 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 の予期しないシャットダウンと予期されたシャットダウン/再起動に違いはありません。重大なイベントと同様に、データ損失に対する計画を立てる必要があります。計画されたシャットダウン/再起動では、ディスクに書き込まれていない一部のトランザクションがシャットダウン前にディスクに保存される場合がありますが、それを予期することはできません。計画されているかどうかに関係なく、シャットダウン/再起動によって重大なイベントと同様にデータが失われるものとして計画してください。

次のステップ

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

次の方法で共有

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

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

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

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

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

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

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

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

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

表 1: ATOMIC ブロックの持続性

COMMIT レベルの制御 - Transact-SQL

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

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

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

データが失われるケース

重大なイベント

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

次のステップ

フィードバック

その他のリソース

次の方法で共有

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

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

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

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

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

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

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

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

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

表 1: ATOMIC ブロックの持続性

COMMIT レベルの制御 - Transact-SQL

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

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

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

データが失われるケース

重大なイベント

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

次のステップ

フィードバック

その他のリソース

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

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

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