org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: interrupt
最近在生产发现一个定时任务，总是报这个错误！如下日志
2021-01-21 13:44:56 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[134]-[Thread-14579] 监控当前设备 subsystemCode = xm_jffc_rddstcc， devId= 1629, devName = 1号门3号道进口栏杆机,newStatus = 01, DeviceFaultRecord is null = true,
2021-01-21 13:44:56 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#handleNormalDevice]-[211]-[Thread-14579] handleNormalDevice 执行, subsystemCode= xm_jffc_rddstcc， devId= 1629, deviceFaultRecord is null :true
2021-01-21 13:44:56 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[88]-[Thread-14579] 监控设备状态所在的项目 code = [xm_jffc_rddstcc] ,name = [（一路）瑞达大厦停车场], devId= 1630，onlineStatus = null
2021-01-21 13:44:57 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[134]-[Thread-14579] 监控当前设备 subsystemCode = xm_jffc_rddstcc， devId= 1630, devName = 1号门1号道进口栏杆机,newStatus = 01, DeviceFaultRecord is null = true,
2021-01-21 13:44:57 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#handleNormalDevice]-[211]-[Thread-14579] handleNormalDevice 执行, subsystemCode= xm_jffc_rddstcc， devId= 1630, deviceFaultRecord is null :true
2021-01-21 13:44:57 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[88]-[Thread-14579] 监控设备状态所在的项目 code = [xm_jffc_rddstcc] ,name = [（一路）瑞达大厦停车场], devId= 1631，onlineStatus = null
2021-01-21 13:44:58 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[134]-[Thread-14579] 监控当前设备 subsystemCode = xm_jffc_rddstcc， devId= 1631, devName = 1号门3号道进口摄像机,newStatus = 01, DeviceFaultRecord is null = true,
2021-01-21 13:44:58 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#handleNormalDevice]-[211]-[Thread-14579] handleNormalDevice 执行, subsystemCode= xm_jffc_rddstcc， devId= 1631, deviceFaultRecord is null :true
2021-01-21 13:44:58 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[88]-[Thread-14579] 监控设备状态所在的项目 code = [xm_jffc_rddstcc] ,name = [（一路）瑞达大厦停车场], devId= 1632，onlineStatus = null
2021-01-21 13:44:59 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[134]-[Thread-14579] 监控当前设备 subsystemCode = xm_jffc_rddstcc， devId= 1632, devName = 2号门2号道出口摄像机,newStatus = 01, DeviceFaultRecord is null = true,
2021-01-21 13:44:59 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#handleNormalDevice]-[211]-[Thread-14579] handleNormalDevice 执行, subsystemCode= xm_jffc_rddstcc， devId= 1632, deviceFaultRecord is null :true
2021-01-21 13:44:59 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[88]-[Thread-14579] 监控设备状态所在的项目 code = [xm_jffc_rddstcc] ,name = [（一路）瑞达大厦停车场], devId= 1633，onlineStatus = null
2021-01-21 13:44:59 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[134]-[Thread-14579] 监控当前设备 subsystemCode = xm_jffc_rddstcc， devId= 1633, devName = 停车场服务器,newStatus = 01, DeviceFaultRecord is null = true,
2021-01-21 13:44:59 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#handleNormalDevice]-[211]-[Thread-14579] handleNormalDevice 执行, subsystemCode= xm_jffc_rddstcc， devId= 1633, deviceFaultRecord is null :true
2021-01-21 13:44:59 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[88]-[Thread-14579] 监控设备状态所在的项目 code = [xm_jffc_rddstcc] ,name = [（一路）瑞达大厦停车场], devId= 1634，onlineStatus = null
2021-01-21 13:45:02 [com.xxl.job.core.thread.JobThread#run]-[167]-[Thread-14579]
----------- JobThread toStop, stopReason:阻塞处理策略-生效：Cover Early
2021-01-21 13:45:02 [com.xxl.job.core.thread.JobThread#run]-[175]-[Thread-14579]
----------- JobThread Exception:org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: interrupt
### The error may exist in URL [jar:file:/home/admin/projects/project/job-center-executor/20201221/job-jianfa-executor-1.0.0.jar!/BOOT-INF/classes!/mapper/DeviceFaultRecordMapper.xml]
### The error may involve com.jieshun.job.jianfa.executor.mapper.DeviceFaultRecordMapper.queryTopOneOfflineByTime
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: interrupt
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy68.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy87.queryTopOneOfflineByTime(Unknown Source)
at com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl.doNomalBusiness(DeviceStatusRecordServiceImpl.java:132)
at com.jieshun.job.jianfa.executor.jobhandler.DeviceStatusRecordJobHandler.execute(DeviceStatusRecordJobHandler.java:33)
at com.xxl.job.core.thread.JobThread.run(JobThread.java:152)
到网上搜了一个遍，发现这篇比较匹配我的问题。
https://blog.csdn.net/qq_33366229/article/details/98479665
但是，仔细分析错误日志，有几个结论：
1、我的定时任务是 15分钟 跑一次， 然而任务每次15分钟都没有跑完！所以可以判断，这个interrupt 并非druid引发，而是xxl-job 主动干扰打断上一个任务；
2、为啥总是在 queryTopOneOfflineByTime 这个查询报错呢？ 仔细看时间消耗，每次 queryTopOneOfflineByTime 时间都要增加 1s，明显此查询很有压力！ sql 查询慢，导致每次interrupt 都卡在这个查询上。
总结上述两点，立即拿出解决方案：
sql 查询，发现数据库表除了主键索引，没有有任务索引，于是根据 queryTopOneOfflineByTime 查询字段，相应增加 devId 索引；
暂时先调整 定时任务，改为 30 分钟跑一次；
经过调整， 任务不再报错，同时任务执行周期减少 一半时间；
热度 {{item.heat}}
{{item.user_info.nickname ? item.user_info.nickname : item.user_name}}
作者 管理员 企业
{{itemf.name}}
{{itemc.user_info.nickname}}
{{itemc.user_name}}
回复 {{itemc.comment_user_info.nickname}}
![]()
{{itemf.name}}