当前位置: 澳门新濠3559 > 数据库 > 正文

解决办法,connection存储过程的执行

时间:2019-10-12 05:05来源:数据库
  SQLSERVER中的sp_reset_connection存储过程的作用 经常有人在论坛提问为什麽在使用SQLTrace的时候会看到大量的sp_reset_connection存储过程的执行 究竟sp_reset_connection存储过程有什么用? 网上的

 

SQLSERVER中的sp_reset_connection存储过程的作用

经常有人在论坛提问为什麽在使用SQL Trace的时候会看到大量的sp_reset_connection存储过程的执行

究竟sp_reset_connection存储过程有什么用?

网上的资料很少

下面说一下这个存储过程是干嘛的

在介绍之前先说一下连接池和事务和阻塞,因为这个存储过程跟连接池、事务和阻塞有关

一般SQL应用都会使用连接池来得到良好的性能。如果有一个连接忘记把事务关闭就退出连接那么这个连接会被交还给连接池

但是这时候,事务不会被清理。客户端驱动程序会在这个连接下一次被重用的时候(又有新的用户要建立连接)

发一句sp_reset_connection命令(这是一个存储过程)来清理当前连接上次遗留下来的所有对象

包括:回滚未提交的事务。

如果连接交还给连接池以后很久都没有被重用,那他的事务就会持续很长时间,引起阻塞。

比如有些JAVA程序使用的JDBC驱动程序提供连接池功能,但是不提供连接重用时的事务清理功能。

这样的连接池对应用开发质量要求很高,比较容易发生阻塞

 

现在知道sp_reset_connection存储过程的作用了吧?

    本篇文章起源于在GCR MVP Open Day的时候和C# MVP张响讨论连接池的概念而来的。因此单独写一篇文章剖析一下连接池。

阻塞的常见原因和解决办法:

本文出处: 

 

1. 由于语句运行时间太长而导致的阻塞,语句本身在正常运行中,只须等待某些系统资源

 

为什么需要连接池

解决办法,connection存储过程的执行。    剖析一个技术第一个要问的是,这项技术为什么存在。

    对于每一个到SQL Server的连接,都需要经历TCP/IP协议的三次握手,身份认证,在SQL Server里建立连接,分配资源等。而当客户端关闭连接时,客户端就会和SQL Server终止物理连接。但是,我们做过数据库开发的人都知道,每次操作完后关闭连接是再正常不过的事了,一个应用程序即使在负载不大的情况下也需要不停的连接SQL Server和关闭连接,同一个应用程序同时也可能存在多个连接。

    因此,如果不断的这样建立和关闭连接,会是非常浪费资源的做法。因此Ado.net中存在连接池这种机制。在对SQL Server来说的客户端的应用程序进程中维护连接池。统一管理Ado.net和SQL Server的连接,既连接池保持和SQL Server的连接,当Connection.Open()时,仅仅从连接池中分配一个已经和SQL Server建立的连接,当Connection.Close()时,也并不是和SQL Server物理断开连接,仅仅是将连接进行回收。

    因此,连接池总是能维护一定数量的和SQL Server的连接,以便应用程序反复使用这些连接以减少性能损耗。

 

解决办法:

之前遇到过这么一种情况:

重置连接的sys.sp_reset_connection

    连接是有上下文的,比如说当前连接有未提交的事务,存在可用的游标,存在对应的临时表。因此为了便于连接重复使用,使得下一个连接不会收到上一个连接的影响,SQL Server通过sys.sp_reset_connection来清除当前连接的上下文,以便另一个连接继续使用。

    当在Ado.net中调用了Connection.Close()时,会触发sys.sp_reset_connection。这个系统存储过程大概会做如下事情:

  •     关闭游标
  •     清除临时对象,比如临时表
  •     释放锁
  •     重置Set选项
  •     重置统计信息
  •     回滚未提交的事务
  •     切换到连接的默认数据库
  •     重置Trace Flag

 

    此外,根据BOL上的信息:

    "The sp_reset_connection stored procedure is used by SQL 
Server to support remote stored procedure calls in a transaction. This stored 
procedure also causes Audit Login and Audit Logout events to fire when a 
connection is reused from a connection pool."

 

    可以知道不能显式的在SQL Server中调用sys.sp_reset_connection,此外,这个方法还会触发Audit Login和Audit Logout事件。

 

a. 语句本身有没有可优化的空间

  连接数据库的部分Session会出现不定时的阻塞,这种阻塞时长时短,有时候持续较长时间,有时间持续时间较短,没有什么规律。
   之后分析相关存储过程和代码写法,发现是阻塞源头的存储过程中开启了事务,而应用程序在调用存储过程发生异常之后没有进行特别的处理(提交或者回滚),
   那么在执行方法发生异常之后,连接关闭了,但是数据库中遗留有活动事务(dbcc opentran对应的SessionId是sleeping状态),于是就产生了阻塞。
   关键是活动事务会不定时自己消失,就有点诡异了,这是本文的重点。

一个简单的示例

    下面我们通过一个简单的示例来看连接池的使用:

    首先我分别使用四个连接,其中第一个和第二个连接之间有10秒的等待时间:

String ConnectionString = "data source=.\sql2012;database=AdventureWorks;uid=sa;pwd=sasasa";
        SqlConnection cn1=new SqlConnection(ConnectionString);
        SqlCommand cmd1=cn1.CreateCommand();
        cmd1.CommandText="SELECT * FROM dbo.ABCD";
        cn1.Open();
        cmd1.ExecuteReader();
        cn1.Close();
        Response.Write("连接关闭时间:" DateTime.Now.ToLongTimeString() "<br />");

        System.Threading.Thread.Sleep(10000);

          SqlConnection cn2=new SqlConnection(ConnectionString);
        SqlCommand cmd2=cn2.CreateCommand();
        cmd2.CommandText="SELECT * FROM dbo.ABCD";
        cn2.Open();
        cmd2.ExecuteReader();
        cn2.Close();
        Response.Write("连接关闭时间:" DateTime.Now.ToLongTimeString() "<br />");



            SqlConnection cn3=new SqlConnection(ConnectionString);
        SqlCommand cmd3=cn3.CreateCommand();
        cmd3.CommandText="SELECT * FROM dbo.ABCD";
        cn3.Open();
        cmd3.ExecuteReader();
        cn3.Close();
        Response.Write("连接关闭时间:" DateTime.Now.ToLongTimeString() "<br />");

        System.Threading.Thread.Sleep(1500);

        SqlConnection cn4=new SqlConnection(ConnectionString);
        SqlCommand cmd4=cn4.CreateCommand();
        cmd4.CommandText="SELECT * FROM dbo.ABCD";
        cn4.Open();
        cmd4.ExecuteReader();
        cn4.Close();
        Response.Write("连接关闭时间:" DateTime.Now.ToLongTimeString() "<br />");

 

    下面我们通过Profile截图:

    澳门新濠3559 1   

 

    我们首先可以看到,每一次Close()方法都会触发exec sp_reset_connection

    此外,我们在中间等待的10秒还可以看到SP51是不断的,剩下几个连接全部用的是SPID51这个连接,虽然Ado.net Close了好几次,但实际上物理连接是没有中断的。

    因此可以看出,连接池大大的提升了效率。

b. Sql Server 整体性能如何,是不是有资源瓶颈影响了语句执行速度,如 内存、硬盘 和 CPU 等

 

2. 由于一个未按预期提交的事务导致的阻塞

这种机制跟连接池有关:

这一类阻塞的特征,就是问题连接早就进入了空闲状态(sysprocesses.status='sleeping'和sysprocesses.cms='awaiting command'),但是,如果检查 sysprocesses.open_tran,就会发现它不为0,以及事务没有提交。这类问题很多都是因为应用端遇到了一个执行超时,或者其他原因,当时执行的语句倍提前终止了,但是连接还保留着。应用没有跟随发来的事务提交或回滚指令,导致一个事务被遗留在 Sql Server 里。

当应用程序连接数据库的时候开启了连接池,如果应用程序调用了一个开启了事务操作的存储过程,
当发生异常的时候,有可能会出现数据库连接关闭,而存储过程中的事务既没有提交,也没有回滚的情况。
这种情况下就会产生“孤立事务”,也就是说,因为打开事务的数据量连接断掉了,而事务还处于活动状态,
实际上开启连接池的情况下,数据库连接的关闭,并不是物理上的关闭,而是将数据库连接返回到连接池。
此时如果没有外界的干预,包括没有对这个数据库连接没有被重用,或者这个连接没有物理断开,或者是没有重启应用程序,或者没有数据库服务器,这个事务将一直持续下去。
因为活动事务将阻塞其他Session对相关表的排他性访问,所以就表现为阻塞。

解决办法:

 

应用程序本身必须意识到任何语句都有可能遇到意外终止的情况,做好错误处理工作。这些工作包括:

 

· 在做 Sql Server 调用的时候,须加上错误捕捉和处理语句:If @@Trancount>0 RollBack Tran;(在程序中设置If @@Error<>0 Rollback Tran; 并不总是能执行到该语句)

 

· 设置连接属性"Set XACT_ABORT ON"。如果没有办法很规范应用程序的错误扑捉和处理语句,一个最快的方法就是在每个连接建立以后,或是容易出问题的存储过程开头,运行 "Set XACT_ABORT ON"

如何判断是否发生了连接池中的连接重用

·考虑是否要关闭连接池。发一句 sp_reset_connection 命令清理当前连接上次遗留下来的所有对象,包括回滚未提交的事务。

首先,一个连接数据库的过程中,有没有重用连接池中的连接,在SQL Server中有哪些区别?
以ado.net为例,如果在连接字符串中加入pooling=false;则表示不启用连接池.
如下,连续执行两次数据库访问,两次数据库访问均在连接字符串中加入了pooling=false;表示不启用连接池

3. 由于客户端没有及时把结果集取出而导致的语句长时间运行

澳门新濠3559 2

澳门新濠3559,

  

语句在 Sql Server 内执行总时间不仅包含 Sql Server 的执行时间,还包含把结果集发给客户端的时间。如果结果集比较大,Sql Server 会分几次打包发出,没发一次,都要等待客户端的确认。只有确认以后,Sql Server 才会发送下一个结果集包。所有结果都发完以后,Sql Server才认为语句执行完毕,释放执行申请的资源(包括锁资源)。如果出于某种原因,客户端应用处理结果非常缓慢甚至没有响应,或者干脆不理睬 Sql Server 发送结果集的请求,则 Sql Server 会耐心的等待,银次会导致语句长时间执行而产生阻塞。

  如下是观察到profile中的连接动作,注意这里第一次连接断开之后,有一个logout,第二次访问数据的时候,有一个login

 

澳门新濠3559 3

解决办法:

  

 

  如果将上述两个方法中连接字符串中的pooling=false;改为pooling=true;再次连续执行两个方法,
  会发现第二次连接数据的之前,也即在第一个logout之后,第二次login之前,有一个exec sp_reset_connection的动作。
  exec sp_reset_connection的执行标志着连接从连接池中重用了连接,关于这个动作的作用下面再说

a. 慎重返回大结果集

澳门新濠3559 4

b. 如果a短期内不能实现,则尝试大结果集的连接使用 Read Uncommitted 事务隔离级别,这样查询就不会申请 S 锁了

 

 

 

4. 阻塞的源头一直处于 RollBack 状态

什么情况下会出现数据库连接关闭,而事务保持活动状态 

  首先,参考如下截图,编写一个事务性存储过程,用waitfor delay '00:00:50'的方式延长其事务提交时间,造成连接超时(默认ado.net连接30秒)

这种情况是由第一类情况衍生来的。有时候发现一个连接阻塞住了别人,为了解决问题,直接让连接主动退出或强制退出(直接 Kill 连接)。对于大部分情况,这些措施会消除阻塞。但是也有例外。在连接退出的时间,为了维护数据库事务的一致性, Sql Server都会对连接还没有来得及完成提交的事务做回滚动作。Sql Server要找到所有当前事务修改过的记录,把它们改回原来的状态。所以,如果一个 Delete、Insert 或 Update 运行了1个小时,可能回滚也需要一个小时。

澳门新濠3559 5

 

 

有些用户可能等不及,直接重启 Sql Server。当 Sql Server 关闭的时候,回滚动作会被中断,Sql Server 会被很快关掉。但是这个回滚动作在下次 Sql Server 重启的时候会重新开始。重启的时候如果回滚不能很快结束,整个数据库都会不可用。

 

解决办法:

在ado.net中调用这个存储过程,连接超过30秒之后超时异常,当前执行方法的数据库连接被关闭,此时并不关闭Visual Studio,模拟应用程序并没有终止

最好的方法是在工作时间尽量不要做这种大的修改操作。这些操作要尽量安排在半夜或周末的时间完成。如果操作已经进行了很久,最好耐心等它做完。如果一定要在有工作负荷的时候做,最好把一个大操作分成若干小操作分布完成

 

 

澳门新濠3559 6

5. 应用程序中产生死锁

   

 

  此时查询数据中的活动事务,发现有一个活动事务,活动事务是上次执行“TimeoutFunction”造成的,
  但此时“TimeoutFunction”发生了异常,数据库连接被正常关闭,  
  此时,执行这个方法造成的事务还是活动状态的,如下截图

 

 澳门新濠3559 7

相关文章:

而此时观察SessionId = 57的状态,他是sleeping啊,已经开始呼呼睡大觉了。 

Sql Server 锁资源模式详解

 澳门新濠3559 8

sql server 2008 R2 连接数过高的解决办法

如果此时对事务中的表执行查询操作,会发现是被阻塞的,事实上t1这张表在上述方法执行之前一行数据都没有

利用 sys.sysprocesses 检查 Sql Server的阻塞和死锁

澳门新濠3559 9

 

 

 

 

本文首发于博客园 :Sql Server 阻塞的常见原因和解决办法

数据库连接被重用,第一次连接遗留在数据库中的事务被回滚

  上面在执行第一个方法之后,并没有中断VS的调试状态,我们继续执行第二个方法,此时第二个方法会重用第一个方法的数据库连接,
  至于为什么说他就重用了第一个方法的数据库连接,一开始就说了。
  当执行exec sp_reset_connection的时候,活动事务被回滚。查询能够正常执行。如下截图

澳门新濠3559 10

 

  查询在exec sp_reset_connection之后正常完,因为事务是被回滚的,所以t1表没有任何数据

澳门新濠3559 11

 

  上述示例就模拟出来类似这么一种场景,当连接字符串中开启了连接池之后
  一个方法执行超时连接被关闭之后,其调用的存储过程中的事务并没有显式的提交或者回滚,造成连接关闭而事务继续保持活动状态的情况
  比如web程序,一个方法执行完成之后,连接超时但是正常关闭(归还连接池),事务保持活动状态,
  此时web服务器并没有停止下来,也就是应用程序没有直接关闭,也就是类似于Visual Studio继续保持DEBUG状态,
  此时事务一直保持活动状态知道连接被重用(或者应用程序被关闭),那么其他Session发起对活动事务锁定的对象,就会发生阻塞。
  问题就出在这里,主观上无法保证连接池中的那个连接什么时候被重用,也就无法保证活动事务要持续多久,
  如果活动事务一直保持,那么阻塞就一直保持,这显然是不可接受的

 

关于sp_reset_connection的作用,我就懒得打字了,参考《Microsoft SQL Server企业级平台实践》第316页

澳门新濠3559 12

 

如何避免连接关闭而事务保持活动

  1,本质因为存储过程执行时间超过了连接的时间导致连接关闭的,那么就可以从分析事务性操作超时的原因入手。

  2,可以在应用程序的代码中catch的中,进行异常处理时候,保证连接关闭之前,活动事物最终提交或者回滚(作出明确的处理)

  3,关闭连接池,这种情况下,任何被物理关闭的数据库连接,其发起的未提交事务都将被回滚,但连接池也是为了提高数据库性能,可行性不大。

  4,从性能上以及连接池机制中分析,以上只能缓解这个问题,而逃不过这个问题,
    实际上,面对连接超时断开而是事务继续保持活动状态这种情况,在存储过程的事务性操作中加入try catch也是无济于事的,
    那么就可以使用SET XACT_ABORT ON;命令,确保在任何异常情况下,对事务进行回滚。关于XACT_ABORT可参考联机丛书。

    澳门新濠3559 13

 

总结:本文浅析了启用数据库连接池的条件下,在对数据库访问异常的情况下,造成孤立事物现象进行了原因进行了分析以及可行的解决方法尝试。

   从中得到一个教训,就是在对数据访问异常处理的时候,应用程序中一定要确保连接与事物的同步释放。同时,对事务处理的时候,存储过程中一定要做到严谨的事务控制和异常处理机制。

   确保在异常情况下,事务能够直接回滚,避免引起类似的阻塞。

 

编辑:数据库 本文来源:解决办法,connection存储过程的执行

关键词: 澳门新濠3559