I am working on a spring boot application, which is processing more than 1k json messages in parallel and updating the database simultaneously with the respective fields. Max 20 threads are running in parallel for this process. It's running fine with less than 800 json messages.
For this bulk message scenario, I'm getting below error, whenever there is an DB update:
ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Connection not established
2022-04-04 16:35:22.647 [HikariPool-1 connection closer] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Closing connection com.informix.jdbc.IfxSqliConnect@43a6bd0c: (connection is dead)
ERROR o.s.o.jpa.EntityManagerFactoryUtils - Failed to release JPA EntityManager
org.hibernate.exception.GenericJDBCException: Unable to release JDBC Connection
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.releaseConnection(LogicalConnectionManagedImpl.java:223)
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.close(LogicalConnectionManagedImpl.java:261)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.close(JdbcCoordinatorImpl.java:175)
at org.hibernate.internal.AbstractSharedSessionContract.close(AbstractSharedSessionContract.java:351)
at org.hibernate.internal.SessionImpl.closeWithoutOpenChecks(SessionImpl.java:376)
at org.hibernate.internal.SessionImpl.close(SessionImpl.java:361)
at org.springframework.orm.jpa.EntityManagerFactoryUtils.closeEntityManager(EntityManagerFactoryUtils.java:427)
at org.springframework.orm.jpa.JpaTransactionManager.doCleanupAfterCompletion(JpaTransactionManager.java:650)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.cleanupAfterCompletion(AbstractPlatformTransactionManager.java:1004)
Caused by: java.sql.SQLException: Connection not established
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
at com.informix.jdbc.IfxSqliConnect.rollback(IfxSqliConnect.java:2324)
at com.zaxxer.hikari.pool.ProxyConnection.close(ProxyConnection.java:257)
at java.base/jdk.internal.reflect.GeneratedMethodAccessor230.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at net.bull.javamelody.JdbcWrapper$ConnectionInvocationHandler.invoke(JdbcWrapper.java:202)
at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:300)
at com.sun.proxy.$Proxy138.close(Unknown Source)
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.closeConnection(DatasourceConnectionProviderImpl.java:127)
Data source properties configured:
spring:
datasource:
type: org.apache.tomcat.jdbc.pool.DataSource
tomcat:
init-SQL: select col1 from table ( set {1} ) as x( col1 )
default-transaction-isolation: 1
initial-size: 10
max-wait: 60000
max-active: 200
max-idle: 200
min-idle: 100
test-while-idle: true
test-on-connect: true
test-on-borrow: true
test-on-return: true
remove-abandoned : true
remove-abandoned-timeout: 300
time-between-eviction-runs-millis: 5000
min-evictable-idle-time-millis: 60000
validation-interval: 30000
validation-query: select col1 from table ( set {1} ) as x( col1 )
validation-query-timeout: 10
jpa:
open-in-view: false
database-platform: org.hibernate.dialect.InformixDialect
show-sql: false
properties.hibernate.format_sql: true
Can anyone please suggest/advice ?
Since you are using Hikari for connection pooling I think you also need to manage properties of hikari
Kindly try to configure using these props: https://docs.spring.io/spring-boot/docs/current/reference/html/application-properties.html#application-properties.data.spring.datasource.hikari