SQL Server事务遭遇网络异常时的处理机制浅析
2017-07-27 17:03 潇湘隐者 阅读(2581) 评论(4) 编辑 收藏 举报SQL Server数据库中，如果应用程序正在执行一个事务的时候突然遭遇了网络异常，例如网络掉包，网络中断等，那么这个事务会怎么样？ SQL Server数据库是通过什么机制来判断处理呢？ 估计很多人跟我一样都有不少疑问， 我们下面构造一个测试实验来测试验证一下。如下所示：
步骤1：在客户端连使用SSMS工具连接到测试数据库，执行下面脚本，显性事务既不提交也不回滚。模拟事务正在执行当中。
USE AdventureWorks2012;
GO
SELECT@@SPID;
BEGINTRAN
DELETEFROM [dbo].[Products] WHERE ProductID=1;
--ROLLBACK;
输出的会话ID为59
步骤2：在测试服务器上开启Profiler跟踪一下具体信息。具体步骤略过。
步骤3：通过VMware vSphere Client的控制台连接到测试服务器，禁用网卡，然后启用网卡，模拟网络异常。（注意：玩过Vmware的应该都知道，这里不详细介绍！）
如下截图所示，在跟踪过程中，我们可以看到当我构造网络异常时，会话ID=59的事务立即回滚了。
![clip_image001[4]](http://images2017.cnblogs.com/blog/73542/201707/73542-20170727170255258-966104363.png "clip_image001[4]")
当然你也可以使用下面函数查看日志里面的相关记录信息。如下所示：
SELECT*
FROMfn_dblog(NULL,NULL)
WHERE Operation ='LOP_ABORT_XACT';
![clip_image002[4]](http://images2017.cnblogs.com/blog/73542/201707/73542-20170727170256383-2069048084.png "clip_image002[4]")
你可以看到 LOP_BEGIN_XACT （事务开始）-> LOP_DELETE_ROWS （删除记录） -> LOP_INSERT_ROWS （插入记录） ->LOP_ABORT_XACT （事务回滚）
![clip_image003[4]](http://images2017.cnblogs.com/blog/73542/201707/73542-20170727170257024-877143337.png "clip_image003[4]")
通过上面实验测试，我们知道当应用程序遭遇网络异常时，数据库会回滚未提交的事务。那么接下来的问题有下面几个：
1： SQL Server需要多长时间才能检测到会话的网络异常？
如上所示，我断开的是服务器的网络，会话立即就回滚了。但是如果我断开的是客户端（执行SSMS客户端的网络），那么会话回滚的时间是30秒。如下截图所示
事务开始时间为： 2017-07-27 13:48:01:820
事务回滚时间为： 2017-07-27 13:48:32.043
![clip_image004[4]](http://images2017.cnblogs.com/blog/73542/201707/73542-20170727170257665-1111273886.png "clip_image004[4]")
![clip_image005[4]](http://images2017.cnblogs.com/blog/73542/201707/73542-20170727170258305-1787502438.png "clip_image005[4]")
这个是服务器上Keep Alive参数控制的，具体位置 “SQL Server Configuration Manager”-> “SQL Server Network Configuration” -> "Protocol for MSSQLSERVER" -> "TCP/IP " 右键单击属性，如下截图所示：
30000 的单位是毫秒， 等价于30秒， 如果你将这个设置为60000 ，那么测试结果就会是60秒或超过60秒。
![clip_image006[4]](http://images2017.cnblogs.com/blog/73542/201707/73542-20170727170258805-464478821.png "clip_image006[4]")
当然这个时间差是你断开网络的时间和事务结束的时间差，而不是事务开始时间与结束时间差，如下测试所示，截图1，由于需找到禁用网络的位置，然后又切换窗口，导致延误了几秒，这个事务开始、结束时间差为70秒。 当然这个值不可能完全等于Keep Alive的值，因为还涉及参数Keep Alive Interval的值，所以这个值玩玩是大于等于Keep Alive的值。具体后面会讲述！
![clip_image007[4]](http://images2017.cnblogs.com/blog/73542/201707/73542-20170727170259352-268088712.png "clip_image007[4]")
![clip_image008[4]](http://images2017.cnblogs.com/blog/73542/201707/73542-20170727170259977-1523162536.png "clip_image008[4]")
![clip_image009[4]](http://images2017.cnblogs.com/blog/73542/201707/73542-20170727170300680-1499873409.png "clip_image009[4]")
2： SQL Server通过什么机制来判断当前会话遭遇了网络异常？
在这篇“ORACLE的Dead Connection Detection浅析”文章里面， 我介绍了Linux系统下TCP KeepAlive概念，顾名思义，TCP keepalive它是用来保持TCP连接的，注意它只适用于TCP连接。系统会替你维护一个timer，时间到了，就会向remote peer发送一个probe package，当然里面是没有数据的，对方就会返回一个应答，这时你就知道这个通道保持正常。与TCP keepalive有关的三个参数tcp_keepalive_time、tcp_keepalive_intvl、tcp_keepalive_probes
/proc/sys/net/ipv4/tcp_keepalive_time 当keepalive起用的时候，TCP发送keepalive消息的频度。默认是2小时。
/proc/sys/net/ipv4/tcp_keepalive_intvl 当探测没有确认时，keepalive探测包的发送间隔。缺省是75秒。
/proc/sys/net/ipv4/tcp_keepalive_probes 如果对方不予应答，keepalive探测包的发送次数。缺省值是9。
其实在Windows系统中也有类似的参数，分别是KeepAliveTime、KeepAliveInterval、TcpMaxDataRetransmissions
KeepAliveTime 默认是7,200,000 milliseconds = 2 hours
KeepAliveInterval 默认是1,000 milliseconds = 1 second
TcpMaxDataRetransmissions 默认值是5次
https://technet.microsoft.com/en-us/library/cc957549.aspx
https://technet.microsoft.com/en-us/library/cc957548.aspx
https://technet.microsoft.com/en-us/library/cc938210.aspx
根据上面文档描述，几个值可以在HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters里设置，但是我检查过Windows 2000/2003/2008/2012 默认情况下，在注册表里面都没有这个值，但是可以在注册表里添加该选项。当然好像在有些操作系统下，有些参数是硬编码值，有些还不能修改.“Important note: If OS is Windows Vista/2008, the number of TCP Keepalive attempts are hardcoded to 10 and could not be adjusted via the registry.” 。 具体参考下面链接，当然这些值个人没有测试过。
那么SQL Server是否也是通过OS的这三个参数来判断会话是否orphaned， 很显然不是。它是通过SQL Server的Keep Alive、Keep Alive Interval来判断会话是否遭遇网络异常。那么操作系统的KeepAliveTime跟SQL Server的Keep Alive是不是一回事？ 又有什么区别呢？ 其实这个可以参考https://blogs.msdn.microsoft.com/apgcdsd/2011/05/02/sql-server-connection-keepalive-faq/。摘抄部分原文如下：
1、什么是SQL Server TCP连接的Keep Alive？
简单说，Keep Alive 是SQL Server在建立每一个TCP 连接的时候，指定了TCP 协议的Keepaliveinterval 和 Keepalivetime参数。这样对每个TCP连接，如果该连接空闲时间（没有任何数据交互）超过Keepalivetime，TCP协议会自动发出Keepalive 包检测连接存活与否。如果Keepalive检测次数超过注册表TcpMaxDataRetransmissions定义的值而对方还是没有回应，那么TCP就认为该连接有问题而关闭它。通过这样的机制SQL Server能够检测出Orphaned Connection等问题。
SQL Server 对每个TCP连接缺省指定Keep Alive 为30秒，Keepaliveinterval为1秒。Windows TCP配置的TcpMaxDataRetransmissions缺省是5次。就是说，如果TCP连接idle了30秒，那么TCP会发送第一个keepalive检查。如果失败，那么TCP会每隔1秒重发Keepalive 包，直到重发5次。如果第五次检测依然失败，则该连接就被Close。所以，一个TCP连接如果出现异常问题，大概在35秒的时候就会被Close。的机制SQL Server能够检测出Orphaned Connection等问题。
3、SQL Server的Keepalive 和Windows的TCP协议里面的Keepalive 是一样的吗？
原理一样，但不相互干扰。Windows 的TCP协议也有keep alive 配置，位置如下：
HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
OS的TCP协议的Keep Alive 和SQL Server 的Keep Alive 工作原理一样的，就是在建立TCP连接的时候指定TCP连接的Keepalive属性（参见后面描述）。但是SQL Server读取自己注册表的Keep Alive来设置TCP连接属性，不理会Windows OS的注册表里面的Keepalivetime和Keepaliveinterval的值。如果一个应用程序没有显式调用函数设置TCP连接的Keepalive属性，那么他的TCP连接默认使用OS 的TCP配置。OSkeep Alive配置默认是关闭的。
参考资料：
https://blogs.technet.microsoft.com/nettracer/2010/06/03/things-that-you-may-want-to-know-about-tcp-keepalives/
https://blogs.msdn.microsoft.com/apgcdsd/2011/05/02/sql-server-connection-keepalive-faq/
https://blogs.msdn.microsoft.com/apgcdsd/2012/06/07/sql-server-connection-keep-alive-faq3/
