SQLSE牧马人VELX570中的sp_reset_connection存款和储蓄进

作者:分分快三全天计划网站

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

SQLSERVER中的sp_reset_connection存储过程的作用

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

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

网上的资料很少

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

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

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

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

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

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

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

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

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

 

现在知道sp_reset_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的连接,以便应用程序反复使用这些连接以减少性能损耗。

 

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

 

 

重置连接的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事件。

 

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

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

 

一个简单的示例

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

    首先我分别使用四个连接,其中第一个和第二个连接之间有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截图:

    分分快三全天计划网站 1   

 

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

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

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

 

 

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

分分快三全天计划网站 2

本文出处: 

 

 

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

 

这种机制跟连接池有关:

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

分分快三全天计划网站 3

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

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

 

 

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

   

  

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

 

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

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

 

 分分快三全天计划网站 4

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

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

 

 分分快三全天计划网站 5

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

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

 

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

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

分分快三全天计划网站 6

分分快三全天计划网站 7

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

 

 

分分快三全天计划网站 8

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

 

    分分快三全天计划网站 9

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

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

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

分分快三全天计划网站 10

 

 

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

 

分分快三全天计划网站 11

分分快三全天计划网站 12

分分快三全天计划网站 13

  

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

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

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划 SQLSERVER SQLSERVER基础 连接池 sp_res