java应用高并发频繁报Unable to acquire JDBC Connection

[小米吃辣椒2022]

问题描述：
1000台农机同时上报数据，每个农机2秒上报一次，上报的数据包括 时间戳，经纬度，速度，高度，航向等30个字段，上报到mqtt服务器，后台服务连接mqtt服务器通过mqtt协议 接收到 这些数据后需要进行 业务逻辑，处理的业务逻辑比较多，涉及到数据库的增删改查自然就比较多。

postgresql数据库配置的max_connection = 10000

spring boot 项目中数据源的配置如下：(最大连接数配置为2000，在处理业务逻辑过程实时最大连接数最大只达到1100多)
datasource:
#url: jdbc:postgresql://xxx.xxx.xx.xxx:5432/agri_devframe?characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false'type='javax.sql.DataSource
type: com.alibaba.druid.pool.DruidDataSource
username: postgres
password: postgres
driver-class-name: org.postgresql.Driver
#driver-class-name: com.mysql.jdbc.Driver
druid:
initial-size: 100
min-idle: 10
max-active: 2000
max-wait: 60000
remove-abandoned: true
remove-abandoned-timeout: 180
#log-abandoned: true
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1
test-while-idle: true
test-on-borrow: true
test-on-return: false
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
filters: stat,wall,log4j,config
stat-view-servlet:
login-username: admin
login-password: admin

但是 发现 处理业务逻辑过程中 频繁报出 以下错误，求助有没有大佬知道怎么解决呢？

2019-02-21 21:31:37 [MQTT Call: E9633-BBSER2471550755544:214769] - [ERROR] [Log:93] [error] com.devframe.common.util.mqtt.TimingPublish.analysisOverlay(445)javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:176)
at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3469)
at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3409)
at sun.reflect.GeneratedMethodAccessor209.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350)
at com.sun.proxy.$Proxy166.find(Unknown Source)
at com.devframe.database.BaseServiceImpl.getById(BaseServiceImpl.java:272)
at com.devframe.database.BaseServiceImpl $$FastClassBySpringCGLIB$$ 82a931da.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684)
at com.devframe.service.impl.TaskServiceImpl $$EnhancerBySpringCGLIB$$ 8d9900b8.getById(<generated>)
at com.devframe.common.util.mqtt.TimingPublish.analysisOverlay(TimingPublish.java:378)
at com.devframe.common.util.mqtt.TimingPublish.run(TimingPublish.java:1074)
at com.devframe.service.impl.MqttServerImpl.callBackChoose(MqttServerImpl.java:223)
at com.devframe.common.util.mqtt.ServerMQTT.messageArrived(ServerMQTT.java:272)
at org.eclipse.paho.client.mqttv3.internal.CommsCallback.deliverMessage(CommsCallback.java:499)
at org.eclipse.paho.client.mqttv3.internal.CommsCallback.handleMessage(CommsCallback.java:402)
at org.eclipse.paho.client.mqttv3.internal.CommsCallback.run(CommsCallback.java:206)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:109)
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:136)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:47)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:145)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:171)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:147)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.prepareQueryStatement(AbstractLoadPlanBasedLoader.java:226)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:190)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:121)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86)
at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:167)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4121)
at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:508)
at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:478)
at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:219)
at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:278)
at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:121)
at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:89)
at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1240)
at org.hibernate.internal.SessionImpl.access$1900(SessionImpl.java:204)
at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.doLoad(SessionImpl.java:2842)
at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2816)
at org.hibernate.internal.SessionImpl.find(SessionImpl.java:3445)
... 25 more
Caused by: java.sql.SQLException: interrupt
at com.alibaba.druid.pool.DruidDataSource.getConnectionInternal(DruidDataSource.java:1412)
at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:1255)
at com.alibaba.druid.filter.FilterChainImpl.dataSource_conne

[tianfang]

应用层的问题： 连接没有释放 也可能是数据库层发生了死锁 建议：再做一个二级消息队列，使同一农机只在一个线程处理，避免并发

[SHIHKEI]

楼主请问这个最后怎么解决的，我目前遇到也是 并发多 的时候就报这个错

[2021美丽的梦]

收集数据，统一 处理。频繁连接数据库肯定不好