图片 4

连年SQLServer时,因启用连接池导致孤立事务的源委深入分析和解决办法

 

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张响讨论连接池的概念而来的。因此单独写一篇文章剖析一下连接池。

本文出处: 

 

 

为什么需要连接池

    剖析一个技术第一个要问的是,这项技术为什么存在。

    对于每一个到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事件。

 

  连接数据库的部分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截图:

   
图片 1   

 

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

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

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

 

这种机制跟连接池有关:

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

 

 

 

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

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

图片 2

  

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

图片 3

  

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

图片 4