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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 原文:SQL Server事务遭遇网络异常时的处理机制浅析SQL Server数据库中,如果应用程序正在执行一个事务的时候突然遭遇了网络异常,例如网络掉包,网络中断等,那么这个事务会怎么样? SQL Server数据库是通过什么机制来判断处理呢? 估计很多人跟我一样都有不少疑问, 我们下面构造一个测试实验来测试验证一下。
原文: 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/

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
1月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
5天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
26 3
|
11天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
14天前
|
SQL 运维 监控
MSSQL性能调优深度剖析:索引优化策略、SQL语句微调与并发管理机制
在Microsoft SQL Server(MSSQL)的运维与优化实践中,索引优化策略、SQL语句的精细微调以及高效的并发管理机制是提升数据库性能的三大支柱
|
17天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
25 2
|
21天前
|
SQL 存储 测试技术
|
20天前
|
SQL 机器学习/深度学习 搜索推荐
SQL SERVER 转换失败
【6月更文挑战第25天】
|
25天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何迁移SQL Server
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
2天前
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
9 0
|
13天前
|
SQL Java 数据库连接
【Java】已解决java.sql.SQLRecoverableException异常
【Java】已解决java.sql.SQLRecoverableException异常
6 0