Failed to obtain JDBC Connection； nested exception is java.sql.SQLException: interrupt (xxl-job)

管理

編輯

刪除

CRMEB-慕白寒窗雪 2023-03-21 18:40:51

其他

org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: interrupt

最近在生產(chǎn)發(fā)現(xiàn)一個(gè)定時(shí)任務(wù)，總是報(bào)這個(gè)錯(cuò)誤！如下日志

2021-01-21 13:44:56 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[134]-[Thread-14579] 監(jiān)控當(dāng)前設(shè)備 subsystemCode = xm_jffc_rddstcc， devId= 1629, devName = 1號(hào)門3號(hào)道進(jìn)口欄桿機(jī),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 執(zhí)行,  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] 監(jiān)控設(shè)備狀態(tài)所在的項(xiàng)目 code = [xm_jffc_rddstcc] ,name = [（一路）瑞達(dá)大廈停車場(chǎng)], devId= 1630，onlineStatus = null
2021-01-21 13:44:57 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[134]-[Thread-14579] 監(jiān)控當(dāng)前設(shè)備 subsystemCode = xm_jffc_rddstcc， devId= 1630, devName = 1號(hào)門1號(hào)道進(jìn)口欄桿機(jī),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 執(zhí)行,  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] 監(jiān)控設(shè)備狀態(tài)所在的項(xiàng)目 code = [xm_jffc_rddstcc] ,name = [（一路）瑞達(dá)大廈停車場(chǎng)], devId= 1631，onlineStatus = null
2021-01-21 13:44:58 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[134]-[Thread-14579] 監(jiān)控當(dāng)前設(shè)備 subsystemCode = xm_jffc_rddstcc， devId= 1631, devName = 1號(hào)門3號(hào)道進(jìn)口攝像機(jī),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 執(zhí)行,  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] 監(jiān)控設(shè)備狀態(tài)所在的項(xiàng)目 code = [xm_jffc_rddstcc] ,name = [（一路）瑞達(dá)大廈停車場(chǎng)], devId= 1632，onlineStatus = null
2021-01-21 13:44:59 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[134]-[Thread-14579] 監(jiān)控當(dāng)前設(shè)備 subsystemCode = xm_jffc_rddstcc， devId= 1632, devName = 2號(hào)門2號(hào)道出口攝像機(jī),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 執(zhí)行,  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] 監(jiān)控設(shè)備狀態(tài)所在的項(xiàng)目 code = [xm_jffc_rddstcc] ,name = [（一路）瑞達(dá)大廈停車場(chǎng)], devId= 1633，onlineStatus = null
2021-01-21 13:44:59 [com.jieshun.job.jianfa.executor.service.impl.DeviceStatusRecordServiceImpl#doNomalBusiness]-[134]-[Thread-14579] 監(jiān)控當(dāng)前設(shè)備 subsystemCode = xm_jffc_rddstcc， devId= 1633, devName = 停車場(chǎng)服務(wù)器,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 執(zhí)行,  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] 監(jiān)控設(shè)備狀態(tài)所在的項(xiàng)目 code = [xm_jffc_rddstcc] ,name = [（一路）瑞達(dá)大廈停車場(chǎng)], 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)

到網(wǎng)上搜了一個(gè)遍，發(fā)現(xiàn)這篇比較匹配我的問(wèn)題。

https://blog.csdn.net/qq_33366229/article/details/98479665

但是，仔細(xì)分析錯(cuò)誤日志，有幾個(gè)結(jié)論：

1、我的定時(shí)任務(wù)是 15分鐘跑一次，然而任務(wù)每次15分鐘都沒(méi)有跑完！所以可以判斷，這個(gè)interrupt 并非druid引發(fā)，而是xxl-job 主動(dòng)干擾打斷上一個(gè)任務(wù)；

2、為啥總是在 queryTopOneOfflineByTime 這個(gè)查詢報(bào)錯(cuò)呢？仔細(xì)看時(shí)間消耗，每次 queryTopOneOfflineByTime 時(shí)間都要增加 1s，明顯此查詢很有壓力！ sql 查詢慢，導(dǎo)致每次interrupt 都卡在這個(gè)查詢上。

總結(jié)上述兩點(diǎn)，立即拿出解決方案：

sql 查詢，發(fā)現(xiàn)數(shù)據(jù)庫(kù)表除了主鍵索引，沒(méi)有有任務(wù)索引，于是根據(jù) queryTopOneOfflineByTime 查詢字段，相應(yīng)增加 devId 索引；

暫時(shí)先調(diào)整定時(shí)任務(wù)，改為 30 分鐘跑一次；

經(jīng)過(guò)調(diào)整，任務(wù)不再報(bào)錯(cuò)，同時(shí)任務(wù)執(zhí)行周期減少一半時(shí)間；

請(qǐng)登錄后查看

常見問(wèn)題

CRMEB-慕白寒窗雪最后編輯于2023-03-21 18:40:56

快捷回復(fù)