トランザクションの持続性の制御
適用対象: 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_DURABLITY = FORCED が有効であれば、コミットは非同期的であり、遅延持続性が適用されます。 詳細については、「データベース レベルの制御」を参照してください。
ON
トランザクションに遅延持続性が適用されます。ただし、データベース オプション DELAYED_DURABLITY = 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
[既定値] トランザクションの COMMIT に完全持続性が適用されます。ただし、データベース オプション DELAYED_DURABLITY = FORCED が有効であれば、COMMIT は非同期的であり、遅延持続性が適用されます。 詳細については、「データベース レベルの制御」を参照してください。
ON
トランザクションの COMMIT に遅延持続性が適用されます。ただし、データベース オプション DELAYED_DURABLITY = DISABLED が有効であれば、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 の予期しないシャットダウンと予期されたシャットダウン/再起動に違いはありません。 重大なイベントと同様に、データ損失に対する計画を立てる必要があります。 計画されたシャットダウン/再起動では、ディスクに書き込まれていない一部のトランザクションがシャットダウン前にディスクに保存される場合がありますが、それを予期することはできません。 計画されているかどうかに関係なく、シャットダウン/再起動によって重大なイベントと同様にデータが失われるものとして計画してください。