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

背景

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

现象

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

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

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

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

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

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

 

分析

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

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

 

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

解决

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

自动查杀会话

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