合 MSSQL 2014新功能之延迟事务持续性(Delayed Transaction Durability)--延迟提交
Tags： MSSQLSQL ServerSQL Server 2014延迟事务持续性
简介
在SQL Server 2014之前， SQL Server提交事务是一个同步的过程，也就是说，只有当SQL Server将该事务相对应的日志记录写入到了磁盘文件之后，才会返回事务提交成功的信号。这也是为了体现事务4个基本特性中的持久性（Durability）而实现的功能。只有这样，我们才能保证当SQL Server因为某些原因突然Crash之后，再重启的时候，那些已经提交但还没有写入到数据文件上的记录可以通过日志文件进行恢复，或者那些还没有提交，但已经有部分数据写入到数据文件上的记录进行回滚。所以，我们可以看到，对于传统的事务提交，由于必须要保证日志写入到磁盘上，这个I/O操作就有可能成为性能的瓶颈。
而从SQL Server 2014开始，我们引入了一个新的特性——Delayed Transaction Durability（DTD）。这个技术可以使得SQL Server在提交事务时，无需等待事务日志写入磁盘就直接返回事务提交成功的信号，I/O操作在后台会以异步的方式写入到数据库事务日志文件中。这样好处是，事务可以去除等待I/O操作完成所带来的延时，以此来提高整个SQL Server的性能。在这整个过程中，SQL Server会在内存中专门开辟出一个特殊的Log Buffer来存放DTD所产生的日志，当这个Log Buffer一旦存满之后会马上写入日志文件，由此将零散的I/O操作变成了一块一块的操作来提高效率，增加吞吐量。
虽然，DTD能提高系统的性能，但是，可以看到，它的缺点也是显而易见的。由于数据是以内存为载体，如果SQL Server crash，那部分还没有来得及写入到磁盘的日志数据就会丢失。导致之前提交的记录在数据库重启之后消失。这样，会使得前端应用（用户）产生不好的体验。同时，这个也从违反了ACID中的Durability这个特性。因此，我们需要根据业务的实际需要来确定是否启用该功能。
通常，我们建议在以下几种情况下来使用这个功能：
- 可以容忍部分的数据丢失
- 系统中存在极为严重的WriteLog等待
在SQL Server 2014中，我们有3种方式来控制什么时候使用DTD：
- Database level control——数据库级别来控制所有的事务是否需要启用DTD
- Atomic block level control——Native SP级别来控制是否需要启用DTD
- COMMIT level control ——T-SQL级别来控制是否需要启用DTD
当三种级别混合使用的时候，会有不同的结果：
最后，我们再来介绍一下“sp_flush_log”，该存储过程是SQL Server 2014引入的一个新的系统存储过程。它的作用是将当前数据库的事务日志刷新到磁盘上，可以保证所有之前提交的DTD事务都是持久的，以此确保数据不会丢失。由于，从内存的Buffer写入到磁盘上的日志文件这个动作都是由SQL Server来自动控制的，所以，如果我们想保证日志最多只丢失多少时间的数据，也可以创建一个Job来定时的执行这个存储过程。这样可以人为来控制这个写入动作。
日志延迟持久化与数据丢失
- 当系统不忙时，也会主动将delayed durability的日志记录固化到磁盘。但目前不知道如何判断"不忙"这个标准。
- checkpoint不会将delayed durability的日志记录固化到磁盘。
- SQL Server正常关闭不会将delayed durability的日志记录固化到磁盘，也就说正常关闭也可能会导致数据丢失，建议之前先执行sp_flush_log。
- 很明显，如果断电等操作，直接内存中的未提交事务日志就丢失了，意味着已提交但脏页未持久化的事务也会丢失。
开启该选项对其他操作的影响
更改跟踪和更改数据捕获
具有更改跟踪属性的所有事务都是完全持久事务。 如果一个事务对支持更改跟踪的表执行了任何写入操作，则该事务具有更改跟踪属性。 使用变更数据捕获 (CDC) 的数据库不支持使用延迟的持续性。
崩溃恢复
一致性可得到保证，但已提交的延迟持久事务的一些更改可能会丢失。
跨数据库和 DTC
如果事务跨数据库或是分布式事务，则无论数据库或事务提交设置如何，它都是完全持久事务。
AlwaysOn 可用性组和镜像
延迟持久事务并不能保证主数据库或任何辅助数据库的持续性。 此外，它们也不保证了解辅助数据库的事务。 提交后，在从同步辅助数据接收到任何确认之前，控制权就会归还客户端。 当主副本上的磁盘刷新时，会继续复制辅助副本。
故障转移群集
某些延迟持久事务写入可能会丢失。
事务复制
事务复制不支持延迟持久事务。
日志传送
传送的日志中仅包含已成为持久事务的事务。
日志备份
备份中仅包含已成为持久事务的事务。
启用
Delayed durability是一个数据库级别的特性，默认是禁用的，我们首先要开启这个选项。
1 2 3 4 5 | ALTER DATABASE [DDtest] SET DELAYED_DURABILITY = FORCED|Allowed|Disabled 如果是forced，那么该数据库内所有的事务都强制使用delayed durability; 如果是allowed，那么delayed durability和full durable transaction可以同时存在; 如果是disabled，那么无法使用delayed durability. |
当该属性发生变化后，errorlog中也会有相应的记录
1 2 3 4 5 | Setting database option delayed_durability to forced for database 'DDtest'. Setting database option delayed_durability to allowed for database 'DDtest'. Setting database option delayed_durability to disabled for database 'DDtest'. |
示例
在描述DELAYED DURABILITY的具体细节前，先来看下他性能提升的神奇效果。
一张表定义如下：
1 2 3 4 5 | create table dbo.t1 ( id int, name varchar(20) ) |
模拟海量小事务：10000个事务，每个事务向“dbo.t1”表中插入一条数据
执行10000个事务，每个事务插入1条数据
1 2 3 4 5 6 7 8 9 10 11 12 | declare @i int; declare @name varchar(20) set @i = 1; while @i <= 10000 begin set @name = 'name' + convert(varchar, @i); begin transaction insert into dbo.t1 values (@i, @name); commit set @i = @i + 1; end go |
在数据库默认配置下，也就是不开启DELAYED DURABILITY情况下，上面这段脚本在我的环境中执行时长：3分29秒
打开数据库的"DELAYED DURABILITY"功能
1 2 | ALTER DATABASE testdb1 SET DELAYED_DURABILITY = FORCED |
再次执行上面的批量命令，执行用时: 0秒！
没看错，性能提升就是这么明显！
然而，别太高兴，DELAYED DURABILITY 提升性能是以有可能丢失数据为代价换来的。
示例2
如果数据库的DELAYED_DURABILITY为Allowed,我们可以在语句级别进行控制，否则就要遵循数据库的设定了（如果语句的设定和数据库级别设定冲突，那么SQL Server会使用数据库级别的设定）。
1 2 | 将数据的 DELAYED_DURABILITY设置为Allowed ALTER DATABASE [DDtest] SET DELAYED_DURABILITY = Allowed |
创建一张表，并循环插入1000行数据，每次插入都是一个单独的事务
1 2 3 4 5 6 7 8 9 10 11 | create table ta(col int) declare @N int=0 while @n<1000 begin begin tran insert ta values(1) commit tran with(delayed_durability=off) set @N+=1 print cast(@N as varchar(1000)) end |
开启Process Monitor，监控对数据库日志文件的操作。
一共对日志文件进行了1012次的写入操作，也就是每次commit都会立刻固化到日志文件
下面比较一下使用delayed durability的情况
