控制事务持续性

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

SQL Server 事务提交可以是完全持久（这是 SQL Server 中的默认方式），也可以是延迟持久（也称为延迟提交）。

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

本文详细介绍延迟的持久性事务。

完全事务持久性与延迟事务持久性

完全事务持久性和延迟事务持久性都各有优缺点。 一个应用程序可能同时包含完全和延迟持久事务。 您应该仔细考虑业务需求以及每个应用程序如何满足这些需求。

完全事务持续性

完全持久事务在将控制权归还给客户端之前将事务日志写入磁盘。 在以下情况下，应使用完全持久性事务：

• 系统无法承受任何数据丢失。 有关何时可能会丢失某些数据的信息，请参阅“ 何时可以丢失 数据”部分。

• 造成瓶颈的原因不是事务日志写入延迟。

延迟事务持久性通过将事务日志记录保留在内存中并分批写入事务日志，减少所需的 I/O 操作次数，从而降低由日志 I/O 导致的延迟。 延迟事务持久性可能会减少日志 I/O 争用，从而减少系统中的等待情况。

完全事务持续性保证

• 事务提交成功后，该事务所做的更改就对系统中的其他事务可见。 有关事务隔离级别的更多信息，请参阅 SET TRANSACTION ISOLATION LEVEL（Transact-SQL） 或 使用内存优化表的事务。

• 提交时即可保证持久性。 在事务提交成功并将控制权归还给客户端之前，对应的日志记录会保存到磁盘。

延迟事务持久性

延迟事务持久性是通过将日志异步写入磁盘来实现的。 事务日志记录保留在缓冲区中并在缓冲区充满或发生缓冲区刷新事件时写入磁盘。 延迟事务持久性可降低系统中的延迟和争用，因为：

• 事务提交处理不会等待日志 IO 完成就将控制权归还给客户端。

• 并发事务争用日志 IO 的可能性较低；相反，日志缓冲区可以以更大的块刷新到磁盘，从而减少争用并提高吞吐量。

  注意

  如果并发度很高，特别是如果填充日志缓冲区的速度比刷新缓冲区的速度快，仍然可能发生日志 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 }

已禁用
[默认] 使用此设置时，在数据库上提交的所有事务都是完全持久的，无论提交级别设置为何 (DELAYED_DURABILITY=[ON | OFF])。 无需更改和重新编译存储过程。 这可确保任何数据都不会因持久化延迟而面临风险。

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

强迫
使用此设置后，在该数据库上提交的每个事务都采用延迟持久性。 无论事务指定为完全持久性 (DELAYED_DURABILITY = OFF)，还是未作任何指定，该事务均为延迟持久。 如果某个数据库适合使用延迟事务持久性，而且您又不想更改任何应用程序代码，那么此设置就很有用。

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

以下代码位于原子代码块中。

DELAYED_DURABILITY = { OFF | ON }

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

开启
事务是延迟持久事务，除非数据库选项 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：原子块中的持久性

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_DURABILITY = FORCED 有效（在这种情况下，提交是异步的，因而是延迟持久事务）。 有关详细信息，请参阅数据库级别控制。

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

各个选项及其交互的总结

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

如何强制刷新事务日志

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

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

• 执行系统存储过程 sp_flush_log。 此过程会强制将此前已提交的所有延迟持久事务的日志记录刷写到磁盘。 有关详细信息，请参阅 sys.sp_flush_log (Transact-SQL)。

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

事务复制、更改跟踪和变更数据捕获

• 对于启用了事务复制或更改数据捕获 (CDC) 的数据库，不支持使用延迟持久性。

• 支持具有延迟持续性的更改跟踪。 具有更改跟踪属性的所有事务都是完全持久事务。 如果一个事务对启用了更改跟踪的表执行了任何写入操作，则该事务具有更改跟踪属性。

从 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 可用性组和镜像
延迟持久事务并不能保证主数据库或任何辅助数据库的持续性。 此外，它们也不能保证知晓辅助副本上的该事务情况。 提交后，在收到任何同步次要副本的确认之前，控制权就会返回给客户端。 当主副本将数据刷新到磁盘时，向辅助副本的复制仍会继续进行。

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

适用于 SQL 的 Azure Synapse Link
适用于 SQL 的 Azure Synapse Link 不支持延迟持久事务。

日志传送
传送的日志中仅包含已持久化的事务。

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

何时可能会丢失数据

如果你对任何表启用了延迟持久性，就应当了解，在某些情况下，这可能会导致数据丢失。 如果您不能容忍任何数据丢失，就不应对表启用延迟持久性。

灾难性事件

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

SQL Server 的关闭和重启

对于延迟持久性，SQL Server 的意外关闭与预期的关闭或重启之间没有区别。 与灾难性事件类似，应制定针对数据丢失的计划。 在进行计划的关闭/重新启动时，一些尚未写入磁盘的事务可能会在关闭前保存到磁盘，但不应对其进行计划。 进行规划时，应假定关机/重启无论是计划内还是计划外，都会像灾难性事件一样导致数据丢失。

后续步骤

• 具有内存优化表的事务