通过

控制事务持续性

SQL Server 事务提交可以是完全持久化（SQL Server 默认值）或延迟持久化（也称为延迟提交或懒惰提交）。

完全持久事务提交是同步的，只有在事务的日志记录写入磁盘后，才确认提交成功，并将控制权返回给客户端。 延迟持久事务提交是异步的，并在事务的日志记录写入磁盘之前报告提交成功。 事务要成为持久事务，必须将事务日志条目写入磁盘。 当事务日志条目刷新到磁盘时，延迟持久事务成为持久事务。

本主题详细介绍延迟执行的持久化事务。

完全事务持久性与延迟的事务持久性相比

完全事务持久性和延迟事务持久性都有其优点和缺点。 应用程序中可以同时存在完全持久事务和延迟持久事务。 应仔细考虑业务需求，以及每个需求如何适应这些需求。

完全事务持久性

在将控制权返回到客户端之前，完全持久事务会将事务日志写入磁盘。 每当以下情况下，都应使用完全持久化事务：

  • 系统不能容忍任何数据丢失。
    请参阅“ 何时可以丢失数据？” 部分，了解何时可能会丢失部分数据。

  • 瓶颈不是由于事务日志写入延迟。

延迟事务持久性通过将事务日志记录保存在内存中，并以批处理方式写入事务日志，从而减少因日志 I/O 导致的延迟，因此需要更少的 I/O 操作。 延迟事务持续性可能会减少日志 I/O 争用，从而减少系统中的等待。

完全事务持续性保证

  • 事务提交成功后，事务所做的更改对系统中的其他事务可见。 有关详细信息，请参阅主题 事务隔离级别 。

  • 在提交时确保数据持久性。 在事务提交成功之前，相应的日志记录将保存到磁盘，并将控制权返回到客户端。

延迟事务持久性

延迟事务持续性是使用异步日志写入磁盘来完成的。 事务日志记录保存在缓冲区中，并在缓冲区填充或缓冲区刷新事件发生时写入磁盘。 延迟事务持续性可减少系统中的延迟和争用，因为：

  • 事务提交处理不会等待日志 IO 完成并将控制权返回到客户端。

  • 并发事务不太可能争用日志 IO；相反，可以将日志缓冲区以更大的块刷新到磁盘，从而减少争用并增加吞吐量。

    注释

    如果存在高并发，则日志 I/O 争用问题仍然可能存在，特别是如果填满日志缓冲区的速度比刷新速度快。

何时适用延迟事务持久性

使用延迟事务持久性，您可以在以下情况下受益：

可以容忍某些数据丢失。
如果可以容忍某些数据丢失，例如，只要拥有大部分数据，单个记录就不重要，则延迟持续性可能值得考虑。 如果无法容忍任何数据丢失，请不要使用延迟事务持久性。

事务日志写入遇到瓶颈。
如果性能问题是由于事务日志写入中的延迟，您的应用程序可能会受益于使用延迟事务持久性。

工作负荷的争用率很高。
如果系统具有具有较高争用级别的工作负荷，则等待锁释放时丢失很多时间。 延迟事务持续性可缩短提交时间，从而更快地释放锁，从而提高吞吐量。

延迟事务持续性保证

  • 事务提交成功后，事务所做的更改对系统中的其他事务可见。

  • 仅在将内存中事务日志刷新到磁盘后，才保证事务持续性。 当以下情况下，内存中事务日志将刷新到磁盘：

    • 同一数据库中的完全持久事务在数据库中进行更改并成功提交。

    • 用户成功执行系统存储过程 sp_flush_log 。

    • 内存中事务日志缓冲区会填满并自动刷新到磁盘。

    如果一种完全持久化的事务或sp_flush_log成功提交，则所有之前提交的延迟耐久事务都已实现持久化。

日志可能会定期地写入磁盘。 但是，SQL Server 不提供除持久事务和sp_flush_log以外的任何持久性保证。

如何控制事务持续性

数据库级别控制

DBA 可以使用以下语句来控制用户是否能够在数据库中使用延迟事务持久性。 必须使用 ALTER DATABASE 设置延迟持续性设置。

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

DISABLED
[default] 启用此设置时，在数据库中提交的所有事务都会完全持久化，无论提交级别设置如何（DELAYED_DURABILITY=[ON | OFF]）。 无需存储过程更改和重新编译。 这样，就可以确保不会因延迟持续性而面临任何数据的风险。

ALLOWED
使用此设置，每个事务的持久性在事务级别确定 - DELAYED_DURABILITY = { OFF |ON }。 有关详细信息，请参阅 原子块级控件 - 本机编译的存储过程 和 COMMIT 级别控制 - Transact-SQL 。

FORCED
使用此设置，对数据库提交的每个事务都会延迟持久。 无论事务是否指定完全持久（DELAYED_DURABILITY = OFF）或未作任何指定，这个事务都是延迟持久的。 如果延迟事务持续性对数据库有用，并且你不希望更改任何应用程序代码，则此设置非常有用。

原子块级控制 - 本机编译的存储过程

以下代码位于原子块内。

DELAYED_DURABILITY = { OFF | ON }

OFF
[default]事务具有完全持久性，除非数据库选项DELAYED_DURABILITY = FORCED生效，在这种情况下，提交是异步的，因此是延迟持久的。 有关详细信息，请参阅 数据库级别控制 。

ON
事务延迟持久，除非数据库选项DELAYED_DURABLITY = DISABLED 有效，在这种情况下，提交是同步的，因此完全持久。 有关详细信息，请参阅 数据库级别控制 。

示例代码：

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：原子块中的持久性

原子块耐久性选项 无现有事务 正在处理的事务（完全或延迟持久）
DELAYED_DURABILITY = OFF 原子块启动新的完全持久事务。 原子块在现有事务中创建保存点，然后开始新事务。
DELAYED_DURABILITY = ON 原子块启动新的延时的持久化事务。 原子块在现有事务中创建保存点，然后开始新事务。

COMMIT 级别控制 - （T-SQL）

COMMIT 语法已扩展，从而可以强制实现延迟事务的持久性。 如果 DELAYED_DURABILITY 在数据库级别被设置为 disabled 或 forced（见上文），则该 commit 选项将被忽略。

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

OFF
[default]事务 COMMIT 是完全持久的，除非数据库选项DELAYED_DURABLITY = FORCED 有效，在这种情况下，COMMIT 是异步的，因此延迟持久。 有关详细信息，请参阅 数据库级别控制 。

ON
事务 COMMIT 延迟持久，除非数据库选项DELAYED_DURABLITY = DISABLED 有效，在这种情况下，COMMIT 是同步的，因此完全持久。 有关详细信息，请参阅 数据库级别控制 。

选项及其交互摘要

下表汇总了数据库级别延迟持续性设置与提交级别设置之间的交互。 数据库级别设置始终优先于提交级别设置。

COMMIT 设置与数据库设置 DELAYED_DURABILITY = DISABLED 延迟耐久性 = 允许 DELAYED_DURABILITY = FORCED
DELAYED_DURABILITY = OFF 数据库级事务。 事务是完全持久的。 事务处理是完全持久的。 事务延迟是持续的。
DELAYED_DURABILITY = ON 数据库级事务。 事务处理具有完全持久性。 事务延迟持久。 事务的延迟具有持久性。
DELAYED_DURABILITY = OFF 跨数据库或分布式事务。 事务是完全持久的。 事务是完全持久的。 事务完全具有持久性。
DELAYED_DURABILITY = ON 跨数据库或分布式事务。 事务具有完全的持久性。 事务具有完全的持久性。 事务是完全持久的。

如何强制刷新事务日志

有两种方法可以强制将事务日志刷新到磁盘。

  • 执行任何完全持久性的事务，以更改同一数据库。 这会强制将所有之前已完成的延迟持久性事务的日志记录刷新到磁盘。

  • 执行系统存储过程 sp_flush_log。 此过程强制刷新所有之前提交的延迟事务的日志记录到磁盘。 有关详细信息，请参阅sys.sp_flush_log（Transact-SQL）。

延迟持久性和其他 SQL Server 功能

更改追踪和更改数据捕获
具有更改跟踪的所有事务都是完全持久的。 如果事务对启用了更改跟踪的表执行任何写入作，则事务具有更改跟踪属性。 对于使用变更数据捕获（CDC）的数据库，不支持延迟持久性。

故障恢复
可以保证一致性，但已提交的推迟的持久化事务的一些更改可能会丢失。

跨数据库和 DTC
如果事务是跨数据库或分布式的，那么无论任何数据库或事务提交设置如何，它都是完全持久的。

AlwaysOn 可用性组和镜像
延迟的持久事务不能保证主数据库或任何辅助数据库的任何持久性。 此外，它们不保证对次级系统中的交易有任何了解。 提交后，在从任何同步辅助副本接收任何确认之前，控制权将返回到客户端。

故障转移群集
某些延迟的持久事务写入操作可能会丢失。

事务复制
事务复制不支持延迟持久事务。

日志传送
只有已持久化的事务才会包含在传送的日志中。

日志备份
备份中仅包含已完成持久化的事务。

何时会丢失数据？

如果在任一表上实现延迟持续性，则应了解某些情况可能会导致数据丢失。 如果不能容忍任何数据丢失，则不应对表使用延迟持续性。

灾难性事件

如果发生灾难性事件（例如服务器崩溃），将丢失所有未保存到磁盘的已提交事务的数据。 每当对数据库中的任何表（持久内存优化或基于磁盘）执行完全持久事务，或者调用 sp_flush_log 时，延迟持久事务将存储到磁盘。 如果使用延迟持久事务，可能需要在数据库中创建一个小表，以便定期更新或定期调用 sp_flush_log 以保存所有未完成的已提交事务。 每当事务日志已满时，事务日志也会刷新，但这很难预测且无法控制。

SQL Server 关闭并重启

对于延迟持续性，意外关闭与 SQL Server 的预期关闭/重启之间没有区别。 与灾难性事件一样，应计划数据丢失。 在计划关闭/重启过程中，某些尚未写入磁盘的事务可能会首先被保存到磁盘，但不应对此抱有期望。 将关闭或重启（无论是计划内还是计划外）视作会导致与灾难性事件相同的数据丢失来加以计划。

另请参阅

事务隔离级别
Memory-Optimized 表的事务隔离级别指南