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

適用対象: ○SQL Server ○Azure SQL Database×Azure SQL Data Warehouse ×Parallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server におけるトランザクションのコミットには、完全持続性、 SQL ServerSQL Server の既定値、または遅延持続性 (低速コミットとも呼ばれます) が適用されます。 transaction commits can be either fully durable, the SQL ServerSQL Server default, or delayed durable (also known as lazy commit).

完全持続性トランザクションのコミットは同期的であり、トランザクションのログ レコードがディスクに書き込まれてからコミットが正常完了として報告され、制御がクライアントに返されます。Fully durable transaction commits are synchronous and report a commit as successful and return control to the client only after the log records for the transaction are written to disk. 遅延持続性トランザクションのコミットは非同期的であり、トランザクションのログ レコードがディスクに書き込まれる前にコミットが正常完了として報告されます。Delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are written to disk. トランザクションを持続可能にするためには、トランザクション ログ エントリをディスクに書き込む必要があります。Writing the transaction log entries to disk is required for a transaction to be durable. 遅延持続性トランザクションは、トランザクション ログ エントリがディスクにフラッシュされる時点で持続的になります。Delayed durable transactions become durable when the transaction log entries are flushed to disk.

このトピックでは、遅延持続性トランザクションについて詳しく説明します。This topic details delayed durable transactions.

トランザクションの完全持続性と遅延持続性Full vs. Delayed Transaction Durability

トランザクションの完全持続性と遅延持続性にはそれぞれ、長所と短所があります。Both full and delayed transaction durability have their advantages and disadvantages. アプリケーションには、完全持続性トランザクションと遅延持続性トランザクションを混在させることができます。An application can have a mix of fully and delayed durable transactions. ビジネス ニーズを慎重に考慮したうえで、それぞれのタイプのトランザクションの適合性を検討する必要があります。You should carefully consider your business needs and how each fits into those needs.

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

完全持続性トランザクションは、クライアントに制御を返す前に、トランザクション ログをディスクに書き込みます。Fully durable transactions write the transaction log to disk before returning control to the client. 次のような場合には、完全持続性トランザクションを使用する必要があります。You should use fully durable transactions whenever:

  • システムで、データの損失が許容されない場合。Your system cannot tolerate any data loss.
    データの一部が失われるケースについては、「 データが失われるケース 」のセクションを参照してください。See the section When can I lose data? for information on when you can lose some of your data.

  • ボトルネックの原因がトランザクション ログの書き込み待機時間ではない場合。The bottleneck is not due to transaction log write latency.

    遅延持続性トランザクションは、トランザクション ログ レコードをメモリに保持してバッチ単位でトランザクション ログ レコードに書き込み、必要な I/O 操作を減らすことによって、ログの I/O による待機時間を短縮します。Delayed transaction durability reduces the latency due to log I/O by keeping the transaction log records in memory and writing to the transaction log in batches, thus requiring fewer I/O operations. 遅延持続性トランザクションでは、ログ I/O の競合が減少し、システム内の待機時間を短縮できる可能性があります。Delayed transaction durability potentially reduces log I/O contention, thus reducing waits in the system.

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

  • トランザクションのコミットが成功した場合、トランザクションによる変更は、システム内の他のトランザクションから認識できる状態になります。Once transaction commit succeeds, the changes made by the transaction are visible to the other transactions in the system. トランザクション分離レベルの詳細については、「SET TRANSACTION ISOLATION LEVEL (Transact-SQL)」または「メモリ最適化テーブルでのトランザクション」を参照してください。For more information about transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL) or Transactions with Memory-Optimized Tables.

  • 持続性はコミット時に保証されます。Durability is guaranteed on commit. 対応するログ レコードがディスクに保存されてからトランザクションのコミットが成功となり、制御がクライアントに返されます。Corresponding log records are persisted to disk before the transaction commit succeeds and returns control to the client.

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

トランザクションの遅延持続性は、ディスクへのログの非同期書き込みを使用して実現します。Delayed transaction durability is accomplished using asynchronous log writes to disk. トランザクション ログ レコードはバッファーに保持され、バッファーがいっぱいになった場合またはバッファーのフラッシュ イベントが発生した場合に、ディスクに書き込まれます。Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. トランザクションの遅延持続性では、次の理由によってシステム内の待機時間と競合の両方が軽減されます。Delayed transaction durability reduces both latency and contention within the system because:

  • トランザクション コミット処理では、ログ IO の完了を待たずに制御がクライアントに返されます。The transaction commit processing does not wait for log IO to finish and return control to the client.

  • 同時実行トランザクション間でログ IO の競合が発生する可能性は高くありません。ログ バッファーは大きな単位でディスクにフラッシュできるため、競合が軽減され、スループットを向上できます。Concurrent transactions are less likely to contend for log IO; instead, the log buffer can be flushed to disk in larger chunks, reducing contention, and increasing throughput.

    注意

    ただしログ バッファーがフラッシュされる前にいっぱいになる場合など、同時実行性が高い場合は、ログ I/O の競合が生じることもあります。You may still have log I/O contention if there is a high degree of concurrency, particularly if you fill up the log buffer faster than you flush it.

    トランザクションの遅延持続性の利用が適したケースWhen to use delayed transaction durability

    次のような場合は、トランザクションの遅延持続性の利用が適しています。Some of the cases in which you could benefit from using delayed transaction durability are:

    ある程度のデータ損失を許容できる場合。 You can tolerate some data loss.
    ある程度のデータ損失を許容できる場合 (データの大部分を確保できていれば個々のレコードがそれほど重要ではない場合など) は、遅延持続性の使用を検討することができます。If you can tolerate some data loss, for example, where individual records are not critical as long as you have most of the data, then delayed durability may be worth considering. 一切のデータ損失を許容できない場合は、トランザクションの遅延持続性は使用しないでください。If you cannot tolerate any data loss, do not use delayed transaction durability.

    トランザクション ログの書き込みでボトルネックが発生している場合。 You are experiencing a bottleneck on transaction log writes.
    パフォーマンスの問題がトランザクション ログの書き込みにおける待機時間によるものであれば、トランザクションの遅延持続性を使用することがアプリケーションにとってのメリットになる可能性があります。If your performance issues are due to latency in transaction log writes, your application will likely benefit from using delayed transaction durability.

    ワークロードの競合率が高い場合。 Your workloads have a high contention rate.
    競合レベルの高いワークロードがシステムに存在する場合、ロックの解放待ちに多くの時間が消費されます。If your system has workloads with a high contention level much time is lost waiting for locks to be released. トランザクションの遅延持続性を使用すると、コミット時間を短縮できるため、早くロックを解放でき、結果として高いスループットにつながります。Delayed transaction durability reduces commit time and thus releases locks faster which results in higher throughput.

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

  • トランザクションのコミットが成功した場合、トランザクションによる変更は、システム内の他のトランザクションから認識できる状態になります。Once transaction commit succeeds, the changes made by the transaction are visible to the other transactions in the system.

  • トランザクションの持続性が保証されるのは、インメモリ トランザクション ログがディスクにフラッシュされた後のみです。Transaction durability is guaranteed only following a flush of the in-memory transaction log to disk. インメモリ トランザクション ログは、次の場合にディスクにフラッシュされます。The in-memory transaction log is flushed to disk when:

    • 完全持続性トランザクションによって、同じデータベース内で変更が行われ、正常にコミットされた場合。A fully durable transaction in the same database makes a change in the database and successfully commits.

    • ユーザーがシステム ストアド プロシージャ sp_flush_log を正常に実行した場合。The user executes the system stored procedure sp_flush_log successfully.

      完全持続性トランザクションまたは sp_flush_log によって正常コミットされた場合、それより前にコミットされた遅延持続性トランザクションはすべて、持続可能な状態になっていることを保証されます。If a fully durable transaction or sp_flush_log successfully commits, all previously committed delayed durability transactions are guaranteed to have been made durable.

    • SQL ServerSQL Server は、すべてのトランザクションが遅延持続性であっても、ログ生成とタイミングの両方に基づいて、ディスクへのログのフラッシュを試みます。 does attempt to flush the log to disk both based on log generation and on timing, even if all the transactions are delayed durable. 通常、IO デバイスが稼働状態を保っている場合、これは成功します。This usually succeeds if the IO device is keeping up. ただし、 SQL ServerSQL Server では持続性トランザクションおよび sp_flush_log 以外にハード持続性は保証されません。However, SQL ServerSQL Server does not provide any hard durability guarantees other than durable transactions and sp_flush_log.

トランザクションの持続性を制御する方法How to control transaction durability

データベース レベルの制御Database level control

DBA は次のステートメントを使用して、トランザクションの遅延持続性をデータベースに対してユーザーが使用できるかどうかを制御できます。You, the DBA, can control whether users can use delayed transaction durability on a database with the following statement. 遅延持続性の設定は ALTER DATABASE で設定する必要があります。You must set the delayed durability setting with ALTER DATABASE.

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

DISABLED DISABLED
[既定値] この設定では、コミット レベルの設定 (DELAYED_DURABILITY=[ON | OFF]) に関係なく、データベースに対してコミットされたトランザクションにはすべて完全持続性が適用されます。[default] With this setting, all transactions that commit on the database are fully durable, regardless of the commit level setting (DELAYED_DURABILITY=[ON | OFF]). ストアド プロシージャの変更および再コンパイルの必要はありません。There is no need for stored procedure change and recompilation. この設定により、遅延持続性によるリスクを負うことなく、すべてのデータを持続可能にできます。This allows you to ensure that no data is ever put at risk by delayed durability.

ALLOWED ALLOWED
この設定では、各トランザクションの持続性がトランザクション レベル (DELAYED_DURABILITY = { OFF | ON }) で決定されます。With this setting, each transaction’s durability is determined at the transaction level – DELAYED_DURABILITY = { OFF | ON }. 詳細については、「 ATOMIC ブロック レベルの制御 – ネイティブ コンパイル ストアド プロシージャ 」と「 COMMIT レベルの制御 – Transact-SQL 」を参照してください。See Atomic block level control – Natively Compiled Stored Procedures and COMMIT level control –Transact-SQL for more information.

FORCED FORCED
この設定では、データベースにコミットされるすべてのトランザクションに遅延持続性が適用されます。With this setting, every transaction that commits on the database is delayed durable. トランザクションで完全持続性 (DELAYED_DURABILITY = OFF) が指定された場合も、指定がまったく行われていない場合も、遅延持続性トランザクションになります。Whether the transaction specifies fully durable (DELAYED_DURABILITY = OFF) or makes no specification, the transaction is delayed durable. データベースに対してトランザクションの遅延持続性が役立ち、アプリケーション コードの変更を行わない場合に、この設定を使用できます。This setting is useful when delayed transaction durability is useful for a database and you do not want to change any application code.

ATOMIC ブロック レベルの制御 – ネイティブ コンパイル ストアド プロシージャAtomic block level control – Natively Compiled Stored Procedures

次のコードは、ATOMIC ブロック内で使用します。The following code goes inside the atomic block.

DELAYED_DURABILITY = { OFF | ON }    

OFF OFF
[既定値] トランザクションに完全持続性が適用されます。ただし、データベース オプション DELAYED_DURABLITY = FORCED が有効であれば、コミットは非同期的であり、遅延持続性が適用されます。[default] The transaction is fully durable, unless the database option DELAYED_DURABLITY = FORCED is in effect, in which case the commit is asynchronous and thus delayed durable. 詳細については、「 データベース レベルの制御 」を参照してください。See Database level control for more information.

ON ON
トランザクションに遅延持続性が適用されます。ただし、データベース オプション DELAYED_DURABLITY = DISABLED が有効であれば、コミットは同期的であり、完全持続性が適用されます。The transaction is delayed durable, unless the database option DELAYED_DURABLITY = DISABLED is in effect, in which case the commit is synchronous and thus fully durable. 詳細については、「 データベース レベルの制御 」を参照してください。See Database level control for more information.

コード例:Example Code:

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: ATOMIC ブロックの持続性Table 1: Durability in Atomic Blocks

ATOMIC ブロックの持続性オプションAtomic block durability option 既存のトランザクションが存在しない場合No existing transaction 処理中の (完全持続性または遅延持続性) トランザクションが存在する場合Transaction in process (fully or delayed durable)
DELAYED_DURABILITY = OFFDELAYED_DURABILITY = OFF ATOMIC ブロックで、新しい完全持続性トランザクションが開始されます。Atomic block starts a new fully durable transaction. ATOMIC ブロックで、既存のトランザクションにセーブポイントが作成され、新しいトランザクションが開始されます。Atomic block creates a save point in the existing transaction, then begins the new transaction.
DELAYED_DURABILITY = ONDELAYED_DURABILITY = ON ATOMIC ブロックで、新しい遅延持続性トランザクションが開始されます。Atomic block starts a new delayed durable transaction. ATOMIC ブロックで、既存のトランザクションにセーブポイントが作成され、新しいトランザクションが開始されます。Atomic block creates a save point in the existing transaction, then begins the new transaction.

COMMIT レベルの制御 – Transact-SQLTransact-SQLCOMMIT level control – Transact-SQLTransact-SQL

COMMIT 構文は、トランザクションの遅延持続性を適用できるように拡張されています。The COMMIT syntax is extended so you can force delayed transaction durability. DELAYED_DURABILITY がデータベース レベルで DISABLED または FORCED に設定されている場合 (上記を参照)、この COMMIT オプションは無視されます。If DELAYED_DURABILITY is DISABLED or FORCED at the database level (see above) this COMMIT option is ignored.

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

OFF OFF
[既定値] トランザクションの COMMIT に完全持続性が適用されます。ただし、データベース オプション DELAYED_DURABLITY = FORCED が有効であれば、COMMIT は非同期的であり、遅延持続性が適用されます。[default] The transaction COMMIT is fully durable, unless the database option DELAYED_DURABLITY = FORCED is in effect, in which case the COMMIT is asynchronous and thus delayed durable. 詳細については、「 データベース レベルの制御 」を参照してください。See Database level control for more information.

ON ON
トランザクションの COMMIT に遅延持続性が適用されます。ただし、データベース オプション DELAYED_DURABLITY = DISABLED が有効であれば、COMMIT は同期的であり、完全持続性が適用されます。The transaction COMMIT is delayed durable, unless the database option DELAYED_DURABLITY = DISABLED is in effect, in which case the COMMIT is synchronous and thus fully durable. 詳細については、「 データベース レベルの制御 」を参照してください。See Database level control for more information.

オプションとその作用の概要Summary of options and their interactions

このテーブルは、データベース レベルの遅延持続性設定とコミット レベルの設定の相互作用をまとめたものです。This table summarizes the interactions between database level delayed durability settings and commit level settings. データベース レベルの設定はコミット レベルの設定よりも常に優先されます。Database level settings always take precedence over commit level settings.

COMMIT の設定/データベースの設定COMMIT setting/Database setting DELAYED_DURABILITY = DISABLEDDELAYED_DURABILITY = DISABLED DELAYED_DURABILITY = ALLOWEDDELAYED_DURABILITY = ALLOWED DELAYED_DURABILITY = FORCEDDELAYED_DURABILITY = FORCED
DELAYED_DURABILITY = OFF データベース レベル トランザクション。DELAYED_DURABILITY = OFF Database level transactions. トランザクションに完全持続性が適用されます。Transaction is fully durable. トランザクションに完全持続性が適用されます。Transaction is fully durable. トランザクションに遅延持続性が適用されます。Transaction is delayed durable.
DELAYED_DURABILITY = ON データベース レベル トランザクション。DELAYED_DURABILITY = ON Database level transactions. トランザクションに完全持続性が適用されます。Transaction is fully durable. トランザクションに遅延持続性が適用されます。Transaction is delayed durable. トランザクションに遅延持続性が適用されます。Transaction is delayed durable.
DELAYED_DURABILITY = OFF 複数データベース間トランザクションまたは分散トランザクション。DELAYED_DURABILITY = OFF Cross database or distributed transaction. トランザクションに完全持続性が適用されます。Transaction is fully durable. トランザクションに完全持続性が適用されます。Transaction is fully durable. トランザクションに完全持続性が適用されます。Transaction is fully durable.
DELAYED_DURABILITY = ON 複数データベース間トランザクションまたは分散トランザクション。DELAYED_DURABILITY = ON Cross database or distributed transaction. トランザクションに完全持続性が適用されます。Transaction is fully durable. トランザクションに完全持続性が適用されます。Transaction is fully durable. トランザクションに完全持続性が適用されます。Transaction is fully durable.

トランザクション ログのフラッシュを強制する方法How to force a transaction log flush

強制的にトランザクション ログをディスクにフラッシュするには、次の 2 つの方法があります。There are two means to force flush the transaction log to disk.

  • 同じデータベースを変更する完全持続性トランザクションを実行する。Execute any fully durable transaction that alters the same database. これにより、それまでにコミット済みの遅延持続性トランザクションのログ レコードがすべて強制的にディスクにフラッシュされます。This forces a flush of the log records of all preceding committed delayed durability transactions to disk.

  • システム ストアド プロシージャ sp_flush_logを実行する。Execute the system stored procedure sp_flush_log. このプロシージャにより、それまでにコミット済みの遅延持続性トランザクションのログ レコードがすべて強制的にディスクにフラッシュされます。This procedure forces a flush of the log records of all preceding committed delayed durable transactions to disk. 詳細については、「sys.sp_flush_log (Transact-SQL)」を参照してください。For more information see sys.sp_flush_log (Transact-SQL).

遅延持続性とその他の SQL ServerSQL Server 機能Delayed durability and other SQL ServerSQL Server features

変更の追跡と変更データ キャプチャ Change tracking and change data capture
変更の追跡を有効にしたすべてのトランザクションには、完全持続可能性が適用されます。All transactions with change tracking are fully durable. 変更の追跡が有効なテーブルに対して書き込み操作を行うトランザクションには、変更追跡プロパティがあります。A transaction has the change tracking property if it does any write operations to tables that are enabled for change tracking. 変更データ キャプチャ (CDC) を使用するデータベースの場合、遅延持続は使用できません。The use of delayed durability is not supported for databases which use change data capture (CDC).

クラッシュ後の復旧 Crash recovery
一貫性は保証されますが、コミット済みの遅延持続性トランザクションから変更内容が失われる場合があります。Consistency is guaranteed, but some changes from delayed durable transactions that have committed may be lost.

複数データベース間と DTC Cross-database and DTC
複数データベース間トランザクションまたは分散トランザクションの場合、データベースまたはトランザクションのコミット設定に関係なく、トランザクションには完全持続性が適用されます。If a transaction is cross-database or distributed, it is fully durable, regardless of any database or transaction commit setting.

AlwaysOn 可用性グループとミラーリング Always On Availability Groups and Mirroring
遅延持続性トランザクションでは、プライマリまたはいずれかのセカンダリに関する持続性は保証されません。Delayed durable transactions do not guarantee any durability on either the primary or any of the secondaries. また、セカンダリでのトランザクションに関するナレッジは保証されません。In addition, they do not guarantee any knowledge about the transaction at the secondary. コミット後、同期セカンダリからの受信確認を受信する前に、制御がクライアントに返されます。After commit, control is returned to the client before any acknowledgement is received from any synchronous secondary. プライマリ上のディスクへのフラッシュ時に、セカンダリ レプリカへのレプリケーションが継続的に発生します。Replication to secondary replicas does continue to happen as flush to disk on the primary happens.

フェールオーバー クラスタリング Failover clustering
遅延持続性トランザクションによる書き込みの一部が失われる場合があります。Some delayed durable transaction writes might be lost.

トランザクション レプリケーション Transaction Replication
遅延持続性トランザクションは、トランザクション レプリケーションではサポートされていません。Delayed durable transactions is not supported with Transactional Replication.

ログ配布 Log shipping
配信されるログに含まれるのは、持続可能な状態になったトランザクションのみです。Only transactions that have been made durable are included in the log that is shipped.

ログ バックアップ Log Backup
バックアップに含まれるのは、持続可能な状態になったトランザクションのみです。Only transactions that have been made durable are included in the backup.

データが失われるケースWhen can I lose data?

遅延持続性をテーブルに実装する場合、状況によってはデータが失われる可能性があることを理解する必要があります。If you implement delayed durability on any of your tables, you should understand that certain circumstances can lead to data loss. 一切のデータ損失を許容できない場合は、テーブルに対して遅延持続性は使用しないでください。If you cannot tolerate any data loss, you should not use delayed durability on your tables.

重大なイベントCatastrophic events

サーバー クラッシュなどの重大なイベントが発生すると、ディスクに保存されていないすべてのコミット済みトランザクションのデータが失われます。In the case of a catastrophic event, like a server crash, you will lose the data for all committed transactions that have not been saved to disk. 遅延持続性トランザクションは、データベース内のいずれかのテーブル (持続性のあるメモリ最適化テーブルまたはディスク ベース テーブル) に対して完全持続性トランザクションが実行されるか、 sp_flush_log が呼び出されるたびに、ディスクに保存されます。Delayed durable transactions are saved to disk whenever a fully durable transaction is executed against any table (durable memory-optimized or disk-based) in the database, or sp_flush_log is called. 遅延持続性トランザクションを使用している場合、定期的に更新するか定期的に sp_flush_log を呼び出すことができる小さいテーブルをデータベース内に作成して、未処理のコミット済みトランザクションすべてを保存できます。If you are using delayed durable transactions, you may want to create a small table in the database that you can periodically update or periodically call sp_flush_log to save all outstanding committed transactions. トランザクション ログもいっぱいになるたびにフラッシュされますが、それを予測するのは難しく、制御は不可能です。The transaction log also flushes whenever it becomes full, but that is hard to predict and impossible to control.

SQL ServerSQL Server のシャットダウンと再起動 shutdown and restart

遅延持続性の場合、 SQL ServerSQL Serverの予期しないシャットダウンと予期されたシャットダウン/再起動に違いはありません。For delayed durability, there is no difference between an unexpected shutdown and an expected shutdown/restart of SQL ServerSQL Server. 重大なイベントと同様に、データ損失に対する計画を立てる必要があります。Like catastrophic events, you should plan for data loss. 計画されたシャットダウン/再起動では、ディスクに書き込まれていない一部のトランザクションが最初にディスクに保存される場合がありますが、それを予期することはできません。In a planned shutdown/restart some transactions that have not been written to disk may first be saved to disk, but you should not plan on it. 計画されているかどうかに関係なく、シャットダウン/再起動によって重大なイベントと同様にデータが失われるものとして計画してください。Plan as though a shutdown/restart, whether planned or unplanned, loses the data the same as a catastrophic event.

参照See Also

メモリ最適化テーブルでのトランザクションTransactions with Memory-Optimized Tables