控制事务持久性

SQL Server 2014之后事务分为2种：完全持久， 默认或延迟的持久。

完全持久，当事务被提交之后，会把事务日志写入到磁盘，完成后返回给客户端。

延迟持久，事务提交是异步的，在事务写入到磁盘前，事务提交返回给客户端。

以前都是完全持久，现在多了个延迟持久，延迟持久只有当日志缓存刷新的时候才会被写入到磁盘保证事务完整性。

目录

控制事务持久性

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

完全持久事务

延迟持久性事务

如何控制事务的持久性

数据库级别

存储过程级别

语句级别

如何强制日志刷新

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

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

说白了，完成持久事务完全保证事务的持久性，延迟持久事务延迟保证事务的持久性。

完全持久事务

只要存在以下情况，就应使用完全持久事务：

1.系统无法承受任何数据丢失

2.造成性能瓶颈的原因不是事务日志写入延迟

完全持久事务保证：

1.事务提交后，修改对其他事务时可见的。

2.完全保证事务的持久性

延迟持久性事务

延迟持久性事务，通过异步的把事务日志写入到磁盘来实现，在写入到磁盘之前，日志被保存在缓存区中，当缓冲区满了或者刷新缓冲区了把日志写入磁盘，依次来减少磁盘资源争用：

1.事务提交不用等待写入到磁盘完成

2.减少磁盘争用从而提高，事务吞吐量。

使用场景：

1.可以忍受一定数据的丢失

2.在事务日志写入的时候发现瓶颈

3.磁盘资源争用率很高

延迟持久事务保证：

1.提交后，修改对其他事务可见

2.持久性只能靠缓存刷新来保证：

事务日志缓存在以下情况下刷新：

a.当同一个数据库的完全持久性事务提交，并成功

b.成功执行存储过程sp_flush_log

c.缓存区满了，自动刷新

如何控制事务的持久性

数据库级别

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

DISABLE:默认设置，不管如何保持完全持久性

ALLOWD:允许延迟持久性执行，要看存储过程，或者TSQL级别的设置

FORCED:强制所有的事务都是延迟持久性的

存储过程级别

DELAYED_DURABILITY = { OFF | ON }

OFF:默认设置，不使用延迟持久事务

ON:启动延迟持久事务

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

语句级别

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

OFF:默认设置，不使用延迟持久事务

ON:启动延迟持久事务

如何强制日志刷新

1.提交完全持久性事务

2.使用系统存储过程sp_flush_log

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

更改跟踪和变更数据捕获

具有更改跟踪属性的所有事务都是完全持久事务。 如果一个事务的所有写入操作都对表进行，而这些表支持更改跟踪或变更数据捕获 (CDC)，则该事务具有更改跟踪属性。

崩溃恢复

一致性可得到保证，但已提交的延迟持久事务的一些更改可能会丢失。

跨数据库和 DTC

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

AlwaysOn 可用性组和镜像

延迟持久事务并不能保证主数据库或任何辅助数据库的持续性。 此外，它们也不保证了解辅助数据库的事务。 提交后，在从同步辅助数据接收到 ACK 之前，控制权就会归还客户端。

故障转移群集

某些延迟持久事务写入可能会丢失。

事务复制

延迟持久事务并不保证其复制。 只有在事务成为持久事务后才会得到复制。

日志传送

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

日志备份

备份中仅包含已成为持久事务的事务。

参考：