为什么sleeping的会话会造成阻塞

时间:2023-02-14 15:09:18

背景

客户反映HIS数据库每天22点后都会发生阻塞,阻塞的源头是一个sleeping的会话,越阻塞越多,只能通过手动KILL掉才能解决,十分不解为什么状态为sleeping的会话会造成阻塞。

现象

在SQL专家云的活动会话中,回溯22点一个小时内的运行情况,从22点开始出现阻塞情况。

为什么sleeping的会话会造成阻塞

转到活动会话原始数据,看到ID为2661的会话是阻塞源头,且状态为sleeping。

为什么sleeping的会话会造成阻塞

查看2661的完整信息,发现该会话中有3个打开的事务,一直没有关闭,打开事务的时间为22:00。

为什么sleeping的会话会造成阻塞

再转到22:00的活动会话原始数据,发现会话2661被会话615阻塞。当时2661正在执行到一个存储过程的UPDATE语句。

为什么sleeping的会话会造成阻塞

在慢语句中找到会话2661,执行时间为30秒多一点。向客户证实,程序上设置的SQL语句的超时时间为30秒,说明2661被阻塞导致超时了。

为什么sleeping的会话会造成阻塞

会话615是一个作业,22点开始执行,执行时间91秒。

为什么sleeping的会话会造成阻塞

 

为什么sleeping的会话会造成阻塞

分析

通过回溯,很容易分析阻塞的原因,首先22:00运行的作业会话615阻塞了会话2661,当时会话2661正在执行的SQL语句为存储过程中的语句update yz_zy_patient。

为什么sleeping的会话会造成阻塞

通过存储过程的定义可以看到,会话2661在被阻塞之前,已经执行完了begin tran和update mz_charge_detail语句。

 为什么sleeping的会话会造成阻塞

因为会话2661一直被阻塞,直到30秒后超时,所以不会执行到下面的COMMIT语句。最重要的是,应用程序实现的不健壮,语句超时报错后没有进行错误处理,回滚事务并关闭连接(会话),导致会话2661变成了一个“僵尸”会话。因为没有处理事务,会话2661一直持有对表mz_charge_detail更改的数据行的排他锁,其他会话在对表mz_charge_detail进行更新时就会被一直阻塞。

为什么sleeping的会话会造成阻塞

解决

  1. 修改应用程序,增加对执行异常的捕获,回滚事务并关闭连接。这是最根本的解决办法。
  2. 修改存储过程,在事务开始之前增加SET XACT_ABORT ON语句,当 SET XACT_ABORT 为 ON 时,如果 SQL 语句产生运行时错误,整个事务将自动终止并回滚。在修改应用程序之前作为临时解决办法。

自动查杀会话

sleeping会话导致阻塞是一个非常普遍的问题,因为很多客户是购买软件厂商的产品,修改程序的根本解决办法不容易落实。因此只能在数据库端进行补偿性的措施,就是配置一个自动查杀会话的作业,根据这种会话的特征定期KILL掉。也可以在SQL专家云中启用自动查杀会话的功能。

 为什么sleeping的会话会造成阻塞

 

 

相关文章