控制事务持续性Control Transaction Durability

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure 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 操作。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

延迟事务持续性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_logThe 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 也会尝试基于日志生成和计时将日志刷新到磁盘。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 }. 有关详细信息,请参阅 原子块级别控制 - 本机编译存储过程提交级别控制 – Transact-SQLSee 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 block level control - Natively Compiled Stored Procedures

下面的代码面向原子块内部。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. 有关详细信息,请参阅 Database level controlSee 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. 有关详细信息,请参阅 Database level controlSee 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:原子块中的持续性Table 1: Durability in Atomic Blocks

原子块持续性选项Atomic block durability option 没有现有事务No existing transaction 事务正在进行(完全或延迟持久)Transaction in process (fully or delayed durable)
DELAYED_DURABILITY = OFFDELAYED_DURABILITY = OFF 原子块启动新的完全持久事务。Atomic block starts a new fully durable transaction. 原子块在现有事务中创建一个保存点,然后开始新事务。Atomic block creates a save point in the existing transaction, then begins the new transaction.
DELAYED_DURABILITY = ONDELAYED_DURABILITY = ON 原子块启动新的延迟持久事务。Atomic block starts a new delayed durable transaction. 原子块在现有事务中创建一个保存点,然后开始新事务。Atomic block creates a save point in the existing transaction, then begins the new transaction.

提交级别控制 -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 有效(在这种情况下,提交是异步的,因而是延迟持久事务)。[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. 有关详细信息,请参阅 Database level controlSee Database level control for more information.

ON ON
事务 COMMIT 是延迟持久事务,除非数据库选项 DELAYED_DURABLITY = DISABLED 有效(在这种情况下,提交是同步的,因而是完全持久事务)。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. 有关详细信息,请参阅 Database level controlSee 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 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

有两种方法可以强制将事务日志刷新到磁盘。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_logExecute 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