一、情况描述
2022年5月16日用户反映系统在业务高峰期会出现系统卡住不动的情况。经过中间件日志发现C3P0连接池报“APPARENT DEADLOCK”错误，是在多线程高并发情况下C3P0报出的BUG；在网上搜了一些资料，都是一些治标不治本的办法。

因系统会出现卡住不动的情况，怀疑是存在阻塞和死锁问题，通过使用以下语句对查询阻塞和死锁对象。
with
lock_wait as(
SELECT l.addr,o.name as 阻塞对象, l.LMODE, l.trx_id, l.tid
FROM v$lock l, sysobjects o
WHERE l.table_id = o.id AND blocked = 1),
waiting as (
select a.addr,a.阻塞对象,a.LMODE,b.user_name,b.clnt_host,b.clnt_ip,b.sess_id,'被阻塞的事务: '||b.sql_text,b.trx_id,b.state
from lock_wait a inner join v$sessions b on a.trx_id = b.trx_id),
waiting_for as (
select a.addr,a.阻塞对象,a.LMODE,b.user_name,b.clnt_host,b.clnt_ip,b.sess_id,'待提交的事务: '||b.sql_text,b.trx_id,b.state
from lock_wait a inner join v$sessions b on a.tid = b.trx_id)
select * from waiting_for union all
select * from waiting;
阻塞和锁查询结果如下

经过排查，确实因存在很多已经update更新完成但未提交的的事务导致的阻塞，经过和开发商沟通排查代码是否存在事务结束但没有commit提交的情况，开发商确认后代码层面并不存在此种情况。现场经过对比分析发现当C3P0连接池报“APPARENT DEADLOCK”错误时就会存在未提交事务引起的阻塞，把对应的WEB应用进程结束掉之后，被阻塞的资源得到释放，卡顿现场消除。经过和开发商沟通后一致认为是因C3P0连接池的BUG所致，经过把C3P0连接池更换为DRUID连接池后观察一段时间，发现阻塞现象消失。但当系统运行到下午16点多的时候报错如下。

网上搜索资料是因有会话长时间没有结束占用着druid连接池资源导致报错，经过sql日志监控确实发现了慢sql。这条慢sql执行了差不多910秒的样子，非常恐怖，经过和开发商沟通后得知是定时报送数据功能，每隔5分钟执行一次定时报送任务。

经过对sql语句的执行计划分析发现，因SQL语句的WHERE条件中存在and instr(jbackup.reportIds, jreport.id) > 0语法，导致无法通过id和reportIds列上的索引快速定位数据，只能通过全表扫描的非常低效的连接方式导致SQL语句执行时间较长。特别是当数据量增大到一定规模后，instr走全表扫描的影响是非常巨大的。

二、分析结论
经过全方位的跟踪分析，首先是因C3P0连接池报“APPARENT DEADLOCK”错误。是在多线程高并发情况下C3P0报出的BUG导致事务结束但未提交引起的阻塞，经过更换为druid连接池解决。
其次是因为SQL语句中存在and instr(jbackup.reportIds, jreport.id) > 0语法，导致无法通过id和reportIds列上的索引快速定位数据，只能通过全表扫描非常低效的连接方式导致SQL语句执行时间较长。

三、解决办法
首先把C3P0连接池更换为性能更好更稳定的druid连接池来解决下C3P0的BUG导致事务结束但未提交引起的阻塞问题。
需要把程序里面所有使用“and instr(n.REPORTIDS, r.id )”语法结构及类似语法结构的SQL语句都进行整改，因为此种语法结构无法使用索引进行快速定位，导致性能低下。需要把类似的语法彻底改为“n.REPORTIDS=r.id”这个语法结构，这个语法结构可以使用索引快速定位查询，效率极高，是前面效率的300倍以上。

四、整改效果
经过长时间的跟踪优化，系统已经平稳运行快一个月了。下图是6月7日整个工作时间段数据库服务器CPU和内存资源使用情况的截图，CPU使用率保持平稳并始终处于较低水平，内存使用情况一直保持平稳。经过和现场香浓运维人员沟通得知数目最近几周一直处于平稳运行的状态。