MyException - 我的异常网
当前位置:我的异常网» SQL » SQL Server事务遭遇网络错误时的处理机制浅析

SQL Server事务遭遇网络错误时的处理机制浅析

www.MyException.Cn  网友分享于:2013-08-06  浏览:0次
SQL Server事务遭遇网络异常时的处理机制浅析

SQL Server数据库中,如果应用程序正在执行一个事务的时候突然遭遇了网络异常,例如网络掉包,网络中断等,那么这个事务会怎么样? SQL Server数据库是通过什么机制来判断处理呢? 估计很多人跟我一样都有不少疑问, 我们下面构造一个测试实验来测试验证一下。如下所示:

 

 

步骤1:在客户端连使用SSMS工具连接到测试数据库,执行下面脚本,显性事务既不提交也不回滚。模拟事务正在执行当中。

 

USE AdventureWorks2012;

GO

SELECT@@SPID;

 

 

BEGINTRAN

 

    DELETEFROM [dbo].[Products] WHERE ProductID=1;

 

    --ROLLBACK;

 

 

输出的会话ID59

 

 

 

步骤2:在测试服务器上开启Profiler跟踪一下具体信息。具体步骤略过。

 

 

 

步骤3:通过VMware vSphere Client的控制台连接到测试服务器,禁用网卡,然后启用网卡,模拟网络异常。(注意:玩过Vmware的应该都知道,这里不详细介绍!)

 

 

如下截图所示,在跟踪过程中,我们可以看到当我构造网络异常时,会话ID=59的事务立即回滚了。

 

 

clip_image001[4]

 

 

当然你也可以使用下面函数查看日志里面的相关记录信息。如下所示:

 

 

SELECT*

FROMfn_dblog(NULL,NULL)

WHERE Operation ='LOP_ABORT_XACT';

 

clip_image002[4]

 

你可以看到 LOP_BEGIN_XACT (事务开始)->   LOP_DELETE_ROWS (删除记录) ->  LOP_INSERT_ROWS (插入记录)  ->LOP_ABORT_XACT (事务回滚)

 

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]

 

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]

 

 

当然这个时间差是你断开网络的时间和事务结束的时间差,而不是事务开始时间与结束时间差,如下测试所示,截图1,由于需找到禁用网络的位置,然后又切换窗口,导致延误了几秒,这个事务开始、结束时间差为70秒。 当然这个值不可能完全等于Keep Alive的值,因为还涉及参数Keep Alive Interval的值,所以这个值玩玩是大于等于Keep Alive的值。具体后面会讲述!

 

clip_image007[4]

 

clip_image008[4]

 

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_timetcp_keepalive_intvltcp_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系统中也有类似的参数,分别是KeepAliveTimeKeepAliveIntervalTcpMaxDataRetransmissions

 

 

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.” 。 具体参考下面链接,当然这些值个人没有测试过。

 

  https://blogs.technet.microsoft.com/nettracer/2010/06/03/things-that-you-may-want-to-know-about-tcp-keepalives/

 

 

 

那么SQL Server是否也是通过OS的这三个参数来判断会话是否orphaned, 很显然不是。它是通过SQL ServerKeep AliveKeep Alive Interval来判断会话是否遭遇网络异常。那么操作系统的KeepAliveTimeSQL ServerKeep 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连接,如果该连接空闲时间(没有任何数据交互)超过KeepalivetimeTCP协议会自动发出Keepalive 包检测连接存活与否。如果Keepalive检测次数超过注册表TcpMaxDataRetransmissions定义的值而对方还是没有回应,那么TCP就认为该连接有问题而关闭它。通过这样的机制SQL Server能够检测出Orphaned Connection等问题。

 

SQL  Server 对每个TCP连接缺省指定Keep Alive 30秒,Keepaliveinterval1秒。Windows TCP配置的TcpMaxDataRetransmissions缺省是5次。就是说,如果TCP连接idle30秒,那么TCP会发送第一个keepalive检查。如果失败,那么TCP会每隔1秒重发Keepalive 包,直到重发5次。如果第五次检测依然失败,则该连接就被Close。所以,一个TCP连接如果出现异常问题,大概在35秒的时候就会被Close。的机制SQL Server能够检测出Orphaned Connection等问题。

 

3SQL Server的Keepalive WindowsTCP协议里面的Keepalive 是一样的吗?

 

原理一样,但不相互干扰。Windows TCP协议也有keep alive 配置,位置如下:

 

HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

 

OSTCP协议的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.msdn.microsoft.com/sql_protocols/2006/03/08/understand-special-tcpip-property-keep-alive-in-sql-server-2005/

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/

文章评论

2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
程序员都该阅读的书
程序员都该阅读的书
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
中美印日四国程序员比较
中美印日四国程序员比较
老程序员的下场
老程序员的下场
总结2014中国互联网十大段子
总结2014中国互联网十大段子
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
代码女神横空出世
代码女神横空出世
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
那些争议最大的编程观点
那些争议最大的编程观点
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
程序员应该关注的一些事儿
程序员应该关注的一些事儿
为什么程序员都是夜猫子
为什么程序员都是夜猫子
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
每天工作4小时的程序员
每天工作4小时的程序员
如何成为一名黑客
如何成为一名黑客
我是如何打败拖延症的
我是如何打败拖延症的
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
鲜为人知的编程真相
鲜为人知的编程真相
漫画:程序员的工作
漫画:程序员的工作
10个调试和排错的小建议
10个调试和排错的小建议
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
我的丈夫是个程序员
我的丈夫是个程序员
一个程序员的时间管理
一个程序员的时间管理
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
程序员必看的十大电影
程序员必看的十大电影
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
旅行,写作,编程
旅行,写作,编程
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
编程语言是女人
编程语言是女人
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有