你的位置:首页 > 数据库

[数据库]连接SQLServer时,因启用连接池导致孤立事务的原因分析和解决办法


 

本文出处:http://www.cnblogs.com/wy123/p/6110349.html 

 

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

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

 

这种机制跟连接池有关:

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

 

 

 

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

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

  

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

   

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

 

 

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

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

 

 

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

 

   

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

 

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

 

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

 

 

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

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

 

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

 

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

 

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

 

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

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

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

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

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