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 分鐘跑一次；
經過調整， 任務不再報錯，同時任務執行周期減少 一半時間；
